二维码 购物车
部落窝在线教育欢迎您!

比SUM、SUMIF、COUNTIF、RANK函数强10倍!!!原来SUMPRODUCT函数才是Excel的求和函数之王……

 

作者:郅龙来源:部落窝教育发布时间:2021-07-16 10:22:11点击:2931

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

在Excel众多函数中,求和函数永远是Excel人逃不掉的必学函数。一说到求和函数,大多数人想到的就是SUM函数,却不知道有一个函数比它强大的多!除了能更轻松的求和,它比SUMIF会条件求和、比COUNTIF会条件计数,甚至比RANK函数还能排名……它就是看似只会对乘积求和的SUMPRODUCT函数!

 

 

SUMPRODUCT函数的功能其实很简单,就是对乘积进行求和,然而结合了逻辑值和数组之后,这个函数好像变得完全不同了,不仅可以挑战SUMIF函数在条件求和领域的权威,而且还敢于挑战COUNTIF函数进行条件计数,甚至将排名函数RANK也斩于马下……

 

SUM函数的功能是对所有参数中的数字求和,PRODUCT函数的功能则是对参数中的所有数字求积。两个函数结合而成的SUMPRODUCT可以实现对参数之积求和的功能。例如根据单价和数量可以直接计算出总金额,如图所示。

 

 

但这只是SUMPRODUCT函数的基本功,今天要和大家分享它的十八般武艺,可以说招招精彩。

 

注:以下案例直接给出公式,有同学们适用的场景可以直接套用。

 

Ps:关于SUMPRODUCT函数的用法和原理,之前有过很多篇教程可以参考,今天就不详细解释每个公式了。

 

SUMPRODUCT第一招:条件求和

如图,对7月的销售数量求和,公式为:=SUMPRODUCT((A2:A15="7")*C2:C15)

 

 

可能你会说,这个SUMIF也能干啊。别急,看看这样的条件求和SUMIF能干吗?

 

SUMPRODUCT第二招:秒了SUMIF的条件求和方法

如图,数据源里没有月份只有具体的销售日期,试问你能用SUMIF求和吗?

SUMPRODUCT是这样干的:=SUMPRODUCT((MONTH(A2:A15)=10)*C2:C15)

 

 

SUMIF干瞪眼没招了,SUMPRODUCT接着秀,还有多列的条件求和呢。

 

SUMPRODUCT第三招:针对多列的条件求和

如图,要求和的数据位于多列。

公式为:=SUMPRODUCT(($A$2:$A$22=I2)*$C$2:$G$22)

 

 

看到这个问题,SUMIF也想试试,但好像一个SUMIF搞不定,得来五个……

SUMPRODUCT又跑去找SUMIFS的麻烦了。

 

SUMPRODUCT第四招:多条件求和

如图,按照销售机构和商品名称对销售数量求和,这是SUMIFS的老本行,但SUMPRODUCT是这样干的:=SUMPRODUCT($C$2:$C$22*($A$2:$A$22=$E2)*($B$2:$B$22=F$1))

 

 

SUMIFS一个劲的翻白眼,SUMPRODUCT跳的更欢了,继续表演多列多条件求和。

 

SUMPRODUCT第五招:针对多列的多条件求和

如图,要求满足两个条件(机构和商品名称)后,并将求和的结果显示到不同列中。SUMPRODUCT是这么干的:

=SUMPRODUCT($C$2:$G$22*($A$2:$A$22=$I2)*($B$2:$B$22=J$1))

 

 

看着SUMPRODUCT的一波操作,SUMIFS一脸懵逼。

远处,COUNTIFCOUNTIFS为好友SUMIFS出面,没想到SUMPRODUCT二话不说,直接秀出一波连环攻击。

 

SUMPRODUCT第六——第十三招:条件计数

数据源如图,涉及四类条件计数问题,每个问题两个公式,共八个公式。

 

 

统计女性人数:

公式1=SUMPRODUCT(N(B2:B20=G2))

公式2=SUMPRODUCT(N(B2:B20=""))

统计男性本科人数:

公式1=SUMPRODUCT((B2:B20=G4)*(C2:C20=G5))

公式2=SUMPRODUCT((B2:B20="")*(C2:C20="本科"))

统计30岁以上的人数:

公式1=SUMPRODUCT(--(D2:D20>G6))

公式2=SUMPRODUCT(--(D2:D20>30))

统计30岁到40岁之间的人数(含40岁):

公式1=SUMPRODUCT((D2:D20>G8)*(D2:D20<=G9))

公式2=SUMPRODUCT((D2:D20>30)*(D2:D20<=40))

 

看了这几个方法,COUNTIFCOUNTIFS不得不服气,指着更远处的RANK,朝SUMPRODUCT努努嘴,想看SUMPRODUCT还能不能一路秀下去。

 

RANK正在处理一个排名次的问题。

 

 

SUMPRODUCT好奇的看着,觉得有点新鲜,沉思了一会,秀出了一招。

 

SUMPRODUCT第十四招:排名次

公式为:=SUMPRODUCT(N($B$2:$B$9>B2))+1

 

 

SUMPRODUCT高兴地冲着RANK说了句,你不行啊,有两个第五名,第六名被你干没了,直接就到第七名了。

RANK回了句,你行你上啊。

只见SUMPRODUCT朝着COUNTIF招了招手说,兄弟来搭把手。

 

SUMPRODUCT第十五招:中国式排名,我也行

公式为:=SUMPRODUCT((B$2:B$9>=B6)/COUNTIF(B$2:B$9,B$2:B$9))

 

 

RANK不服气的说,你找人帮忙了,不算。

SUMPRODUCT挑了挑眉毛,大喊一声,不找人也行,我还会分组排名和综合排名,不信你看。

 

SUMPRODUCT第十六招:分组排名

公式为:=SUMPRODUCT((A$2:A$9=A2)*(C$2:C$9>=C2))

 

 

RANK大吃一惊,一时间无法相信自己看到的一切,SUMPRODUCT还在继续表演。

 

SUMPRODUCT第十七招:综合排名

鉴于这个问题比较复杂,要对规则做一说明,即多权重综合排名常用于有多项考核指标同时存在的情况,需要根据每个指标的重要程度与结果进行综合排名。

例如,下图数据中,需要同时考虑业绩增长率(最重要)、任务达成率(其次重要)和投诉解决率(第三重要)三项指标,可以使用公式:

=SUMPRODUCT(N(B$2:B$8*10000+C$2:C$8*10+D$2:D$8>=B2*10000+C2*10+D2))

 

 

看着毫无还手之力的RANKSUMPRODUCTCOUNTIF说,最后一招还是咱们兄弟两一起吧。

 

SUMPRODUCT第十八招:统计不重复个数

有若干条销售数据,需要统计出有几位销售人员。

公式为=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

 

 

十八招表演完毕,就问你SUMPRODUCT厉害不厉害。

其实只要是函数就有死穴,而SUMPRODUCT函数就是:数组参数必须具有相同的维数,否则将返回#VALUE!错误值。

 

另外SUMPRODUCT函数还有三个小秘密:

1、函数SUMPRODUCT将非数值型的数组元素作为0处理;

2、函数SUMPRODUCT不能使用通配符,但可以与函数FIND组合实现包含功能;

3、函数SUMPRODUCT是不用按三键结束的数组运算函数。

 

今天的内容看的过瘾吗,记得转发哦。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel必会函数之SUMPRODUCT函数

加了*的 SUMPRODUCT函数无所不能

sumif和countif函数应用

TEXT和SUMPRODUCT强强联合,只为解决一个“微不足道”的编号问题??

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。