Excel中SUMIF函数的10个高级用法!超级经典~
发布时间:2022-07-20 13:55:02
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大经典用法就介绍完了,教程写的好辛苦啊!写得这么详细,有些知识百度都出来,希望能够对大家有用,更重要的是为大家提供一种解决问题的思路。
猜你喜欢
- 复制表格是办公中常用到的,但是很多伙伴在复制表格的时候都会遇到表格列宽和格式不一样。今天给大家分享在Excel表格中复制表格并保留表格原格式
- Excel如何制作斜线表头?制作表格的时候,经常会用到斜线表头,你知道斜线表头怎么做的吗?1、选中单元格,右键设置单元格格式-边框,再点击斜
- 而今天,网上又出现了版本号为16.0.3823.1005新版Office 2016截图,从截图来看这个版本变化不大,应该只是修复了部分Bug
- EXCEL做数据分析的时候,经常会遇到分段统计数量的问题。今天以学生成绩分析为例,来述如何使用FREQUENCY函数简单方便的统计各分数段的
- 在Office 2010中,Excel没有像其它主要的应用程序一样作过多的改动,但是增加了一些有用的功能。最重要的被称为“Sparkline
- WORD安全模式问题的现象是:双击WORD文档时,程序弹出出错对话框,显示“正在处理的信息有可能丢失,Microsoft Office Wo
- 这篇文章跟大家分享如何为Word中不同的表格统一应用相同的表格样式。案例一如果文档中的表格比较少,比如像下图这样只有2个,可以按住ctrl键
- Excel中的裁剪功能具体该如何使用呢?接下来是小编为大家带来的excel裁剪功能的使用方法,供大家参考。excel裁剪功能的使用方法(一)
- 相信很多小伙伴在日常办公时,都会用到Word文档这款软件。在Word文档中,我们可以设置使用功能智能段落选择功能,也就是我们在Word文档中
- word作为常用软件已经走进每个职场人的日常工作,但并不是所有人都会游刃有余的编辑Word文档,那么下面就由小编给大家分享下word设置奇偶
- Excel中经常需要使用到数据排名的技巧,表格内的数据具体该如何进行排名呢?下面是小编带来的关于excel表格数据排名的教程,希望阅读过后对
- 本文介绍Microsoft Excel中INT函数的语法和用法。函数说明INT函数的主要作用是将数字向下舍入到最接近的整数。函数语法INT(
- Microsoft Office Excel 2007 打开 Excel 2003 创建的文件在筛选时出现卡顿,要等待几分钟甚至更长时间才会
- Excel表格中的算式如何计算?在Excel中如果算式在一个单元格中,不能按照常规的求和方式计算结果,那么要怎么样快速的求出算式的结果呢?1
- 在excel2007录入数据的时候,我们很多时候都会选择用多斜线来表明不同的含义。下面让小编为你带来excel2007制作多斜线的方法。ex
- 很多使用word编辑的朋友希望页眉和文件名的一致,并能随着文件名同步更新。那么在word文档中怎么自动插入页眉内容呢?下面小编就为大家详细介
- 今天要给大家分享的这个功能是office 2016新功能,应该还很少人知道这个功能,因为这个功能是最新版office2016才有的功能叫平滑
- 当我们重新加载win10操作系统时,我们发现在进入磁盘分区步骤时,系统提示我们无法创建新分区或找到现有分区。在这种情况下,小编认为可以尝试重
- 在工作中做的一些表格数据有时非常重要,一般属于公司的 * ,为了保证文件的安全性我们会选择对文件进行加密,你知道吗,其实excel自身就具
- 1、创建一个表格并打开它。 2、打开表格后,我们举个例子,在A1,B1中分别输入数字1,2。 &nb