时光如白驹过隙,不知不觉已经毕业10年了,人生中第一次接触Excel是在大学里帮班主任录入班级同学们的身份证号,由于不知道设置单元格格式,输入的身份证号显示的是科学计数法,被这个弄的死去活来,往事不堪回首......

毕业后进入一家公司,这个公司是有自己的ERP系统的,但是还是需要把数据导出来进行匹配,由于导出来的数据基本上都是竖排,所以必须掌握一个列查询函数,就是VLOOKUP,我已经忘记了我是如何学会的,可能是同事教的,也许是我自己在网上查看资料学习的。后来我发现VLOOKUP这个函数是普及度最广的,就好像用Excel用户都会用SUM求和一样,而且很多人跟我一样人生中第一个函数都是VLOOKUP。既然这个函数这么“好用”,那本文就让我们一起重温一下这个函数,并且学习VLOOKUP的进阶用法。

先来看下这个函数的参数 VLOOKUP(查询值,查询区域,返回列数,模糊/精确匹配)

最普遍的用法

如下图所示,A1:B7这个区域是我模拟的数据区域,D2单元格是查询的序号,需要在E2单元格匹配序号对应的型号。

excel如何正确使用vlookup函数(我人生中学会的第一个Excel函数VLOOKUP)(1)

在E2单元格输入=VLOOKUP(D2,A:B,2,0) 首先讲解一下这4个参数

1、查询D2单元格的值(D2的值现在是1)

2、在A列到B列这个区域查询,第二个参数也可以输入A2:B7,效果是一样的。还需要注意的是查询的区域,查询值的列必须居于首列,现在查询的值是序号1,序号列必须居于第二参数所选区域的首列。也可以理解为,你只能查询序号列右边的数据

3、返回对应的列数,这个列数也是基于第二参数所选择的区域而言,首列数为1,第二列为2,以此类推。

4、最后一个参数如果输入1就是模糊匹配,输入0就是精确匹配,在我的日常工作中99%都是输入0,精确匹配,一个字都不带差的。

如何突破向右查询,可以向左查询

现在把序号跟型号列对调,如果跟上一个图一样输入公式,那肯定是匹配不上的,这个时候需要用到IF函数来重构一个数据区域,在E2单元格输入=VLOOKUP(D2,IF({1,0},B2:B7,A2:A7),2,0)现在的写法和上面不一样的地方就是第二个参数,现在来单独讲下这个参数。

excel如何正确使用vlookup函数(我人生中学会的第一个Excel函数VLOOKUP)(2)

我们知道IF函数有3个参数如下图所示

excel如何正确使用vlookup函数(我人生中学会的第一个Excel函数VLOOKUP)(3)

第一个参数如果成立为TRUE也就是1,则返回第二个参数,如果为FALSE也就是0,则返回第三个参数。如果我们在IF的第一个参数同时输入1和0会出现什么效果?我们先在任何单元格输入=IF({1,0},B2:B7,A2:A7)然后按F9看运算结果,会得到一个数组把它复制出来看看,{1,"A";2,"B";3,"C";4,"D";5,"E";6,"F"}这是一个什么东西?其实就是把这两个列又调回来,序号1对应型号A,序号2对应型号B,其实我们还是不能改变VLOOKUP的属性,我们只是通过这个方法重构了一个让VLOOKUP用的“舒服”的参数而已

excel如何正确使用vlookup函数(我人生中学会的第一个Excel函数VLOOKUP)(4)

VLOOKUP的模糊查询

上面说的都是VLOOKUP的精确查询,下面就来讲下VLOOKUP的模糊查询用法,请看下图

excel如何正确使用vlookup函数(我人生中学会的第一个Excel函数VLOOKUP)(5)

这个案例是判断成绩优良中差,判断的规则写在F2:G5这个区域,这个判断可以写IF来搞定,同样的也可以通过VLOOKUP的模糊查询来搞定,但是前提是需要构建一个辅助区域,构建区域每个成绩段取最低值。如上图所示,辅助区域是I2:J5也就是我标注成黄色的区域。

在D2单元格输入=VLOOKUP(B2,$I$2:$J$5,2,1)然后向下填充,得到的结果和IF返回的结果一致。

当VLOOKUP第四个参数输入1时,就会进行模糊匹配,首先会找是否有相同的值,当查询值在区域内查询不到时,会找区域中比查询值小,且最接近查询值的值,比如第三个李华的成绩是87,首先判定在I2:I5这个区域内有没有等于87的值,显然是找不到的,那就会找到比87小的,此时比87小的有三个值,分别是80,60,0 最后找到80,为什么?因为只有80同时满足了,比查询值小,并且最接近查询值,然后VLOOKUP反回了80对应的“良”

好了VLOOKUP函数就分享到这里。

关注我,查看更多Excel实用技巧视频。

,