【原】Excel函数应用技巧：全面解读SUMIF函数的查找匹配功能

【原】Excel函数应用技巧：全面解读SUMIF函数的查找匹配功能

2024-11-19 03:12

编按：SUMIF函数作为一个条件求和函数，竟然还能实现诸多的查找功能？确实！如果查找的是数字，不管是多表查找还是逆向查找、横向查找，SUMIF都是最快的。下面我们介绍8种SUMIF查找运用，全面解读SUMIF的查找功能。

查找函数，多年来，VLOOKUP一直独领风骚。

人红脾气大，比如常让人诟病的：不能逆向查找；不能表里不一；脸盲症中度患者；中二症重度患者等等。

有没有函数能治治VLOOKUP这些臭毛病呢？

还真有！他就是SUMIF函数。

SUMIF用于查找，其函数结构也只有区区三个参数。

=SUMIF(条件区域,指定的条件,查找区域)

千万不要小看这三个参数，以为他解决不了复杂的问题。

和那些爱嵌套的函数（对，VLOOKUP，说的就是你）相比，SUMIF崇尚极简主义，一不爱找函数帮忙，二不爱省略参数，一贯的作风就是将复杂问题简单化。

1. SUMIF可以在多张表中查找数字

在多张表中查找数字用什么函数最快？当然是SUMIF函数！如下图所示，当我们需要查询多个表格里的数据，比如工号为1003、1004、1011、1012的考试得分，但这些员工分别在两个表中。

如果要用VLOOKUP来写公式的话，又得寻求其他函数的帮助了。

与其说他是查找之王，不如说他是交际之花。

VLOOKUP：各位大哥，行行好~

众函数：你怎么又来了！

函数公式：=IFERROR(VLOOKUP(I2,$A$2:$C$9,3,0),VLOOKUP(I2,$E$2:$G$9,3,0))

这里是用两个VLOOKUP函数的查找结果来作为IFERROR的两个参数，如果不懂这个函数的同学可以看我们的IFERROR专题文章。

用SUMIF来解决这个问题就简明扼要多了，什么嵌套，什么求助，通通没有！小爷我遇神杀神，遇佛杀佛。

函数公式 =SUMIF(A$2:E$9,I2,C$2:G$9)

注：多张表必须位于同一张工作表中。

2.SUMIF可以轻松实现逆向查找数字

VLOOKUP默认从左向右查询，如果要用VLOOKUP返回左边列的效果，有些“高手”习惯用IF({0,1},,)这种句式去重构数组，确实是解决了一时的问题，但在我看来，往这个方向走，你会很容易“走火入魔”，发展受限。

函数公式：

=VLOOKUP(E2,IF({1,0},B$2:B$12,A$2:A$12),2,0)

用SUMIF来解决这个问题，轻而易举。

还是平平无奇的三个参数，条件区域、查找区域、条件，挨个往上套即可。

函数公式：

=SUMIF(B$2:B$12,E2,A$2:A$12)

3.SUMIF可以进行横向查找数字

VLOOKUP不支持横向查询，如果你非得指名道姓要他，那VLOOKUP也只得请出自己的兄弟HLOOKUP函数。

这是一个专门用于横向查询的函数，其参数如下：

要查找的值,要查找的区域,返回第几行,模糊匹配/精确匹配函数公式：=HLOOKUP(B7,$B1:$L3,3,0)

横向查询对于SUMIF来说，依然是小菜一碟，依葫芦画瓢即可。

函数公式=SUMIF($B1:$L1,B7,$B3:$L3)

4.条件区域和求和区域位于多行SUMIF也可以查找数字

当条件区域和求和区域位于多行，怎么办？就算是HLOOKUP想必也傻眼了吧，但是这种级别的问题，根本难不倒SUMIF。

函数公式 =SUMIF($B1:$G5,B10,$B2:$G6)

这个功能其实是运用了SUMIF的错行求和的原理，因为每个业务人员名单不重复，所以求和即是查找。

当条件区域和求和区域是多行多列时，SUMIF会依次去判断条件区域是否满足条件，如果满足，则将查找区域的数据提取出来，得到结果。

5.被查找值与条件值数据格式不一样也能用SUMIF查找使用VLOOKUP进行查找时，讲究的是表里如一，除开大小写区分外必须严格相同。这就导致他经常在查找上出错，相同的数据，只因一个是文本，一个是数值，那结果必然就是错误！

