Excel Data Analysis 简明教程

Excel Data Analysis - Lookup Functions

您可以使用 Excel 函数来 −

You can use Excel functions to −

  1. Find values in a range of data - VLOOKUP and HLOOKUP

  2. Obtain a value or the reference to a value from within a table or range - INDEX

  3. Obtain the relative position of a specified item in a range of cells - MATCH

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

You can also combine these functions to get the required results based on the inputs you have.

Using VLOOKUP Function

VLOOKUP 函数的语法为

The syntax of the VLOOKUP function is

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

其中

Where

  1. lookup_value − is the value you want to look up. Lookup_value can be a value or a reference to a cell. Lookup_value must be in the first column of the range of cells you specify in table_array

  2. table_array − is the range of cells in which the VLOOKUP will search for the lookup_value and the return value. table_array must contain the lookup_value in the first column, and the return value you want to find Note − The first column containing the lookup_value can either be sorted in ascending order or not. However, the result will be based on the order of this column.

  3. col_index_num − is the column number in the table_array that contains the return value. The numbers start with 1 for the left-most column of table-array

  4. range_lookup − is an optional logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. range_lookup can be omitted, in which case it is assumed to be TRUE and VLOOKUP tries to find an approximate match TRUE, in which case VLOOKUP tries to find an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned FALSE, in which case VLOOKUP tries to find an exact match 1, in which case it is assumed to be TRUE and VLOOKUP tries to find an approximate match 0, in which case it is assumed to be FALSE and VLOOKUP tries to find an exact match

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

Note − If range_lookup is omitted or TRUE or 1, VLOOKUP works correctly only when the first column in table_array is sorted in ascending order. Otherwise, it may result in incorrect values. In such a case, use FALSE for range_lookup.

Using VLOOKUP Function with range_lookup TRUE

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

Consider a list of student marks. You can obtain the corresponding grades with VLOOKUP from an array containing the marks intervals and pass category.

table_array −

vlookup function with true

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

Note that the first column marks based on which the grades are obtained is sorted in ascending order. Hence, using TRUE for range_lookup argument you can get approximate match that is what is required.

将此数组命名为 Grades 。

Name this array as Grades.

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

It is a good practice to name arrays in this way so that you need not remember the cell ranges. Now, you are ready to look up the grade for the list of marks you have as follows −

grades

您可以观察到，

As you can observe,

  1. col_index_num − indicates the column of the return value in table_array is 2

  2. the range_lookup is TRUE The first column containing the lookup value in the table_array grades is in ascending order. Hence, the results will be correct. You can get the return value for approximate matches also. i.e. VLOOKUP computes as follows −

Marks

Pass Category

< 35

Fail

>= 35 and < 50

Third Class

>= 50 and < 60

Second Class

>=60 and < 75

First Class

>= 75

First Class with Distinction

您将获得以下结果−

You will get the following results −

vlookup function with true result

Using VLOOKUP Function with range_lookup FALSE

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

Consider a list of products containing the Product ID and price for each of the products. The product ID and price will be added to the end of the list whenever a new product is launched. This would mean that the product IDs need not be in ascending order. The product list might be as shown below −

table_array −

table array

将此数组命名为 ProductInfo。

Name this array as ProductInfo.

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

You can obtain the price of a product given the product ID with the VLOOKUP function as the product ID is in the first column. The price is in column 3 and hence col_index_ num should be 3.

  1. Use VLOOKUP Function with range_lookup as TRUE

  2. Use VLOOKUP Function with range_lookup as FALSE

vlookup function with false

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

The correct answer is from the ProductInfo array is 171.65. You can check the results.

vlookup function with false result

您会观察到获得−

You observe that you got −

  1. The correct result when range_lookup is FALSE, and

  2. A wrong result when range_lookup is TRUE.

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

This is because, the first column in the ProductInfo array is not sorted in ascending order. Hence, remember to use FALSE whenever the data is not sorted.

Using HLOOKUP Function

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

