当我们使用 Excel 统计数据时,往往需要引用另一张表格中的部分数据。我们拿最常见的发工资这件事举个例子。

假设公司原先有一张记录了「工号、姓名、性别、手机号、身份证号」的员工信息表。最近需要为员工发工资了,公司又统计了一张含有「姓名、工资金额、工号、银行卡号」的工资表。我们需要做的是在「员工信息表」中加上银行卡号和工资金额信息,去掉不需要的敏感信息。

需要将两张表格的信息组合起来

然而令人头疼的是,两张表格的顺序不尽相同,无法直接将工资单的数据直接复制进员工信息表中,一一填写未免过于麻烦。遇到这种情况应该怎么办呢?

Excel 内置的 VLOOKUP 功能就是为此而生的,可以用它来实现数据的查找与引用。它就像我们去银行取钱时,银行根据我们给出的「银行卡号」,来告诉我们卡里的「余额」一样,使用起来并不麻烦。VLOOKUP 就可以实现类似的功能。

这里给出一份 📑 演示数据.xlsx(点此下载),文中的所有内容都可以在这张表格中实践。(其中的所有敏感数据均为随机生成的假数据)

VLOOKUP 是什么?

VLOOKUP 其实是一个函数,它作用是「纵向查询」,可以按列查找值,并返回另一列的值。

就像上面讲的「根据银行卡号查余额」一样,我们可以查询任意一列的数据,来得到与它对应的其他数值。所以 VLOOKUP 通常被我们用来查询数据引用数据

在实际使用 VLOOKUP 之前,需要简单了解一下它的基础语法。

许多地方都把 VLOOKUP 语法写的很复杂,包括 微软官网的 VLOOKUP 文档 也使用了比较严谨的描述,需要脑子转个弯才能明白它在讲什么:

=VLOOKUP(查阅值、包含查阅值的区域、区域中包含返回值的列号以及(可选)为近似匹配指定 TRUE 或者为精确匹配指定 FALSE)

其实,VLOOKUP 的语法很简单,翻译成一眼就能看明白的话就是——VLOOKUP 函数需要有 4 个部分:「需要查什么、查哪个区域、第几列、是否返回近似值」

这个式子中的最后一个值是选填的,0(False)是精确,1(True)是近似。

实际使用时只要记住最简单的 =VLOOKUP(查什么,区域,第几列,0)这样一个式子,就能顺利地使用 VLOOKUP 函数了。

VLOOKUP 由 4 个部分组成

这个式子里还有一个地方需要注意,「需要查的内容」必须是「查哪个区域」第一列。很多时候查不出数据,就是因为没有遵守这个规则。不过这个问题也是有办法可以解决的,文章下面会提到。

我们先来看一看 VLOOKUP 的基础用法。

基础用法:查询单个信息

VLOOKUP 的最基础用法实际上和搜索类似:用「工号」去查询「工号和工资对应信息」,来获得「工资」这个数值。

直接查询工号

在这个例子中,VLOOKUP 函数的作用就是告诉电脑「我的工号,工号和工资信息,工资在第几列,是否模糊查询」 。比如我想查询工号为 13062714 的工资,工号和工资分别在 A 和 C 列,需要查询的数据在第 3 列。

于是只要把内容一一对应,就可以很容易获得一个式子——

=VLOOKUP(13062714,A:C,3,0)
=VLOOKUP(工号,工号和工资在 A 到 C 列,工资在第 3 列,使用精确匹配)

Excel 就能通过这个式子明白你想做的事情,把需要的工资信息显示出来。

常见用法:批量引用数值

有人可能会问:「Excel 里已经内置了搜索功能,为什么不用搜索功能直接查姓名呢?」

因为如果只查一次姓名,用直接搜索并不麻烦。但如果需要一次处理成千上万个姓名,搜索就显得心有余而力不足,此时用 VLOOKUP 的优势就体现出来了。

批量引用数值

现在我们需要为每个人自动填写工资信息,只要对上面这个式子=VLOOKUP(13062714,A:C,3,0)稍加改动就可以实现。那就是把具体的工号「13062714」改成工号所在的单元格「A2」,得到=VLOOKUP(A2,A:B,2,0)这个式子,然后拖拽单元格右下角的十字标,对下列所有的内容进行自动填充,就可在瞬间完成批量查询工资信息了。

