|
有人说还不太会用Sumif，我和我的小伙伴们都惊呆了！ 我又仔细想了想，你可能还真不一定会用Sumif！ Sumif作为日常工作中应用频率非常高的一个函数，实在是变化无穷。我们来看看Sumif都有哪些实用的应用。如果你对下面这些都了如指掌，那么恭喜你！目测你已经晋级函数达人！ 语法 SUMIF(range, criteria, [sum_range]) 大白话 SUMIF(条件单元格区域，条件，求和单元格区域) 说明：求和区域跟条件区域的大小、形状可以不一样，计算时根据求和区域参数的左上角单元格往外扩展成跟条件区域大小、形状一样的区域。 经典用法一：基础用法 公式： 根据销售员求销量总和 =SUMIF(B2:B6,A9,D2:D6) 经典用法二：替代Vlookup 公式： 根据销售员查找对应的销量 =SUMIF($B$2:$B$6,A9,$D$2:$D$6) 说明： 求和区域中的内容是单行的，才能实现类似Vlookup的效果，并且，如果找不到，结果直接就是0，而不是错误值，这一点比Vlookup要方便。 这里只是为了给大家介绍这种应用实例，其实还是公式的最基础用法。 关于Vlookup的用法，点击下面链接直达。 经典用法三：使用通配符 公式： 通配符在条件中的应用 =SUMIF($C$2:$C$10,A13,$D$2:$D$10) 说明： 通配符有2个，星号*代表任意多个字符，问号?代表任意单个字符。 经典用法四：使用运算符 公式： 1、销量>50的销量的和 =SUMIF($D$2:$D$10,'>50') 2、销量>50的销售额的和 =SUMIF($D$2:$D$10,'>50',$E$2:$E$10) 3、低于平均销量的记录的总销售额 =SUMIF($D$2:$D$10,'<'&AVERAGE($D$2:$D$10),$E$2:$E$10) 4、销售额前三名的和 =SUMIF($E$2:$E$10,'>='&LARGE($E$2:$E$10,3)) 关于条件中使用运算符，下面这个问题需要注意，点击链接直达。 经典用法五：间隔求和 公式： =SUMIF($B$2:$B$7,$B9,C$2:C$7) 使用SUM函数的数组公式也可以完成，如下： =SUM(IF(MOD(ROW($B$2:$B$7),2)=0,C2:C7)) 说明： 这也是基础用法的形式，只是为了给大家介绍这种应用实例。 经典用法六：分组求和 公式： =SUMIF($C$1:$H$1,I$2,$C3:$H3) 说明： 这也是基础用法的形式，只是为了给大家介绍这种应用实例。 关于分组求和，请参考之前推送的另一种方法，点击下面链接直达。 经典用法七：动态求和 公式： 1、结合Offset、Match确定求和区域 =SUMIF(B2:B7,A10,OFFSET(B2:B7,,MATCH(B10,C1:E1,0))) 2、结合Index、Match确定求和区域 =SUMIF(B2:B7,A10,INDEX(C2:E7,,MATCH(B10,C1:E1,0))) 关于Offset的用法，请参考之前推送的文章，点击下面链接直达。 经典用法八：多条件求和 公式： 1、单元格区域作为参数 =SUM(SUMIF(B2:B10,A13:A14,D2:D10))，数组公式 2、直接输入数组作为参数 =SUM(SUMIF(B2:B10,{'Lily','Mary'},D2:D10))，数组公式 经典用法九：模糊匹配并去掉指定项 公式： 求包含空调、电视但是不包含商用电视的销售额 =SUM(SUMIF(C2:C10,{'*空调*','*电视*','商用电视'},E2:E10)*{1,1,-1}) 说明： 这是通配符、多条件求和的综合运用，注意最后面的*{1,1,-1}的用法，这里是跟条件参数{'*空调*','*电视*','商用电视'}对应的。 经典用法十：跨表求和 公式： 1、文本和数字数组作为Indirect参数生成工作表引用 =SUM(SUMIF(INDIRECT(''Data '&{1,2,3}&''!B:B'),D2,INDIRECT(''Data '&{1,2,3}&''!C:C'))) 2、直接输入工作表名称作为Indirect参数生成工作表引用 =SUM(SUMIF(INDIRECT({''Data 1'',''Data 2'',''Data 3''}&'!B:B'),D3,INDIRECT({''Data 1'',''Data 2'',''Data 3''}&'!C:C'))) 3、使用Row函数结合文本生成工作表名称作为Indirect参数来生成工作表引用 =SUM(SUMIF(INDIRECT(''Data '&ROW(1:3)&''!B:B'),D4,INDIRECT(''Data '&ROW(1:3)&''!C:C')))，数组公式 4、使用宏表函数生成工作表名称作为Indirect参数来生成工作表引用 =SUM(SUMIF(INDIRECT('''&ws&''!B:B'),D5,INDIRECT('''&ws&''!C:C')))， 数组公式 注意，需要创建名称ws。 ws=MID(GET.WORKBOOK(1)&T(NOW()),FIND(']',GET.WORKBOOK(1)&T(NOW())) 1,100) 5、跨表、多条件求和 =SUM(SUMIF(INDIRECT(''Data '&{1,2,3}&''!B:B'),{'Lily';'Cherry'},INDIRECT(''Data '&{1,2,3}&''!C:C')))
关于这种用法，在之前的文章中有详细介绍，点击下面链接直达。 经典用法十一：同时对多区域求和 公式： 1、求和区域参数只有起始单元格 =SUMIF($A$2:$D$6,A9,B2) 2、求和区域参数只有起始列 =SUMIF($A$2:$D$6,A10,B2:B6) 3、求和区域参数是完全的区域 =SUMIF($A$2:$D$6,A11,B2:E6) 说明： 最后一个参数会自动扩展成跟条件区域大小、形状一样的区域，所以上面三个公式都能得出正确结果。
经典用法十二：同时对多列求和 公式： =SUM(SUMIF(A2:A6,A9,OFFSET($A2,,COLUMN(B2:D2)-1))) 说明： 这是数组公式。Offset函数中的Column返回一个序列数，这样Offset返回多个单元格，分别对应到一月、二月、三月的数据所在的单元格，Sumif求和时会自动扩展求和区域。这是多个知识点的综合运用。
经典用法十三：错行、错列求和 公式： =SUMIF(C2:G5,'',B2:F5) 说明： 求每行最后一个非空单元格的和，使用了错列的技巧。
经典用法十四：忽略错误值求和 公式： =SUMIF(B2:B6,'<9E307') 说明： 9E307是科学计数法的写法，是一个非常大的数，现实应用数据中一般不会超过这个数。
|
|
|
