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同样适用。