当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel中SUMIF函数的10个高级用法!超级经典~

酷网1个月前 (10-22)Excel教程29

SUMIF函数可以对范围中符合指定条件的值求和,该函数拥有十分强大的条件求和功能,在工作中有极其广泛的应用,在之前的文章中,罂粟姐姐也给大家介绍了该函数的一些经典应用场景。

但是,今天,罂粟姐姐将带大家深入了解SUMIF函数,因为它不仅有漂亮的外表,还有丰富的内涵。(当然,最主要原因是之前有小伙伴表示没看懂,这一次再看不懂就……就……就给姐姐发红包,手把手教!)

1、单字段单条件求和

计算“杨过”总销售额,公式:SUMIF(D2:D16,”杨过”,E2:E16)

 

单字段单条件求和

2、单字段多条件求和

计算“杨过”和“杨逍”两个人的总销售额,共有两种方法。

方法1:借助SUMIF进行数学运算,杨过总销售额+杨逍总销售额。

公式:SUMIF(D2:D16,”杨过”,E2:E16)+SUMIF(D2:D16,”杨逍”,E2:E16)

 

方法1

方法2:借助SUM和SUMIF实现单字段多条件求和,SUM(杨过销售额,杨逍销售额)。

公式:SUM(SUMIF(D2:D16,{“杨过”,”杨逍”},E2:E16))

 

方法2

注意:SUMIF(D2:D16,{“杨过”,”杨逍”},E2:E16)是一个数组公式,指代的结果是{杨过销售额,杨逍销售额},再用SUM公式求和,即可得到杨过与杨逍的总销售额。

3、包含日期的单字段多条件求和

计算8月20日至9月20日总销售额,有两种方法。

方法1:借助SUM和SUMIF实现单字段多条件求和,SUM(8月20日以后的销售额,9月20日之前的销售额)。

公式:SUM(SUMIF(A2:A16,{“>=2016/8/20″,”>2016/9/20″},E2:E16)*{1,-1})。

相当于=SUMIF(A2:A16,”>=2016/8/20″,E2:E16)-SUMIF(A2:A16,”>2016/9/20″,E2:E16),即8月20日至9月20日的销售额=8月20日以上的销售额-9月20日以后的销售额。

 

方法1

方法2:借助SUMIFS实现多条件求和,同时满足8月20日以后和9月20日之前两个条件的销售额。

公式:SUMIFS(E2:E16,A2:A16,”>=2016-8-20″,A2:A16,”<=2016-9-20″)

 

sumifs多条件求和

注意:SUMIFS函数是 Excel 2007及以后版本的新增函数,不能在 Excel 2003 中使用,但是可以使用SUMIF函数的普通公式来实现(如方法1)。

4、模糊条件求和

SUMIF语法中criteria参数中支持使用通配符(包括问号“?”和星号“*”)。关于通配符的介绍,请移步姐姐之前的文章:妙用Excel通配符,让工作效率倍增。

计算姓“杨”销售员的总销售额,公式:SUMIF(D2:D16,”杨*”,E2:E16)。

 

模糊条件求和

5、多列区域求和

计算各月借方和贷方合计。

公式:SUMIF($B$2:$I$2,J$2,$B3:$I3)。

 

多列区域求和

注意:选中单元格,按F4,则单元格绝对引用,再按一次F4,则单元格相对引用锁定行,再按一次F4,则单元格相对引用锁定列,尽量避免一个个输入“$”符号。

6、错列求和

杨过的销售总额。

公式:SUMIF(A1:C16,”杨过”,B1:D16)。

 

错列求和

注意:查找条件为销售员“杨过”,条件区域为销售员,求和区域比查找区域往右偏移一列,意思是统计销售员右一列的数据,即各销售员的销售额。

7、错行求和

计算销售员总销售额。

方法1公式:SUMIF(A1:A8,A2,B1:B8),与基本用法一致。

公式2公式:SUMIF(B1:B8,”*”,B2:B9)。

 

错行求和

注意:查找条件为“*”,说明查找的是0个或多个字符,求和区域比查找区域往下偏移一行,意思是统计0个或多个字符下一行单元格的数据,即为各销售员的销售额。

8、查找引用

一说到查找引用,可能很多小伙伴会说,查找引用不是应该是VLOOKUP函数或者INDEX+MATCH函数吗?关SUMIF函数什么事儿呢?它只是一个求和函数而已。

的确,在绝大多数时候,查找引用不需要SUMIF,但是当求和区域符合条件的数值只有一个时,求和得到的结果就是数值本身,因此可以借助SUMIF来实现查找引用。

