Excel函數的魅力

06-21

Excel函數的魅力

朱猛

筆者曾在一本電腦雜誌上看到這樣一道有關編程的比賽題，即已知一個有序數組a，其中有n個元素（整數），a中相同的元素全部集中在一起，形成一個個「平台」。要求設計一個演算法，求出a中最大平台的長度。例如，a中元素依次為：2 2 2 2 3 3 3 3 3 1 1 1 1 1 1 1 1 1 4 4，則它的最大平台的長度為9（即元素「1」的個數）。編程語言不限。

　　筆者經常使用Excel，深感其內置函數的功能十分豐富，因此試著用Excel函數解決了這個問題，覺得非常簡單可行，根本無須編程。當然，這可能違背了賽題的初衷，但筆者介紹此法的目的僅在於能引起讀者對Excel的興趣，以便充分利用Excel函數，領略其高效、便捷之處。

　　以上面比賽題為例，具體方法為：

　　如圖1所示，在A列中依次輸入數組a的20個元素，即2 2 2 …… 1 4 4，然後在C1單元格中輸入數組公式{=MAX(COUNTIF(A1:A20,A1:A20))}，即可求出最大平台的長度為9，非常簡單。

　　幾點說明：

　　1.數組a不要求一定是有序的，打亂輸入各元素一樣能得到正確的結果。

　　2.本例中，COUNTIF函數用於返回一個數值數組。它通常採用2個參數：一個區域（range）和一個判斷準則（criteria），其中判斷準則是一個簡單測試，COUNTIF函數依給定準則計算區域中非空白且符合準則單元格的數目。本例巧用區域本身作為判斷準則，依次統計每個元素在區域中的出現次數，因而函數必須按數組方式輸入。輸入數組公式時，不要自己鍵入花括弧「{}」，而應該在輸入公式後按「Ctrl＋Shift＋Enter」組合鍵鎖定數組公式，Excel將在公式兩邊自動加上花括弧。

　　3.為通用起見，可將公式中的區域設置大一點，如A1:A100或者更大，從而避免數組a中元素個數n不同時修改公式。

　　4.本例還可用來判斷一個數組中元素是否唯一，只須將公式改為：{=MAX(COUNTIF(A1:An,A1:An))=1}，其中n為大於等於數組元素個數的數值。若此公式返回TRUE，說明數組中元素是唯一的，返回FALSE則說明數組中元素不唯一。

　　下面筆者再舉幾個巧用Excel函數的例子，以求拋磚引玉。

　　【例1】巧用VLOOKUP函數實現「自動更正」功能

　　Word用戶都知道，利用Word的「自動更正」功能可以實現數據的快速輸入，即只需鍵入代碼就可以快速輸入諸如單位名稱、通信地址等數據。但在Excel中卻沒有類似「自動更正」功能的菜單命令。其實，使用VLOOKUP函數可以巧妙地解決這一問題。舉例如下：

　　假如在sheet1中建有物品領用登記表，「領料單位」一列往往要輸入大量同一單位的名稱。為了簡化輸入，可先在B列前插入一新列，再在B1單元格內輸入「代碼」，如圖2所示。然後，在另一工作表sheet2中建立如圖3所示的代碼表，依次輸入各領料單位的名稱及相應的代碼(本例假設有30個單位)。要注意的是，代碼必須按升序排列。為直觀起見，可雙擊sheet2，將其改名為「代碼表」。最後，單擊sheet1，在C2單元格內輸入公式{=VLOOKUP(B2,代碼表！ A 2: B 31,2)}，並拖動C2單元格的填充句柄向下複製公式。以後，只要在B2、B3、b4、…單元格中輸入各領料單位的代碼（如1、2、3、…），C2、C3、C4、…單元格中即自動輸入了相應的領料單位名稱。

　　【例2】巧用INDIRECT函數

　　如何計算單元格中數值的每位數字的和？假設A1單元格的數值為51432，怎樣用函數來求這5位數字的和？通用的方法是使用數組公式：

　　{=SUM(1＊MID(A1,ROW(INDIRECT(「1:」＆LEN(A1))),1))}

　　其中，因為Excel不允許使用ROW(1:LEN(A1))這樣的公式，所以巧用函數INDIRECT(「1:」＆LEN(A1))來產生需要的自然數組，以便據此數組從A1中逐個截取數字進行求和。

　　【例3】巧用IS函數「俘獲」出錯值

　　Excel提供了三個專用的IS函數：ISERR、ISERROR和ISNA，它們測試一個參數或單元格的值，以判斷是否含有出錯值。巧用這些函數能有選擇地「俘獲」出錯值，避免它們進入到工作表中。例如，圖4中的工作表C列中若使用形如「=A1/B1」的公式時，C3單元格將返回錯誤值「＃DIV/0！」。避免這種情況的方法是使用以下公式：

　　{=IF(ISERROR(A1/B1),「」,A1/B1)}

　　【例4】利用FREQUENCY函數分析數據分布

　　利用FREQUENCY函數可以進行頻度分析。如圖5所示，D2:D51中為50名學生的成績，若想分析在60分、70分、80分、90分、100分以內各有多少人，可先在F2:F6中輸入相應的分數區間，然後選擇G2:G6區域，輸入數組公式：{=FREQUENCY(D2:D51,F2:F6)}，完成後G2:G6區域將顯示出成績的分布情況。

　　【例5】創建隨機測試數據　　有時，為了測試工作表，可能需要一些有代表性的隨機測試數據。以下方法供參考：

　　給定範圍的隨機數：=RANDBETWEEN(1,100)

　　某年內的隨機日期：=「1/1/98」＋INT(RAND()＊365)

　　隨機地區或名單：=CHOOSE(INT(RAND()＊6)＋1,「淮陰縣」，「漣水縣」，「淮安市」，「洪澤縣」，「盱眙縣」，「金湖縣」)

　　【例6】巧用函數計算單元格中的單詞數

　　Excel中可以利用Len函數方便地計算單元格中的字元串長度，但沒有提供計算單元格中的單詞數的函數。其實，可以使用公式計算出出現在字元串內部的空格（ASCII碼為32）數，加上1即得到字元串中的單詞數。例如，使用下述數組公式可以計算出單元格B2中的單詞數：

{=SUN(IF(MID(TRIM(B2),ROW( A 1:OFFSET( A 1,LEN(TRIM(B2))－1,0)),1)=CHAR(32),1,0))＋1} 　　說明：

　　1使用Trim函數消除B2單元格字元串中多餘的空格字元，包括前導空格、後隨空格以及中間多餘的空格（只在單詞之間保留一個空格符）。

　　2Offset函數返回從基 A 2單元格開始向下的單元格區域引用，其單元格數目為B2單元格字元串長度減1。

　　3Row函數引用了單元格 A 1產生一個以1開始和以B2單元格字元長度結尾的連續遞增的整型數組該數組作為Mid函數的第二個參數。

　　4使用Mid函數從B2單元格字元串中逐個截取字元，判斷是否為空格，並進行累計。最後，將此累計數加1即得單詞數。

（本文來自CPCW網站）