2.3.6　计算全能王——SUMPRODUCT

我们前面已学过SUMIF和COUNTIF函数，一个是条件求和，另一个是条件计数，实现的是不同的功能。下面介绍的一个函数能通过灵活设置参数，高度统一SUMIF和COUNTIF的功能，甚至是SUMIFS、COUNTIFS的功能。它就是“计算全能王”SUMPRODUCT。

从图2-64函数的图解可以看出，SUMPRODUCT函数先进行乘积运算，再进行求和运算。具体的功能是在给定的几组数组中将数组间对应的元素相乘，并返回乘积之和。

SUMPRODUCT函数的语法形式为：SUMPRODUCT(array1, [array2], [array3], ...)

• array1：必需，是需要进行相乘并求和的第一个数据组。

• array2, array3,……：可选，第2～255个数据组。

举一个SUMPRODUCT最基本的应用案例。如图2-65示，需要求出所购买商品的总费用，总费用等于商品的单价乘以数量，然后求和，在F2单元格中输入公式=SUMPRODUCT(C2:C12,D2:D12)，即可求出两个区域的乘积之和。

1. SUMPRODUCT函数万能公式

在学习SUMPRODUCT函数万能公式之前，我们先回顾一个知识点，IF函数判断的结果为逻辑值，逻辑值分为TRUE和FALSE，分别用1和0表示。知道这个原理后，如果我们能够构造一组1和0的不同组合，其实就是在模拟IF函数的判定结果。下面根据这个原理进行SUMPRODUCT函数的拓展应用。

案例1：充当SUMIF进行条件求和。

我们在图2-65的数据表的基础上设置一个条件：如果单价大于3元，标记为1；小于3元，则标记为0。这其实是模拟IF进行判断，只不过需要把每一个判断结果用0或1量化表示，那么数据表变成如图2-66所示的形式。

此时，再使用SUMPRODUCT对图2-66中的D列和E列数据进行乘积后求和，它代表的是什么含义呢？试想：大于3元的商品，数量乘以1之后参与到求和运算，而小于3元的商品，数量乘以0之后变成0，不参与求和运算。因此，函数=SUMPRODUCT(D2:D12,E2: E12)的结果就是单价大于3元的商品的数量之和，这不就是条件求和吗？

在上面的分析过程中，我们添加了一列辅助列自行判断价格是否大于3元，并将结果填入D列。事实上，在SUMPRODUCT函数中可以使用条件自行判断，如图2-67所示，在G2单元格中输入公式=SUMPRODUCT((C2:C12>3)*E2:E12)。

使用“F9”功能键快速查看 (C2:C12>3)的计算结果是什么。

选中公式中需要查看结果的运算部分=SUMPRODUCT((C2:C12>3)*E2:E12)，按“F9”键得出计算结果：=SUMPRODUCT({1;1;0;0;1;1;1;0;0;0;1}*E2:E12)，发现判断结果和我们手工判断的结果一样。

因此，=SUMPRODUCT（条件*数值区域）可以替代SUMIF函数进行条件求和。

在此基础上更进一步，如果要求单价大于3元，且数量大于5个的文具数量之和，应该如何写公式呢？问题的难度进一步提升，不仅需要对单价进行判断，还需要对数量进行判断，这属于多条件求和，功能相当于SUMIFS。但是不管有几个条件，使用SUMPRODUCT计算的思路是一样的，公式如下：

=SUMPRODUCT((C2:C12>3)*(E2:E12>5)*E2:E12)。

案例2：充当COUNTIF进行条件计数。

在介绍COUNTIF函数时就说过，计数属于求和的特例：计数就是因子都为1的求和。可见，求和函数可以用来计数，本案例需要求单价大于3元的商品的种类数。

这就将原来的求和问题转化成了计数问题，所以应该代替COUNTIF函数的功能进行计数，如图2-68所示，于是可以这样写公式：=SUMPRODUCT((C2:C12>3)*1)。

公式=SUMPRODUCT((C2:C12>3)*1)中要乘以1，是因为SUMPRODUCT函数只能识别数值，数值以外的都被当作0处理。而C2:C12>3得出的结果是逻辑值，不能被SUMPRODUCT函数直接识别。通常情况下，乘以1的计算既可以将逻辑值变为数值，又不改变数值的大小。

