厉害了,我的SUMIFS函数

Excel函数 2023-05-29 22:29:58

今天讲的sum ifS函数，相对LOOKUP而言，它有两个优势：

1、计算效率更高，当数据超过1万行，LOOKUP函数就会很卡，而sum ifS函数依然不卡。

2、显示效果会更好，LOOKUP函数查找不到对应值显示错误值，而sum ifS函数查找不到对应值显示0。

比如我们用sum ifS函数来查找金额。=sum ifS(C:C,A:A,E2,B:B,F2)

将项目改成【函数班】，因为没有对应值，就直接显示0。

sum ifS函数语法：=sum ifS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域N,条件N)

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

一、统计每个学校的金额。

正常情况下，单条件我们想到的是用sum if函数统计，其实sum ifS函数不仅可以多条件求和，也可以单条件求和。=sum ifS(D:D,A:A,F2)

学会了sum ifS函数，sum if函数也可以不用学。

二、统计每个学校对应分类的金额。

在J2输入公式，下拉和右拉。=sum ifS($D:$D,$A:$A,$I2,$C:$C,J$1)

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

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

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

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

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

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

三、计算每个员工号的每一列单位金额。

原来使用一个个单元格加起来的方法，如果引用的单元格比较少，这种也是不错的选择。当引用的单元格多的情况下，容易出错。这时sum ifS函数就派上用场了。=sum ifS(B2:Q2,$B$1:$Q$1,”单位金额”)

财务人对金额比较敏感，有的时候差1,2分钱都不行，这时在函数外面嵌套一个ROUND函数进行四舍五入，保留2位小数点。=ROUND(sum ifS(B2:Q2,$B$1:$Q$1,”单位金额”),2)

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

格式相同的明细表

汇总表

求1月份的销售量可以用：=sum ifS(1月!C:C,1月!A:A,A2,1月!B:B,B2)

常规方法是写6个sum ifS相加。，但6个月的数据改起来也不是太费劲，不过如果是12个月，甚至有的是按天进行分表格，一年365个表格，直接写晕。

比较智能的方法应该是这样写公式：=sumPRODUCT(sum ifS(INDIRECT(ROW($1:$6)&”月!C:C”),INDIRECT(ROW($1:$6)&”月!A:A”),A2,INDIRECT(ROW($1:$6)&”月!B:B”),B2))

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

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

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

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

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

这时用sum ifS函数统计出来的结果是6个数据：={151;0;0;0;0;0}

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

如果现在有12个月，只需将公式中的6改成12就可以。=sumPRODUCT(sum ifS(INDIRECT(ROW($1:$12)&”月!C:C”),INDIRECT(ROW($1:$12)&”月!A:A”),A2,INDIRECT(ROW($1:$12)&”月!B:B”),B2))

上一篇：快速给合并单元格添加序号下一篇：SUMIF函数基本用法解析