Excel 数据分析 - 查找函数

---

---

您可以使用 Excel 函数来：

• 在数据范围内查找值 - VLOOKUP 和 HLOOKUP
• 从表格或区域中获取值或值的引用 - INDEX
• 获取指定项在单元格区域中的相对位置 - MATCH

您还可以组合这些函数，根据您的输入获得所需的结果。

使用 VLOOKUP 函数

VLOOKUP 函数的语法为

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

其中

• lookup_value − 是您要查找的值。Lookup_value 可以是值或单元格引用。Lookup_value 必须位于您在 table_array 中指定的单元格区域的第一列中

• table_array − 是 VLOOKUP 将在其中查找 lookup_value 和返回值的单元格区域。table_array 必须包含

  • 第一列中的 lookup_value，以及

  • 您要查找的返回值

    注意 − 包含 lookup_value 的第一列可以按升序排序，也可以不排序。但是，结果将基于此列的顺序。

• col_index_num − 是 table_array 中包含返回值的列号。数字从 table-array 最左列的 1 开始

• range_lookup − 是一个可选的逻辑值，指定您希望 VLOOKUP 查找精确匹配还是近似匹配。range_lookup 可以是

  • 省略，在这种情况下，它被假定为 TRUE，并且 VLOOKUP 尝试查找近似匹配

  • TRUE，在这种情况下，VLOOKUP 尝试查找近似匹配。换句话说，如果找不到精确匹配，则返回小于 lookup_value 的下一个最大值

  • FALSE，在这种情况下，VLOOKUP 尝试查找精确匹配

  • 1，在这种情况下，它被假定为 TRUE，并且 VLOOKUP 尝试查找近似匹配

  • 0，在这种情况下，它被假定为 FALSE，并且 VLOOKUP 尝试查找精确匹配

注意 − 如果 range_lookup 被省略或为 TRUE 或 1，则只有当 table_array 中的第一列按升序排序时，VLOOKUP 才能正常工作。否则，可能会导致错误的值。在这种情况下，请为 range_lookup 使用 FALSE。

使用 range_lookup 为 TRUE 的 VLOOKUP 函数

考虑一个学生成绩列表。您可以使用 VLOOKUP 从包含分数区间和通过类别的一个数组中获取相应的等级。

table_array −

请注意，用于获取等级的第一列分数按升序排序。因此，使用 TRUE 作为 range_lookup 参数，您可以获得近似匹配，这正是需要的。

将此数组命名为Grades。

建议以这种方式命名数组，这样您就不需要记住单元格范围。现在，您可以按如下方式查找您拥有的分数列表的等级：

如您所见，

• col_index_num − 表示 table_array 中返回值的列为 2

• range_lookup 为 TRUE

  • table_array 等级中包含查找值的第 1 列按升序排列。因此，结果将是正确的。

  • 您还可以获得近似匹配的返回值。即 VLOOKUP 计算如下：

您将获得以下结果：

使用 range_lookup 为 FALSE 的 VLOOKUP 函数

考虑一个包含产品 ID 和每个产品的价格的产品列表。每当推出新产品时，产品 ID 和价格都将添加到列表的末尾。这意味着产品 ID 不需要按升序排列。产品列表可能如下所示：

table_array −

将此数组命名为 ProductInfo。

您可以使用 VLOOKUP 函数获取给定产品 ID 的产品价格，因为产品 ID 在第一列中。价格在第 3 列中，因此 col_index_num 应为 3。

• 使用 range_lookup 为 TRUE 的 VLOOKUP 函数
• 使用 range_lookup 为 FALSE 的 VLOOKUP 函数

ProductInfo 数组的正确答案是 171.65。您可以检查结果。

您观察到您得到了：

• 当 range_lookup 为 FALSE 时得到正确的结果，以及
• 当 range_lookup 为 TRUE 时得到错误的结果。

这是因为 ProductInfo 数组中的第一列未按升序排序。因此，请记住，当数据未排序时，请使用 FALSE。

