Excel匹配功能与公式示例

本教程解释了如何在公式示例中使用excel中的匹配函数。它还显示了如何通过使用Vlookup和匹配的动态公式来改善查找公式。

在Microsoft Excel中，有许多不同的查找/参考功能可以帮助您在一系列单元格中找到某个值，而匹配就是其中之一。基本上，它标识了项目在一系列单元格中的相对位置。但是，匹配函数可以做的远远超过其纯粹的本质。

• Excel匹配功能 - 语法和基本用途
• 如何在Excel -Formula示例中使用匹配
  • 匹配公式与通配符
  • 病例敏感的匹配公式
  • 比较2列的匹配和差异（ISNA匹配）
  • vlookup and Match
  • hlookup和匹配

Excel匹配功能 - 语法和使用

Excel中的匹配函数在一系列单元格中搜索指定值，并返回该值的相对位置。

匹配函数的语法如下：

匹配（lookup_value，lookup_array，[match_type]）

Lookup_value （必需） - 您想要找到的值。它可以是数字，文本或逻辑值以及单元格的参考。

Lookup_array （必需） - 要搜索的单元格范围。

match_type （可选） - 定义匹配类型。它可以是以下值之一：1，0，-1。 Match_Type参数设置为0仅返回确切的匹配，而其他两种类型允许大致匹配。

• 1或省略（默认） - 近似匹配（最接近较小）。在查找阵列中找到最大的值，该值小于或等于查找值。需要按升序排序查找阵列，从最小到最大或从A到Z进行排序。
• 0-确切匹配。在数组中找到完全等于查找值的第一个值。无需分类。
• -1-近似匹配（最接近较大）。找到大于或等于查找值的最小值。查找阵列应以降序从最大到最小或从z到A进行排序。

为了更好地理解匹配功能，让我们根据此数据制作一个简单的公式：A列中的学生名称及其在B列中的考试成绩，从最大到最小。要找出特定学生（例如，劳拉）的位置，请使用此简单的公式：

=MATCH("Laura", A2:A8, 0)

可选地，您可以将查找值放入某些单元格（在此示例中的E1）中，并在Excel匹配公式中引用该单元格：

=MATCH(E1, A2:A8, 0)

如您在上面的屏幕截图中看到的那样，学生名称是按任意顺序输入的，因此我们将Match_type参数设置为0（精确匹配），因为只有此匹配类型不需要查找数组中的排序值。从技术上讲，比赛公式返回劳拉在该范围内的相对位置。但是，由于分数从最大到最小的分类，所以它也告诉我们，劳拉在所有学生中的得分排名^第五。

提示。在Excel 365和Excel 2021中，您可以使用XMATCH函数，该功能是现代，更强大的接班人。

关于匹配功能，您应该知道的4件事

正如您刚刚看到的那样，在Excel中使用Match很容易。但是，就像几乎所有其他功能一样，您应该注意的是：

1. 匹配函数返回数组中查找值的相对位置，而不是值本身。
2. 匹配是不敏感的，这意味着它在处理文本值时不会区分小写和大写字符。
3. 如果查找数组包含查找值的几个出现，则返回第一个值的位置。
4. 如果在查找数组中找不到查找值，则返回＃N/A错误。

如何在Excel -Formula示例中使用匹配

现在，您知道了Excel匹配功能的基本用途，让我们讨论一些超出基础知识的公式示例。

与通配符的部分比赛

像许多其他功能一样，Match了解以下通配符：

• 问号（？） - 替换任何一个字符
• 星号（*） - 替换任何字符序列

笔记。通配符只能用于匹配公式中，匹配公式设置为0。

当您想匹配整个文本字符串，而是字符串的某些部分时，与通配符的匹配公式在情况下很有用。为了说明这一点，请考虑以下示例。

假设您在过去一个月中有区域经销商及其销售数据列表。您想在列表中找到某个经销商的相对位置（按降序销售量排序），但是您不记得他的名字，尽管您确实记得一些第一个字符。

假设转销商名称在A2：A11范围内，并且您正在搜索以“汽车”开头的名称，则该公式如下：

=MATCH("car*", A2:A11,0)

为了使我们的匹配公式更具用途，您可以在某些单元格中键入查找值（在此示例中的E1），并将该单元格与通配符的字符相连，因此：

=MATCH(E1&"*", A2:A11,0)

如下屏幕截图所示，公式返回2，这是“ Carter”的位置：

要替换查找值中的一个字符，请使用“？”通配符操作员，这样：

=MATCH("ba?er", A2:A11,0)

上面的公式将与“贝克”的名称匹配，并重新重新列出其相对位置，即5。

病例敏感的匹配公式

如本教程开头所述，匹配函数不能区分大写和小写字符。要制作对案例敏感的匹配公式，请将匹配与精确比较单元格（包括字符案例）的确切函数结合使用。

这是匹配数据的通用案例敏感公式：

匹配（true，精确（查找数组，查找值），0）

该公式可与以下逻辑合作：

• 确切的函​​数将查找值与查找数组的每个元素进行比较。如果比较的单元格完全相等，则该函数将返回真实，否则为false。
• 然后，匹配函数将true（这是其Lookup_value ）与由精确返回的数组中的每个值进行比较，并返回第一匹配的位置。

请记住，这是一个阵列公式，需要按CTRL Shift Enter正确完成。

