SUMPRODUCT函数是Excel中的数学函数,也是一个“神函数”。之所以称之为“神”,是因为它求和、计数、多权重统计、排名,都能完成。
一、函数解释
基本语法为:
查过SUMPRODUCT()函数的使用方法,其解释为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。按我个人解释就是两个以上的数组乘积之和。
其语法格式为SUMPRODUCT(array1, [array2], [array3],…),其中每个array都表示一个数组,array个数不超过255个。
特别注意:
数组参数必须具有相同的维数。否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值#REF!。
二、用法举例
1、基本用法
SUMPRODUCT函数最基本的用法是:
数组间对应的元素相乘,并返回乘积之和。
当sumproduct函数中的参数只有一个数组时,即对数组{1;2;3;4;5;6;7}进行求和,1+2+3+4+5+6+7=28。
当sumproduct函数中参数为两个数组时,两个数组的所有元素对应相乘。
公式=sumproduct(A2:A8,B2:B8)可转化为
=sumproduct(数组1,数组2)
=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。
当sumproduct函数中参数为三个数组时,三个数组的所有元素对应相乘。
2、单条件求和
目的:计算“上海区”的销售总额。
方法:
在目标单元格中输入公式:=SUMPRODUCT((F3:F9="上海")*(C3:C9*D3:D9))。
备注:
a.对于单条件求和,本来用SUMIF函数就可以搞定。公式:=SUMIF(F3:F9,"上海",E3:E9)。
b.或者用多条件求和的SUMIFS也可以搞定:=SUMIFS(E3:E9,F3:F9,"上海")。
3、多条件求和
目的:求“上海区”“王东”的销售总额。
方法:
在目标单元格中输入公式:=SUMPRODUCT((B3:B9="王东")*(F3:F9="上海")*(C3:C9*D3:D9))。
备注:
对于多条件求和,本身是SUMIFS函数的功能。公式:=SUMIFS(E3:E9,B3:B9,"王东",F3:F9,"上海")。
4、模糊条件求和
如下图,计算销售部门女员工业绩得分和:
销售部门不止一个,要查找所有的销售部门,就要按照关键字“销售”查找,就属于模糊查找。
公式:
=SUMPRODUCT(ISNUMBER(FIND("销售",A2:A11))*(C2:C11="女"),D2:D11)
其中:
FIND("销售",A2:A11):
在A2:A11各单元格值中查找"销售",如果能查到,返回"销售"在单元格值中位置,如果差不到,返回错误值#VALUE!。
本部分的结果是:
{#VALUE!;1;1;1;#VALUE!;1;#VALUE!;#VALUE!;1;#VALUE! }
ISNUMBER(FIND("销售",A2:A11)):
判断上述数值中各值是不是数字,如果是,返回TRUE,否则返回FALSE,所以,本部分公式的结果是:
{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}
5、单条件计数
计算女员工人数:
公式:
=SUMPRODUCT(N(B2:B11="女"))
N函数:
语法:N(VALUE);
功能:将不是数值的值转换为数值形式;
不同参数VALUE,对应的返回值:
本示例中,N(B2:B11="女"),是将等于女的值TRUE返回1,不等于女的值FALSE返回0。
6、多条件计数
计算女员工业绩得分高于15的人数
公式:
=SUMPRODUCT((B2:B11="女")*(C2:C11>15))
7、模糊条件计数
计算销售部门女员工人数
公式:
=SUMPRODUCT(ISNUMBER(FIND("销售",A2:A11))*(C2:C11="女"))
8、按月份统计数据
公式思路
SUMPRODUCT 函数内可以用其他方法对一列数据进行转换;
根据要求,需要将日期转换为月,因此使用 MONTH 函数;
转换后的值与指定月比较,相等返回 TRUE,否则返回 FALSE;
使用双减(–)符号把逻辑值转换成 0、1;
利用 SUMPRODUCT 函数的特性,比较结果(0、1值)与求和列相乘,得到值 1 对应数据之和
通用公式
根据以上思路,写出按月汇总通用公式如下:
=SUMPRODUCT(--(MONTH(日期列)=月)*求和列)
本例中,具体的公式如下:
=SUMPRODUCT(--(MONTH(B3:B11)=B15)*C3:C11)
相关公式
根据上述思路,对公式稍加更改,使用 YEAR 函数和 DAY 函数,就可以写出按年汇总、按天汇总公式。
按年汇总通用公式如下:
=SUMPRODUCT(--(YEAR(日期列)=月)*求和列)
按天汇总通用公式如下:
=SUMPRODUCT(--(DAY(日期列)=月)*求和列)
9、跨列统计
要求:
统计三个仓库的销售总量与库存总量
公式为:
=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)
(此公式中一定要注意相对引用于绝度引用的使用)
10、多权重统计
要求:
根据分项得分与权重比例计算总分
如下图是某公司招聘应聘者的成绩,要求面试占总成绩的40%,笔试占60%现在想要快速计算出各应聘者总分。
在D2单元格输入公式=SUMPRODUCT(B3:C3,$B$1:$C$1)
将D2单元格公式下拉到底即可完成
11、二维区域统计
要求:
统计各销售部门各商品的销售总额
如下图左侧是某公司销售一维表,现在想要将它填充到右侧的二维表中。
在F2单元格输入公式=SUMPRODUCT(($A$2:$A$10=$E2)*($B$2:$B$10=F$1)*$C$2:$C$10)
将F2单元格公式填充整个表格即可完成
12、不间断排名
用RANK函数排名,如果有数值相同的情况,会出现名次间断现象,用SUMPRODUCT函数,很好的避免这种名次的间断。
如下图:
C6单元格公式为:
=SUMPRODUCT(($B$2:$B$7>=B6)/COUNTIF($B$2:$B$7,$B$2:$B$7))
($B$2:$B$7>=B6),返回值是:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}
即:{1;1;1;1;1;0}
COUNTIF($B$2:$B$7,$B$2:$B$7),返回值是:
{1;1;2;2;1;1}
SUMPRODUCT(($B$2:$B$7>=B6)/COUNTIF($B$2:$B$7,$B$2:$B$7))
即是:SUMPRODUCT({1;1;0.5;0.5;1;0}),即得名次4。
联系客服