Excel數組公式之新手上路

姚文連

經常使用Excel進行數據統計分析的朋友，想必都使用過各種各樣的公式，如“=C4*D4”、“=SUM(E4:E19)”等等,本文將向大家介紹一種平時不太常用，有時又特別管用的Excel數組公式，它是可以對數組的一項或多項同時執行多個計算的公式，并返回一個或多個結果。這里，我們可以將數組理解為一組連續數據項的集合，如“C4:C19”、“D4:D19”等等。

計算自行車專賣店的銷售總額

首先通過一個應用實例來說明數組公式的基本用法。已知某電動自行車專賣店去年7月銷售統計表（如圖1），現在要計算7月份的銷售總額。這對于使用過Excel進行數據統計的朋友，肯定是易如反掌：先在E4單元格中輸入公式“=C4*D4”，然后按回車鍵得到計算結果4400。接下來向下拖動復制該公式到E5：E19單元格，最后在E20單元格中執行自動求和命令（∑）或插入求和函數SUM，得到銷售總額的計算結果89250。

如果用數組公式來計算銷售總額，則可以不創建E列數據，直接在D20單元格中輸入數組公式“=SUM(B4:B19*C4:C19)”，然后按下“Ctrl+Shift+Enter”組合鍵，這時Excel 會自動使用一對大括號 {}將該公式括起，并依次計算B4*C4、B5*C5、B6*C6、…、B19*C19的乘積（亦即數組B4:B19與C4:C19相乘），再使用 SUM 函數將這些乘積累加，最后算出的銷售總額同樣等于89250。如果你手動輸入大括號{}，數組公式將會轉換為文本字符串，并且不再起任何計算作用。

計算銷售人員的月度獎金

接下來，再通過另外一個計算銷售獎金的問題進一步說明數組公式使用方法（如圖2）。已知銷售統計表，要計算各位銷售人員的月銷售額，并取其5%作為銷售人員的月度獎金。具體操作方法如下：先選中銷售人員數據列，執行“數據/刪除重復項”命令，篩選出所有不同的銷售人員，并進行基本的格式設置。然后在J4單元格中輸入數組公式“= SUM(IF(B4:B19=I4,C4:C19*D4:D19))”，該公式使用了SUM和IF函數的嵌套，具體含義如下：如果數組B4:B19中的數據項等于I4，就將其對應的行的J列和K列數據相乘，然后對它們求和。為了能將該公式正確地向下復制到J5:J9單元格，還需要把三個數組B4:B19、C4:C19和D4:D19設置成絕對定位（在行列標號前面加$符號），即“=SUM(IF($B$4:$B$19=I4,$C$4:$C$19*$D$4:$D$19))”，然后按“Ctrl+Shift+Enter”組合鍵，得到張成杰的銷售額為15500，然后再將該公式向下拖動復制，就可以很快完成其他銷售人員的月銷售額的計算。當然，你可以先嘗試使用分類匯總命令來計算，然后再與使用數組公式的計算過程進行比較，以加深對數組公式使用方法的理解。

計算導師的輔導成果

筆者曾經碰到過一個計算導師跟蹤獎的問題（如圖3）。已知各位教師所跟蹤學生的班級和學號（這些數據由教務處隨機生成），學生的期中、期末考試成績分別在工作表qz和qm當中，S列為年級名次。這里要在工作表qz和qm當中，分別查找對應學生的期中和期末考試的年級名次，并以兩者之差作為評價教師跟蹤輔導效果的主要指標。通過數組公式可以使這個問題計算更簡單：在D2單元格中輸入數組公式“=SUM(IF((qz!$A$2:$A$1766=B2)*(qz!$B$2:$B$1766=C2),qz!$S$2:$S$1766))”，具體含義如下：當數組A2:A1766中的某一項等于B2，并且數組B2:B1766中的某一項等于C2時，取其所在數據行中S列的“年名”，因為IF函數只能判斷而不能得到數據項，并且符合條件的數據項有且僅有一個，因此這里借用了SUM函數的求和功能，在數組S2:S1766中查找符合前面兩個條件的數據項。其中，“*”為多個條件的交集符號，而多個條件的并集符號則為“+”。

與一般公式相比，數組公式也必須以“=”開始，還可以在數組公式中插入任何Excel內置函數，最主要的不同是輸入或修改數組公式后必須按 “Ctrl+Shift+Enter”，否則會出現“#VALUE！”等錯誤提示，也正因如此，數組公式有時也被稱為“CSE 公式”。以上操作在Excel 2007完成，對Excel 2003同樣適用。