Excel 中的 VLOOKUP 函数 - 初学者指南：语法和示例

今天我们开始发表一系列文章，描述 Excel 最有用的功能之一 - VPR （VLOOKUP）。 同时，此功能是最复杂和最不了解的功能之一。

在本教程中 VPR 我将尝试尽可能简单地列出基础知识，以便让没有经验的用户尽可能清楚地了解学习过程。 此外，我们将研究几个带有 Excel 公式的示例，这些示例将演示该函数的最常见用例 VPR.

  • 一般描述和语法
  • VLOOKUP 示例
    • 如何使用 VLOOKUP 在另一个 Excel 工作表中进行搜索
    • 使用 VLOOKUP 在另一个工作簿中搜索
    • 如何使用 VLOOKUP 在公式中使用命名区域或表格
    • 在 VLOOKUP 公式中使用通配符
  • VLOOKUP 函数中的精确或近似匹配
  • Excel 中的 VLOOKUP - 您需要记住这一点！

Excel 中的 VLOOKUP 函数 – 一般描述和语法

那是什么 VPR? 嗯，首先，它是一个 Excel 函数。 她在干嘛？ 它查找您指定的值并从另一列返回相应的值。 从技术上讲， VPR 在给定范围的第一列中查找值，并从同一行中的另一列返回结果。

在最常见的应用中，函数 VPR 在数据库中搜索给定的唯一标识符，并从数据库中提取一些与之相关的信息。

函数名中的第一个字母 VPR (VLOOKUP) 是指 В垂直的 （V垂直的）。 通过它你可以区分 VPR 止 探地雷达 （HLOOKUP），它在范围的顶行中搜索一个值 - Г水平的 （H水平）。

功能 VPR 在 Excel 2013、Excel 2010、Excel 2007、Excel 2003、Excel XP 和 Excel 2000 中可用。

VLOOKUP 函数的语法

功能 VPR (VLOOKUP) 具有以下语法：

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

如您所见，一个函数 VPR 在 Microsoft Excel 中有 4 个选项（或参数）。 前三个是强制性的，最后一个是可选的。

  • Lookup_Array中 (lookup_value) – 要查找的值。 这可以是一个值（数字、日期、文本）或单元格引用（包含查找值），或由其他 Excel 函数返回的值。 例如，此公式将查找值 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

如果查找值小于被查找范围的第一列中的最小值，则函数 VPR 会报错 ＃在 （#不适用）。

  • 表格数组 (table) – 两列或多列数据。 记住，函数 VPR 总是在参数给定范围的第一列中查找值 表格数组 （桌子）。 可视范围可以包含各种数据，例如文本、日期、数字、布尔值。 该函数不区分大小写，这意味着大写和小写字符被认为是相同的。 所以我们的公式将寻找值 40 在细胞中 A2 至 A15，因为 A 是参数中给定的范围 A2:B15 的第一列 表格数组 （桌子）：

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • Col_index_num为 (column_number) 是给定范围内的列号，从该列号中返回找到的行中的值。 给定范围内最左边的列是 1，第二列是 2，第三列是 3 等等。 现在您可以阅读整个公式：

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    寻找价值的公式 40 范围中 A2：A15 并从 B 列返回相应的值（因为 B 是 A2:B15 范围内的第二列）。

如果参数的值 Col_index_num为 (column_number) 小于 1然后 VPR 会报错 ＃值！ （＃价值！）。 如果它超过范围内的列数 表格数组 (table)，函数会返回错误 ＃REF！ （＃关联！）。

  • 范围查找 (range_lookup) – 确定要查找的内容：
    • 完全匹配，参数必须相等 FALSE （错误的）;
    • 近似匹配，参数等于 真实代码 (TRUE) 或根本未指定。

    这个参数是可选的，但非常重要。 稍后在本教程中 VPR 我将向您展示一些示例，解释如何编写用于查找精确匹配和近似匹配的公式。

VLOOKUP 示例

希望功能 VPR 对你来说更清楚一点。 现在让我们看一些用例 VPR 在具有真实数据的公式中。

如何使用 VLOOKUP 在另一个 Excel 工作表中进行搜索

在实践中，具有函数的公式 VPR 很少用于在同一个工作表上搜索数据。 通常，您会从另一张工作表中查找并检索相应的值。

为了使用 VPR, 在另一个 Microsoft Excel 工作表中搜索，您必须在参数中 表格数组 （表）用感叹号指定工作表名称，后跟一系列单元格。 例如，下面的公式表明范围 A2：B15 在一张名为 Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

当然，工作表名称不必手动输入。 只需开始输入公式，当涉及到参数时 表格数组 （表格），切换到所需的工作表并用鼠标选择所需的单元格范围。

下面屏幕截图中显示的公式在工作表的 A 列（它是范围 A1:B1 的第一列）中查找文本“产品 2” 价格.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

请记住，在搜索文本值时，必须将其括在引号 (“”) 中，这在 Excel 公式中通常是这样做的。

