Excel 简明教程

Cross Referencing in Excel 2010

Graphic Objects in MS Excel

当你的信息分散在多个不同的电子表格中时，将所有这些不同的数据集汇集到一个有意义的列表或表中似乎是一项艰巨的任务。这就是 Vlookup 函数大显身手的地方。

When you have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. This is where the Vlookup function comes into its own.

VLOOKUP

VlookUp 垂直向下搜索查找表的值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 有 4 个参数，如下所示。

VlookUp searches for a value vertically down for the lookup table. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) has 4 parameters as below.

  1. lookup_value − It is the user input. This is the value that the function uses to search on.

  2. The table_array − It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need.

  3. Col_index_num − It is the column of data that contains the answer that you want.

  4. Range_lookup − It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value be formatted in ascending order.

VLOOKUP Example

让我们来看一个交叉引用两个电子表格的非常简单的例子。每个电子表格都包含有关同一组人的信息。第一个电子表格有他们的出生日期，第二个电子表格有他们最喜欢的颜色。我们如何建立一个显示个人姓名、出生日期和他们最喜欢的颜色的列表？VLOOKUP 将在这种情况下提供帮助。首先，让我们看看两张表中的数据。

Let’s look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth, and the second shows their favorite color. How do we build a list showing the person’s name, their date of birth and their favorite color? VLOOOKUP will help in this case. First of all, let us see data in both the sheets.

这是第一张表中的数据

This is data in the first sheet

data first sheet

这是第二张表中的数据

This is data in the second sheet

data second sheet

现在，为了从另一张表中找到属于该人的相应最喜欢的颜色，我们需要查找数据。VLOOKUP 的第一个参数是查找值（在这种情况下，为个人姓名）。第二个参数是表数组，即从 B2 到 C11 的第二张表中的表。VLOOKUP 的第三个参数是列索引 num，这是我们要寻找的答案。在这种情况下，它是 2，颜色列数为 2。第四个参数是返回部分匹配的 True 或返回完全匹配的 False。应用 VLOOKUP 公式后，它将计算颜色，并且结果如下显示。

Now for finding the respective favorite color for that person from another sheet we need to vlookup the data. First argument to the VLOOKUP is lookup value (In this case it is person name). Second argument is the table array, which is table in the second sheet from B2 to C11. Third argument to VLOOKUP is Column index num, which is the answer we are looking for. In this case, it is 2 the color column number is 2. The fourth argument is True returning partial match or false returning exact match. After applying VLOOKUP formula it will calculate the color and the results are displayed as below.

vlookup results

如您在上面的屏幕截图中看到的那样，VLOOKUP 的结果已在第二张表中搜索颜色。在未找到匹配项的情况下，它返回了 #N/A。在这种情况下，Andy 的数据不存在于第二张表中，因此返回 #N/A。

As you can see in the above screen-shot that results of VLOOKUP has searched for color in the second sheet table. It has returned #N/A in case where match is not found. In this case, Andy’s data is not present in the second sheet so it returned #N/A.