打开APP
userphoto
未登录

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

开通VIP
VLOOKUP函数的偷懒指南,新同事用这个方法竟提早了整整2小时完工!

每天一点小技能

职场打怪不得怂

编按:在EXCEL查询工作中,VLOOKUP是当之不愧的最强函数。而要完全掌握这个函数,我们首先必须对它的每个参数有掌握。今天,小E和大家介绍的就是VLOOKUP中的第三个参数,这个返回数据所在列的参数,以往总是要我们重复输入相似的数值,花费了很多不必要的时间。但其实只需让它做一点变化就可以让整个函数都得到升级,提高办公效率,让我们偷懒!

员工小张是公司的上进青年,自从入职后,他认认真真地学习了EXCEL中各种函数,对VLOOKUP这个明星函数也算是有所小成。可是,小张最近有点小烦恼——自己每天的工作都在反反复复地录入VLOOKUP中度过,操作到手软,也是够无聊的。小张心想,有没有办法能够减少输入VLOOKUP的次数呢?下面,我们来看一下这个偷懒方法吧。

大家先看一下数据。如下方右图所示,G列为序号列,H列至L列中保存的是员工的基本信息,分别是姓名、部门、职位、入职月数和基础工资。

下方左图则是需要制作的查询表。A列是查询值(即员工姓名),要查询的字段分别是部门、职位、入职月数和基础工资。

大家先看一看,小张是如何操做的。小张首先在B2中输入“=VLOOKUP(A2,H:L,2,0)”。然后,分别在C2中输入“=VLOOKUP(A2,H:L,3,0)”,在D2中输入“=VLOOKUP(A2,H:L,4,0)”,在E2中输入“=VLOOKUP(A2,H:L,5,0)”。做了四次VLOOKUP操作之后,小张选中B2:E2区域,通过下拉向下复制填充公式,就得出了各个查询结果,如下图所示。


本例中只列举了四个查询字段的情况,小张一列一列地写VLOOKUP函数,写四次,勉强还是可以接受。可是,当查询字段变多的时候,小张就觉得烦了,因为不管是不停的录入公式还是不断地复制粘贴修改公式,都是一样既考验耐心又考验眼力的工作,一不小心就可能手僵眼酸出错。

要想帮助小张在工作中能够偷偷懒、摸摸鱼,大家先看看小张输入的四个公式有什么共性,然后再去找解决办法。现在将四个VLOOKUP函数整理到一个表中,以便对照观察,如下图所示。

通过上面这个表,大家可以看到,四个公式的区别,仅在于第三参数不一样,即查询值所在的列不一样(分别为2、3、4、5)。而其他的几个参数,都一模一样:查询值均为A2,查询区域均为H:L,查询方式均为0(精确查询)。

发现这个规律之后 ,大家就可以转换思路了:只要通过函数自动生成2、3、4、5这几个数,那么小张的烦恼就可以解决了。

解决方案(一)——COLUMN函数

第一个救兵就是COLUMN函数。

大家先找一个单元格,输入公式测试一下。如,可以在B10中输入“=COLUMN(B:B)”,然后将公式向右复制到C10、D10、E10单元格。这时,可以看到,在B10:E10中就生成了2、3、4、5这样的数字。

函数原理:

COLUMN函数的功能,就是计算某列的列号。例如,在B10单元格中,公式为“=COLUMN(B:B)”,就代表着要计算B列的列号,即为2。在C10单元格中,公式为 “=COLUMN(C:C)”,它计算的是C列的列号,即为3。以此类推,这些数字,刚好可以作为VLOOKUP函数的第三参数!

注意:

解决了生成第三参数的问题,就可以把公式嵌套起来了。因为A列是查询值所在的列,是不变的,所以在B2中写公式的时候,要将写作$A;同理,因为H:L区域是数据底表,也要始终不变,所以要写作$H:$L。

在B2中输入“=VLOOKUP($A2,$H:$L,COLUMN(B:B),0)”,然后再向右向下复制填充公式,就行啦!只需要写一次公式,就可以完成B到L列中多个字段的查询!

总结:

在刚才所展示的方法中,查询字段有一个特点——查询字段的排列顺序与数据底表中字段的排列顺序是一致的,在这种情况下,用COLUMN函数代替VLOOKUP函数内的第三参数,就能实现一一对应。如此,只需要写一次公式就可以了,不用一再录入公式并修改,更不会手麻眼酸失误!

那么,现在问题来了,如果查询字段的排列顺序与底表中字段的顺序不一样呢,该如何做?

解决方案(二)——MATCH函数

如下图所示,假设想要匹配的字段依次为职位、基础工资、部门、入职月数,底表中的数据不变,那么该如何生成VLOOKUP函数的第三参数呢?

一个新的救兵——MATCH函数!它的作用是在某一个区域中,找出查询值所在的位置(注意:是返回查询值在该区域中的位置的值,而不是具体的数值)。

为了让大家更能理解这个参数,这次表中A1:E1内单元格中的内容顺序与第一种方法时不同:

先来计算一下:对于B2,要查询的是职位,对应的是H:L区域中的第3列,应返回3;对于C2,要查询的是基础工资,对应的是H:L中的第5列,应返回5;对于D2,要查询的是部门,对应的是H:L区域中的第2列,应返回2;对于E2,要查询的是入职月数,对应的是H:L区域中的第4列,应返回4。

这次,大家同样可以在B10单元格中进行测试,在B10中输入“=MATCH(B1,H1:L1,0)”,得到的结果如下图所示。

可以看到,其返回值为3,就是说,职位位于H1:L1区域中的第3列。考虑到公式将要向右向下复制填充,需要对B后面的“1”加上绝对引用,对H1:L1也加上绝对引用。

将B10的公式修改为“=MATCH(B$1,$H$1:$L$1,0)”后,将公式向右复制填充到C10、D10、E10,就可以得到3、5、2、4这四个数值了,这样,就解决了VLOOKUP函数第三参数的问题。

将函数嵌套一下,大家在B2中输入“=VLOOKUP($A2,$H:$L,MATCH(B$1,$H$1:$L$1,0),0)”,然后向右向下复制填充公式,就可以得到正确的结果了。如此,就算查询字段的排列顺序与底表中字段的顺序不一样,也可以只写一次公式,不用一再录入公式并修改,成功告别手僵眼酸失误的困扰!

注意:

使用以上方法时,查询字段的文字内容和数据底表中相应字段的文字内容要完全一致,否则MATCH函数是得不到正确结果的,从而导致VLOOKUP函数也不能成功的哦!

亲爱的小伙伴,张三已经可以熟练地偷懒了,你学会了吗?


本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
VLOOKUP MATCH, 快速匹配指定多列内容,不加班秘技,快来看看吧~
每日一题:VLOOKUP公式的快速复制
Xlookup再牛,也打不过Vlookup Match公式组合
VLOOKUP函数的使用方法(初级篇)
excel函数应用:Column函数替代列参数提高公式灵活性
真正的Excel高手,能把「引用」玩得风生水起~
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服