返回

实务交流

发帖

厉害了,我的SUMIFS函数

Excel不加班楼主

2018-03-09 17:43:57 1207 4

回顾一下昨天的文章《LOOKUP函数,以一敌百》最后一个案例。


根据姓名和项目名称两个条件查找金额。


LOOKUP函数的条件,可以是一个,也可以是多个,都是直接套用就可以,都很简单。

=LOOKUP(1,0/(($A$2:$A$10=E2)*($B$2:$B$10=F2)),$C$2:$C$10)


有读者私聊卢子,提到多条件查找金额其实用SUMIFS函数更好,查找文本更适合用LOOKUP函数。


这个读者说的没错。昨天是专门讲LOOKUP函数,今天讲讲SUMIFS函数,相对LOOKUP而言,它有两个优势:

01 计算效率更高,当数据超过1万行,LOOKUP函数就会很卡,而SUMIFS函数依然不卡。


02 显示效果会更好,LOOKUP函数查找不到对应值显示错误值,而SUMIFS函数查找不到对应值显示0。


这里我们用SUMIFS函数来查找金额。

=SUMIFS(C:C,A:A,E2,B:B,F2)


将卢子的项目改成函数班,因为没有对应值,就直接显示0。


SUMIFS函数语法:

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


第一参数为求和区域,后面的条件区域和条件一一对应,下面通过4个案例进行说明。


1.统计每个学校的金额。


正常情况下,单条件我们想到的是用SUMIF函数统计,其实SUMIFS函数不仅可以多条件求和,也可以单条件求和。

=SUMIFS(D:D,A:A,F2)


学会了SUMIFS函数,SUMIF函数也可以不用学。


2.统计每个学校对应分类的金额。


在J2输入公式,下拉和右拉。

=SUMIFS($D:$D,$A:$A,$I2,$C:$C,J$1)


混合引用是所有引用方式中最难理解的,有不少读者到现在都没弄清楚该如何使用。这里,卢子再说明一下。


公式在下拉的时候,我们希望第一行的单元格不改变,将美元符号放在数字前面就锁定行号不变J$1。


公式右拉的时候,我们希望I列这一列的单元格不改变,将美元符号放在列前面就锁定列号不变$I2。


公式既要下拉,也要右拉,区域都是使用绝对引用。假设我们现在不是引用整列,区域就需要在行号跟列号前面都加美元符号,如$D$2:$D$221。


整个公式,这个引用方式是难点,自己动手多操练几次,可以通过按F4键不断切换引用方式。现在来演示一下,在编辑栏切换引用方式。


提示:有的电脑比较特殊,需要按Fn+F4组合键才可以。


3.计算每个员工号的每一列单位金额。


原来使用一个个单元格加起来的方法,如果引用的单元格比较少,这种也是不错的选择。当引用的单元格多的情况下,容易出错。这时SUMIFS函数就派上用场了。

=SUMIFS(B2:Q2,$B$1:$Q$1,"单位金额")


作为财务,对金额比较敏感,有的时候差1,2分钱都不行,这时在函数外面嵌套一个ROUND函数进行四舍五入,保留2位小数点。

=ROUND(SUMIFS(B2:Q2,$B$1:$Q$1,"单位金额"),2)


前面3个案例都是比较基础的,最后一个案例比较难。


4.统计1-6月每个销售人员对应商品的销售量。


格式相同的明细表


汇总表


求1月份的销售量可以用:

=SUMIFS(1月!C:C,1月!A:A,A2,1月!B:B,B2)


常规方法是写6个SUMIFS相加。

=SUMIFS(1月!C:C,1月!A:A,A2,1月!B:B,B2)+SUMIFS(2月!C:C,2月!A:A,A2,2月!B:B,B2)+SUMIFS(3月!C:C,3月!A:A,A2,3月!B:B,B2)+SUMIFS(4月!C:C,4月!A:A,A2,4月!B:B,B2)+SUMIFS(5月!C:C,5月!A:A,A2,5月!B:B,B2)+SUMIFS(6月!C:C,6月!A:A,A2,6月!B:B,B2)


6个月的数据改起来也不是太费劲,不过如果是12个月,甚至有的是按天进行分表格,一年365个表格,直接写晕。


比较智能的方法应该是这样写公式:

=SUMPRODUCT(SUMIFS(INDIRECT(ROW($1:$6)&"月!C:C"),INDIRECT(ROW($1:$6)&"月!A:A"),A2,INDIRECT(ROW($1:$6)&"月!B:B"),B2))


公式看起来很复杂,其实并不难。


ROW($1:$6)的作用就是获取1-6的数字。


ROW($1:$6)&"月!C:C"的作用就是获取1-6月C列的文本字符串,并不能参与计算。


INDIRECT(ROW($1:$6)&"月!C:C")的作用就将1-6月C列的文本字符串转变成区域,并能参与计算。


同理,其他参数的INDIRECT都是一样的作用。


这时用SUMIFS函数统计出来的结果是6个数据:

={151;0;0;0;0;0}


在最外面嵌套SUMPRODUCT函数,就可以对这些数据进行求和。


如果现在有12个月,只需将公式中的6改成12就可以。

=SUMPRODUCT(SUMIFS(INDIRECT(ROW($1:$12)&"月!C:C"),INDIRECT(ROW($1:$12)&"月!A:A"),A2,INDIRECT(ROW($1:$12)&"月!B:B"),B2))


其实写这篇文章目的是告诉你,函数有400-500个,我们不需要知道每一个函数的用法,我们只需要精通常用函数的用法足矣。

作者:卢子,清华畅销书作者;个人公众号:Excel不加班(ID:Excelbujiaban)

飞翎恋雪1楼

2018-03-09 20:42:15

※星星※2楼

2018-03-10 09:04:52

筱(honey)3楼

2018-03-20 22:28:35