1.2.3 SUMPRODUCT()函数

关于SUMPRODUCT ()函数，Excel的帮助给它的功能定义是：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

初看起来，这个函数似乎只是进行数组计算，对乘积求和。但用过的人都知道，该函数在多条件求和方面功能超强。如用于学生成绩处理，统计该班各科男生、女生参加考试的实际人数。这里假设区域D42:D48存储有性别、H42代表性别“男”或“女”、区域E42:E48存储有学生成绩。我们可以根据前面学过的SUM()函数知识，列出数组公式：

      {=SUM((D42:D48=H42)*(E42:E48>0))}

也能解决这个问题。但对初学者而言，一是对数组不熟悉，二是不知有“Ctrl+Shift+Enter”组合键来结束数组的编辑，如果要实现上述的功能，可以用公式=SUMPRODUCT((D42:D48=H42)*(E42:E48>0))来代替上述的数组公式，计算结果仍然是正确的。因为该函数支持数组间运算(相同尺寸的数组相乘后再加总)，而且不需要按“Ctrl+Shift+Enter”组合键来结束输入，直接按Enter键即可。

请读者打开“配套光盘\第1章\xls\数学函数.xls”文件，单击进入“SUMPRODUCT”工作表，本工作表中有关于SUMPRODUCT()函数的详细应用实例，结合本节内容一起操作一遍。

SUMPRODUCT()函数的功能与表达式如图1.38所示。

图1.38 SUMPRODUCT()函数的功能与表达式

SUMPRODUCT()参数引用

如图1.39所示，求数组B13:C15和D13:E15相乘后的和。

图1.39 两个数组相乘的公式

在C17单元格中输入公式：

      =SUMPRODUCT(B13:C15, D13:E15)

说明

两个数组的所有元素对应相乘，然后把乘积相加，即3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3，结果为156。

特别注意的一点就是，数组间相乘时，数组维数必须相同；否则，函数SUMPRODUCT将返回错误值#VALUE!。这里所讲的维数，指的是一行或一列，或由行列构成一个平面区域。

函数SUMPRODUCT将非数值型的数组元素作为0处理的应用情况，在调用含有文本的参数时，如果继续用“*”号，就会出错了，如将数组区域用“,”号分隔开，SUMPRODUCT将忽略字符，而对数字进行正确的计算。

示例一

重名求和。

如图1.40所示，在B列品名中有相同的，利用SUMPRODUCT()函数可进行查找相同的品名，并对数量统计求和。

图1.40 重名求和公式

在D33单元格中输入公式：

      =SUMPRODUCT((B28:B33=D31)*C28:C33)

按Enter键后，得出铅笔的统计数量为18。

对比该函数的表达式，发现参数是用“*”号分隔开的。如果将上述公式中参数间的“*”号换成“,”号，即：

      =SUMPRODUCT((B28:B33=D31),C28:C33)

结果为0。因为B28:B33区域中存放的是文本，被当做0处理，与数值区域C28:C33，所有结果都为0。

如果将上述公式改为：

      =SUMPRODUCT(--(B28:B33=D31),C28:C33)

即将文本区域用括号括起来，并将前面加上两个负号。这样做的目的，是将文本格式转换成数值格式，然后再与后面的数值数组相乘，结果为18。

一些Excel函数高手往往将文本格式转换成数值格式，就将文本格式字符进行+0、-0、*1处理，因为文本格式执行数学运算就能转换成数字格式，这样就达到转换的目的。上述的“--”表示的是减负操作，相当于0-(-文本格式)，根据负负得正的原理，文本格式就转换成数值格式了，这样操作起来方便、快捷。

示例二

多条件求和。

如图1.41所示，根据性别统计出语文成绩大于100的人数。

注意

该函数的参数用两种方式列出。

图1.41 多条件求和

在I42单元格中输入公式：

      =SUMPRODUCT((D42:D48=H42)*(E42:E48>100))

得出结果为2。

在I43单元格中输入公式：

      =SUMPRODUCT((D42:D48=H43)*1,(E42:E48>100)*1)

得出结果为2。读者注意，在这个公式中，数组参数是用“,”号隔开的，如果省略掉了数组区域后面的尾巴“*1”，计算出来的结果就为0。

在I44单元格中，输入SUM()函数的数组公式：

      {=SUM((D42:D48=H42)*(E42:E48>100))}

亦能求出正确的结果。

示例三

多列求和。

如图1.42所示，求需购买某一品名在商场A的付款总额。

图1.42 多列求和

在B63单元格建立品名选择系列。

在C63单元格输入公式：

      =SUMPRODUCT((B57:B62=B63)*(C57:C62)*(D57:D62)*(E57:E62))

求出“剥笔器”在商场A的采购总金额。

在C64单元格中输入公式：

      =SUMPRODUCT(--(B57:B62=B63),(C57:C62)*(D57:D62)*(E57:E62))

结果与C63单元格的值一样，但第一个数组采用了“--”处理，并能用“,”号隔开。

请读者结合示例一的解释仔细体会。

示例四

针对文本值求和。

如图1.43所示，在商场A中，没有采购钢笔，输入符号“--”代替。

图1.43 针对文本值求和

在C77单元格中输入公式：

      =SUMPRODUCT((B71:B76=B77)*(C71:C76)*(D71:D76)*(E71:E76))

因E74单元格中是文本，导致计算出现错误信息#VALUE。

在C78单元格中输入公式：

      =SUMPRODUCT((B71:B76=B77)*(C71:C76)*(D71:D76),(E71:E76))

与上述公式相比，只不过用“,”号将含有文本符号“--”的E71:E76数组区域隔开，计算结果正确。

由此可看出，一个小小的“,”就能解决此问题。