论据 表格数组 （表）最好总是使用绝对引用（带有 $ 符号）。 在这种情况下，将公式复制到其他单元格时，搜索范围将保持不变。

使用 VLOOKUP 在另一个工作簿中搜索

发挥作用 VPR 在两个 Excel 工作簿之间工作，您需要在工作表名称前的方括号中指定工作簿名称。

例如，下面是一个查找值的公式 40 在单子上 Sheet2 在这本书中 数字.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

这是在 Excel 中创建公式的最简单方法 VPR链接到另一个工作簿：

  1. 打开两本书。 这不是必需的，但以这种方式创建公式更容易。 您不想手动输入工作簿名称，是吗？ 此外，它将保护您免受意外输入错误的影响。
  2. 开始输入函数 VPR当谈到争论时 表格数组 （表），切换到另一个工作簿并在其中选择所需的搜索范围。

下面的屏幕截图显示了将搜索设置为工作簿中某个范围的公式 价格表.xlsx 在单子上 价格.

功能 VPR 即使您关闭搜索到的工作簿并且工作簿文件的完整路径出现在编辑栏中，也将起作用，如下所示：

如果工作簿或工作表的名称包含空格，则必须用撇号括起来：

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

如何使用 VLOOKUP 在公式中使用命名区域或表格

如果您计划在多个函数中使用相同的搜索范围 VPR，您可以创建一个命名范围并将其名称作为参数输入到公式中 表格数组 （桌子）。

要创建命名范围，只需选择单元格并在字段中输入适当的名称 名字，在公式栏的左侧。

现在您可以写下以下公式来查找产品的价格 产品1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

大多数范围名称适用于整个 Excel 工作簿，因此无需为参数指定工作表名称 表格数组 （表），即使公式和搜索范围位于不同的工作表上。 如果它们在不同的工作簿中，则需要在范围名称之前指定工作簿的名称，例如，像这样：

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

所以公式看起来更清晰，同意吗？ 此外，使用命名范围是绝对引用的一个很好的替代方法，因为当您将公式复制到其他单元格时，命名范围不会改变。 这意味着您可以确保公式中的搜索范围始终保持正确。

如果您使用以下命令将一系列单元格转换为完整的 Excel 电子表格 表 （表）选项卡 插入 （插入），然后当您用鼠标选择一个范围时，Microsoft Excel 会自动将列名（如果您选择整个表，则为表名）添加到公式中。

完成的公式将如下所示：

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

或者甚至可能是这样的：

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

使用命名范围时，无论您将函数复制到何处，链接都将指向相同的单元格 VPR 工作簿内。

在 VLOOKUP 公式中使用通配符

与许多其他功能一样， VPR 您可以使用以下通配符：

  • 问号 (?) – 替换任何单个字符。
  • 星号 (*) – 替换任何字符序列。

在函数中使用通配符 VPR 在许多情况下可能很有用，例如：

  • 当您不记得确切需要查找的文本时。
  • 当您想查找属于单元格内容的某个单词时。 我知道 VPR 按整个单元格的内容进行搜索，就像启用了该选项一样 匹配整个单元格内容 （整个单元格）在标准 Excel 搜索中。
  • 当单元格在内容的开头或结尾包含额外的空格时。 在这种情况下，你可以绞尽脑汁想办法弄清楚为什么公式不起作用。

示例 1：查找以某些字符开头或结尾的文本

假设您要在如下所示的数据库中搜索特定客户。 您不记得他的姓氏，但您知道它以“ack”开头。 这是一个可以很好地完成工作的公式：

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

现在您确定您找到了正确的名称，您可以使用相同的公式来查找该客户支付的金额。 为此，只需更改函数的第三个参数 VPR 到所需的列号。 在我们的例子中，这是 C 列（范围内的第 3 列）：

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

以下是更多使用通配符的示例：

~ 查找以“man”结尾的名字：

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ 查找以“ad”开头并以“son”结尾的名称：

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ 我们在列表中找到第一个名字，由 5 个字符组成：

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

发挥作用 VPR 通配符可以正常工作，作为您应该始终使用的第四个参数 FALSE （错误的）。 如果搜索范围包含多个值与通配符的搜索词匹配，则将返回找到的第一个值。

示例 2：在 VLOOKUP 公式中组合通配符和单元格引用

现在让我们看一个稍微复杂一点的例子，说明如何使用函数进行搜索 VPR 按单元格中的值。 假设 A 列是许可证密钥列表，B 列是拥有许可证的名称列表。 此外，您在单元格 C1 中有某种许可证密钥的一部分（几个字符），并且您想查找所有者的姓名。

这可以使用以下公式完成：

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

此公式从给定范围内的单元格 C1 中查找值，并从列 B 中返回相应的值。请注意，在第一个参数中，我们在单元格引用前后使用与号 (&) 字符来链接文本字符串。

如下图所示，函数 VPR 返回“Jeremy Hill”，因为他的许可证密钥包含单元格 C1 中的字符序列。

