如何在 Excel 中使用 VLOOKUP

已发表: 2020-06-04

您是否曾经在 Excel 中使用过包含数据的大型电子表格，并且需要一种简单的方法来从中过滤和提取特定信息？ 如果您了解如何在 Excel 中使用 VLOOKUP，则只需一个强大的 Excel 函数即可完成此查找。

Excel中的VLOOKUP函数让很多人感到害怕，因为它有很多参数，并且有多种使用方法。 在本文中，您将了解在 Excel 中使用 VLOOKUP 的所有方法以及该功能为何如此强大的原因。

目录

    Excel 中的 VLOOKUP 参数

    当您开始在 Excel 中的任何单元格中键入=VLOOKUP(时，您将看到一个显示所有可用函数参数的弹出窗口。

    让我们检查每个参数及其含义。

    • lookup_value ：您要从电子表格中查找的值
    • table_array ：要搜索的工作表中的单元格范围
    • col_index_num ：要从中提取结果的列
    • [range_lookup] ：匹配模式（TRUE = 近似，FALSE = 精确）

    这四个参数使您可以对非常大的数据集中的数据进行许多不同的、有用的搜索。

    一个简单的 VLOOKUP Excel 示例

    VLOOKUP 不是您可能学过的基本 Excel 函数之一，所以让我们看一个简单的示例来开始。

    对于以下示例，我们将使用美国学校的 SAT 成绩大型电子表格。 该电子表格包含 450 多所学校以及个人 SAT 阅读、数学和写作成绩。 随意下载跟随。 有一个外部连接可以拉取数据，因此打开文件时会收到警告，但它是安全的。

    搜索如此庞大的数据集以找到您感兴趣的学校将非常耗时。

    相反，您可以在表格一侧的空白单元格中创建一个简单的表单。 要进行此搜索，只需为学校创建一个字段，并为阅读、数学和写作分数创建三个附加字段。

    接下来，您需要使用 Excel 中的 VLOOKUP 函数来使这三个字段起作用。 在阅读字段中，创建 VLOOKUP 函数，如下所示：

    1. 输入 =VLOOKUP(
    2. 选择 School 字段，在本例中为I2 。 键入逗号。
    3. 选择包含要查找的数据的整个单元格区域。 键入逗号。

    选择范围时，您可以从用于查找的列（在本例中为学校名称列）开始，然后选择包含数据的所有其他列和行。

    注意：Excel 中的 VLOOKUP 功能只能通过搜索列右侧的单元格进行搜索。 在此示例中，学校名称列需要位于您正在查找的数据的左侧。

    1. 接下来，要检索阅读分数，您需要从最左边的选定列中选择第三列。 因此，键入3 ，然后键入另一个逗号。
    2. 最后，键入FALSE以获得完全匹配，并使用 a )关闭该函数。

    您最终的 VLOOKUP 函数应如下所示：

     =VLOOKUP(I2,B2:G461,3,FALSE)

    当您第一次按 Enter 并完成该功能时，您会注意到 Reading 字段将包含一个#N/A 。

    这是因为 School 字段是空白的，VLOOKUP 函数找不到任何内容。 但是，如果您输入要查找的任何高中的名称，您将在该行中看到阅读分数的正确结果。

    如何处理 VLOOKUP 区分大小写

    您可能会注意到，如果您键入的学校名称的大小写与它在数据集中的列出方式不同，您将看不到任何结果。

    这是因为 VLOOKUP 函数区分大小写。 这可能很烦人，尤其是对于一个非常大的数据集，其中您正在搜索的列与事物的大写方式不一致。

    为了解决这个问题，您可以在查找结果之前强制将要搜索的内容切换为小写。 为此，请在您正在搜索的列旁边创建一个新列。 键入函数：

     =修剪（降低（B2））

    这将小写学校名称并删除可能位于名称左侧或右侧的任何无关字符（空格）。

    按住 Shift 键并将鼠标光标放在第一个单元格的右下角，直到它变为两条水平线。 双击鼠标自动填充整列。

    最后，由于 VLOOKUP 将尝试使用公式而不是这些单元格中的文本，因此您只需将它们全部转换为值。 为此，请复制整个列，右键单击第一个单元格，然后仅粘贴值。

    现在您的所有数据都已在此新列中清除，稍微修改 Excel 中的 VLOOKUP 函数以使用此新列而不是前一列，方法是从 C2 而不是 B2 开始查找范围。

     =VLOOKUP(I2,C2:G461,3,FALSE)

    现在您会注意到，如果您始终以小写形式输入搜索内容，您将始终获得良好的搜索结果。

    这是一个方便的 Excel 技巧，可以克服 VLOOKUP 区分大小写的事实。

    VLOOKUP 近似匹配

    虽然本文第一部分中描述的精确匹配 LOOKUP 示例非常简单，但近似匹配稍微复杂一些。

    近似匹配最适合用于搜索数字范围。 要正确执行此操作，需要对搜索范围进行正确排序。 最好的例子是 VLOOKUP 函数，用于搜索与数字等级对应的字母等级。

    如果老师有一长串全年的学生作业成绩，并带有最终平均列，那么自动显示与该最终成绩相对应的字母成绩会很好。

    这可以通过 VLOOKUP 功能实现。 所需要的只是右侧的查找表，其中包含每个数字分数范围的适当字母等级。

    现在，使用 VLOOKUP 函数和近似匹配，您可以找到与正确数字范围相对应的正确字母等级。

    在这个 VLOOKUP 函数中：

    • lookup_value : F2，最终平均成绩
    • table_array : I2:J8，字母等级查找范围
    • index_column : 2，查找表中的第二列
    • [range_lookup] ：真，近似匹配

    在 G2 中完成 VLOOKUP 功能并按 Enter 后，您可以使用上一节中描述的相同方法填写其余单元格。 您会看到正确填写的所有字母等级。

    请注意，Excel 中的 VLOOKUP 函数从具有指定字母分数的成绩范围的底端搜索到下一个字母分数的范围的顶端。

    所以，“C”需要是分配给较低范围（75）的字母，而B被分配给它自己的字母范围的底部（最小）。 VLOOKUP 将“找到” 60 (D) 的结果作为 60 到 75 之间任何值的最接近的近似值。

    Excel 中的 VLOOKUP 是一个非常强大的功能，已经使用了很长时间。 它对于在 Excel 工作簿中的任何位置查找匹配值也很有用。

    但是请记住，每月订阅 Office 365 的 Microsoft 用户现在可以访问更新的 XLOOKUP 功能。 这个函数有更多的参数和额外的灵活性。 半年订阅的用户将需要等待更新在 2020 年 7 月推出。