认识这对搭档,解决 90% 的查询问题(搭档很重要英文怎么写)

在excel里,对于“查找”的实现,vlookup绝对是使用得最为频繁的一个函数。

但是,遇到下面问题,vlookup就没用了。

下面的表格记录了员工的信息,现在想通过“姓名”查找对应的“工号”。如图所示,通过输入不同的姓名,就会返回对应的工号。

在原数据里,“工号”在A列,“姓名”在B列,如果是通过工号来查询对应的姓名,用vlookup函数就能秒杀。但现在是通过B列来查询对应的A列的内容,是反向查询。

vlookup函数在查询的时候只能从左往右查询,且查询对象所在的列,必须要在查询区域的第一列,也就是说,只能通过A列来查询B列或其它列,而不能通过B列来反向查询A列。

你可能会说了,把原数据里的A列和B列调换一下顺序不就轻易地避开了上面的问题吗?但是,这样做原始数据就发生了改变,在工作中很多时候我们拿到的表数据,标题中是会有合并单元格的,这就更限制了列的移动。

像这种反向查询问题,就必须祭出我们的“大杀器”了:index+match组合,你就可以更灵活地实现查询。

1. 什么是index?

index函数能根据指定的行号和列号来返回一个值。语法规则如下:

index(单元格区域,第几行,第几列)

单元格区域:就是要查找的数据范围;

第几行:在查找范围的第几行;

第几列:在查找范围的第几列。(其中“第几列”是可以省略的。)

对index函数有了基本的认识后,下面通过案例来看下如何使用。

沿用上面案例中的员工信息表,现在想要查询员工“猴子大大”的工号。

要找的工号在A列,姓名“猴子大大”在第7行,所以输入公式=i-dex($A$2:$A$11 ,7)。

也就是告诉index函数,我们要查找的是A2:A11这个区域的第7行的信息,于是返回了正确的工号(A2002)。

聪明的你肯定发现了端倪:我在这儿是用肉眼来看,然后用手指头戳着一个一个数,最后才知道猴子大大位于第7航。

那么,问题就来了。如果数据量非常大,或者要查找的员工非常多,难道我还要靠肉眼来看靠手指来数数吗?

所以,这时候就得要有一个函数来告诉index,让它去取第几个。下面隆重请出index的最佳搭档:match函数。

2.什么是match?

match在英文中是匹配的意思,所以大家应该很自然就能想到它的作用就是进行数据匹配。

匹配什么呢?

就是拿你要查找的值,去指定的区域进行匹配。匹配上了,就会返回目标值所在的单元格位置。它的语法规则是:

match(要查找的值,在哪里找,是否精确匹配)

要查找的值:就是我们想匹配的值。在这个案例中是姓名“猴子大大”;

在哪里找:去哪个区域找想匹配的值。在这个案例中就是去哪里找姓名“猴子大大”,就是要去“姓名”列中找;

是否精确匹配:它有三个选项,-1,0,1。其中,0代表的是精确匹配。在这个案例中是要在“姓名”列精确匹配“猴子大大”,所以选择的值是0。-1表示查找大于等于“要查找的值”。1表示查找小于等于“要查找的值”。

这个案例中,我们愉快地就写下了这样的公式

=match(“猴子大大”,$B$2:$B$11,0)

返回结果是7,表示匹配到“猴子大大”在姓名列的第7行。

在案例演示中,我们把要查找的猴子大大,放在了单元格H2,所以上面的公式也可以改为:

=match(H2,$B$2:$B$11,0)

返回的结果同样也是7。

3. index+match搭档

在最开始的时候,我们靠肉眼来查找来数数。但是现在有了match函数,我们就把这个查找的任务丢给它,让它来传递。因此原本的公式

=index($A$2:$A$11 ,7)

就可以把公式中的7修改成match函数

=index($A$2:$A$11 ,match(H2,$B$2:$B$11,0))

也就是说,使用match函数来为index函数的第二个参数提供值,告诉index要返回的是第几个值。

所以,通过index+match函数的组合,我们就可以打造一个下面这样的查询系统啦。

无论你要查找的范围有多大,要查找的量有多少,都是秒秒间就能有结果啦。

(动图中所示的下拉菜单是用“数据验证”来实现的,有关这个功能的用法,可戳链接详细了解)

4. 如何实现多条件查找?

上面小试牛刀之后,我们再来进阶一下。index+match的最强大的之处是,它们能实现多条件查找。

上面案例演示中,我们先match出猴子大大在B列的位置,然后再用index返回A列对应的值,得出了对应的工号。但是,如果我还想查询出猴子大大的其它信息呢?如下图:

除了工号,我还想查其对应的“基本工资”“部门”“籍贯”信息。而且,这些信息与数据源的顺序是不一致的。

怎么写公式呢?

有人说,那我就用案例一查询其工号的方法呗,依葫芦画瓢分别再写三个公式,一一来查“基本工资”“部门”“籍贯”信息。

这是一个方法,却是一个很笨的方法。实际工作中,我们面对的可能是很庞大的数据,要查询的列会很多,手动地一个列对应一个公式的写下来,不仅效率低下,还容易出错。

那有没有办法可以只写一次公式,就能返回所有列的结果吗?办法当然是有的。

首先,我们来理清一下:要用index函数来返回值,我们就得告诉它,我们要在指定区域的哪里去找。如要查询“工号”,就得告诉它,要去第1列查找;要查找“基本工资”,就得告诉它,要去第6列查找;要查“部门”,就得告诉它,要去第5列查找。那谁来告诉它呢?用match来告诉它。

match不是最擅长匹配吗?好,就用它来定位位置信息。我们要查询“工号”,我们就用match来匹配,定位到“工号”在数据源里,它是位于第1列;要查询“基本工资”,我们就用match来匹配,定位到“基本工资”在数据源里,它是位于第6列。

把match得到的位置信息就存储起来,然后传给index,index收到定位信息后,就去指定区域对应的位置查询,于是返回对应值,查询结束。

好了,思路清楚后,我们在I2单元格写下公式吧:

=index($A$2:$F$11,

match($H2,$B$2:$B$11,0),

match(I1,$A$1:$F$1,0))

公式解读:我们要在A2:F11这个区域查找,区域这么大,在哪里找呢?给个定位信息吧。好,让match来告诉你横坐标、纵坐标。

根据单元格H2的引用值,用match来匹配“猴子大大”,定位到他所在的行,为第7行,作为横坐标。

那纵坐标呢?因为要查他的工号,所以,再用match对“工号”进行匹配,“工号”在数据源A1:A11里,位于第1列,作为纵坐标。

好了,在A2:F11的这个区域里第7行第1列交叉处的单元格的值,就为猴子大大的工号信息。对于 “基本工资”的查找,同理,在指定区域的第7行第6列查找;其它信息,依次类推。

因为公式还要往右填充,所以,要把单元格H2进行列的锁定,防止公式在填充过程中发生了列的变化。因此,得出了上面的公式。

再通过“数据验证”使得H2的单元格内容自由选择,就能打造一个查询系统了:

想查询哪个员工的哪些信息,也就只是眨眼的功夫。

5.总结

通过index+match这对搭档,我们可以灵活自如地解决90%的查询问题。match用来定位,index根据定位来返回指定位置的值,你学会了吗?

温馨提示:本文最后更新于2023-09-20 16:49:59,某些文章具有时效性,若有错误或已失效,请在下方留言或联系D崽
© 版权声明
THE END
喜欢就支持一下吧
点赞0
分享
评论 抢沙发
D崽的头像 - 迪尔笔记

昵称

取消
昵称表情代码图片