请注意，参数 表格数组 上面屏幕截图中的 (table) 包含表 (Table7) 的名称，而不是指定单元格范围。 这就是我们在前面的例子中所做的。

VLOOKUP 函数中的精确或近似匹配

最后，让我们仔细看看为函数指定的最后一个参数 VPR – 范围查找 （间隔视图）。 正如本课开始时提到的，这个论点非常重要。 你可以用它的值在同一个公式中得到完全不同的结果 真实代码 (真) 或 FALSE （错误的）。

首先，让我们了解一下 Microsoft Excel 中的精确匹配和近似匹配是什么意思。

  • 如果论证 范围查找 (range_lookup) 等于 FALSE (FALSE)，公式查找完全匹配，即与参数中给出的值完全相同 Lookup_Array中 （查找值）。 如果在范围 t 的第一列能数组 (table) 遇到两个或多个与参数匹配的值 Lookup_Array中 (search_value)，然后将选择第一个。 如果没有找到匹配，函数会报错 ＃在 （#不适用）。 比如下面的公式会报错 ＃在 (#N/A) 如果 A2:A15 范围内没有值 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • 如果论证 范围查找 (range_lookup) 等于 真实代码 (TRUE)，公式查找近似匹配。 更准确地说，首先是函数 VPR 查找完全匹配，如果没有找到，则选择一个近似匹配。 近似匹配是不超过参数中指定值的最大值。 Lookup_Array中 （查找值）。

如果论证 范围查找 (range_lookup) 等于 真实代码 (TRUE) 或不指定，则范围第一列的值应按升序排列，即从小到大。 否则，函数 VPR 可能会返回错误的结果。

为了更好地理解选择的重要性 真实代码 （真相）或 FALSE (FALSE)，让我们看看更多的公式与函数 VPR 并查看结果。

示例 1：使用 VLOOKUP 查找完全匹配

你还记得，要搜索完全匹配，函数的第四个参数 VPR 应该很重要 FALSE （错误的）。

让我们从第一个例子回到表格，找出哪种动物可以快速移动 50 英里每小时。 我相信这个公式不会给你带来任何困难：

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

请注意，我们的搜索范围（A 列）包含两个值 50 – 在细胞中 A5 и A6. 公式从单元格返回值 B5. 为什么？ 因为在寻找完全匹配时，函数 VPR 使用找到的第一个与正在搜索的值匹配的值。

示例 2：使用 VLOOKUP 查找近似匹配

当您使用该功能时 VPR 搜索近似匹配，即当参数 范围查找 (range_lookup) 等于 真实代码 (TRUE) 或省略，您需要做的第一件事是按第一列升序对范围进行排序。

这非常重要，因为函数 VPR 返回给定值之后的下一个最大值，然后搜索停止。 如果您忽略了正确的排序，您最终会得到非常奇怪的结果或错误消息。 ＃在 （#不适用）。

现在您可以使用以下公式之一：

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

如您所见，我想找出哪种动物的速度最接近 69 英里每小时。 这是函数返回给我的结果 VPR:

如您所见，公式返回了一个结果 羚羊 （羚羊），它的速度 61 每小时英里数，尽管该列表还包括 Cheetah （猎豹）奔跑的速度 70 英里每小时，70 比 69 更接近 61，不是吗？ 为什么会这样？ 因为函数 VPR 搜索近似匹配时，返回不大于正在搜索的值的最大值。

我希望这些示例对使用该函数有所帮助 VPR 在 Excel 中，您不再将她视为局外人。 现在简单地重复我们研究过的材料的关键点，以便更好地在记忆中固定它并没有什么坏处。

Excel 中的 VLOOKUP - 您需要记住这一点！

  1. 功能 VPR Excel 无法向左看。 它总是在参数给定范围的最左列中查找值 表格数组 （桌子）。
  2. 在功能上 VPR 所有值都不区分大小写，即大小写字母是等价的。
  3. 如果您要查找的值小于要查找的范围的第一列中的最小值，则函数 VPR 会报错 ＃在 （#不适用）。
  4. 如果第三个参数 Col_index_num为 (column_number) 小于 1功能 VPR 会报错 ＃值！ （＃价值！）。 如果大于范围内的列数 表格数组 (table)，函数会报错 ＃REF！ （＃关联！）。
  5. 在参数中使用绝对单元格引用 表格数组 （表），以便在复制公式时保留正确的搜索范围。 尝试在 Excel 中使用命名范围或表格作为替代方法。
  6. 在进行近似匹配搜索时，请记住您要查找的范围中的第一列必须按升序排序。
  7. 最后，记住第四个论点的重要性。 使用价值观 真实代码 （真相）或 FALSE (FALSE) 故意的，你会摆脱很多头痛。

在我们功能教程的以下文章中 VPR 在 Excel 中，我们将学习更高级的示例，例如使用 VPR，从多列中提取值等等。 感谢您阅读本教程，希望下周再次见到您！

发表评论