姚文連

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

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

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

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

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

接下來(lái)，再通過(guò)另外一個(gè)計(jì)算銷售獎(jiǎng)金的問(wèn)題進(jìn)一步說(shuō)明數(shù)組公式使用方法（如圖2）。已知銷售統(tǒng)計(jì)表，要計(jì)算各位銷售人員的月銷售額，并取其5%作為銷售人員的月度獎(jiǎng)金。具體操作方法如下：先選中銷售人員數(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，就將其對(duì)應(yīng)的行的J列和K列數(shù)據(jù)相乘，然后對(duì)它們求和。為了能將該公式正確地向下復(fù)制到J5:J9單元格，還需要把三個(gè)數(shù)組B4:B19、C4:C19和D4:D19設(shè)置成絕對(duì)定位（在行列標(biāo)號(hào)前面加$符號(hào)），即“=SUM(IF($B$4:$B$19=I4,$C$4:$C$19*$D$4:$D$19))”，然后按“Ctrl+Shift+Enter”組合鍵，得到張成杰的銷售額為15500，然后再將該公式向下拖動(dòng)復(fù)制，就可以很快完成其他銷售人員的月銷售額的計(jì)算。當(dāng)然，你可以先嘗試使用分類匯總命令來(lái)計(jì)算，然后再與使用數(shù)組公式的計(jì)算過(guò)程進(jìn)行比較，以加深對(duì)數(shù)組公式使用方法的理解。

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

筆者曾經(jīng)碰到過(guò)一個(gè)計(jì)算導(dǎo)師跟蹤獎(jiǎng)的問(wèn)題（如圖3）。已知各位教師所跟蹤學(xué)生的班級(jí)和學(xué)號(hào)（這些數(shù)據(jù)由教務(wù)處隨機(jī)生成），學(xué)生的期中、期末考試成績(jī)分別在工作表qz和qm當(dāng)中，S列為年級(jí)名次。這里要在工作表qz和qm當(dāng)中，分別查找對(duì)應(yīng)學(xué)生的期中和期末考試的年級(jí)名次，并以兩者之差作為評(píng)價(jià)教師跟蹤輔導(dǎo)效果的主要指標(biāo)。通過(guò)數(shù)組公式可以使這個(gè)問(wèn)題計(jì)算更簡(jiǎn)單：在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í)，取其所在數(shù)據(jù)行中S列的“年名”，因?yàn)镮F函數(shù)只能判斷而不能得到數(shù)據(jù)項(xiàng)，并且符合條件的數(shù)據(jù)項(xiàng)有且僅有一個(gè)，因此這里借用了SUM函數(shù)的求和功能，在數(shù)組S2:S1766中查找符合前面兩個(gè)條件的數(shù)據(jù)項(xiàng)。其中，“*”為多個(gè)條件的交集符號(hào)，而多個(gè)條件的并集符號(hào)則為“+”。

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