姚文連

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

計(jì)算自行車專賣店的銷售總額

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

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

計(jì)算銷售人員的月度獎金

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

計(jì)算導(dǎo)師的輔導(dǎo)成果

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

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