SUIMF显然没有这些穷讲究，只要条件符合，就能立马匹配到数据。

函数公式：=SUMIF(A1:A12,E3,C1:C12)

条件区域是A1到A12，条件值是E3，要返回的区域是C1到C12。

即我们去A1到A12的数据区域查询E3这个值，然后返回对应的C1到C12里的某个值。

6.SUMIF查找不会返回错误值，表格更美观

VLOOKUP查找不到就会立刻返回错误值，他才不会管大量的错误值堆积，有没有影响到表容表貌。

如果你想要屏蔽掉错误值，那还得用IFERROR函数来帮帮忙。

函数公式=IFERROR(VLOOKUP(E2,$A$1:$C$12,3,0),"")

SUMIF对待错误值显然温和了很多，查找不到，会返回0，这让整张表看上去整洁了不少。

函数公式=SUMIF(A$1:A$12,E2,C$1:C$12)

7.SUMIF可以查找多个值并求和现在我们要根据学历、职称、技能等级查找得分，然后求3个得分之和。

比如王虹青，学历本科加2分，中级工程师加2分，中级技能加2分，一共6分，怎么做？

使用VLOOKUP函数的话，我们需要查找3次，最后再来套一个SUM函数。

函数公式：

=SUM(IFERROR(VLOOKUP(B2,$B$11:$C$14,2,0),0),IFERROR(VLOOKUP(C2,$B$15:$C$16,2,0),0),IFERROR(VLOOKUP(D2,$B$17:$C$19,2,0),0))

公式原理也不难，就是像叠罗汉一样，3个VLOOKUP层层嵌套，用IFERROR函数将查找不到的值返回为0，最后用sum函数来求和。

这个函数没有问题，但实在是太长了，就像老太娘的裹脚布。

好巧不巧，这个问题，SUMIF也能行，比如我们要查找王虹青在学历这一栏的得分。

函数公式=SUMIF($B$11:$B$14,B2,$C$11:$C$14)

那我们是不是又要嵌套3个SUMIF呢？

答案肯定不是啊！

什么档次？和VLOOKUP一样？那不是往SUMIF脸上打吗？

直接修改SUMIF的第二参数，将条件B2修改成$B2:$D2，即可一次性查找全部。

=SUMIF($B$11:$B$19,$B2:$D2,$C$11:$C$19)

注意：不是Office365版本的朋友，公式输完后不能直接回车，否则得到的结果是错的。如果要查看运算结果，可以在编辑栏选中整个公式，按F9键，3个结果分别是{2,2,2}。再按Ctrl+Z返回。

要获取这3个结果之和，外套一个SUM即可搞定，因为是数组公式，需要按Ctrl+Shift+Enter三键结束。

=SUM(SUMIF($B$11:$B$19,$B2:$D2,$C$11:$C$19))

这个公式很好地诠释了一个道理：条条大路通罗马，但有的人一出生就在罗马。

8.SUMIF可以进行模糊查找

上面这些示例，其实很好地说明了一个问题：

SUMIF的第二参数是三个参数中唯一的变量，他可以是单个条件，也可以是多个条件。

他还可以与通配符使用，达到模糊查找的功能，这一点与VLOOKUP不分高低。

如下图所示，我们要查找这些业务员的考核得分，在条件区域中，有些人的名字是连在一起的。

可以用通配符*与连接符&来完成这个公式。

函数公式=SUMIF(A$2:A$9,"*"&D2&"*",B$2:B$9)

【原】Excel函数应用技巧：全面解读SUMIF函数的查找匹配功能

SUMIF简单三把斧，走遍天下无敌手。

有人可能会问，既然SUMIF那么好用，那为什么声名赫赫的却是VLOOKUP呢？

难道他有后台？

答案是： SUMIF虽好用，却有两个限制条件，这两个限制条件会让SUMIF的查找功能大打折扣。

限制1：结果必须为数字

上面演示的案例，它们的结果都是数值。当且仅当查询的结果是数值的时候，才可以使用 sumif进行查询，因为它本质上是一个求和函数。

限制2：查找值不允许存在重复

利用sumif进行数据查询，如果查找值存在重复，sumif函数就会把它们加在一起，因为它本质上是一个求和函数。

求和的本质，限制着查找功能的使用。

这就好比上天入地无所不能的孙悟空，头上永远戴着一个紧箍咒一样。