假设您的查找值在单元格E1中，并且查找阵列为A2：A9，则公式如下：

=MATCH(TRUE, EXACT(A2:A9,E1),0)

以下屏幕截图显示了Excel中的案例敏感匹配公式：

比较2列的匹配和差异（ISNA匹配）

检查两个列表中的比赛和差异是Excel中最常见的任务之一，可以通过多种方式完成。 ISNA/匹配公式是其中之一：

if（isna（匹配（ list1中的1个值， list2，0 ）），“不在列表1”，“”）

对于列表1中不存在的列表2的任何值，公式返回“不在列表1 ”。这就是：

• 匹配函数搜索列表2中列表1的值。如果找到一个值，它将返回其相对位置，否则＃n/a错误否则。
• Excel中的ISNA函数只能做一件事 - 检查＃n/a错误（意味着“不可用”）。如果给定值是＃n/a错误，则该函数将返回true，否则为false。在我们的情况下，true意味着列表1中未找到一个值1（即，匹配返回＃n/a错误）。
• 因为您的用户对于未出现在列表1中的值的值可能会很困惑，所以您将IF函数包裹在ISNA周围以显示“不在列表1 ”或您想要的任何文本。

例如，要将B中的值与A列中的值进行比较，该公式采用以下形状（其中B2是最高单元格）：

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

如您所记得的那样，Excel中的匹配功能本身对细节不敏感。为了区分角色情况，请在Lookup_array参数中嵌入确切的函数，并记住按Ctrl Shift Enter Enter以完成此数组公式：

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

以下屏幕截图显示了两种行动中的公式：

要学习其他方法来比较Excel中的两个列表，请参见以下教程：如何比较Excel中的2列。

Excel Vlookup和匹配

此示例假设您已经对Excel Vlookup函数有一些基本知识。而且，如果您这样做的话，您很可能会遇到其众多限制（在为什么Excel Vlookup不起作用的原因中可以找到详细的概述），并且正在寻找更强大的替代方案。

Vlookup最令人讨厌的缺点之一是，它在在查找表中插入或删除列后停​​止工作。之所以发生这种情况，是因为Vlookup根据您指定的返回列的数量（索引编号）提取匹配值。由于公式中的索引号是“硬编码”的，因此当将新列添加到表中时，Excel无法将其调整。

Excel匹配函数涉及查找值的相对位置，这使其非常适合col_index_num vlookup的参数。换句话说，您没有将返回列指定为静态号码，而是使用匹配来获取该列的当前位置。

为了使事情更容易理解，让我们再次使用学生的考试成绩（类似于我们在本教程开始时使用的考试），但是这次我们将检索真正的分数，而不是其相对位置。

假设查找值在单元格F1中，则表数组为$ a $ 1：$ c $ 2（如果您计划将公式复制到其他单元格中，则使用绝对单元格锁定是一个好习惯），该公式如下：

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

3 ^rd参数（ col_index_num ）设置为3，因为我们要拉的数学分数是表中的3^列列。正如您在下面的屏幕截图中看到的那样，此常规vlookup公式效果很好：

但是，只有插入或删除列之前：

所以，为什么要#ref！错误？因为col_index_num设置为3，告诉excel从第三列获取一个值，而现在表格数组中只有2列。

为了防止此类事情发生，您可以通过包含以下匹配功能来使您的vlookup公式更具动态性：

MATCH(E2,A1:C1,0)

在哪里：

• E2是查找值，它完全等于返回列的名称，即您要从中提取值的列（此示例中的数学分数）。
• A1：C1是包含表标头的查找阵列。

现在，将此匹配函数包含在您的Vlookup公式的Col_index_num参数中，例如：

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

并确保它无可挑剔地工作，无论您添加多少列或删除​​：

在上面的屏幕截图中，即使我的用户将其移至工作表中的另一个位置，我也锁定了公式正确工作的所有单元。一个您可以在下面的屏幕截图中看到，删除列后，公式正常工作；此外，Excel足够聪明，可以在这种情况下正确调整绝对参考：

Excel Hlookup和Match

以类似的方式，您可以使用Excel匹配功能来改善Hookup公式。一般原理与Vlookup的情况基本相同：您使用匹配函数获取返回列的相对位置，并将该数字提供给您的hlookup公式的row_index_num参数。

假设查找值在单元格B5中，表数组为b1：h3，返回行的名称（匹配的查找值）在单元格A6中，并且行标头为a1：a3，完整的公式如下：

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

正如您刚刚看到的那样，Hlookup/Vlookup＆Match的组合无疑是对常规Hlookup和Vlookup公式的改进。但是，匹配函数并不能消除其所有局限性。特别是，Vlookup匹配公式仍然无法查看其左侧，而HlookUp匹配也无法在最上方的任何行中搜索。

为了克服上述（以及其他一些）限制，请考虑使用索引匹配的组合，该组合提供了一种非常强大且通用的方式来在Excel中进行查找，在许多方面都优于Vlookup和Hlookup。详细的指导和公式示例可以在Excel中的索引和匹配中找到 - vookup的更好替代方案。

这就是您在Excel中使用匹配公式的方式。希望本教程中讨论的示例对您的工作有所帮助。我感谢您阅读，并希望下周在我们的博客上见到您！

练习工作簿下载

excel匹配公式示例（.xlsx文件）

以上是Excel匹配功能与公式示例的详细内容。更多信息请关注PHP中文网其他相关文章！