现需匹配郑州、长沙、武汉、合肥4个省会城市的销售量、单价、销售额、利润额。

公式:SUMIF($A$2:$A$16,$G2,B$2:E$16)。

 

查找引用

注意:当原始数据表中有两个及以上郑州、长沙等省会城市名称时,销售量、单价、销售额、利润额等结果均为求和以后的结果。

9、排除错误值求和

计算全国销售总量。由于销售量中有不同类型的错误值,所以不能用SUM直接求和,可以使用SUMIF函数实现排除错误值求和。

公式:SUMIF(B2:B16,”<9e307″,B2:B16)

 

排除错误值求和

注意:9e07是科学记数法表示的9*10^307,是接近Excel允许键入的最大数值9.99999999999999E+307的一个数。

在这里表示对小于最大值9e07的数据进行求和,也就是对“数值”单元格进行求和。使用9e07不是规定,不是原则,是大家约定俗成的用法。

当然结合案例,也可以使用“<500”或者“<1000”等任意大于最大值253的值。

 

替换9e07结果仍然保持不变

10、跨表条件求和

在Oh,NO!你竟然以为Excel求和函数只有SUM?一文中姐姐介绍过跨表求和,较为基础的求和方式,今天再给大家介绍如何在跨表的基础上还能满足一定的条件进行求和。

 

原始数据

如果只有1月这一个表,则合计公式为:SUMIF(‘1月’!$A:$A,$A2,’1月’!$B:$B)

 

单表汇总

实现跨表条件求和需要借助INDIRECT函数(对文本描述的单元格引用,也就是说INDIRECT的括号里的参数是一个字符串(文本)描述的文本形式,INDIRECT取得这个引用。)

最终公式为:

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&”月!$A:$A”),$A2,INDIRECT(ROW($1:$3)&”月!$B:$B”)))

 

跨表求和公式分析

好啦,SUMIF函数的10大经典用法就介绍完了,教程写的好辛苦啊!写得这么详细,有些知识百度都出来,希望能够对大家有用,更重要的是为大家提供一种解决问题的思路。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40294.html

分享给朋友:

相关文章

COUNTIF函数在文本排序中的应用

COUNTIF函数在文本排序中的应用

我们知道,COUNTIF函数通常用于查找指定单元格区域中满足条件的单元格数量。然而,COUNTIF函数有一个比较有用的用法,它可以统计指定区域中大于或小于指定值的单元格数量。例如,公式: COUNTIF({a,b,c,d...

Excel表格如何使用行列拼接功能?方方格子工具教你快速使用行列拼接

Excel表格如何使用行列拼接功能?方方格子工具教你快速使用行列拼接

  Excel表格如何使用行列拼接功能?相信许多小伙伴都有使用过Excel表格来制作一些数据,但是当我们将一些数据在录入的时候没有注意顺讯,导致数据混乱不好找,这个时候我们就可以使用方...

利用excel数据透视表对数据进行分段统计

利用excel数据透视表对数据进行分段统计

前两天,朋友问有没有方法对几个班级的考试成绩进行各分数段人数统计?今天就来分享一种利用数据透视表对数据进行分段统计的方法。 关键提示 原数据: 第一步:建立数据透视表 列区域为“班级”,行区域为“分数值”,数据区域为...

怎么给Excel图表加一条Y轴?Excel图表加Y轴

怎么给Excel图表加一条Y轴?Excel图表加Y轴

如果能给Excel表格里的图表再加一条数据线的话会很有用。但要是数据单元不一样,你可能会觉得无法实现这一点。别担心,其实你可以做到的,而且这也相当简单!中文版步骤打开要建立次坐标轴的数据,选中数据,然后【插入】,在【图表】功能区选择柱形图,...

Excel图表,怎么做出双坐标轴?

Excel图表,怎么做出双坐标轴?

最近几天,留言请教图表问题的不少,就来做几期图表知识的解。 今天 一位朋友的问题: 那怎么做三个坐标刻度的表啊? 这里说的三个刻度,是指:X轴、两个Y轴,如下图: 步骤分解第一步:插入图表 选中数据:【插入】——【图表...

获取每行中第一个非空单元格

获取每行中第一个非空单元格

如何使用公式获取每行中第一个非空单元格?例如下所示工作表,要求使用公式根据上半部分的表格求各项目的开工日期。 也就是说,要求出单元格区域B2:G6的每行中第一个非空单元格对应的B1:G1中的日期。 先不看答案,自已动手...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。