You can use HLOOKUP function if the data is in rows rather than columns.

Example

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

Let us take the example of product information. Suppose the array looks as follows −

hlookup function

  1. Name this Array ProductRange. You can find the price of a product given the product ID with HLOOKUP function.

HLOOKUP 函数的语法为：

The Syntax of HLOOKUP function is

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

其中

Where

  1. lookup_value − is the value to be found in the first row of the table

  2. table_array − is a table of information in which data is looked up

  3. row_index_num − is the row number in table_array from which the matching value will be returned

  4. range_lookup − is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match

  5. range_lookup can be omitted, in which case it is assumed to be TRUE and HLOOKUP tries to find an approximate match TRUE, in which case HLOOKUP tries to find an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned FALSE, in which case HLOOKUP tries to find an exact match 1, in which case it is assumed to be TRUE and HLOOKUP tries to find an approximate match 0, in which case it is assumed to be FALSE and HLOOKUP tries to find an exact match

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

Note − If range_lookup is Omitted or TRUE or 1, HLOOKUP works correctly only when the first column in table_array is sorted in ascending order. Otherwise, it may result in incorrect values. In such a case, use FALSE for range_lookup.

Using HLOOKUP Function with range_lookup FALSE

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

You can obtain the price of a product given the product ID with the HLOOKUP function as the product ID is in the first row. The price is in row 3 and hence row_index_ num should be 3.

  1. Use HLOOKUP Function with range_lookup as TRUE.

  2. Use HLOOKUP Function with range_lookup as FALSE.

hlookup function with false

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

The correct answer from the ProductRange array is 171.65. You can check the results.

hlookup function with false result

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

You observe that as in the case of VLOOKUP, you got

  1. The correct result when range_lookup is FALSE, and

  2. A wrong result when range_lookup is TRUE.

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

This is because the first row in the ProductRange array is not sorted in ascending order. Hence, remember to use FALSE whenever the data is not sorted.

Using HLOOKUP Function with range_lookup TRUE

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

Consider the example of student marks used in VLOOKUP. Suppose you have the data in rows instead of columns as shown in the table given below −

table_array −

hlookup function with true

将此数组命名为 GradesRange。

Name this array as GradesRange.

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

Note that the first row marks based on which the grades are obtained is sorted in ascending order. Hence, using HLOOKUP with TRUE for range_lookup argument, you can get the Grades with approximate match and that is what is required.

gradesrange

您可以观察到，

As you can observe,

  1. row_index_num − indicates the column of the return value in table_array is 2

  2. the range_lookup is TRUE The first column containing the lookup value in the table_array Grades is in ascending order. Hence, the results will be correct. You can get the return value for approximate matches also. i.e. HLOOKUP computes as follows −

Marks

< 35

>= 35 and < 50

>= 50 and < 60

>=60 and < 75

>= 75

Pass Category

Fail

Third Class

Second Class

First Class

First Class with Distinction

您将获得以下结果−

You will get the following results −

student grades

Using INDEX Function

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

When you have an array of data, you can retrieve a value in the array by specifying the row number and column number of that value in the array.

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

Consider the following sales data, wherein you find the sales in each of the North, South, East and West regions by the salespersons who are listed.

salesdata

  1. Name the array as SalesData.

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

Using INDEX Function, you can find −

  1. The Sales of any of the Salespersons in a certain Region.

  2. Total Sales in a Region by all the Salespersons.

  3. Total Sales by a Salesperson in all the Regions.

using index function

您将获得以下结果−

You will get the following results −

using index function result

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

Suppose you do not know the row numbers for the salespersons and column numbers for the regions. Then, you need to find the row number and column number first before you retrieve the value with the index function.

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

You can do it with the MATCH function as explained in the next section.

Using MATCH Function

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

If you need the position of an item in a range, you can use the MATCH function. You can combine MATCH and INDEX functions as follows −

using match function

您将获得以下结果−

You will get the following results −

using match function result