Excel办公技巧-怎样快速掌握Vlookup?

2021-02-05 06:42

花了一天时间,整理了 VLOOKUP 的 16 种用法上(有这个想法很久了),这应该是目前能找到的 VLOOKUP 比较全的用法了,所有的用法我都使用了函数案例+讲解+源文件提供,希望对大家有帮助呀~

虽然随着 365 版本的普及,大家使用 XLOOKUP 的频率越来越高,但是还是说一句,VLOOKUP YYDS!练习文件也分享给大家了,希望对大家有帮助呀~

01. VLOOKUP基础用法

首先来看下 VLOOKUP 的基础用法,函数共有 4 个参数,如下:

= VLOOKUP(查找的值, 查找区域, 返回值所在列数, 匹配模式)

这 4 个参数理解起来也非常简单:

  • 查找的值:要查找的词或者单元格引用,支持通配符;
  • 查找区域:包含查找字段和返回字段的单元格区域,其中查找区域必须位于第 1 列;
  • 返回值所在列数:返回值在查找区域中的列数;
  • 匹配模式:默认为 1 代表模糊匹配,0代表精确匹配;

今天的Word小技巧就介绍到这里啦,如果你还知道其他Word小技巧,也欢迎评论区留言跟我们分享呀~

至此基础语法就已经学习完毕了,一般数据规规矩矩使用起来没有任何问题,但是数据结构稍微变化下,VLOOKUP 的难度就上升了不少~

02. VLOOKUP实现单条件查询

首先是 VLOOKUP 最简单也是最基础的单条件查询,案例:根据工号匹配出对应的工资,公式如下:

=VLOOKUP(H2,A2:F11,6,0)

现在即可轻松将工号为 6 的员工工资匹配出来,效果如下:

简单解释下这个公式:

  1. H2:要查找的单元格引用;
  2. A2:F11:要查找的区域;
  3. 6:代表「工资」列位于查找区域的第 6 列;
  4. 0:代表使用精确匹配模式;

现在终于知道为什么有的时候 VLOOKUP 的第 3 个参数有时候填写 2,有时候不是 2 了吧~

03. VLOOKUP屏蔽错误

单条件查询虽然简单,但是查询不到的时候仍然会返回很难看的错误——#N/A,特别影响阅读,如果想将公式写的更健壮一些,可以使用 IFERROR 对错误进行屏蔽。

例如:使用公式,当匹配不到数据的时候返回“/”而不是错误。

=IFERROR(VLOOKUP(H2,A2:F11,6,0),"/")

这也是很多初学的小伙伴经常碰到的一个问题,为什么数据正确,就是匹配不出来?

因为 VLOOKUP 对数据的匹配是非常严格的,要一模一样才可以,差一个空格,函数都会认为他们是不一样的,例如:“芒种课堂”和“芒种课堂 ”只差了一个空格,如果不查看编辑栏根本看不出来,但是就是不匹配。

IFERROR除了适用于VLOOKUP,在Excel中可以和任何函数进行搭配进行错误屏蔽。

04. VLOOKUP实现反向匹配

在学习 VLOOKUP 基础语法的时候,我们说过“查找词必须在查找区域的第 1 列”,但是在实际工作中经常会碰到不符合的场景,这时候可以利用 IF 数组公式巧妙将两列在内存中调换位置。

例如:“根据姓名查找对应的工号”,利用 IF 数组公式配合实现,公式如下:

=VLOOKUP(H2,IF({1,0},B2:B11,A2:A11),2,0

而且由于该公式为数组公式,需要使用三键「Ctrl+Shift+Enter」结束,不能直接按回车,效果如下:

这里的“IF({1,0}, B2:B11, A2:A11)”其实也非常好理解,即将 B 列和 A 列互换下位置,然后重新组成一个数据区域,这样“姓名”就跑到第 1 列上了。

05. VLOOKUP实现模糊匹配

如果在匹配的时候,只能确定某个词的一部分,可以使用通配符+VLOOKUP实现模糊匹配。

例如:“查找包含阳字姓名的员工工资”,可以使用如下公式:

=VLOOKUP("*"&H2&"*",B2:F11,5,0)

简单理解下这个公式,这里的“*”是通配符,代表任意的字符,使用&在关键词的前后都拼接上“*”,则代表关键词的前后可以是任意的字符,巧妙实现了模糊匹配,效果如下:

06. VLOOKUP代替IF多区间匹配

除了能匹配精确的数值,VLOOKUP在某些场合下还可以实现区间查找,代替 IF 让公式简化。

例如:“根据不同的销量计算提成数”,在数据排序的前提下(升序),可以使用如下公式:

=VLOOKUP(D2,A2:B6,2,1)

这里我们修改了函数的第 4 个参数为 1,代表模糊匹配,会匹配最接近的数值,效果如下:

而如果利用 IF 函数来实现,需要嵌套 4 层才能实现,哪怕是 IFS 函数,也需要写一大堆判断条件才能实现,使用 VLOOKUP 就简单很多了,不过对数据也是有要求的,数据必须升序排序。

07. 去除空格/不可见字符匹配

前面说过哪怕有一个空格不一样,VLOOKUP也会认为这是两个不同的文本,其实针对这些数据的“小错误”,我们可以使用 SUBSTITUTE 或 CLEAN 函数去除空格或者不可见字符,再进行数据匹配。

例如:将数据中的空格替换后再进行 VLOOKUP 匹配,使用如下公式。

=VLOOKUP(SUBSTITUTE(H2," ",""),B2:F11,2,0)

简单理解就是,将查找关键词中的空格删除,而这两个函数在使用上还有一些差异:

1.SUBSTITUTE 可以替换可见的空格,对于一些不可见字符无能为力;

2.CLEAN 则可以将字符串中的不可见字符通通删除,例如“打印符号/音字符”等;

整体的实现效果如下,这时候哪怕数据中有空格,也可以轻松匹配了: