vlookup示例_VLOOKUP示例–如何在Excel中执行VLOOKUP

vlookup示例

Microsoft Excel includes a variety of different functions that help users with calculations of any kind. The functionality of Excel is so comprehensive that average users don't even take advantage of most utilities.

Microsoft Excel包括各种不同的功能，可以帮助用户进行任何类型的计算。 Excel的功能是如此全面，以至于普通用户甚至无法利用大多数实用程序。

However, if you often scroll through columns and rows looking for the same information, chances are that you will appreciate the VLOOKUP function. VLOOKUP, which stands for “vertical lookup,” can help you quickly find the data associated with a certain value that you enter.

但是，如果您经常在列和行中滚动以查找相同的信息，则很有可能会欣赏到VLOOKUP函数。 VLOOKUP代表“垂直查找”，可以帮助您快速查找与您输入的特定值关联的数据。

For example, you may have a table that contains products with unique IDs and prices. VLOOKUP can show you the price of a certain product if you enter its ID.

例如，您可能有一个表，其中包含具有唯一ID和价格的产品。 如果输入特定产品的ID，VLOOKUP可以向您显示其价格。

You can use VLOOKUP in many different ways and it will simplify your work significantly, especially when dealing with large tables.

您可以通过多种方式使用VLOOKUP，这将显着简化您的工作，尤其是在处理大型表时。

You don’t need to spend a lot of time looking for a certain cell because this function will find it for you. However, beginner users often find it difficult to set up VLOOKUP. Therefore, I decided to help you by preparing this detailed guide.

您无需花费大量时间来查找特定单元格，因为此功能将为您找到它。 但是，初学者经常会发现很难设置VLOOKUP。 因此，我决定通过准备此详细指南来帮助您。

什么是VLOOKUP？ (What is VLOOKUP?)

First of all, VLOOKUP is a function. Therefore, if you’re new to Excel, you may want to familiarize yourself with some basic functions, like AVERAGE, SUM, or TODAY. This way it will be easy for you to understand how this function works.

首先，VLOOKUP是一个函数。 因此，如果您不熟悉Excel，则可能需要熟悉一些基本功能，例如AVERAGE，SUM或TODAY。 这样，您将很容易理解此功能的工作原理。

VLOOKUP is a database function, so it’s intended for database tables. Such tables are basically lists of different items. For instance, you may use this function when working with lists of products, employees, customers, etc.

VLOOKUP是数据库功能，因此适用于数据库表。 这样的表基本上是不同项目的列表。 例如，在处理产品，员工，客户等的列表时，可以使用此功能。

Let’s say, you have a list of products that consists of four columns. It might include the item code in the first column, the name or description of the product in the second column, and the price and the number of items available in stock in the third and fourth columns, respectively.

假设您有一个包含四列的产品列表。 它可能在第一列中包含商品代码，在第二列中包含产品名称或说明，并在第三列和第四列中分别包含价格和可用库存数量。

Database tables usually have some sort of unique identifier for each item. In this case, it’s the item code. This column is necessary for the VLOOKUP function to operate, and it must be the first column in your table.

数据库表通常对每个项目都有某种唯一标识符。 在这种情况下，它是商品代码。 该列是VLOOKUP函数运行所必需的，并且它必须是表中的第一列。

If you’re a beginner, the first thing you should do is to understand what exactly VLOOKUP does. Simply put, it shows information from a list or database based on the unique identifier entered by the user.

如果您是初学者，那么您应该做的第一件事就是了解VLOOKUP的确切功能。 简而言之，它根据用户输入的唯一标识符显示列表或数据库中的信息。

If we consider the example above, this function could show the price, description, or availability of a product based on its item code. What exactly it will show depends on the formula you write. VLOOKUP supports both exact and approximate matching, as well as wildcards for partial matches.

如果我们考虑上面的示例，此功能可以根据产品的物料代码显示产品的价格，描述或可用性。 具体显示什么取决于您编写的公式。 VLOOKUP支持精确和近似匹配，以及部分匹配的通配符。

VLOOKUP如何工作 (How VLOOKUP Works)

Here is the syntax for formulas that describe the VLOOKUP function:

这是描述VLOOKUP函数的公式的语法：

