当前位置: > 热评

SUMPRODUCT函数自称求和之王,SUMIFS不服气要来PK

时间:2022-04-20 05:33:32 热评 我要投稿

条条道路通罗马。其实在Excel中,同一个问题也有很多种解决方法,比如条件求和。SUMIFS和SUMPRODUCT都能实现,区别在哪,哪个更好用?

将SUMPRODUCT函数的语法变形,求和区域移动到最前面。两者的语法超级像。

=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2)

=SUMPRODUCT(求和区域*(条件区域1=条件1)*(条件区域2=条件2))

下面通过具体案例进行详细说明两者的区别。

1、根据左边的明细,统计部门的总金额

一般情况下,SUMIFS支持直接引用整列,而SUMPRODUCT只能引用有内容的区域,因此前者的公式看起来更加简洁。

=SUMIFS(E:E,B:B,G2)

=SUMPRODUCT($E$2:$E$12*($B$2:$B$12=G2))

特殊情况下,SUMPRODUCT也支持引用整列,不过不建议使用,太卡了。

2、根据左边的明细,按部门、姓名统计总金额

条件越多,SUMIFS的简洁越能体现。

=SUMIFS(E:E,B:B,G2,C:C,H2)

=SUMPRODUCT($E$2:$E$12*($B$2:$B$12=G2)*($C$2:$C$12=H2))

3、根据左边的明细,按部门、姓氏统计总金额

姓氏就是每个姓名的首个字。

SUMIFS支持使用通配符,H2&"*"就代表首字符。SUMPRODUCT不支持通配符,只能用LEFT从左边提取字符。

=SUMIFS(E:E,B:B,G2,C:C,H2&"*")

=SUMPRODUCT($E$2:$E$12*($B$2:$B$12=G2)*(LEFT($C$2:$C$12,1)=H2))

4、根据左边的明细,按部门、姓名简称统计总金额

经常可以遇到简称和全称的查找,有的人为了贪图方便,记录内容都用简称。

SUMPRODUCT不支持通配符的弊端在这里进一步体现,需要借助FIND判断姓名简称有没出现,FIND的结果还会出现错误值,还需要借助ISNUMBER判断内容是不是数字,很麻烦。

=SUMIFS(E:E,B:B,G2,C:C,"*"&H2&"*")

=SUMPRODUCT($E$2:$E$12*($B$2:$B$12=G2)*ISNUMBER(FIND(H2,$C$2:$C$12)))

5、根据左边的明细,统计每月的总金额

SUMIFS的条件区域不支持嵌套MONTH,只能通过辅助列解决。

=MONTH(A2)

再根据辅助列进行条件求和。

=SUMIFS(E:E,F:F,G2)

而SUMPRODUCT每个参数都支持嵌套其他函数,因此不需要辅助列,这是优势。

=SUMPRODUCT($E$2:$E$12*(MONTH($A$2:$A$12)=G2))

6、根据左边的二维明细表,统计部门的总金额

SUMIFS不支持统计二维明细表,需要用辅助列对所有列先求和。

=SUM(B2:K2)

再根据辅助列进行条件求和。

=SUMIFS(L:L,A:A,M2)

而SUMPRODUCT不需要辅助列,这是优势。

=SUMPRODUCT($B$2:$K$12*($A$2:$A$12=M2))

总结:在卢子眼中,如果只是常规的条件求和,SUMIFS简直就是无敌的存在。如果区域需要嵌套其他函数,以及二维数据源条件求和,SUMPRODUCT会更有优势。

推荐:年轻人都在发EDG,而我却在研究DGET的妙用。。。

上篇:万般皆套路!Excel中让你爽到爆的查找、求和套路

你还想看什么函数PK?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)