当前位置: 首页 > 办公技巧 > 正文

excel办公技巧学习vlookup(入门vlookup函数,看这一篇就够啦,4000字总结vlookup函数用法)

  • 叁碗诸角 叁碗诸角
  • 办公技巧
  • 2023-07-07 06:14:19
  • 0

Vlookup函数是电子表格中的一个查找匹配函数,适用于Excel与WPS各个版本。

在所有的函数中,vlookup函数应该是使用频率最高的函数之一,对新手小伙伴来说,掌握它是十分有必要的。

本篇文章从其基本用法、进阶用法、匹配不出来原因分析、如何规避错误值、冷门用法科普等五个维度来详细介绍下vlookup函数,希望能够帮助小伙伴们快速入门、以及加深对此函数的认识。


一、基本用法

语法解释

vlookup函数一共有4个参数,参数1要搜索的值、参数2搜索区域、参数3返回的列数、参数4匹配模式,参数有点多,没关系,我们用一个实例来看下每个参数的具体作用。


精准匹配

下图中,根据左边的信息表,利用vlookup函数匹配出李白的年龄,G2单元格中的公式该如何输入?

参数1:搜索值,找谁?

虽然最终结果是获取年龄字段,但是查找值并不是年龄,而是查找“李白的年龄”,主语是“李白”,所以参数1、查找值是“李白”,也就是F2单元格。

参数2:搜索区域,去哪找?

重点来了:查找区域的第一列必须是搜索值所对应的列(查找对象必须位于查询区域的最左列),参数1搜索值是“李白”,对应到左边到表格就是“姓名”字段,所以参数2搜索区域必须以B列作为首列,以B列作为首列向后拖动到我们需要匹配的值所在列,也就是D列年龄列(也可以继续向后拖动,只要包括年龄列就可以),所以参数为就是B:D列。

参数3:在第几列?

这里有个误区,很多小伙伴觉得左边的匹配表一共4列(A-D列),年龄列在D列,也就是第4列,所以参数3输入了“4”,这是错误的。

参数3实际指的是返回值(年龄)在搜索区域中所处的列数,也就是在参数2框选区域中所处的列数,上面我们框选了B:D列,实际上仅框选了3列,B是第一列、C是第二列、D是第三列....所以返回值“年龄”在第三列,参数3输入数字“3”。

参数4:匹配模式?

匹配模式分为精准匹配(输入0、FALSE或者省略都可以)、近似匹配(输入1或者TRUE),最常用的当属精准匹配,也就是要完完全全找到“李白”这个人,而不是“李小白”、“李白小”,所以参数4输入数字“0”,表示精准匹配,也可以直接省略或者输入FALSE。

日常工作中,99%的情况下都是使用vlookup函数的精准匹配,也就是参数4大家可以默认输入0。

通过以上例子,介绍了vlookup函数的最基本、最实用的用法,最容易出错的两点在于:

1、参数2/搜索区域 的首列必须是返回值所在的列;

2、参数3/列数 必须是返回值在搜索区域对应的列数。


近似匹配

上面说到99%的情况下,vlookup函数的参数4都是0,也就是精准匹配,那么还剩下1%的情况,需要用到vlookup函数的近似匹配,在表格中也有非常合适的应用场景,那就是数据分组功能。

下图中如何根据左侧A-B列的评级表,去匹配每位同学所属的评级?

首先建立一列辅助列,在G2单元格内输入公式:=VLOOKUP( F2,B:C,2,1),向下填充,完成区间匹配。

这里利用vlookup函数近似匹配的功能,它会返回小于等于查找值最大值。


查找数字56,等于查找 “小于等于56的最大值”,60/80/90都要大于56,只有0小于56,所以返回0对应的评级“不及格”; 查找数字88,等于查找 “小于等于88的最大值”,0/60/80都要小于88,但80最大,所以返回80对应的评级“良好”;


最重要的一点,利用vlookup函数近似匹配之前,需要对匹配表数据进行升序处理,即上图中先对辅助列B列升序(上图中B列数字由小到大排序)。

因为vlookup函数近似匹配采用的是二分法,一般是从中间开始向上下两端查找,不断二分,默认数据升序处理。

不升序会怎么样?

下图中,匹配序列处于乱序状态,利用vlookup函数近似匹配、返回小于等于查找值最大值,正确结果应该是a1,(6是小于等于6.5的最大值),但是函数返回结果却是a2。

二分法中间开始查找,序列中间值为7,7要大于查找值6.5,所以继续向上查找,上面的1要小于6.5,查找停止,返回1对应值。所以,在近似匹配的时候,切记要升序处理。


​二、进阶用法

进阶用法主要是vlookup函数搭配辅助列或者其它函数,来实现数据匹配,常用的方法主要分为以下几种。

在此之前,还有一个比较重要的概念就是单元格引用,在vlookup函数匹配的时候,参数1与参数2经常涉及到相对引用与绝对引用,十分重要。Excel相对引用与绝对引用,傻傻分不清?