=VLOOKUP(value, table, column_index, [range])

=VLOOKUP(value, table, column_index, [range])

• value is what this function will look for in the first column

  value是此函数将在第一列中查找的value

• table is the table from which the function will retrieve the necessary information

  table 是函数将从中检索必要信息的表

• column_index is the number of the column from which the function will retrieve the information

  column_index是函数将从中检索信息的列号

• range is a Boolean parameter that can be either TRUE or FALSE. TRUE is the default value, and it corresponds to approximate matching. FALSE will show you exact matches only.

  range 是布尔值参数，可以为TRUE或FALSE。 TRUE是默认值，它对应于近似匹配。 FALSE仅显示完全匹配。

Even the name of this function includes “vertical,” and VLOOKUP is only intended for tables where data is organized in vertical columns. Therefore, if you’re organizing your data horizontally, this function will be useless. In this case, you can use a similar function for horizontal lookup — HLOOKUP.

甚至此函数的名称都包括“ vertical”，而VLOOKUP仅适用于在垂直列中组织数据的表。 因此，如果您要水平组织数据，则此功能将无用。 在这种情况下，可以对水平查找使用类似的功能-HLOOKUP 。

You should also keep in mind that this function only works from left to right. In other words, if the unique identifier is not in the first column of your table, the function won’t be able to retrieve information from the columns to the left of the identifier.

您还应该记住，此功能仅从左到右起作用。 换句话说，如果唯一标识符不在表的第一列中，则该函数将无法从标识符左侧的列中检索信息。

Every column has its number, and all columns are numbered from left to right. If you want to obtain a value from a certain column, you should specify its number in your formula. In the formula template above, this number is called column_index.

每列都有其编号，所有列均从左到右编号。 如果要从某个列中获取值，则应在公式中指定其编号。 在上面的公式模板中，此数字称为column_index 。

For instance, if you want to retrieve the name of the product from the example above, the column index should be 2.

例如，如果要从上面的示例中检索产品名称，则列索引应为2。

As I've already mentioned above, the VLOOKUP function supports two matching modes: approximate and exact. This parameter is the fourth argument in the formula. Approximate matching is set by default. If you want to choose exact matching, you should set the lookup range to FALSE.

如前所述，VLOOKUP函数支持两种匹配模式：近似和精确。 此参数是公式中的第四个参数。 默认情况下设置近似匹配。 如果要选择完全匹配，则应将查找范围设置为FALSE 。

Therefore, both of the formulas below will retrieve data using approximate matching:

因此，以下两个公式都将使用近似匹配来检索数据：

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index, TRUE)

=VLOOKUP(value, table, column_index, TRUE)

As you can see, if you want to use the exact match mode you should be careful. If you don’t provide any lookup range value, the function will still use the approximate match mode.

如您所见，如果您想使用完全匹配模式，则应该小心。 如果您不提供任何查找范围值，该函数仍将使用近似匹配模式。

The following formula will force the exact matching mode:

以下公式将强制使用完全匹配模式：

=VLOOKUP(value, table, column_index, FALSE)

=VLOOKUP(value, table, column_index, FALSE)

Make sure to set the value to FALSE if you’re going to use the exact match mode. Chances are that you’ll need exact matching in most cases, so if you’re new to Excel don’t forget about this detail.

如果要使用完全匹配模式，请确保将值设置为FALSE 。 在大多数情况下，您可能需要完全匹配，因此，如果您不熟悉Excel，请不要忘记此详细信息。

Exact match is the right choice if you have a column with the item identifier. It may also be any unique value that can be used for an exact lookup. For example, it may be a unique title of a book or movie, as well as any other unique keyword. Keep in mind that VLOOKUP is not case-sensitive.

如果您有一列带有项目标识符的项目，则完全匹配是正确的选择。 它也可以是可用于精确查找的任何唯一值。 例如，它可以是一本书或电影的唯一标题，以及任何其他唯一关键字。 请记住，VLOOKUP不区分大小写。

However, sometimes you may need not the exact match but the best match possible one. In this case, you can use the approximate match mode.

但是，有时您可能不需要精确匹配，但可能需要最佳匹配。 在这种情况下，您可以使用近似匹配模式。

