本文总共2146个字,阅读需8分钟,全文加载时间:1.945s,本站办公入门专栏收录该内容! 字体大小:

文章导读:可以求和的函数很多,但是SUMPRODUCT是绕不过去的那个,它不仅可以条件计数还可以求和,且自带数组计算,不用三键,对新手非常友好,今天我们就来聊聊SUMPRODUCT这个函数!我们还是通过一系列案例,在案例中讲解我……各位看官请向下阅读:

可以求和的函数很多,但是SUMPRODUCT是绕不过去的那个,它不仅可以条件计数还可以求和,且自带数组计算,不用三键,对新手非常友好,今天我们就来聊聊SUMPRODUCT这个函数!
我们还是通过一系列案例,在案例中讲解我们相关知识点!

案例1 | 根据数量和单价求金额

=SUMPRODUCT(E2:E14,F2:F14)

正常应该考虑添加一列计算出金额,然后再求和,SUMPRODUCT可以完成对应的单元格相乘最后相加,当然我们也可以写成一下的样式,可能更好的理解

=SUMPRODUCT(E2:E14*F2:F14)

结果虽然一样,但是下面的大家看上去更直观,SUMPRODUCT 正确的理解是 PRODUCT(乘) SUM(求和),先乘后加,但是两种写法也是有区别的,这个差别也就是SUMPRODUCT其中一个特性,我们来看一下

如果我们价格中有部分价格还没确定,就好像考试成绩写着缺考一样,这个时候,我们使用乘法的格式是得不到想要的结果的,错误值

但是两个数组分开写是可以的,其中的价格 待确定这项会被忽略,
这个非常符合我们实际情况!
这种我们就来说一下SUMPRODUCT的特性:SUMPRODUCT 将非数值条目视为零,这种的非数值包括逻辑值(TRUE和FALSE),但是值得注意的是他无法忽略错误值,比如下面这样就会报错,想要得到正确的结果,我们需要其他函数辅助比如IFERROR

遇到错误值正确的处理方式如下:

=SUMPRODUCT(IFERROR(E2:E14,),F2:F14)

IFERROR把错误值转换成了0,这样我们就可以得到正确的结果!
其实SUMPRODUCT还有一个特性,就是要求数组或者区域大小一致,这个好理解,就是尺寸一致,一一对应起来,不能单价3行,数量5行!
案例1,主要带大家认识一下SUMPRODUCT,了解一下数组逗号写好的可以忽略非数值的特性!

案例2 | 单条件求和-东北区域的金额合计

=SUMPRODUCT((B2:B14="东北区域")*E2:E14*F2:F14)

一样如果单价中有文本,需要写成如下公式:

=SUMPRODUCT((B2:B14="东北区域")*1,E2:E14*F2:F14)

肯定有人会问为什么乘以1,所以我们解释一下!由于SUMPRODUT忽略非数值,所以逻辑值也会被忽略,B2:B14="东北区域" 结果是一组逻辑是TRUE和FALSE,
想要计算就需要转成数字,逻辑值记过加减乘除四则运算就会变成数值,其中TRUE*1=1,FALSE*1=0,这样上面就好理解,不是东北区域的也就是都乘以了0,最后结果都是0,东北区域的就乘以1,最后相加OK!

案例3 | 条件计数 - 小易共销售几次

=SUMPRODUCT((C2:C14="小易")*1)

原理我们在案例2中说过,逻辑值乘以1,TRUE*1=1,FALSE*1=0,最后相加,也就是条件计数,当然你也可以使用COUTNIF(S)也是一样!

=COUNTIF(C2:C14,"小易")

案例4 | 多条件求和-华南区域李四的销售金额合计

=SUMPRODUCT((B2:B14="华南区域")*(C2:C14="李四")*E2:E14*F2:F14)

连乘即可,更多条件同理,就不再多啰嗦了!

案例4 | 或者关系-张三和李四的销售数量合计

=SUMPRODUCT((C2:C14="张三") (C2:C14="李四"),F2:F14)

这里更多的是Excel逻辑值计算的问题,而不是SUMPRODUCT的知识点!由于同一个单元格不可能既是张三又是李四,(C2:C14="张三") (C2:C14="李四") 结果只会是1 或者0,可能出现2,这样最后和数量相乘即可
当然我们也可以使用常量数组简化写法!

=SUMPRODUCT((C2:C14={"张三","李四"})*F2:F14)

案例5 | 字段处理后求和-求7月销售数量合计

=SUMPRODUCT((MONTH(A2:A14)=7)*(F2:F14))

这个案例主要是说明,我们可以对某一列或者几列加工处理后作为SUMPRODCUT的条件,比如还可以字符截取,姓赵的销售数量合计等等都是可以的,这样可以让我们灵活!
这里顺便带一下SUMIFS的思路

=SUMIFS(F:F,A:A,">=2019-7-1",A:A,"<="&EOMONTH(--"2019-7-1",0))

利用EOMONTH找到指定日期的当月最后一天的日期~!

OK,SUMPRODUCT今天我们就说这么多,你会用了吗 ?快去试试吧!
小结

1、忽略非数值,这点算是SUMPRODUCT的一个优点,使用其他函数可能还要使用容错处理
2、SUMPRODCUT虽然好用,但是相对于SUMIF函数而已是一个“慢”函数,当逻辑复杂和数据较多时卡的情况会非常明显,实战中一般会考虑SUMI(S)来替换,尤其复杂量大的情况下!
3、不要使用整列或者整行作为SUMPRODCUT的条件区域,否则一个公式会计算1048576次
4、SUMPRODCUT自带数组计算,案例中从来都没有出现过{}三键录入的情况,这点对于新手是非常的友好!

分享、点赞、在看(CTRL+D)收藏本网站

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注