推广 热搜: 未来    中国  企业  服务  政策  公司  快速  上海  设备 

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

   日期:2024-11-19     作者:xasic    caijiyuan   评论:0    移动:http://xiaoguoguo.dbeile.cn/mobile/news/1065.html
核心提示:编按:SUMIF函数作为一个条件求和函数,竟然还能实现诸多的查找功能?确实!如果查找的是数字,不管是多表查找还是逆向查找、横

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

【原】Excel函数应用技巧:全面解读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函数就会把它们加在一起,因为它本质上是一个求和函数。

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

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


相关推荐:

八大查找函数公式,轻松搞定数据中的多条件查找

10种职场人最常用的excel多条件查找方法!(建议收藏)

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文地址:http://xiaoguoguo.dbeile.cn/news/1065.html    多贝乐 http://xiaoguoguo.dbeile.cn/ , 查看更多
 
 
更多>同类行业资讯
0相关评论

新闻列表
企业新闻
推荐企业新闻
推荐图文
推荐行业资讯
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  排名推广  |  广告服务  |  积分换礼  |  网站留言  |  RSS订阅  |  违规举报  |  鄂ICP备2023001713号