SUMPRODUCT函数的3个经典案例

发布时间:2020-01-07 来源:网络


SUMPRODUCT函数向来被称之为万能函数,可以进行各种条件计数和条件求和。今天技巧妹要分享的是SUMPRODUCT函数的3个比较典型但是你不一定熟悉的应用。


1、模糊条件求和

SUMPRODUCT函数经常用来根据指定条件进行精确查找,其实也可以进行模糊查找。如下图表格所示,如何统计城区小学所有男学生的成绩之和?



解决:这里城区小学包括城区一小、城区二小和城区三小,可以进行模糊条件查找。因为SUMPRODUCT函数函数是不支持通配符*或者?的,所以我们在进行模糊条件查找时,需要结合其它函数来实现。输入公式

=SUMPRODUCT(ISNUMBER(FIND("城区",C2:C13))*(B2:B13="男"),

D2:D13)



说明:先用FIND函数在C2:C13这个区域中查找“城区”这个字符串,若存在返回相应位置,不存在则返回#VALUE!错误值;ISNUMBER函数是用来检测是否为数值,是的话返回TRUE,否则返回FALSE;最后用SUMPRODUCT函数进行多条件求和。


2、按季度求和


如下图表格所示,我们如何求出各个季度的成交总数?



解决:选中统计表中的空白单元格区域,在E2单元格中输入公式

=SUMPRODUCT(N(CEILING(MONTH($A$2:$A$15)/3,1)=D2),$B$2:$B$15),

按Ctrl+Enter组合键完成所有公式填充。



说明:先利用MONTH函数求出日期所在的季度,再结合CEILING函数向上舍入为最接近的指定基数的倍数,从而判断出对应的季度,然后用N函数把逻辑值转化为数值,最后利用SUMPRODUCT函数是将数组间对应的元素相乘,并返回乘积之和。


3、二维区域条件求和


在工作中,我们经常需要根据一维表数据源,在二维表里进行分类统计。如下图表格所示,如何统计各门店各类商品的销量?



解决:选中二维表中的空白单元格区域,在F2单元格中输入公式

=SUMPRODUCT(($A$2:$A$25=F$1)*($B$2:$B$25=$E2),$C$2:$C$25),

按Ctrl+Enter组合键完成所有公式填充。



说明:这里用到的实际上是SUMPRODUCT函数的多条件求和,公式中第一个条件是指定的门店,注意F1单元格的引用是锁定行,第二个条件指定的商品,注意E2单元格的引用是锁定列。