使用 HLOOKUP 函数

如果数据位于行而不是列中，则可以使用HLOOKUP 函数。

示例

让我们以产品信息为例。假设数组如下所示：

• 将此数组命名为 ProductRange。您可以使用 HLOOKUP 函数查找给定产品 ID 的产品价格。

HLOOKUP 函数的语法为

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

其中

• lookup_value − 是要在表格的第一行中查找的值

• table_array − 是查找数据的表格

• row_index_num − 是 table_array 中将返回匹配值的行的行号

• range_lookup − 是一个逻辑值，指定您希望 HLOOKUP 查找精确匹配还是近似匹配

• range_lookup 可以是

  • 省略，在这种情况下，它被假定为 TRUE，并且 HLOOKUP 尝试查找近似匹配

  • TRUE，在这种情况下，HLOOKUP 尝试查找近似匹配。换句话说，如果找不到精确匹配，则返回小于 lookup_value 的下一个最大值

  • FALSE，在这种情况下，HLOOKUP 尝试查找精确匹配

  • 1，在这种情况下，它被假定为 TRUE，并且 HLOOKUP 尝试查找近似匹配

  • 0，在这种情况下，它被假定为 FALSE，并且 HLOOKUP 尝试查找精确匹配

注意 − 如果 range_lookup 被省略或为 TRUE 或 1，则只有当 table_array 中的第一列按升序排序时，HLOOKUP 才能正常工作。否则，可能会导致错误的值。在这种情况下，请为 range_lookup 使用 FALSE。

使用 range_lookup 为 FALSE 的 HLOOKUP 函数

您可以使用 HLOOKUP 函数获取给定产品 ID 的产品价格，因为产品 ID 在第一行中。价格在第 3 行中，因此 row_index_num 应为 3。

• 使用 range_lookup 为 TRUE 的 HLOOKUP 函数。
• 使用 range_lookup 为 FALSE 的 HLOOKUP 函数。

ProductRange 数组的正确答案是 171.65。您可以检查结果。

您观察到，与 VLOOKUP 的情况一样，您得到了

• 当 range_lookup 为 FALSE 时得到正确的结果，以及

• 当 range_lookup 为 TRUE 时得到错误的结果。

这是因为 ProductRange 数组中的第一行未按升序排序。因此，请记住，当数据未排序时，请使用 FALSE。

使用 range_lookup 为 TRUE 的 HLOOKUP 函数

考虑在 VLOOKUP 中使用的学生成绩示例。假设您将数据存储在行而不是列中，如下表所示：

table_array −

将此数组命名为 GradesRange。

请注意，用于获取等级的第一行分数按升序排序。因此，使用 HLOOKUP 并将 TRUE 作为 range_lookup 参数，您可以获得近似匹配的等级，这正是需要的。

如您所见，

• row_index_num − 表示 table_array 中返回值的列为 2

• range_lookup 为 TRUE

  • table_array 等级中包含查找值的第 1 列按升序排列。因此，结果将是正确的。

  • 您还可以获得近似匹配的返回值。即 HLOOKUP 计算如下：

您将获得以下结果：

使用 INDEX 函数

当您有一个数据数组时，您可以通过指定该值在数组中的行号和列号来检索数组中的值。

考虑以下销售数据，其中您将找到销售人员在每个北部、南部、东部和西部地区的销售额。

• 将数组命名为 SalesData。

使用 INDEX 函数，您可以找到：

• 某个特定区域中任何销售人员的销售额。
• 所有销售人员在某个区域中的总销售额。
• 某个销售人员在所有区域中的总销售额。

您将获得以下结果：

假设您不知道销售人员的行号和区域的列号。然后，在使用索引函数检索值之前，您需要先找到行号和列号。

您可以使用下一节中解释的 MATCH 函数来执行此操作。

使用 MATCH 函数

如果您需要项在区域中的位置，则可以使用 MATCH 函数。您可以按如下方式组合 MATCH 和 INDEX 函数：

您将获得以下结果：