Excel Data Analysis 简明教程

Excel Data Analysis - Lookup Functions

您可以使用 Excel 函数来 −

  1. 在数据范围内查找值 - VLOOKUP 和 HLOOKUP

  2. 从表或范围内获取值或对值的引用 - INDEX

  3. 获取单元格范围内指定项的相对位置 - MATCH

您还可基于提供的输入将这些函数结合使用，以获得所需的结果。

Using VLOOKUP Function

VLOOKUP 函数的语法为

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

其中

  1. lookup_value − 是您想要查找的值。Lookup_value 可以是值或对单元格的引用。Lookup_value 必须位于您在 table_array 中指定单元格范围的第一列

  2. table_array − 是 VLOOKUP 将在其中搜索 lookup_value 和返回值的单元格范围。table_array 必须在第一列包含 lookup_value，并且您要查找的返回值 Note − 包含 lookup_value 的第一列可以按升序排列，也可以不排列。但是，结果将基于此列的顺序。

  3. col_index_num − 是 table_array 中包含返回值的列号。对于 table-array 最左侧的列，号码从 1 开始

  4. range_lookup − 是一个可选的逻辑值，用于指定您希望 VLOOKUP 查找完全匹配还是近似匹配。可以省略 range_lookup，在这种情况下，假定它为 TRUE，并且 VLOOKUP 尝试查找近似匹配结果。也就是，如果找不到完全匹配，则会返回小于 lookup_value 的下一个最大值。TRUE，在这种情况下，VLOOKUP 尝试查找近似匹配。换言之，如果找不到完全匹配，则会返回小于 lookup_value 的下一个最大值FALSE，在这种情况下，VLOOKUP 尝试查找完全匹配1，在这种情况下，假定它为 TRUE，并且 VLOOKUP 尝试查找近似匹配0，在这种情况下，假定它为 FALSE，并且 VLOOKUP 尝试查找完全匹配

Note − 如果省略了 range_lookup，或者将其设为 TRUE 或 1，则 VLOOKUP 只有在 table_array 中的第一列按升序排列时才会正确运行。否则，可能会导致值不正确。在这种情况下，请对 range_lookup 使用 FALSE。

Using VLOOKUP Function with range_lookup TRUE

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

table_array −

vlookup function with true

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

将此数组命名为 Grades 。

按照这种方式命名数组是一个好习惯，这样您就不必记住单元格范围。现在，您可以按照以下步骤准备查找您所拥有的分数列表的成绩−

grades

您可以观察到，

  1. col_index_num – 表示 table_array 中返回值列为 2

  2. range_lookup 为 TRUEtable_array 成绩中包含查找值的第一列按升序排列。因此，结果将是正确的。您还可以获取近似匹配的返回值。即 VLOOKUP 计算如下−

Marks

Pass Category

< 35

Fail

>= 35 且 < 50

Third Class

>= 50 且 < 60

Second Class

>=60 and < 75

First Class

>= 75

First Class with Distinction

您将获得以下结果−

vlookup function with true result

Using VLOOKUP Function with range_lookup FALSE

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

table_array −

table array

将此数组命名为 ProductInfo。

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

  1. 将 VLOOKUP 函数与 range_lookup 用作 TRUE

  2. 将 VLOOKUP 函数与 range_lookup 用作 FALSE

vlookup function with false

正确答案来自 ProductInfo 数组为 171.65。您可以检查结果。

vlookup function with false result

您会观察到获得−

  1. range_lookup 为 FALSE 时的正确结果，和

  2. range_lookup 为 TRUE 时的错误结果。

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

Using HLOOKUP Function

如果数据是以行而不是列的形式，则可以使用 HLOOKUP 函数。

Example

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

hlookup function

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

HLOOKUP 函数的语法为：

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

其中

  1. lookup_value − 是表的第一行中要查找的值

  2. table_array − 是查找数据的资料表

  3. row_index_num − 是 table_array 中的行号，将从中返回匹配的值

  4. range_lookup − 是逻辑值，用于指定希望 HLOOKUP 查找精确匹配还是近似匹配

  5. range_lookup 可以省略，省略时假定为 TRUE，并且 HLOOKUP 尝试查找近似匹配。换句话说，如果找不到精确匹配，则返回小于 lookup_value 的下一个最大值。TRUE，这意味着 HLOOKUP 尝试查找近似匹配。换句话说，如果找不到精确匹配，则返回小于 lookup_value 的下一个最大值。FALSE，这意味着 HLOOKUP 尝试查找精确匹配。1，这意味着假定为 TRUE，并且 HLOOKUP 尝试查找近似匹配。0，这意味着假定为 FALSE，并且 HLOOKUP 尝试查找精确匹配

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

Using HLOOKUP Function with range_lookup FALSE

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

  1. 使用 HLOOKUP 函数，其中 range_lookup 为 TRUE。

  2. 使用 HLOOKUP 函数，其中 range_lookup 为 FALSE。

hlookup function with false

ProductRange 数组中的正确答案为 171.65。您可以查看结果。

hlookup function with false result

您观察到，就像在 VLOOKUP 的情况下，您获得了

  1. range_lookup 为 FALSE 时的正确结果，和

  2. range_lookup 为 TRUE 时的错误结果。

这是因为 ProductRange 数组中的第一行没有按升序排序。因此，请记住，只要数据未按升序排序，就始终使用 FALSE。

Using HLOOKUP Function with range_lookup TRUE

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

table_array −

hlookup function with true

将此数组命名为 GradesRange。

请注意，基于此获得成绩的第一行按升序排序。因此，对 range_lookup 参数使用 TRUE，可以使用 HLOOKUP 获取近似匹配的成绩，这就是所需的内容。

gradesrange

您可以观察到，

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

  2. range_lookup 为 TRUEtable_array Grades 中包含查找值的第一列按升序排列。因此，结果将是正确的。您还可以获取近似匹配的返回值。即 HLOOKUP 计算如下 −

Marks

< 35

>= 35 且 < 50

>= 50 且 < 60

>=60 and < 75

>= 75

Pass Category

Fail

Third Class

Second Class

First Class

First Class with Distinction

您将获得以下结果−

student grades

Using INDEX Function

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

考虑以下销售数据，其中您可以找到由所列销售员在北、南、东和西各地区中的销售额。

salesdata

  1. 将数组命名为 SalesData。

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

  1. 在特定区域中任何销售人员的销售额。

  2. 由所有销售人员在某个区域中的销售总额。

  3. 销售人员在所有区域中的销售总额。

using index function

您将获得以下结果−

using index function result

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

您可以使用 MATCH 函数执行此操作，如下一节所述。

Using MATCH Function

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

using match function

您将获得以下结果−

using match function result