For instance, you may use this mode when dealing with data tables where the necessary information corresponds to certain numerical values, and you want to retrieve results for a value that isn’t included in the table.

例如，当处理必要信息对应于某些数值的数据表时，您可能会使用此模式，并且您想要检索表中未包含的值的结果。

You can use this approach when making calculations based on the existing data. If you enter a value and the function finds the exact match, it will retrieve information from the corresponding row. However, if there’s no exact match in the table, the function will match the previous row.

在基于现有数据进行计算时，可以使用此方法。 如果您输入一个值并且该函数找到了完全匹配，它将从相应的行中检索信息。 但是，如果表中不存在完全匹配项，则该函数将匹配上一行。

Why would it match the previous row, not another one? Well, it won’t if you don’t organize your table in the right way. To enable VLOOKUP to look for the best approximate value, you should make sure that all the values in this column are sorted in ascending order. In this case, the function will just step back and retrieve the nearest value.

为什么要匹配上一行而不匹配另一行？ 好吧，如果您不以正确的方式组织表格，那将是不可能的。 为了使VLOOKUP能够寻找最佳的近似值，您应该确保此列中的所有值都按升序排序。 在这种情况下，该函数将退后一步并获取最接近的值。

N / A错误 (N/A Errors)

When using the VLOOKUP function, as well as many other functions in Excel, you may often get #N/A errors. This error means that the value was not found.

当使用VLOOKUP函数以及Excel中的许多其他函数时，您可能经常会遇到＃N / A错误。 此错误意味着找不到该值。

You can get this error for several reasons. Here are some of the most common cases:

出现此错误的原因有很多。 以下是一些最常见的情况：

• you’ve misspelled the value or added an extra space
  您拼写了错误的值或添加了额外的空格
• the necessary value doesn’t exist in the table
  表格中没有必要的值
• you’re using the exact match mode when searching for an approximate value
  搜索近似值时使用的是完全匹配模式
• you haven’t entered the correct table range
  您没有输入正确的表格范围
• you’ve copied VLOOKUP while the table reference isn’t locked.
  您已在未锁定表引用的情况下复制了VLOOKUP。

If your table has an absolute reference, it means that the rows and columns won’t change if copied. However, this isn’t the case with a relative reference. In this case, you will need to switch to the absolute reference.

如果表具有绝对引用，则意味着复制后行和列不会更改。 但是，相对引用不是这种情况。 在这种情况下，您将需要切换到绝对参考 。

You can customize the text of the #N/A error by using the IFNA function. In this case, you have to write a longer formula with IFNA that includes VLOOKUP.

您可以使用IFNA函数自定义＃N / A错误的文本。 在这种情况下，您必须使用包含VLOOKUP的IFNA编写更长的公式。

Here’s an example of a formula that will return “not found” instead of #N/A:

这是一个公式示例，它将返回“未找到”而不是＃N / A：

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

And here is a formula that will return a blank result:

这是一个将返回空白结果的公式：

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

Although you can customize the message, you may consider using the #N/A error because it will immediately attract your attention and let you know if something goes wrong.

尽管您可以自定义消息，但是您可以考虑使用＃N / A错误，因为它会立即引起您的注意，并在出现问题时通知您。

如何使用VLOOKUP (How to Use VLOOKUP)

You may write formulas from scratch, or you may also use the Excel menu. Select the cell where you want to display the result, and then select the “Formulas” tab.

您可以从头开始编写公式，也可以使用Excel菜单。 选择要在其中显示结果的单元格，然后选择“公式”选项卡。

After this, click “Insert Function.” You will see a box where you can select categories of functions and choose the VLOOKUP function. You can also use the “Search for a function” box, and enter “vlookup.”

之后，单击“插入功能”。 您将看到一个框，您可以在其中选择功能类别并选择VLOOKUP功能。 您还可以使用“搜索功能”框，然后输入“ vlookup”。

Select the function, and the “Function Argument” box will appear. Here you can enter the necessary parameters of the function. In this window, you should specify the unique identifier you’re looking for, database location, and the information that corresponds to the identifier that you want to retrieve.

选择功能，将出现“功能参数”框。 您可以在此处输入功能的必要参数。 在此窗口中，您应该指定要查找的唯一标识符，数据库位置以及与要检索的标识符相对应的信息。