=VLOOKUP(A2,A:B,2,0)
=VLOOKUP(每个人的工号,工号和工资在 A 和 B 两列,工资在第 2 列,精确匹配)

在这个用法中,只是将一个固定的值变成了动态的值,产生的效果也从单一的值变成了动态的值。这种方法与其说是批量查询,不如说是批量引用,这样的做法在后面还会用到许多次。

组合技巧:配合 IF 实现组合判断

VLOOKUP 函数中的每一个部分都可以和其他函数组合使用。当 VLOOKUP 和 IF 组合起来时还可以有更多的用法。

比如通过 IF 来判断工资是否超过 10000 元。这个用法中,VLOOKUP 起到过滤的作用,将需要用到的数据作为过滤条件,实现组合判断。

=IF(VLOOKUP($E2,$A:$C,3,0)>10000,">10000",VLOOKUP($E2,$A:$C,3,0))
=如果(判断查询数值是否大于 10000,是则显示 >10000,否则显示数值)

通过这条式子,得出的结果是「如果超过 10000 元就显示 >10000,如果不足 10000 元就显示具体金额」。

组合判断数值

组合技巧:更友好地显示错误数据

当我们使用 VLOOKUP 时常常会碰到一种情况:找不到符合的数值。这种情况下会默认显示为 #N/A 来表示错误。但这个字符串往往会让其他看表格的人摸不着头脑。而配合 Iserror 函数就可以让这个错误值看上去变得更友好,比如查不到某个工号时显示「查无此人」,我们可以用下面这个式子来实现。

=IF(ISERROR(VLOOKUP($E2,$A:$C,2,0)),"查无此人",VLOOKUP($E2,$A:$C,2,0))
=如果(判断查询数值是否有错,是则显示「查无此人」,否则显示数值)

通过 IF、ISERROR 和 VLOOKUP 一起使用,就可以实现对错误值的处理。

判断数值是否有误

组合技巧:一次搞定多列信息

上面的技巧大多是围绕着两列函数进行的,我们可以直接使用自动填充来实现一次性填充多列。但是这样会出现一个问题,公式中的所有数值都会向右移动一列,这就可能会导致 VLOOKUP 失效或错误。如果希望一次返回多列内容,是不是只能靠复制粘贴来解决呢?

其实我们可以用 $ 和 COLUMN 来实现一个全表格通用的 VLOOKUP 函数。在式子的列数前加上一个 $ 标记。这样,需要筛选的内容、范围都不会因为向右移动而改变。

=VLOOKUP(A2,A:C,2,0) // 原式
=VLOOKUP($A2,$A:$C,2,0) // 加上 $ 来固定列

但是这样又出现了一个问题,VLOOKUP 式子中的第三个值「第几列」不会自动改变,但我们希望它根据表格内容自动变化。这里可以配合 COLUMN 函数一起使用。

COLUMN 函数的作用是「返回选中的列数」。比如 =COLUMN(D10) 中,不管单元格中的值是什么,这个式子的结果永远都是 4,因为 D 这一列就是第 4 列。如果不输入,直接使用 =COLUMN() 就会返回当前这一列的列数。

了解了 COLUMN 的用法,再对式子做一次调整,加入 COLUMN(C1) 函数。

=VLOOKUP(A2,A:C,2,0) // 原式
=VLOOKUP($A2,$A:$C,2,0) // 加上 $ 来固定列
=VLOOKUP($A2,$A:$C,COLUMN(C1),0) // 用 COLUMN 函数使得列数据自动改变
=VLOOKUP($A2,$A:$C,COLUMN(C1)-1,0) // 如果需要查询第二列数值,则需要改变列的关系,比如 -1 列
=VLOOKUP(固定查询,固定信息列,自动改变需要的列数,精确查询)

现在对右侧的一列进行自动填充,就能获得正确的数值了,有效实现了数据的自动更新。

COLUMN 函数中采用的是单元格而非具体数字,所以内容会随着自动填充而改变。这样就实现了横跨多列拖拽也能完美自动填充的效果

一次填充多列信息

组合技巧:配合 IF 实现换列查询

有时候我们会碰到一种情况,表格的第一列是「工号」,第二列是「姓名」。那我想用第二列的「姓名」去查「工号」该怎么办呢?

