SUMPRODUCT函数在实际工作是一个使用频率很高的函数,你会用吗?本期技巧妹与大家详细分享相关的用法。
一、SUMPRODUCT函数的基础知识
SUMPRODUCT函数是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。其语法如下:
=SUMPRODUCT(array1,[array2], [array3], ...
Array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。
Array2, array3,...:可选。2 到 255 个数组参数,其相应元素需要进行相乘并求和。
SUMPRODUCT函数注意事项:
1.在使用SUMPRODUCT函数时,数组参数必须具有相同的维数。
2.区域或数组中非数值元素将会当0处理
二、SUMPRODUCT实际应用
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单元格的引用是锁定列。
教程推荐
领取专属 10元无门槛券
私享最新 技术干货