Excel 数据分析 - 查找函数

您可以使用 Excel 函数来 −

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

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

使用 VLOOKUP 函数

VLOOKUP 函数的语法是

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

Where

lookup_value − 是您要查找的值。 Lookup_value 可以是值或对单元格的引用。 Lookup_value 必须位于您在 table_array 中指定的单元格范围的第一列
table_array − 是 VLOOKUP 将在其中搜索 lookup_value 和返回值的单元格范围。 table_array 必须包含
- 第一列中的查找值，以及
- 要查找的返回值
  
  注意 − 包含 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 为真时使用 VLOOKUP 函数

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

table_array −

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

将此数组命名为Grades。

以这种方式命名数组是一种很好的做法，这样您就无需记住单元格范围。现在，您可以按如下方式查找您所拥有的标记列表的成绩 −

如您所见，

col_index_num − 表示 table_array 中返回值的列为 2
range_lookup 为 TRUE
- table_array grades 中包含查找值的第一列按升序排列。因此，结果将是正确的。
- 您还可以获得近似匹配的返回值。即 VLOOKUP 计算如下 −

标记	传递类别
< 35	Fail
>= 35 and < 50	Third Class
>= 50 and < 60	Second Class
>=60 and < 75	First Class
>= 75	First Class with Distinction

你会得到以下结果 −

在 range_lookup FALSE 的情况下使用 VLOOKUP 函数

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

table_array −

将此数组命名为 ProductInfo。

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

使用 range_lookup 为 TRUE 的 VLOOKUP 函数
使用 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])

Where

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 为 Omitted 或 TRUE 或 1，则仅当 table_array 中的第一列按升序排序时 HLOOKUP 才能正常工作。否则，它可能会导致不正确的值。在这种情况下，对 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 计算如下 −