未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
细数LOOKUP系列函数在Excel数据查询中的应用

​数据查询是Excel数据处理中的一项核心业务,也是日常办公中使用频率非常高的一项操作 。而LOOKUP系列函数则是数据查询最基础的方法,也是日常办公中必须掌握的一项Excel技能。

LOOKUP系列函数指LOOKUP、VLOOKUP、HLOOKUP这三个函数(以及其他的扩展函数),其中应用最广泛的自然要数VLOOKUP了。本文将会逐一介绍VLOOKUP函数的各种应用场景,及其相应的方法技巧,并深入分析每种查询方法的特点和注意事项。同时,对查询操作涉及到的其他典型函数(或组合)应用进行简要介绍。

本文内容较长,先给大家梳理一下文章目录。

一、目录

二、Excel中的数据查询基础知识

三、正向查询和逆向查询

1.用VLOOKUP进行正向查询

2.用VLOOKUP进行逆向查询

3.更加便捷的查询方法INDEX+MATCH

四、用VLOOKUP进行精确查询和模糊查询

五、多条件多结果查询

1.一对一查询

2.一对多查询

(1)用VLOOKUP函数,借助辅助列查询多个结果

(2)借助“Excel扩展函数包”查询多个结果

(3)用INDEX+SMALL+IF函数组合查询多个结果

3.多对一查询

4.多对多查询

六、总结

二、Excel中的数据查询基础知识

如下图所示的一张表格,如果按照【姓名】查询对应【身份证号】叫作“正向查询”反之则叫作“逆向查询”。

如果查询包含“渔坪村”这个关键字的信息,叫作“模糊查询”,反之如果查找“高峰镇渔坪村3组”这个完整字段信息,则叫作“精确查询”。

如果是查询【身份证号】对应的【姓名】,叫作一对一查询,即一个条件一个查询结果。同理,根据【姓名】【班级】这两个字段,来查找对应的【身份证号】则叫作多对一查询,比如下表中的“李杰”有两个,一个在2班,一个在3班,只根据【姓名】一个字段,无法准确定位到正确的身份证号,因此需要进行“多对一查询”(也叫多条件查询)。至于一对多查询、多对多查询也是类似的道理。

三、正向查询和逆向查询

  1. 用VLOOKUP进行正向查询

正向查询是VLOOKUP函数最基础的用法。如下图所示,我们要查找金凯风的班级信息,只需在I3单元格输入公式=VLOOKUP(H3,A2:F17,2,0),确定即可。

这个公式的基本原理如下图所示。我们在A2:F17这个范围来查询H3单元格(金凯风)对应的信息,返回对应查询范围A2:F17的第2列(班级)信息,最后一个参数一般为0(或FALSE),表示精确查找。

后面介绍的所有关于VLOOKUP函数的查询方式也都是基于上述原理,只是稍加变通而已。

  1. 用VLOOKUP进行逆向查询

从本质上说,VLOOKUP函数只能进行上述类似的正向查询,即条件列必须在结果列之前。但是我们可以通过互换两列的位置来实现逆向查询。当然这里的互换位置不是真的要改变原表结构,而是通过数组重装的的方式来实现。

比如,如下图所示,现在要根据身份证号查询对应姓名信息,可以用如下公式实现:

=VLOOKUP(H3,IF({1,0},C2:C17,A2:A17),2,0)

这里的第2个参数依然表示查询范围,但却并不像A2:F17这么直接,而是用一个IF函数互换了C列和A列数据的前后顺序。相当于查询范围变成了如下图所示的结果,它是一个16行,2列的数据块儿。我们需要返回的结果在这个范围的第2列,因此第3个参数才是2。最有一个参数为0,同样表示精确查找。

我们可以选中上述公式的IF({1,0},C2:C17,A2:A17)部分,按F9键查看这个重装之后的数组。

  1. 更加便捷的查询方法INDEX+MATCH

其实在Excel中有这么一个函数组合,可以无视查询条件和返回结果的前后顺序,逆向查询和正向查询一样简单,它就是INDEX+MATCH函数组合

