启明办公

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 65|回复: 0

Excel的函数Sumproduct详解

[复制链接]

1

主题

2

帖子

3

积分

新手上路

Rank: 1

积分
3
发表于 2022-12-11 21:51:16 | 显示全部楼层 |阅读模式

大家好,我是永不止步的老牛
今天我们来了解下Excel的Sumproduct函数的实用用法


Sumproduct函数的语法是:
Sumproduct(array1,[array2],[array3],...),其中
array1:必需,表示表示一个数组
array2:可选,表示表示一个数组
array3,...:可选,表示表示一个数组
这里要注意一下

  • 参数是多个数组,但最多不能超过255个
  • 参数最少要有一个数组,就是说第一个参数是必需项
  • 多个参数时,数组必需具备相同的维度,否则Sumproduct函数返回错误
  • 会自动将非数值型数组元素按0来处理
就是说这个函数将多个数组对应元素相乘,并返回乘积之和,如果只有一个数组,那么返回这个数组各元素之和
用例子来解释一下
Sumproduct({1;2;3}):返回的是1+2+3=6
Sumproduct({1;2;3},{1;2;3}):返回的是1*1+2*2+3*3=1+4+9=14
Sumproduct({1;2;3},{1;2;3},{1;2;3}):返回的是1*1*1+2*2*2+3*3*3=1+8+27=36
我们用例子演示一下Sumproduct函数使用的使用场景
1. 基础用法

A. 一个参数



统计销售数量总数
=SUMPRODUCT(F4:F13)
表示计算F4:F13的各元素之和,就是1+12+1+8+8=5+5+8+1+2=53
B. 两个参数



统计销售额总数
=SUMPRODUCT(E4:E13,F4:F13)
表示计算E4:E13和F4:F13各元素乘积之和
就是4999*3+3599*12+2888*1……4999*1+3599*2=204593
C. 三个参数



统计提成总数
=SUMPRODUCT(E4:E13,F4:F13,G4:G13)
表示计算E4:E13、F4:F13和G4:G13各元素乘积之和
就是4999*3*0.05+3599*12*0.2+2888*1*0.05……4999*1*0.05+3599*2*0.05=2786.84
2. 带条件计算

A. 单条件求和



统计电视机的销售数量
=SUMPRODUCT((C4:C13="电视机")*F4:F13)
C4:C13="电视机":表示在C4:C13区域内逐项和“电视机”比较,等于返回TRUE,不等于返回FALSE,
用返回的数组{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}和F4:F13对应元素相乘,并返回乘积之和
就是0*3+0*12+1*1+0*8+0*8+0*5+1*5+1*8+0*1+0*2=14
SUMPRODUCT函数会充分用到TRUE等于1,FALSE等于0
B. 多条件求和



统计销售一部电视机的销售数量
=SUMPRODUCT((B4:B13="销售一部")*(C4:C13="电视机")*F4:F13)
多条件和单条件原理一样,只是多进行了一次比对,更多条件只需要增加对应比对项
C. 模糊条件求和



