在电子表格中使用 VLOOKUP 进行数据整理

有时，数据科学并不总是关于深度学习、大数据或推荐系统。尤其是在金融等行业，或在曾长期依赖 Excel 的公司工作时，您很可能需要大量处理 Excel 电子表格。我本人就属于后者。我的雇主高度依赖 Excel，因此在履行部分工作职责时，我不得不深入学习它的各种功能。

尽管 R 和 Python 在许多方面相对于 Excel 有明显优势，但在某些任务上（例如制作业务报告），Excel 仍是一款强大的工具。不过，在本教程中，我将专注于 Excel 的一个内置函数，它在同时处理多张 Excel 表格时非常有用。

我指的这个函数是 VLOOKUP。VLOOKUP 允许用户在同一工作表中的不同列之间，或在同一工作簿中不同工作表之间建立关联。实质上，它使用户能够在一列中查找某个值，并返回另一列中与之对应的值。如果您有大型且未排序的数据表，并且需要基于某一列（理想情况下包含唯一键）将它们关联起来，那么这个功能就非常有用。

下面用一个简单的数据表示例演示在同一工作表内使用 VLOOKUP 函数在列间关联的过程：

上面的截图展示了一个常见的 VLOOKUP 表达式，通常用于业务报告。它的组成如下：

• IFERROR 函数：当键为空或在数据表中不存在时，用连字符（-）替换错误。这是一种良好的错误处理实践，能在出现错误时让工作表更整洁，但并非让 VLOOKUP 工作所必需。
• VLOOKUP 参数：
  • E2 是查找值（此处示例使用 KOG，但可以是任意键）。该值必须位于您要搜索的所选表格区域的最左列。
  • B1:C18 是用于查找的表格区域/数组。最左列的索引为 1，如图所示。该列还必须是包含查找值的列，函数才能工作。
  • 2 是所选表格区域中您希望取值的列索引号。在本例中我们要获取描述，因此选择表数组的第 2 列。
  • FALSE 是一个标志，表示您需要查找值的精确匹配。在这里我们选择 FALSE，因为要精确匹配 KOG 键。另一种选择是 TRUE，即查找值的近似匹配，如果不指定该参数，默认即为 TRUE。一般不建议在匹配键或字符串时选择 TRUE，因为可能会得到奇怪的结果。

现在，输入该 VLOOKUP 语句后，在 Excel 中按下回车，您会得到如下结果：

如您所见，VLOOKUP 函数返回了预期的 "King of Gondor"，也就是与 KOG 键对应的描述。这对您现在在 E2 单元格中输入的任何键都成立。比如，我们来看一下输入键 KIN 会发生什么。

北境之王！没错，如果我们输入 KIN 键，就会得到它的描述，我们亲爱的 Jon（Snow）。

不过，VLOOKUP 的用途不止是根据一个键取回单个值。假设我们将该表拆分为两张，分别放在同一工作簿中的不同工作表里。

连接表格

如您所见，现在数据被拆分到两个不同的工作表（S1 和 S2），且第二个工作表（S2）中的键顺序不同。幸运的是，无论顺序如何，键列在两个工作表中都是公共的，因此我们可以在 S1 中使用 VLOOKUP 将数据连接起来（可以将其视为单列的左连接）。实现此目的的语法如下：

这一次，第一个参数（查找值）是 B2，因为我们要在 S2 的表中匹配键 B2。现在的表格区域是 S2 中整个数据表，其第一列包含公共键。您可能注意到表格区域中嵌入了美元符号。这是选定区域后按 F4 完成的，用于锁定区域，这样当您在 S1 中向下拖动公式时，VLOOKUP 会持续查看原始区域，而不会随之下移。列索引号仍为 2，因为我们需要的是描述，它是 S2 的第二列。最后一个参数我们依然选择 FALSE，因为我们需要对键进行精确匹配。

最后一步，只需将公式向下拖拽到表格的最后一行。S1 中得到的结果表如下：