这个问题在于,VLOOKUP 默认在第一列的数据中查询,在工号这列找姓名当然是找不到的。所以我们就要想办法把这两列调换一个位置,让 VLOOKUP 函数得以正常运行。但是公司内部的表格结构通常是统一的,不能随意调换位置。这里就要配合 IF 函数来实现换列查询。

IF 函数有一个用法可以实现调换两列的位置 IF({1,0},B:B,A:A),这个函数的作用就是把 B 列和 A 列互换一个位置。

然后再用 VLOOKUP 函数查询调换位置后的第二列,我们可以得到这样一个公式——

=VLOOKUP(E2,IF({1,0},B:B,A:A),2,0)
=VLOOKUP(查什么,调换两列,查询调换后的第 2 列,精确匹配)

不要看式子变得这么复杂,它和上面那些式子的区别也只有「查哪个区域」变成了 IF({1,0},B:B,A:A),总体的结构仍然没有改变。如果换个表格改为第三列是姓名。其实也是一样的,把 B:B 换成 C:C 就好了。

实现调换查询

高级用法:实现多个条件查询

有时候满足条件的值有许多种,比如演示数据中有两个叫「林晓」的员工,此时我们可以通过工号+姓名的方式来实现准确的匹配。

遇到这种情况,我们可以用 & 把条件组合起来查询,也就是 E2&F2,但是情况比想象的要复杂一些,这个公式会报错,因为 VLOOKUP 函数默认只允许单个条件搜索,如果要用双重条件,就需要给出两个条件的范围。那么将范围 A:A&B:B 组合起来是否就可以了呢?但是仍然报错。

这个问题对很多人造成了困扰,实际上,这里需要用到前面提到的 IF({1,0}, , )将多列的数据组合为数组,才能进行查询。在这里应该用 =VLOOKUP(E14&F14,IF({1,0},A:A&B:B,C:C),2,0)

=VLOOKUP(E2,A:C,3,0) // 单条件查询
=VLOOKUP(E2&F2,A:C,3,0) // 错误示范 1
=VLOOKUP(E2&F2,A:A&B:B,3,0) // 错误示范 2
=VLOOKUP(E2&F2,IF({1,0},A:A&B:B,C:C),2,0) // 将多列的数据组合为数组
=VLOOKUP(两个查询条件,需要查询的内容组成数组,查询的列数,精确查询)

当我们使用到数组进行查询时,完成编辑后需要按下 Ctrl + Shift + Enter 才能使其生效。

多个条件查询

实例讲解:引用另一张的表格信息

有了上面这些技巧,再回到开头提到的问题,是不是可以很快解决了?

当你输入 =VLOOKUP 函数后,只要直接打开另一张表格,就可以选中其中的数据了。Excel 会自动帮我们把引用的数据转换为可识别的信息并加入到公式中。

我们需要查找的信息是「工号」这类具有唯一性的信息,所以利用「常见方法:批量查询信息」中的内容可以轻松搞定。银行卡号的批量填充。

=VLOOKUP(A2,工资单信息!C:D,2,0) // 银行卡号

在示例表格中,「工资单信息」的排序方式是姓名、工资金额、工号、银行卡号。工资金额在工号之前,利用「组合技巧:配合 IF 实现换列查询」中的内容,将两列信息进行调换,即可实现工资金额的自动填充。

=VLOOKUP(A2,IF({1,0},工资单信息!C:C,工资单信息!B:B),2,0) // 工资金额
用 VLOOKUP 实现开头的需求

注意事项:VLOOKUP 是动态的

VLOOKUP 函数的最基本作用之一是「引用」,所以得到的值是动态改变的。当你在一张表格中使用 VLOOKUP 时,如果源数据发生了变动,VLOOKUP 函数查询到的值也会跟着变动。

VLOOKUP 还支持跨工作表、跨文件引用数值,这个功能方便了使用,但万一数据源文档被删除,引用的数据也会消失。为了防止这种情况出现,可以在使用 VLOOKUP 获取数值之后,再来一步「复制 - 粘贴为值」来格式化数据,这样就不会让引用的数据消失。

其中的第二项为「粘贴为值」

除了上面这些技巧之外,VLOOKUP 还有一个同胞兄弟:HLOOKUP。与 VLOOKUP 的纵向查询对应,HLOOKUP 可以实现横向的数据查询。

本文中的所有内容都以工作表的形式放在了开头的 📑 演示数据.xlsx 表格中,如果对 VLOOKUP 的用法还有困惑,可以下载后慢慢研究。