在此基础上更进一步，如果要求单价大于3元，且数量大于5个文具的种类数，应该如何写公式？问题的难度进一步提升，这属于多条件计数范畴，功能相当于COUNTIFS。同样，公式应该这样写=SUMPRODUCT((C2:C12>3)* (E2:E12>5))，C2:C12>3用于判断单价，E2:E12>5用于判断数量。

通过案例1和案例2，我们可以总结出SUMPRODUCT函数的万能公式。

SUMPRODUCT条件求和公式为：

=SUMPRODUCT（（条件1）*（条件2）*……*求和区域）

例如：=SUMPRODUCT((C2:C12>3)*(D2:D12>5)*D2:D12)，SUMPRODUCT条件求和公式可以实现单一条件求和、多条件求和。

SUMPRODUCT条件计数公式为：

=SUMPRODUCT（（条件1）*（条件2）*……*（条件N））

例如：=SUMPRODUCT((C2:C12>3)*(D2:D12>5))，SUMPRODUCT条件计数公式可以实现单一条件计数和多条件计数。

SUMPRODUCT函数在求和与计数方面达到了高度统一，在某种层面上，可以替代SUMIF、COUNTIF、SUMIFS、COUNTIFS函数，而这些功能的实现仅仅是通过函数的参数灵活配置实现的，并不需要高深的函数嵌套、相对引用等复杂的知识。

2. SUMPRODUCT中国式排名公式

中国式排名的特点是，如果遇到并列的情况，接下来的排名会延续上一个排名，比如第二名并列，有两个第二名，下一个名次依然是第三名。为对比理解，普通排名方式是并列之后，下一个排名会跳过一个名次，比如第二名有两个并列，下一个名次就是第四名。

如图2-69所示，为了得到学生成绩的“中国式排名”，在C2单元格输入公式=SUMPRODUCT((B2<=B$2:B$6)/COUNTIF(B$2:B$6,B$2:B$6))，然后向下复制填充函数，即可得出数值的中国式排名。

下面分析SUMPRODUCT和COUNTIF嵌套的公式=SUMPRODUCT ((B2<=B$2:B$6)/COUNTIF (B$2:B$6,B$2:B$6))。

①关键部分COUNTIF(B$2:B$6,B$2:B$6)。

我们知道，CUNTIF是条件计数，如果第二个参数是一个数组区域，那么将返回一个数组结果。这里COUNTIF (B$2:B$6,B$2:B$6)的意思就是分别以第二个参数B$2:B$6区域中的5个单元格为条件，查找在第一个参数B$2:B$6区域中等于B$2或等于B$3……或等于B$6的元素数。因此返回结果是{1;2;2;1;1}，也就是区域中每个元素的重复次数。

如果使用“F9”功能键，将公式中的COUNTIF(B$2:B$6,B$2:B$6)涂黑验证，会出现这样的结果=SUMPRODUCT((B2<=B$2:B$6)/{1;2;2;1;1})，和我们的分析是一致的。

那么，将函数求得的数组结果作为分母，被1除，即1/COUNTIF(B$2:B$6,B$2:B$6)的计算结果就是={1,0.5,0.5,1,1}，我们把这部分单独放在了一列，如图2-69中D列所示。

②外层的SUMPRODUCT函数。

先不管(B2<=B$2:B$6)这个条件，直接看“=SUMPRODUCT(1/{1;2;2;1;1})”，即=SUMPRODUCT(1/COUNTIF(B$2:B$6,B$2:B$6))，这个公式的意思就是对1/{1;2;2;1;1}进行乘积后求和，因为只有一列数据，所以相当于直接求和，你会发现：总和=“区域中不重复元素的个数”。

其实原理很简单：比如98重复了两次，那么图2-69中D列对应的位置计算出的结果都是0.5，两个0.5相加等于1，相当于只被计算了一次。

依此类推，如果某个数据重复了N次，那么它对应的COUNTIF()结果为n，而1/COUNTIF()=1/n，因为一共有n个元素（因为重复了N次），因此它们的个数总和=n*(1/n)=1，所以不管数据重复了多少次，都被算作一次。因此，=SUMPRODUCT(1/COUNTIF(B$2:B$6,B$2:B$6))其实就是计算B$2:B$6区域中不重复元素的个数。

最后，根据SUMPRODUCT函数的万能公式可知=SUMPRODUCT((B2<=B$2:B$6)/COUNTIF(B$2:B$6,B$2:B$6))。

最终的含义是：以符合(B2<=B$2:B$6)为条件，统计区域中不重复元素的个数，这个结果就是中国式排名。