如下图所示,在I3单元格输入公式=INDEX(C2:C17,MATCH(H3,A2:A17,0))即可得到查询结果。

INDEX+MATCH函数组合的工作原理如下:

四、用VLOOKUP进行精确查询和模糊查询

在90%以上的情境中我们用的都是精确查询,正如上文所示的查询案例一样。但有时候模糊查询也能取得意想不到的效果。

比如我们现在要查询一个“渔坪村”的学生,就只能根据家庭住址中包含“渔坪村”关键字的信息进行查询。其实原理也很简单,就是利用通配符*来进行模糊匹配。

如下图所示,查询公式为=VLOOKUP("*"&H3&"*",IF({1,0},D2:D17,A2:A17),2,0)

五、多条件多结果查询

  1. 一对一查询

上文所示的正向查询和逆向查询案例,都是一对一查询的典型应用,也即根据一个查询条件,查询得到一个结果。因此VLOOKUP的一对一查询就不再赘述。

  1. 一对多查询

一对多查询,即根据一个条件查询出多个满足条件的结果。比如,我们可以一次性查询出所有的贫困户学生的姓名。虽然通过筛选也可以做好这件事,但很多时候我们需要的只能是查询的方式。

这里有3种方法,第1种方法是Excel原生的VLOOKUP查询,但是需要借助一个辅助列,步骤相对稍微复杂;第2种方法非常简单,但是需要借助一个叫做“Excel扩展函数包”的插件;第3种方法最复杂,是利用INDEX+SMALL+IF函数组合来实现一对多查询。下面逐一介绍。

(1)用VLOOKUP函数,借助辅助列查询多个结果

第一步,建立一个辅助列,一般情况下最好是在表格的最前面插入一列,方便正向查询,如下图所示。

然后,在A3单元格输入公式=($I$3=F3)+N(A2),其作用是用来统计F3:F17这个范围内,“是”第几次出现。

这个公式可以这么来理解:

$I$3=F3判断I3和F3是否相同,如果相同返回TRUE,在计算中会自动转化为数字1。注意这里的$I$3这种绝对引用方式,其目的是为了在向下拖动的过程中,I3单元格的引用始终不变,即始终拿I3和F列数据进行比对。

公式后面的+N(A2)是最巧妙的地方,如果没有这部分,那么辅助列返回的就是1,1,0,1,0,0...这样的结果。N(A2)函数是用来取得A2单元格的数值,如果是非数值,则返回0,这样才能得到累加的效果。

第二步,用VLOOKUP进行查询。

在J3单元格输入公式=VLOOKUP(ROW(A1),$A$2:$B$17,2,0),然后向下拖动(或双击右下角)填充至最后一行。这样就查询出了所有的贫困户学生姓名。

这个公式中查询条件大家也发现了,并不是I3单元格,而是ROW(A1),而ROW(A1)=1,再向下拖动过程中公式中的ROW(A1)会变成ROW(A2)、ROW(A3)...对应数字1、2、3...也就是说我们是在查询贫困户第几次出现,即A列对应的信息。

有了前文的正向查询的基础,公式剩下的部分就好理解了。

第三步,用IFERROR函数美化查询结果。

我们也发现了除了查询出所有贫困户学生姓名,还在末尾出现了一些#N/A符号,它表示查询不到结果。我们可以通过在原公式外面套一个IFERROR函数即可解决问题。对应公式为=IFERROR(VLOOKUP(ROW(A1),$A$2:$B$17,2,0),"")表示,如果查询不到则留空。

注意事项:公式中的第一个参数一定要采用$A$2:$B$17这样的绝对引用方式。

(2)借助“Excel扩展函数包”查询多个结果

第一步,先去“方方格子”官网下载“Excel扩展函数包”,根据自己电脑安装的OFFICE版本,选择安装32位还是64位,一般情况下安装32位即可。

然后就可以在Excel中进行一对多查询了。

