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 中包含返回值的列号。表数组最左边的列的数字从 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。

将 VLOOKUP 函数与 range_lookup TRUE 结合使用

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

表数组 -

请注意，获得成绩所依据的第一列分数按升序排序。因此，使用 TRUE 作为 range_lookup 参数，您可以获得所需的近似匹配。

将此数组命名为Grades。

以这种方式命名数组是一个很好的做法，这样您就不需要记住单元格范围。现在，您已准备好查找您的分数列表的成绩，如下所示 -

正如你所观察到的，

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

• range_lookup为TRUE

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

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

您将得到以下结果 -

将 VLOOKUP 函数与 range_lookup FALSE 结合使用

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

表数组 -

将此数组命名为 ProductInfo。

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

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

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。

将 HLOOKUP 函数与 range_lookup FALSE 结合使用

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

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

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

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

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

• 当 range_lookup 为 TRUE 时，结果错误。

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

将 HLOOKUP 函数与 range_lookup TRUE 一起使用

考虑 VLOOKUP 中使用的学生分数的示例。假设您的数据位于行而不是列中，如下表所示 -

表数组 -

将此数组命名为 GradesRange。

请注意，获得成绩所依据的第一行分数按升序排序。因此，使用 HLOOKUP 和 TRUE 作为 range_lookup 参数，您可以获得近似匹配的成绩，这就是所需要的。

正如你所观察到的，

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

• range_lookup为TRUE

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

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

您将得到以下结果 -

使用索引函数

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

考虑以下销售数据，您可以在其中找到列出的销售人员在北、南、东、西每个区域的销售额。

• 将数组命名为 SalesData。

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

• 某个区域内任何销售人员的销售额。
• 某个地区所有销售人员的总销售额。
• 所有区域销售人员的总销售额。

您将得到以下结果 -

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

您可以使用 MATCH 函数来完成此操作，如下一节所述。

使用 MATCH 函数

如果需要某个范围内某个项目的位置，可以使用 MATCH 函数。您可以组合 MATCH 和 INDEX 函数，如下所示 -

您将得到以下结果 -