sumproduct函数公式大全讲解(sumproduct是什么意思)
SUMPRODUCT函数是Excel中的数学函数,也是一个“神函数”。之所以称之为“神”,是因为它求和、计数、多权重统计、排名,都能完成。
函数解释
基本语法为:
SUMPRODUCT(array1,[array2], [array3], …)
SUMPRODUCT函数语法具有下列参数:
Array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。
Array2, array3,…:可选。2到255个数组参数,其相应元素需要进行相乘并求和。
特别注意:
数组参数必须具有相同的维数。否则,函数SUMPRODUCT将返回#VALUE!错误值#REF!。
1、基本用法
SUMPRODUCT函数最基本的用法是:
数组间对应的元素相乘,并返回乘积之和。
如下图:
公式:=SUMPRODUCT(B2:B9,C2:C9)
该公式的含义是:
B2*C2 B3*C3 B4*C4 B5*C5 B6*C6 B7*C7 B8*C8 B9*C9
2、单条件求和
如下图,计算女员工业绩得分高于15的得分和:
如下图,计算女员工业绩得分和:
公式:
=SUMPRODUCT((B2:B11=’女’)*C2:C11)
其中:
B2:B11=’女’:
将B2:B11内每个单元格值与“女”比较,凡是性别为“女”的是TRUE,否则是FALSE,结果返回一组逻辑值:
{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;}
(B2:B11=’女’)*C2:C11:
将上述逻辑数组内的值与对应的C2:C11的数值相乘。
3、多条件求和
如下图,计算女员工业绩得分高于15的得分和:
公式:
=SUMPRODUCT((B2:B11=’女’)*(C2:C11>15),C2:C11)
多条件求和的通用写法是:
=SUMPRODUCT((条件一)*(条件二)*……*(条件N),求和范围)
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($A$2:$A$13)=D2)*($B$2:$B$13))
9、跨列统计
要求:
统计三个仓库的销售总量与库存总量
公式为:
=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)
(此公式中一定要注意相对引用于绝度引用的使用)
10、多权重统计
要求:
根据分项得分与权重比例计算总分
公式为:
=SUMPRODUCT(B$2:D$2,B3:D3)
11、二维区域统计
要求:
统计各销售部门各商品的销售总额
公式为:
=SUMPRODUCT(($B$2:$B$13=$E2)*($A$2:$A$13=F$1)*$C$2:$C$13)
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。
1.本资源价格绝非商品价格,而是收集、整理资源的成本,因个人电脑环境不同不保证所有人都能正常运行。
2.本站资源默认使用百度云下载,建议使用7Z、RAR软件解压,多分卷包需全部下载后解压,敏感资源可能需要修改后缀名或二次解压,若出现文件损坏请重新下载。
3.解压密码一般位于链接下方,如密码错误请尝试手动输入。请勿在线解压!请勿在线解压!请勿在线解压!
4.部分游戏不能含有中文路径,关闭杀毒软件等,更多游戏问题戳→这里
5.若出现爆链、失效等情况请联系客服反馈、补链。
VAM游戏网 » sumproduct函数公式大全讲解(sumproduct是什么意思)