最新WPS办公软件学习教程_免费企业办公软件下载_金山WPS Office官网论坛

 找回密码
 
查看: 75465|回复: 1007

[函数公式] 利用 SUMPRODUCT 函数进行多条件计数、求和

    [复制链接]

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-16 23:52 |显示全部楼层
分享到: 新浪微博 腾讯微博
本帖最后由 松风水月 于 2014-1-10 02:44 编辑

教程附件:
游客,如果您要查看本帖隐藏内容请回复


在需要进行单条件计数、求和时,我们一般都会使用 COUNTIF 函数、SUMIF 函数。但是对于多条件计数/求和时,这两个函数就不适用了。这时,我们可以使用 SUMPRODUCT 函数来实现我们的需求。


                                           目录

一、使用 SUMPRODUCT 函数进行多条件计数……………………(2楼

(一)例 1:条件间关系为 and…………………………………………(2楼
(二)例 2:条件间关系为 or,且各条件互斥……………………(3楼
(三)例 3:条件间关系为 or,且各条件相容……………………(4楼
(四)例 4:复杂条件的拆分……………………………………………(5楼

二、使用 SUMPRODUCT 函数进行多条件求和……………………(
6楼
三、SUMPRODUCT 与 COUNTIFS 比较...........................(470楼

已有 7 人评分威望 收起 理由
shengmeizhiyue + 8 赞一个!
轩少 + 18 WPS有你更精彩!
翘尾的k + 8
月夜清泉 + 24 很给力!
落寞冬日 + 10 很给力!
1149737746 + 10 强力顶起!
zhouyiran1@126.com + 20 好精彩的教程,很给力!

总评分: 威望 + 98   查看全部评分

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 03:54 |显示全部楼层
本帖最后由 松风水月 于 2014-1-10 02:46 编辑

返回目录

一、使用 SUMPRODUCT 函数进行多条件计数

(一)例 1:条件间关系为 and

利用 SUMPRODUCT 函数进行多条件计数、求和_16110004


如图,我们要求类别为“b”,且长=12的物品的种类的数量共有多少个

利用 SUMPRODUCT 函数进行多条件计数、求和_16110005


我们可以使用下面这个公式:
  1. =SUMPRODUCT((B2:B10="b")*(C2:C10=12)*1)
复制代码
公式讲解:

利用 SUMPRODUCT 函数进行多条件计数、求和_16110006


① SUMPRODUCT 函数。
功能:在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
语法:SUMPRODUCT(数组1, [数组2], [数组3], ...)
参数说明:
1. 数组1:必须。
2. 数组2,数组3…… :可选,最多支持30个数组,所有数组维数必须相同。

由于 SUMPRODUCT 函数可以对数组对各元素进行计算,所以我们可以利用它来进行多条件计数/求和。

② 检验单元格 B2:B10 中各单元格的数值是否符合条件 1的要求(等于"b")。
检验单元格 C2:C10 中各单元格的数值是否符合条件 2的要求(等于"=12")。
④ 条件 1 与条件 2 需要同时成立,即条件间关系为 and,所以此处为乘号
⑤ 因为我们要进行的是多条件计数,所以最后在最后添加 "*1"

计算结果

在表中:
符号条件 1 的物品分别为:物品3、物品4、物品5、物品6、物品7,共5个。
符号条件 2 的物品分别为:物品5、物品6、物品7,共3个。
同时符合条件1、条件2的物品分别为:物品5、物品6、物品7,共3个。
所以计算结果为:3


返回目录
回复

使用道具 举报

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 04:02 |显示全部楼层
本帖最后由 松风水月 于 2012-9-17 15:50 编辑

返回目录

(二)例 2:条件间关系为 or,且各条件互斥

利用 SUMPRODUCT 函数进行多条件计数、求和_16110007


我们来看下面这个需求:要计算类别为"b",且长为12或9 的物品的种类有多少个。

利用 SUMPRODUCT 函数进行多条件计数、求和_16110017


可以使用公式:
  1. =SUMPRODUCT((B2:B10="b")*((C2:C10=12)+(C2:C10=9))*1)
复制代码
公式讲解:

利用 SUMPRODUCT 函数进行多条件计数、求和_16110009


① 检验单元格 B2:B10 是否符合条件 1(类别="b")
② 检验单元格 C2:C10 是否符合条件 2(长=12,或长=9)
③ 检验单元格 C2:C10 是否符合分条件 1(长=12)
④ 检验单元格 C2:C10 是否符合分条件 2(长=9)
⑤ 条件 1 与条件 2 需要同时成立,即条件间关系为 and,所以此处为乘号
⑥ 分条件 1 与分条件 2 只需其中一个成立即满足,即条件间关系为 or,所以此处为加号
⑦ 因为我们要进行的是多条件计数,所以最后在最后添加 "*1"

计算结果

在表中:

符号条件 1 的物品分别为:物品3、物品4、物品5、物品6、物品7,共5个。

符号分条件 1 的物品分别为:物品5、物品6、物品7,共3个。
符号分条件 2 的物品分别为:物品3,共1个。
所以符合条件 2 的物品为:3+1=4个

所以同时符合条件1、条件2的物品分别为:物品3、物品5、物品6、物品7,共4个。
所以计算结果为:4


返回目录
回复

使用道具 举报

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 04:54 |显示全部楼层
本帖最后由 松风水月 于 2014-4-15 19:17 编辑

返回目录

(三)例 3:条件间关系为 or,且各条件相容

利用 SUMPRODUCT 函数进行多条件计数、求和_16110010


如图,我们要求长>10 或 宽>10 的物品的种类的数量共有多少个


利用 SUMPRODUCT 函数进行多条件计数、求和_16110011


这个例子和例 2 是相似的。我们可以仿造例2,写下如下的公式:
  1. =SUMPRODUCT(((C2:C10>10)+(D2:D10>10))*1)
复制代码
但是这个公式的计算结是是 11,求得的结果比总数还多。所以很明显,它是错的。

我们来分析一下,它错误的原因。


利用 SUMPRODUCT 函数进行多条件计数、求和_16110012


① 检验单元格 C2:C10 是否符合条件 1(长>10)。符合该要求的物品有物品5——物品9,共5个。
② 检验单元格 C2:C10 是否符合条件 2(宽>10)。符合该要求的物品有物品1,以及物品5——物品9,共6个。
③ 因为这两个条件之间的关系是 or,所以用加号连接。

因为分式①的计算结果为5,分式②的计算结果为6,所以整个公式的计算结果为 5+6=11

现在我们可以找到错误的原因了。原因在于:物品5——物品9被重复计算了两次。

所以我们应该依据下面的结构修改我们的公式:


利用 SUMPRODUCT 函数进行多条件计数、求和_16110014


修改后的公式为:
  1. =SUMPRODUCT(((C2:C10>10)+(D2:D10>10)-((C2:C10>10)*(D2:D10>10)))*1)
复制代码
利用 SUMPRODUCT 函数进行多条件计数、求和_16110015


分式①计算符合分条件 1或符合分条件 2的物品的种类的数量。计算结果为11。
分式②计算符合分条件 1且符合分条件 2的物品的种类的数量。计算结果为6。
因为条件1和条件2之间的关系是 not,所以③这个地方用减号。

所以整个公式的计算结果为:11-6=5


思考

我们需要思考一个问题。为什么例2的公式在这里不能直接套用?

我们观察后可以发现,例2的条件是“长=12,或长=9”,两者是同一个项目,条件之间互相排斥,不会被重复计算。而例3中的条件是“长>10,或宽>10”,两者不是同一个项目,条件之间是相容的,不会互相排斥,所以会被重复计算,因此需要把重复计算部分减去。

这里所讨论的是不同项目只有2个的情况。如果有多个项目,情况可能更复杂。如要计算数据源中,符合条件1,或条件2,或条件3的物品的个数,则需要使用下面的公式。

=SUMPRODUCT(((条件1)+(条件2)
+(条件3)-(条件1*条件2)-(条件1*条件3)-(条件2*条件3)+(条件1*条件2*条件3))*1)

(感谢坛友 1149737746 指正原公式中的错误!)


利用 SUMPRODUCT 函数进行多条件计数、求和_16110016


返回目录
回复

使用道具 举报

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 05:51 |显示全部楼层
本帖最后由 松风水月 于 2012-9-17 15:51 编辑

返回目录

例 4:复杂条件的拆分

利用 SUMPRODUCT 函数进行多条件计数、求和_16110022


让我们看这个需求如何解决:

利用 SUMPRODUCT 函数进行多条件计数、求和_16110023


“长和宽这两个数的最大值大于15”,意思为,先将长与宽进行比较,取两者中最大值,然后再将此最大值与15比较,若大于15,则符合条件。

若按照常规的思路,我们可以再增加一列F列,然后令 F2=max(C2, D2) ,再在单元格 F3:F10 将此公式进行填充。然后使用公式:
  1. =SUMPRODUCT((F2:F10>15)*(E2:E10>2)*1)
复制代码
求得需要的结果。

但是,若不允许增加一列新列,有什么办法可以解决呢? MAX函数的输出结果只能是一个数值,而无法是一个数组,在SUMPRODUCT 函数里面是不好使用了。

我们需要换另一种思路,将条件 1这一个较复杂的条件分解为几个简单条件的组合。如下图:


利用 SUMPRODUCT 函数进行多条件计数、求和_16110026


经过这样拆解之后,公式就很容易写出来了。
  1. =SUMPRODUCT(((C2:C10>=D2:D10)*(C2:C10>15))+((C2:C10<D2:D10)*D2:D10>15)*(E2:E10>2)*1)
复制代码
公式讲解:

利用 SUMPRODUCT 函数进行多条件计数、求和_16110033


分条件1:检验单元格 C2:C10 和 D2:D10 是否符合分条件 1(长>=宽,且长>15)。
分条件2:检验单元格 C2:C10 和 D2:D10 是否符合分条件 2(长<宽,且宽>15)。
① 因为两个分条件只要有一个成立即可,所以它们的关系是 or,因此此处用加号连接。
② 条件1=分条件 ①+分条件 ②。因为条件 1和条件 2需要同时成立,所以此处用乘号。

因为分条件 1 与分条件 2互斥,不会重复计算的情况,所以不用像例 3一样减去重复计算的部分。

计算结果:


符合分条件 1 的物品分别为:物品8、物品9,共2种。
符合分条件 2 的物品分别为:物品1、物品5、物品6,共3种。
所以符合条件 1 的物品一共有 2+3=5 种。
所有物品都符合条件 2 (数量>2)要求,共有10种。
所以既符合条件 1,也符合条件 2的物品,共有5种。


返回目录
回复

使用道具 举报

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 05:52 |显示全部楼层
本帖最后由 松风水月 于 2014-1-16 16:56 编辑

返回目录

二、使用 SUMPRODUCT 函数进行多条件求和

我们再来看上面例1中的例子。原来例 1 中我们要求的是求符合条件的物品的种类的数目,现在我们改为求符合条件的物品数量。这样,我们就从原来的要进行多条件计数,变为要进行多条件求和了。

利用 SUMPRODUCT 函数进行多条件计数、求和_16110074


利用 SUMPRODUCT 函数进行多条件计数、求和_16110076


需求②即是我们的例 1,所需公式为:
  1. =SUMPRODUCT((B2:B10="b")*(C2:C10=12)*1)
复制代码
需求①则是我们现在要求的问题,所需公式为:
  1. =SUMPRODUCT((B2:B10="b")*(C2:C10=12)*(E2:E10))
复制代码
对比上面两个公式,我们发现在条件相同的情况下,用 SUMPRODUCT 函数进行多条件计数和多条件求和,使用的公式只有一点不同。
也就是除了各个条件之间的运算外,多条件计数最后要加上的是“*1”,而多条件求和最后要加上的是“*求和区域”。

公式其余部分,在条件相同的情况下,它们可以是相同的。


利用 SUMPRODUCT 函数进行多条件计数、求和_16110077


三、SUMPRODUCT 与 COUNTIFS 比较
第470楼
(全文完)


返回目录
已有 1 人评分威望 收起 理由
知道了 + 1 很给力!

总评分: 威望 + 1   查看全部评分

回复

使用道具 举报

153

主题

109

听众

1万

积分

解答支持团长

老菜鸟

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 240 天

[LV.8]以坛为家I

金币
1254
威望
25965
帖子
10314
精华
1

活跃会员奖 解答支持团 测试体验团 乐于助人奖 优秀会员奖

发表于 2012-9-17 08:26 |显示全部楼层
本帖最后由 zhouyiran1@126.com 于 2012-9-17 08:27 编辑

刚露端倪,即是如此精彩,团长真是好手笔,学习了!

推荐精华帖!

点评

落寞冬日  是的,移动过了  发表于 2012-9-17 20:35
松风水月  谢谢老先生!  发表于 2012-9-17 15:55
回复

使用道具 举报

78

主题

61

听众

5047

积分

解答支持团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

该用户从未签到

金币
257
威望
9809
帖子
3187
精华
0

WPS粉丝团 勤奋版主奖 荣誉版主奖 乐于助人奖 优秀会员奖 活跃会员奖 技术分享团 解答支持团 测试体验团 重阳节勋章 技巧教程分享达人 版主勋章

发表于 2012-9-17 09:06 |显示全部楼层
我想,楼主发到for windows是不是好点呢。那边人多:lol

点评

zhouyiran1@126.com  应该是office学院或office技巧发帖,for windows分享做广告,大家说对吗?  发表于 2012-9-17 20:35
zhouyiran1@126.com  没在for windows?就在for Windows,难道是移动过了?  发表于 2012-9-17 20:33
回复

使用道具 举报

310

主题

35

听众

1万

积分

技术分享团员

Rank: 14Rank: 14Rank: 14Rank: 14

签到天数: 1069 天

[LV.10]以坛为家III

金币
3496
威望
22770
帖子
4219
精华
0

WPS粉丝团 技术分享团 测试体验团 重阳节勋章 优秀会员奖 技巧教程分享达人 乐于助人奖 活跃会员奖

发表于 2012-9-17 09:13 |显示全部楼层
本帖最后由 1149737746 于 2012-9-17 09:23 编辑

团长的教程思路清晰,便于初学者,又不辞辛苦,忙至很晚,应予褒奖!
不过智者千虑,难免一疏,与团长商榷:
这里应该是
:=SUMPRODUCT(((条件1)+(条件2)+(条件3)-(条件1*条件2)-(条件1*条件3)-(条件2*条件3)+(条件1*条件2*条件3))*1)
吧?

点评

赖一6068  辛苦了,谢谢  详情 回复 发表于 2014-3-18 20:18
松风水月  您说得对,谢谢指出!我马上改正!  详情 回复 发表于 2012-9-17 09:35
已有 1 人评分威望 收起 理由
松风水月 + 10 感谢您的指正!

总评分: 威望 + 10   查看全部评分

回复

使用道具 举报

19

主题

107

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-9-17 09:35 |显示全部楼层
1149737746 发表于 2012-9-17 09:13
团长的教程思路清晰,便于初学者,又不辞辛苦,忙至很晚,应予褒奖!
不过智者千虑,难免一疏,与团长商榷 ...

您说得对,谢谢指出!我马上改正!
回复

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 更多账号登录:

快速回帖:

WPS论坛更新日志|WPS Office官方论坛     

GMT+8, 2017-11-22 13:28

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部