1 SUMPRODUCT基础
1.1 概述
SUMPRODUCT通常用于2个或多个数组中对应的每个元素相乘并求和。例如,下图所示的表
使用=SUMPRODUCT(A1:A3,B1:B3),结果为32。具体的计算为:1×4+2×5+3×6。
可以扩充到多个数组。注意,虽然这个函数是相乘和求和,但是没有乘号或加号。然而,要有逗号,用来分开要相乘的数组。
1.2 SUMPRODUCT 函数作为COUNTIF函数使用
假设在A2:A20中有一些数据,下面通过一个简单的条件测试开始: =IF(A2=”test”,1,0)
在单元格B2中输入上述公式,向下复制到B20,然后简单地合计B2:B20,这将统计在A2:A20中有多少项等于“test”。当然,能够使用一个简单的公式COUNTIF(A2:A20,”test”)。这里,不详细讨论这个公式,重点关注下面使用的更聪明的方法。
如果在单元格B2中输入=A2:A20=”test”,会得到TRUE或FALSE,这取决于单元格A2中的值是否为“test”。
现在,使用SUMPRODUCT函数,如下 =SUMPRODUCT(A2:A20=”test”)
得到0,即使在单元格区域A2:A20里有一些值为“test”。难道SUMPRODUCT函数没有帮助吗?我们必须再做一些工作。
使用公式=SUMPRODUCT(A2:A20=”test”),我们试图汇总一组TRUE和FALSE的值。因此,我们需要强迫这组TRUE和FALSE值成为1和0,以便汇总为有意义的结果。为什么是1和0,而不是23或76呢?由于我们简单地试图统计A2:A20中值等于”test”的数量,因此结果为TRUE就在总计中加1,有效地统计相匹配的单元格数量。
有一种简单的方式来实现。Excel将TRUE当作1,FALSE当作0。如果我们在这些值中执行某种算术运算,那么可以强迫使一组TRUE/FALSE转换为1/0。一种方式是将结果数组乘1,如下: =SUMPRODUCT((A2:A20=”test”)*1)
有效地将每次结果乘1,得到1或0,并在结果数组中存储这些值。
另一种方式是加0: =SUMPRODUCT((A2:A20=”test”)+0)
对每次结果加0,同样得到1或0,并在结果数组中存储这些值。
有许多其它方式,其中一种是在前面添加两个减号: =SUMPRODUCT(–(A2:A20=”test”))
第一个减号否定每次的结果,得到值为-1和0的数组;第二个减号再次否定,得到值为1和0的数组。即第一个减号强迫使TRUE和FALSE为数字,第二个切换-1为1,因此不会弄乱结果。
1.3 小结
本文中我们学习了两点。第一,可以使用SUMPRODUCT包括条件测试,返回能够进一步处理的数组值。第二,可以通过使用算术运算强迫条件成为能够合计的数值,例如双减号、乘1或加0。这是所有SUMPRODUCT解决方案的基本原理。
至此,我们仅仅介绍了如何统计数组里与所给条件匹配的项目,这只是强大的SUMPRODUCT函数的用途之一。
|