这里用到的函数是MLookup,它和VLOOKUP函数非常类似,比如查询所有姓名为“李杰”的班级信息,只需在I3单元格输入公式=MLookup(H3,A2:B17,2,-1)即可,返回的所有查询结果会用逗号分隔。

MLookup函数最后一个参数表示返回第几个查询结果,如果为-1,则表示返回所有查询结果。

这个MLookup函数虽然好用,但是也有缺点,它无法进行逆向查询,比如无法查询出所有贫困户学生的姓名。其原因在于MLookup不支持数组查询,所以通过重整数组顺序进行逆向查询的方法是行不通的。

(3)用INDEX+SMALL+IF函数组合查询多个结果

其实一对多查询在日常使用中很少用VLOOKUP函数,更多的则是使用INDEX+SMALL+IF函数组合来实现。

先在I3单元格输入如下公式,然后按CTRL+SHIFT+ENTER三键确定,最后用鼠标拖动向下填充即可。

=IFERROR(INDEX($A$1:$A$17,SMALL(IF($E$3:$E$17=$H$3,ROW($A$3:$A$17)),ROW(A1))),"")

这是一个数组公式,因为相对比较复杂,就不做详细解释,其主要思路是用SMALL和IF两个函数配合,查询对应行号,然后用INDEX函数定位对应行的数据。

  1. 多对一查询

“多对一查询”又叫“多条件查询”,比如根据【姓名】和【班级】查询对应的身份证号。

如下图所示,叫“李杰”的同学有两个,无法根据【姓名】这一个条件精确定位其【身份证号】,还需要一个【班级】条件。

这个时候VLOOKUP就有些不太好用了,还得LOOKUP函数来救场。如下图所示,我们在J3单元格输入公式=LOOKUP(1,0/((A2:A17=H3)*(B2:B17=I3)),C2:C17),回车确定,这样就可以查询到2班李杰的身份证号。

LOOKUP函数在进行多条件查询中的应用可以套用如下公式:

LOOKUP(1,0/(条件1*条件2*...*条件n),返回结果的区域)

LOOKUP函数有一个特点,就是如果找不到查询值,则返回查询区域中小于或等于查询值的最大值。正是利用LOOKUP函数的这个特点,才能实现多条件查询。

  1. 多对多查询

多对多查询可以看做是“一对多查询”和“多对一查询”的组合,这里用前面介绍的INDEX+SMALL+IF函数组合来做。

如下图所示,在J3单元格输入如下公式:

=IFERROR(INDEX($A$1:$A$17,SMALL(IF(($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3),ROW($A$3:$A$17)),ROW(A1))),"")

然后,还是用CTRL+SHIT+ENTER三键确定输入。

其实参考前文介绍的“用INDEX+SMALL+IF函数组合查询多个结果”,这个公式唯一的变化就是把条件由$E$3:$E$17=$H$3变成了($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3),即两个条件相乘的形式。现在如果让你根据三个条件查询,也是轻而易举的,是需要稍微修改上述公式即可。

六、总结

Excel中的数据查询是一个相对比较大的话题,文章中介绍的LOOKUP系列函数查询应用示例,仅占其中的一小部分,更多的相关内容,以后再慢慢给大家分享。也欢迎大家分享自己在日常办公中的一些Excel使用经验。

关于文中的一些相对复杂的公式并没有做太过深入的解读,因为任何一个公式单独拿出来讲,其都足以写出一篇长文。但如果有朋友对其感兴趣,想深究其原理,也可以私信交流。

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:方寸星河  > 办公技巧
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
Excel | 空格——交叉运算符,秒杀INDEX、VLOOKUP、HLOOKUP、LOOKUP查询...
分手吧Excel IF函数,我有这六个更帅气的云备胎
Excel查询函数Lookup和Vlookup区别
【函数攻略】5个常用Excel函数套路
Excel合并单元格真是大忌!还好我会一些高级函数!
4组超级变态的Excel函数公式,好用到哭!
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!