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

SUMIF函数可以对满足指定条件的范围内的值求和。该函数具有非常强大的条件求和功能,在工作中应用广泛。在上一篇文章中,Poppy姐也介绍了该功能的一些经典应用场景。

不过今天Poppy姐就带大家了解一下SUMIF函数,因为它不仅有漂亮的外观,还有丰富的内涵。(当然主要还是有朋友说之前没看懂。如果这次他们再不懂,他们会懂的...仅仅...给妹妹发红包,手把手教!)

1.单字段单条件求和

计算“杨过”的总销售额,公式为SUMIF(D2:D16,& # 8221;杨过& # 8221;,E2:E16)

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

单字段单条件求和

2.单字段多条件求和

有两种方法可以计算杨过和杨晓的总销售额。

方法:在SUMIF的帮助下,计算杨过的总销售额+杨晓的总销售额。

公式:SUMIF(D2:D16,& # 8221;杨过& # 8221;,E2:E16)+SUMIF(D2:D16,& # 8221;杨晓& # 8221;,E2:E16)

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

方法1

2.借助SUM和SUMIF,单字段多条件求和,实现SUM(杨过销售,杨晓销售)。

公式:SUM(SUMIF(D2:D16,{ & # 8220;杨过& # 8221;,”杨晓& # 8221;},E2:E16))

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

方法2

注:SUMIF(D2:D16,{ & # 8220;杨过& # 8221;,”杨晓& # 8221;},E2:E16)是一个数组公式,引用{杨过销售额,杨晓销售额}的结果,然后用求和公式求和,得到杨过和杨晓的总销售额。

3.带日期的单字段多条件求和

有两种方法可以计算8月20日到9月20日的总销售额。

1.借助SUM和SUMIF,单字段多条件求和,实现SUM(8月20日后销售,9月20日前销售)。

公式:SUM(SUMIF(A2:A16,{ & # 8220;& gt=2016/8/20″,”& gt2016/9/20″},E2:E16)*{1,-1}).

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

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

方法1

2.可以用SUMIFS实现多条件求和,同时满足8月20日之后和9月20日之前的销售。

公式:SUMIFS(E2:E16,A2:A16,& # 8221;& gt=2016-8-20″,A2:A16,& # 8221;& lt=2016-9-20″)

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

Sumifs多条件求和

注意:SUMIFS函数是Excel 2007及更高版本中的新函数。在Excel 2003中无法使用,但可以使用SUMIF函数的常用公式(如方法1)实现。

4.模糊条件的和

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

计算名为“杨”的销售人员的总销售额,公式为SUMIF(:D16,& # 8221;杨* & # 8221;E2:E16).

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

模糊条件求和

5.多列区域求和

计算每个月的借贷总额。

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

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

多列区域求和

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

6.交错列的总和

杨过的总销售额。

公式:SUMIF(A1:C16,& # 8221;杨过& # 8221;,B1:D16).

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

交错列的总和

注意:搜索条件为业务员“杨过”,条件区为业务员,求和区向右偏移一列,表示统计业务员右列的数据,即每个业务员的销售额。

7.总结错误的台词

计算销售人员的总销售额。

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

2公式公式:SUMIF(B1:B8,& # 8221;*”,B2:B9).

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

交错行的总和

注意:搜索条件为“*”,表示搜索0个或0个以上字符,求和区域从搜索区域向下偏移一行,表示计算下一行0个或0个以上字符单元格的数据,即每个销售人员的销售额。

8.查找参考资料

说到查找引用,可能很多朋友会说,查找引用不应该是VLOOKUP函数或者INDEX+MATCH函数吗?SUMIF函数和它有什么关系?这只是一个求和函数。

事实上,在大多数情况下,不需要SUMIF来查找参考资料。但是当求和区域只有一个合格的数值时,求和的结果就是数值本身。因此,可以使用SUMIF来查找引用。

现在需要匹配郑州、长沙、武汉、合肥的销量、单价、销量、利润。

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

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

查找参考

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

9.消除误差值的总和。

计算全国总销售额。因为销售量中存在不同类型的误差值,所以不能直接使用SUM,但是可以使用SUMIF函数来消除误差值。

公式:苏米夫(B2:B16,& # 8221;& lt9e307 & # 8243、B2:B16)

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

排除误差值的总和

注:9e07在科学记数法中是9 * 10 ^ 307,是一个接近Excel允许的最大值9 * 307的数字. 400000000005

这里是指对小于最大值9e07的数据求和,也就是对“值”单元格求和。用9e07不是规则,不是原则,而是常用。

当然,也可以使用“< 00”或“< 000”以及任何大于最大值253的值。

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

替换9e07的结果不变。

10.跨表条件求和

哦,不!你居然以为Excel的SUM函数只有SUM?在这篇文章中,我姐介绍了跨表求和,一种比较基础的求和方法。今天我就给大家展示一下如何在跨表求和的基础上满足一定的条件。

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

原始资料

如果一月只有一张表,总公式为:SUMIF(& # 8216;一月& # 8217;!$A:$A,$A2,& # 8217;一月& # 8217;!$B:$B)

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

单表摘要

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

最后的公式是:

= SUMPRODUCT(SUMIF(INDIRECT(ROW $ 1:$ 3)& amp;”月!$ A:$ A & # 8221;)、$A2、间接(ROW $ 1:$ 3)& amp;”月!$ B:$ B& # 8221;)))

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

跨表求和公式分析

好了,已经介绍了SUMIF函数的十个经典用法。教程写的多辛苦啊!写的这么详细,一些知识百度出来了,希望对大家有用,更重要的是为大家提供解决问题的方法。