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 则可以将字符串中的不可见字符通通删除，例如“打印符号/音字符”等；

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