多列查找

1、结合column函数

下图中,匹配各同学的性别、年龄、城市三个字段,直接利用COLUMN函数(返回单元的列),动态返回需求列。公式=VLOOKUP($F2,$A:$D,COLUMN(B2),0)其中参数1与参数2皆涉及到单元格的引用。

2、结合match函数

上图需要匹配的列与原表列的位置顺序一样,可以用COLUMN函数解决,如果顺序不一致的话,需要结合match函数使用,公式=VLOOKUP($F2,$A:$D,MATCH(G$1,$A$1:$D$1,0),0) 注意其中的引用方式。

match函数返回查找值在数组中的位置,比如查找字段“性别”,它在数组“A1:D1”中处于第2位,所以vlookup函数参数三就等于2。

以上两种方式都是通过动态变更参数三,从而完成多字段匹配。


逆向查找

由于vlookup函数的参数2、查找区域首列必须为搜索值对应的列,如果返回值所在的列在搜索值对应的列前方的话,vlookup正常用法无法完成匹配。

下图中,根据A-B列的数据源,匹配出E列的学号,根据前文,搜索区域只能从B列开始、向后拖动,但是返回值在A列,肯定查不到正确的结果。

这种情况我的建议是调整列的位置,比如在学号列前方复制插入姓名列,逆向转正向匹配。

当然也可以使用INDEX MATCH组合以及XLOOKUP函数解决逆向匹配的问题,这里不是我们的讨论范围,感兴趣的小伙伴可以自行了解。


多条件匹配

多个字段匹配的情况,可以在源数据的基础上,增加一列辅助列,利用“&”将各列数据进行连接。


​模糊查找

“*”是通配符,代表0到多个字符,"*"&D2&"*"则表示包含D2关键字的任意字符串


一对多查找

下图中,根据B-C列数据源,匹配出E列部门的所有员工姓名。由于vlookup函数只能返回首个值,然后有多个员工,这里利用COUNTIF函数将每个部门的个数进行编号。

辅助列公式=B2&COUNTIF($B$1:B2,B2)注意其中的绝对引用。这样每个部门都会被标上序号。

接着F2输入公式=VLOOKUP($E$2&ROW(A1),A:C,3,0),公式下拉,即可返回多个结果。


​三、匹配不出来的原因

明明有数据,vlookup却匹配不出来,在工作中是比较常见的。

第一种常见错误是匹配区域未绝对引用、发生变化所致。下图中匹配年龄字段。

在E2单元格内输入公式=VLOOKUP(D2,A2:B9,2,0),由于参数2未采用绝对引用,公式在下拉到“赵云”的时候,引用区域变成了“A4:B11”,区域内并无此人,当然查找不到数据。

正确的做法是:参数2引用区域采用绝对引用(选中引用区域,按下F4键)

第二种常见错误是匹配字段中存在不可见字符,比如空白符,利用Ctrl H将空白替换掉就可以了。

其它错误可以参考下面这篇文章。明明有数据,为什么我的VLOOKUP总是匹配不出来?


四、规避错误值

vlookup函数在未匹配到数据的情况下,函数会返回#N/A,如果想到规避这种错误值,可以利用IFNA或者IFERROR函数。

公式=IFNA(VLOOKUP(D2,A:B,2,0),"查无此人"),在vlookup函数外层嵌套一个IFNA函数,表示内层的函数表达式(参数1)结果为#N/A,则返回指定的值(参数2),否则返回表达式本身的结果。

IFERROR函数与IFNA用法一致,只不过前者比后者更加强大,关于Excel的错误类型总结,可以参考下面的文章。 Excel科普文,常见8种错误类型总结,以及如何规避错误值?


五、冷门用法(仅作了解)

这里的冷门用法是指公式写起来比较麻烦、或者有更高效的函数可以替代,让我们一起看看吧~

IF函数{1,0}用法,每位使用vlookup的童鞋不得不面对的问题。

比如vlookup函数逆向查找,公式=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)

这里完全可以用match index函数替代(高版本可用Xlookup)。

比如多条件匹配,公式=VLOOKUP( E2&F2,IF({1,0},A:A&B:B,C:C),2,0),需要Ctrl shift Enter三键齐按完成公式的输入。这里也可以用sumif或者sumproduct函数替代。

vlookup函数搭配IF函数的{1,0}功能是不推荐大家使用的,如果想要了解IF{1,0}的具体原理,可以看下面这篇文章。拜托,Vlookup函数的这个功能真的别再用了

最后再给大家分享一个vlookup的小技巧,提取固定数字字符串的技巧。

下图中,提取A列是首次出现的手机号码,输入以下公式:

=VLOOKUP(0,MID(A2&"a",ROW($1:$99),11)*{0,1},2,0),Ctrl shift Enter三键。

好了,以上就是今天的分享,想要了解更多干货,点个关注哟,我们下期再见。


最新文章