vlookup 查找值模糊_如何在一系列值上使用VLOOKUP

vlookup 查找值模糊

VLOOKUP is one of Excel’s most well-known functions. You’ll typically use it to look up exact matches, such as the ID of products or customers, but in this article, we’ll explore how to use VLOOKUP with a range of values.

VLOOKUP是Excel最著名的功能之一。 通常，您将使用它来查找完全匹配的内容，例如产品或客户的ID，但是在本文中，我们将探讨如何将VLOOKUP与一系列值一起使用。

示例1：使用VLOOKUP为考试分数分配字母等级 (Example One: Using VLOOKUP to Assign Letter Grades to Exam Scores)

As an example, say we have a list of exam scores, and we want to assign a grade to each score. In our table, column A shows the actual exam scores and column B will be used to show the letter grades we calculate. We’ve also created a table off to the right (the D and E columns) that show the score necessary to achieve each letter grade.

例如，假设我们有一个考试分数列表，并且我们希望为每个分数分配一个分数。 在我们的表格中，A列显示实际考试成绩，B列将显示我们计算的字母等级。 我们还在右侧创建了一个表格(D和E列)，该表格显示了达到每个字母等级所必需的分数。

With VLOOKUP, we can use the range values in column D to assign the letter grades in column E to all the actual exam scores.

通过VLOOKUP，我们可以使用D列中的范围值将E列中的字母等级分配给所有实际考试成绩。

VLOOKUP公式 (The VLOOKUP Formula)

Before we get into applying the formula to our example, let’s have a quick reminder of the VLOOKUP syntax:

在将公式应用于示例之前，让我们快速回顾一下VLOOKUP语法：

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

In that formula, the variables work like this:

在该公式中，变量的工作方式如下：

• lookup_value: This is the value for which you are looking. For us, this is the score in column A, starting with cell A2.
  lookup_value：这是您要查找的值。 对于我们来说，这是从A2单元格开始的A列中的得分。
• table_array: This is often referred to unofficially as the lookup table. For us, this is the table containing the scores and associated grades ( range D2:E7).
  table_array：这通常被非正式地称为查找表。 对于我们来说，这是包含成绩和相关成绩(范围D2：E7)的表格。
• col_index_num: This is the column number where the results will be placed. In our example, this is column B, but since the VLOOKUP command requires a number, it’s column 2.
  col_index_num：这是将放置结果的列号。 在我们的示例中，这是列B，但是由于VLOOKUP命令需要一个数字，因此它是列2。
• range_lookup> This is a logical value question, so the answer is either true or false. Are you performing a range lookup? For us, the answer is yes (or “TRUE” in VLOOKUP terms).
  range_lookup>这是一个逻辑值问题，因此答案为true或false。 您正在执行范围查找吗？ 对于我们来说，答案是肯定的(或者在VLOOKUP术语中为“ TRUE”)。

The completed formula for our example is shown below:

本示例的完整公式如下所示：

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

The table array has been fixed to stop it changing when the formula is copied down the cells of column B.

修复了表格数组，可防止在将公式向下复制到B列的单元格时停止更改。

要注意的事情 (Something to Be Careful About)

When looking in ranges with VLOOKUP, it is essential that the first column of the table array (column D in this scenario) is sorted in ascending order. The formula relies on this order to place the lookup value in the correct range.

当使用VLOOKUP查找范围时，必须将表数组的第一列(在本方案中为D列)以升序排序。 该公式依靠此顺序将查找值放置在正确的范围内。

Below is an image of the results we’d get if we sorted the table array by the grade letter rather than the score.

下面是一张图像，如果我们按成绩字母而不是分数对表格数组进行排序，则会得到结果。

It is important to be clear that the order is only essential with range lookups. When you put False on the end of a VLOOKUP function, the order is not so important.

重要的是要清楚，该顺序仅对于范围查找至关重要。 当在VLOOKUP函数的末尾放置False时，顺序不是那么重要。

示例二：根据客户的消费金额提供折扣 (Example Two: Providing a Discount Based on How Much a Customer Spends)

In this example, we have some sales data. We would like to provide a discount on the sales amount, and the percentage of that discount is dependent upon the amount spent.

在此示例中，我们有一些销售数据。 我们想对销售金额提供折扣，折扣的百分比取决于所花费的金额。

A lookup table (columns D and E) contains the discounts at each spending bracket.

查找表(D和E列)包含每个支出级别的折扣。

The VLOOKUP formula below can be used to return the correct discount from the table.

下面的VLOOKUP公式可用于从表中返回正确的折扣。

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

This example is interesting because we can use it in a formula to subtract the discount.

这个例子很有趣，因为我们可以在公式中使用它来减去折扣。

You will often see Excel users writing complicated formulas for this type of conditional logic, but this VLOOKUP provides a concise way of achieving it.

您经常会看到Excel用户为这种条件逻辑编写复杂的公式，但是此VLOOKUP提供了一种简洁的方法来实现它。

Below, the VLOOKUP is added to a formula to subtract the discount returned from the sales amount in column A.

下面，将VLOOKUP添加到公式中，以从A列的销售额中减去返回的折扣。

=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)

---

---

VLOOKUP is not just useful for when looking for specific records such as employees and products. It’s more versatile than many people know, and having it return from a range of values is an example of that. You can also use it as an alternative to otherwise complicated formulas.

VLOOKUP不仅在查找员工和产品等特定记录时有用。 它比许多人所知道的更加通用，并且使它从一系列值中返回就是一个例子。 您也可以将其用作其他复杂公式的替代方法。

翻译自: https://www.howtogeek.com/406053/how-to-use-vlookup-on-a-range-of-values/

vlookup 查找值模糊