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，则 VLOOKUP 仅在 table_array 中的第一列按升序排序时才能正常工作。否则，可能会导致不正确的值。在这种情况下，对 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 等级中包含查找值的第一列按升序排列。因此，结果将是正确的。

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

Marks	通行证类别
< 35	失败
>= 35 and < 50	三等舱
>= 50 and < 60	二等舱
>=60 and < 75	头等舱
>= 75	一流的一流

您将获得以下结果 –

使用带有 range_lookup FALSE 的 VLOOKUP 函数

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

table_array –

将此数组命名为 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函数。

例子

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

• 将此数组产品范围命名为。您可以使用 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，则 HLOOKUP 仅在 table_array 中的第一列按升序排序时才能正常工作。否则，可能会导致不正确的值。在这种情况下，对 range_lookup 使用 FALSE。

使用带有 range_lookup FALSE 的 HLOOKUP 函数

您可以使用 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。

使用带有 range_lookup TRUE 的 HLOOKUP 函数

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

table_array –

将此数组命名为 GradesRange。

请注意，获得成绩所依据的第一行标记按升序排列。因此，将 HLOOKUP 与 TRUE 用于 range_lookup 参数，您可以获得近似匹配的成绩，这就是所需要的。

正如你所观察到的，

• row_index_num – 表示 table_array 中返回值的列是 2

• 该range_lookup为TRUE

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

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

Marks	< 35	>= 35 和 < 50	>= 50 和 < 60	>=60 和 < 75	>= 75
Pass Category	失败	三等舱	二等舱	头等舱	一流的一流

您将获得以下结果 –

使用 INDEX 函数

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

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

• 将该数组命名为 SalesData。

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

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

您将获得以下结果 –

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

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

使用 MATCH 函数

如果需要某个项目在某个范围内的位置，可以使用 MATCH 函数。您可以按如下方式组合 MATCH 和 INDEX 函数 –

您将获得以下结果 –