These arguments are “Lookup_value,” “Table_array,” and “Col_index_num.” These fields are written in bold because these arguments are mandatory.

这些参数是“ Lookup_value”，“ Table_array”和“ Col_index_num”。 这些字段以粗体显示，因为这些参数是强制性的。

The fourth argument if for the lookup mode, and you may or may not specify it. The approximate mode is set by default.

第四个参数，如果为查找模式，则可以指定也可以不指定。 默认情况下设置为近似模式。

To enter the first argument, which is the unique identifier, you can select the necessary cell and press Enter. In this case, the value of this cell will be automatically entered as the first argument of the VLOOKUP function.

要输入第一个参数，即唯一标识符，可以选择必要的单元格，然后按Enter键。 在这种情况下，该单元格的值将自动作为VLOOKUP函数的第一个参数输入。

Now you have to enter the second argument. The database shouldn’t necessarily start at the top left corner. For instance, you may also have a row that describes columns, which serves as a header.

现在，您必须输入第二个参数。 数据库不必一定从左上角开始。 例如，您可能还具有描述列的行，该行用作标题。

“One of the best things about the VLOOKUP function is that the location of the database can also be customized,” notes Bridget Allen, an accountant at a Best Writers Online.

“关于VLOOKUP功能的最好的事情之一就是还可以自定义数据库的位置，” 在线最佳作家在线会计师布里奇特·艾伦(Bridget Allen) 说道 。

Given that VLOOKUP only works with numbers of columns, you should specify what area of your table you want to use for lookup. This is what the “Table_array” box is for.

鉴于VLOOKUP仅适用于列数，因此应指定要用于查询的表区域。 这就是“ Table_array”框的作用。

For example, if your table starts at the top left corner, and its first row is a header, you can select the whole database without the first line. If your database has four columns (A-D) and five items in it, the table array will be A2:D6, because cells A1-D1 will contain the header.

例如，如果您的表从左上角开始，并且其第一行是标题，则可以选择整个数据库而无需第一行。 如果您的数据库有四个列(AD)和五个项目，则表数组将为A2：D6，因为单元格A1-D1将包含标头。

You can click on the necessary worksheet tab, and select the area with the database. Press Enter and the selected range of cells will be automatically added to the second argument of the VLOOKUP function. Here is an example of a function argument:

您可以单击必要的工作表选项卡，然后选择数据库区域。 按Enter键，所选单元格范围将自动添加到VLOOKUP函数的第二个参数中。 这是一个函数参数的示例：

‘database_name’!A2:D6

'database_name'!A2:D6

In this case, “database_name” is the name of the worksheet tab.

在这种情况下，“ database_name”是工作表选项卡的名称。

Now you only need to specify what information you want to retrieve and provide the number of the necessary column.

现在，您只需要指定要检索的信息并提供必要列的编号即可。

For example, if you want to retrieve the price of an item from the table at the beginning of this article, you should use the third row, and the “Col_index_num” value will be 3.

例如，如果您想从本文开头的表格中检索某商品的价格，则应使用第三行，“ Col_index_num”值将为3。

结语 (Wrapping Up)

Microsoft Excel has a vast variety of functions that can help users deal with different calculations and other tasks. VLOOKUP is a very useful function that can retrieve information that corresponds to a certain value from a database table.

Microsoft Excel具有多种功能，可以帮助用户处理不同的计算和其他任务。 VLOOKUP是一个非常有用的功能，可以从数据库表中检索与某个值相对应的信息。

If you’re new to Excel, you may experience difficulties with this function because it has four arguments.

如果您不熟悉Excel，则此函数可能会遇到困难，因为它具有四个参数。

However, if you follow our guide, you’ll be able to select the right arguments and to use the right formulas. If you use this function a few times in practice, you will quickly understand how it works so you’ll be able to use VLOOKUP whenever you need it.

但是，如果您遵循我们的指南，则可以选择正确的参数并使用正确的公式。 如果您在实践中几次使用此功能，您将快速了解它的工作原理，因此可以在需要时使用VLOOKUP。

翻译自: https://www.freecodecamp.org/news/vlookup-in-excel/

vlookup示例