统计电脑和电视机的销售数量
=SUMPRODUCT(ISNUMBER(FIND("电",C4:C13))*F4:F13)
FIND函数:表示在C4:C13各单元格内中查找包含“电”的产品,找到就返回在单元格的位置,没找到就返回错误#VALUE!
FIND("电",C4:C13):返回的是{1;#VALUE!;1;#VALUE!;1;1;1;1;1;#VALUE!}
这里的#VALUE!导致我们没法计算,所以再用ISNUMBER函数转换一下
ISNUMBER函数:判断是不是数值,是就返回TRUE,不是就返回FALSE
ISNUMBER(FIND("电",C4:C13)):返回的是
{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}
现在就和单条件一样了,两个数组对应元素相乘,并返回乘积之和
D. 单条件计数



统计电视机的销售员人数
=SUMPRODUCT(N(C4:C13="电视机"))
C4:C13="电视机":返回{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
如果只有这一个数组,数组里面不是数字,而是TRUE或FALSE,
我们前面说过:SUMPRODUCT函数会自动将非数值型数组元素按0来处理
所以如果不加处理,结果将会返回0,计算不正确了
处理办法很多,包含TRUE和FALSE的数组,和运算符运算都会自动变成1和0
所以我们只要给(C4:C13="电视机")*1,就会变成{0,0,1,0,0,0,1,1,0,0}
那+1可以让TRUE和FALSE变成数值吗?可以,但是结果就不对了
(C4:C13="电视机")+1,就会变成{1,1,2,1,1,1,2,2,1,1}
还可以加“--”,变成“--(C4:C13="电视机")”,结果也是{0,0,1,0,0,0,1,1,0,0}
“--”:就是将不能参与计算的字符转换成可以计算的数字
还可以用N函数,
N函数:将不是数值形式的值转换为数值形式,TRUE转换成1,FALSE转换成0,日期转换成序列值,其他的值转换成0
N((C4:C13="电视机")):返回的是{0,0,1,0,0,0,1,1,0,0}
那么N函数和“--”的区别是,对SUMPRODUCT来说,区别在:
—(“1”):结果是1
N(“1”):结果是0,“1”是文本,N函数将文本全部转换成0
E. 多条件计数



统计销售一部电视机的销售员人数
=SUMPRODUCT((B4:B13="销售一部")*(C4:C13="电视机"))
多条件因为有了*运算符,所以不需要用N函数转换
F. 模糊条件计数



统计电脑和电视机的销售员人数
=SUMPRODUCT(N(ISNUMBER(FIND("电",C4:C13))))
FIND("电",C4:C13):返回的是{1;#VALUE!;1;#VALUE!;1;1;1;1;1;#VALUE!}
ISNUMBER(FIND("电",C4:C13)):返回的是
{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}
N(ISNUMBER(FIND("电",C4:C13))):返回的是{1;0;1;0;1;1;1;1;1;0}
3. 其他场景

A. 跨列统计



统计三次应聘的笔试平均分
=SUMPRODUCT(($C$3:$H$3=I$3)*$C4:$H4)/3
$C$3:$H$3=I$3:返回的是{TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}
下来自己练习时,这里需要注意$的使用
B. 加权重统计



统计总分,笔试占70%,面试占30%
=SUMPRODUCT(I4:J4*$I$13:$J$13)
这是基本用法,但是一种经典使用场景,需要注意$的使用
C. 排名不间断



对总分排名,有相同名次时,下个名次不跳跃
RANK排名函数,如果碰到并列名次,下一个排名会断开,如图,没有第4名,因为第3名是两名人员并列
而在很多场景,需要碰到并列名次时,下一个排名不间断,这也是SUMPRODUCT的一个经典使用场景
=SUMPRODUCT(($K$4:$K$12>K4)/COUNTIF($K$4:$K$12,$K$4:$K$12))+1
$K$4:$K$12>K4:{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
COUNTIF($K$4:$K$12,$K$4:$K$12):{1;1;1;2;2;1;1;1;1}
这里不好理解的,可以下来自己练习一下,练习时,可以在公式栏,选中函数,比如选中COUNTIF($K$4:$K$12,$K$4:$K$12),然后按F9,可以看到函数的返回值
D. 表格转换



如下图,将表一转换成表二的样子
=SUMPRODUCT(($B$4:$B$13=$J4)*($C$4:$C$13=K$3)*$F$4:$F$13)
这其实是多条件的一种经典使用场景,需要注意$的使用


Ok,掌握了Sumproduct函数的用法,你离大神又进了一步
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|启明办公

Copyright © 2001-2013 Comsenz Inc.Template by Comsenz Inc.All Rights Reserved.

Powered by Discuz!X3.4

快速回复 返回顶部 返回列表