瞧！就这样，我们用 VLOOKUP 把数据拼接回来了。在业务环境中，这样连接表格非常常见，而 SQL、R 或 Python 并不是高管们习惯使用的工具。举例来说，您可能有两个文件，分别包含关于合格销售机会的不同细节，可以用唯一的机会 ID 关联起来，用于每月的销售 Excel 报告（以我为例，这是 Excel 的最佳用法）。

使用近似匹配

到目前为止，您可能注意到我们一直没有将最后一个参数设置为 TRUE（即近似匹配）。这是因为我们一直专注于精确匹配唯一键。不过，当处理数值数据时，您可以安全地在 VLOOKUP 的最后一个参数中使用 TRUE。

例如，假设我们卖鱼，并在顾客购买一定数量时提供如下折扣（如果有人问为什么是鱼，很简单：鱼很棒！）。

这次，F2 单元格中的 VLOOKUP 函数是：

一旦设置好该公式，折扣将根据表中的阈值取最近的级别。例如，若我们买 25 条鱼，折扣为 5%；买 70 条鱼，折扣为 10%；买 120 条，则折扣为 15%。如下所示：

INDEX-MATCH 入门

VLOOKUP 是用于关联不同列的强大函数。不过，还有一种替代方式：INDEX-MATCH 组合。与单一函数的 VLOOKUP 不同，INDEX-MATCH 是两个函数的组合，即使编写稍微复杂一些，它也有多个优点。

在本教程中，我们将通过一个简单示例，使用 INDEX-MATCH 基于公共键连接两张表（就像上面使用 VLOOKUP 一样），并解释其语法（见下图）。

INDEX-MATCH 组合的组成如下：

• INDEX 参数：
  • M2:M18：这是您希望取值的列范围。在此示例中，我们需要描述列，因此选择该范围。注意我们在选定范围后使用了 F4 进行锁定，这样在拖动公式时范围不会移动。
• MATCH 参数：
  • B2：这是我们要查找的键，等同于 VLOOKUP 中的查找值。
  • L2:L18：这是包含键的列范围，查找值必须位于其中。注意这里我们同样使用了 F4。
  • 0：与 VLOOKUP 中的 FALSE 相同，即表示精确匹配的标志。

从语法上看，您已经可以意识到 INDEX-MATCH 相较 VLOOKUP 的主要优势之一：列免疫。也就是说，如果我们决定在右侧表格中的 Key 和 Description 之间新增一列，公式仍会调整为引用 Description 列，因此不会受影响。如下所示：

使用 VLOOKUP 则无法做到这一点，除非返回去修改公式，因为它使用的是静态列引用。换句话说，在上述示例中，最初您会将列索引号设为 2 来取回 Description。然而在添加 Fish 列后，VLOOKUP 会返回 Fish 列的值，因为该列现在是表格区域中的第 2 列。您必须手动进入 VLOOKUP，将列索引号改为 3，函数才会再次正确工作。

如上所示，使用 INDEX-MATCH 将右侧表中的 description 连接到左侧表中的 name，与使用 VLOOKUP 的流程相同。我们只需按下图所示向下拖拽公式：

需要注意的是，在同一工作簿的不同工作表之间连接表格，步骤也是相同的。唯一的区别在于，您需要在第二个工作表中选择包含公共键的列范围以及需要取值的列范围。

结语

希望您喜欢本教程。在业务环境中，VLOOKUP 和 INDEX-MATCH 几乎是任何使用 Excel 的人都必备的函数。我鼓励您在此基础上进一步深入学习 INDEX-MATCH，它还有许多实用功能，例如区分大小写匹配（是的，默认情况下 INDEX-MATCH 和 VLOOKUP 都不区分大小写）或基于多列的匹配。持续学习，前景无限！

如果您想进一步了解 VLOOKUP，请学习 DataCamp 的Data Analysis with Spreadsheets课程。