excel xMatch函数与公式示例

本教程介绍了新的Excel XMatch函数，并显示了与求解一些常见任务相比它比匹配更好。

在Excel 365中，添加了XMATCH函数以取代匹配功能。但是，在您开始升级现有公式之前，了解新功能的所有优势以及它与旧功能的不同是明智的。

总而言之，XMATCH函数与匹配相同，但更灵活和健壮。它可以在垂直和水平阵列中查找，搜索第一至持久的次数或最后一级，找到精确的，近似和部分匹配，并使用更快的二进制搜索算法。

• XMATCH功能在Excel中
• 基本Excel XMatch公式
• 如何在Excel中使用Xmatch -formula示例
  • 确切的匹配与近似匹配
  • 与通配符的部分比赛
  • xMatch反向搜索
  • 比较两列的比赛
  • 索引XMatch公式
  • 索引XMatch XMatch公式
  • 案例敏感的XMATCH公式
• XMATCH与Excel中的比赛

Excel XMatch函数

Excel中的XMATCH函数返回值在阵列或一系列单元格中的相对位置。

它具有以下语法：

xmatch（lookup_value，lookup_array，[match_mode]，[search_mode]）

在哪里：

Lookup_value （必需） - 要寻找的值。

Lookup_array （必需） - 搜索的数组或单元格范围。

match_mode （可选） - 指定要使用的匹配类型：

• 0或省略（默认） - 确切匹配
• -1-确切的匹配或下一个最小的值
• 1-确切的匹配或第二大价值
• 2-通配符匹配（*，？）

search_mode （可选） - 指定搜索方向和算法：

• 1或省略（默认） - 从第一个到最后一个搜索。
• -1-最后到第一个以相反顺序搜索。
• 2-二进制搜索上升。需要Lookup_array以升序排序。
• -2-二进制搜索下降。需要Lookup_array以降序排序。

二进制搜索是一种更快的算法，可在排序的数组上有效工作。有关更多信息，请参阅搜索模式。

哪个Excel版本具有XMatch？

XMATCH函数仅在Microsoft 365和Excel 2021的Excel中可用。在Excel 2019，Excel 2016及更早版本中，不支持此功能。

excel中的基本Xmatch公式

为了了解该函数的能力，让我们构建一个最简单的XMATCH公式，仅定义了前两个必需的参数，并将可选的参数定义为默认值。

假设您有一个按大小排名的海洋清单（C2：C6），您希望找到特定海洋的排名。要完成它，只需将海洋的名称（例如印第安人）作为查找价值，而名称的整个列表则作为查找阵列：

=XMATCH("Indian", C2:C6)

为了使公式更灵活，请输入某些单元中感兴趣的海洋，例如F1：

=XMATCH(F1, C2:C6)

结果，您将获得一个XMatch公式，可以在垂直阵列中查找。输出是阵列中查找值的相对位置，在我们的情况下，该位置与海洋等级相对应：

类似的公式也适用于水平阵列。您需要做的就是调整Lookup_Array参考：

=XMATCH(B5, B1:F1)

excel xmatch函数 - 要记住的事物

要有效地在工作表中使用XMatch并防止意外结果，请记住以下3个简单事实：

• 如果查找阵列中有两个或多个查找值的出现，则如果将search_mode参数设置为1或省略，则返回第一个匹配的位置。将search_mode设置为-1，该功能以相反顺序搜索，并返回最后一场匹配的位置，如本示例所示。
• 如果找不到查找值，则会发生＃N/A错误。
• XMATCH函数本质上是对病例敏感的，无法区分字母案例。要区分小写和大写字符，请使用此对案例敏感的Xmatch公式。

如何在Excel中使用Xmatch -formula示例

以下示例将帮助您对XMATCH功能及其实际用途有更多了解。

确切的匹配与近似匹配

Xmatch的匹配行为由可选的Match_mode参数控制：

• 0或省略（默认） - 公式仅搜索精确匹配。如果找不到确切的匹配，则返回＃N/A错误。
• -1-公式首先搜索精确匹配，然后搜索下一个较小的项目。
• 1-公式首先搜索精确匹配，然后搜索下一个较大的项目。

现在，让我们看看不同的匹配模式如何影响公式的结果。假设您想找出某个区域（例如80,000,000公里² ）位于所有海洋中。

精确匹配

如果将0用于match_mode ，则会获得＃n/a错误，因为该公式找不到完全等于查找值的值：

=XMATCH(80000000, C2:C6, 0)

下一个最小的项目

如果将-1用于match_mode ，则公式将返回3，因为最接近查找值的匹配值为70,560,000，并且是查找数组中的第3^个项目：

=XMATCH(80000000, C2:C6, -1)

下一个最大的项目

如果将1用于match_mode ，则公式将输出2，因为最接近的匹配值大于查找值是85,133,000，这是查找数组中的2^个项目：

=XMATCH(80000000, C2:C6, -1)

下图显示了所有结果：

如何将Excel中的部分文本与通配符匹配

XMATCH函数具有针对通配符的特殊匹配模式： Match_mode参数设置为2。

在通配符匹配模式下，Xmatch公式接受以下通配符字符：

• 问号（？）匹配任何单个字符。
• 星号（*）匹配任何字符序列。

请记住，通配符仅处理文本而不是数字。

例如，要查找以“南方”开头的第一个项目的位置，公式为：

=XMATCH("south*", B2:B6, 2)

或者，您可以在某些单元格中键入通配符表达式，例如F1，并为Lookup_value参数提供单元格参考：

=XMATCH(F1, B2:B6, 2)

使用大多数Excel功能，您将使用Tilde（〜）将星号（〜*）或问号（〜？）视为字面角色，而不是通配符。使用XMatch，不需要Tilde。如果您不定义通配符匹配模式，则XMatch会假设？ *是常规字符。

例如，以下公式将搜索A2：A7的范围，以确切地使用星号字符：

=XMATCH("*", A2:A7)

xMatch反向搜索以查找最后一场比赛

如果查找数组中有几次查找值出现，则有时可能需要获得最后一次发生的位置。

搜索方向是控制XMatch命名s​​earch_mode的第4^个参数。要以相反的顺序搜索，IE在垂直数组中从底部到顶部，在水平数组中从右到左，应将search_mode设置为-1。

在此示例中，我们将返回特定查找值的最后一个记录的位置（请参阅下面的屏幕截图）。为此，设置参数如下：

• Lookup_value- H1中的目标销售员
• Lookup_array-销售员名称：C2：C10
• match_mode是0或省略（确切的匹配）
• search_mode是-1（最后一开始）

将四个论点汇总在一起，我们得到了这个公式：

=XMATCH(H1, C2:C10, 0, -1)

返回劳拉（Laura）进行的最后一次交易的数量：

如何比较Excel中的两列以进行比赛

要比较匹配项的两个列表，您可以将xMatch函数与if and isna一起使用：

if（isna（xmatch（ target_list ， search_list ，0）），“ no Match”，“匹配”）

例如，要比较b2：b10中的列表2与a2：a10中的列表1的列表，该公式采用以下表格：

=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")

在此示例中，我们仅识别匹配，因此if函数的值是一个空字符串（“”）。

输入上面的公式在最上方的单元格（在我们的情况下C2）中，按Enter ，然后将“溢出”自动“溢出”（称为溢出范围）：

该公式如何工作

在公式的核心中，Xmatch函数从列表1中的列表2搜索一个值。如果找到一个值，则返回其相对位置，否则＃n/a错误。在我们的情况下，xMatch的结果是以下数组：

{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}

此数组被“馈送”到ISNA函数，以检查＃N/A错误。对于每个＃N/A错误，ISNA返回true；对于任何其他值 - false。结果，它产生以下逻辑值数组，其中True的代表不匹配，而False表示匹配：

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

上面的数组用于IF函数的逻辑测试。根据您如何配置最后两个参数，公式将输出相应的文本。在我们的情况下，这是一个非匹配（ value_if_true ）的空字符串（“”），匹配项（ value_if_false ）的“列表1中的匹配”。

笔记。该公式仅在支持动态阵列的Excel 365和Excel 2021中起作用。如果您使用Excel 2019，Excel 2016或更早版本，请查看其他解决方案：如何比较Excel中的两列。

在Excel中索引XMatch

Xmatch可以与索引函数结合使用，以从与查找值关联的另一列中检索一个值，就像索引匹配公式一样。通用方法如下：

索引（ return_array ，xmatch（ lookup_value ， lookup_array ）

逻辑非常简单，易于遵循：

XMATCH函数计算查找阵列中查找值的相对位置，并将其传递到索引的row_num参数。基于行号，索引函数从您指定的任何列返回一个值。

例如，要查找E1中的海洋区域，您可以使用此公式：

=INDEX(B2:B6, XMATCH(E1, A2:A6))

索引xMatch xMatch以执行2维查找

要同时查找列和行，请将索引与两个XMatch函数一起使用。第一个Xmatch将获得行号，第二个将检索列号：

索引（ data ，xmatch（ lookup_value ，垂直_ lookup_array ），xmatch（查找值，水平_ lookup_array ））

该公式类似于索引匹配项，除非您可以省略match_mode参数，因为它默认为精确匹配。

例如，要在特定月份（G2）中检索给定项目（G1）的销售号码，该公式为：

=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))

其中b2：d8是排除行和列标题的数据单元，A2：A8是项目列表，B1：D1是月份的名称。

案例敏感的XMATCH公式

如前所述，Excel Xmatch函数是通过设计对大小写的。为了区分文本案例，请将XMatch与确切函数结合使用：

匹配（true，cressect（ lookup_array ， lookup_value ））

以相反顺序从最后到第一个搜索：

匹配（true，exkeck（ lookup_array ， lookup_value ），0，-1）

下面的示例显示了此通用公式。假设您在b2：b11中有一个对病例敏感的产品ID的列表。您正在寻找在E1中找到该项目的相对位置。 E2中的案例敏感公式与此一样简单：

=XMATCH(TRUE, EXACT(B2:B11, E1))

该公式如何工作：

确切的功能将查找值与查找数组中的每个项目进行比较。如果比较的值完全相等（包括字符案例），则该函数将返回true，否则为false。这个逻辑值数组（其中true代表精确匹配）用于XMatch的Lookup_array参数。并且由于查找值是正确的，因此XMATCH函数返回第一个找到的精确匹配或最后一个精确匹配的位置，具体取决于您如何配置search_mode参数。

XMATCH与Excel中的比赛

Xmatch被设计为更强大，更通用的匹配替代品，因此这两个功能具有很多共同点。但是，存在基本差异。

不同的默认行为

匹配函数默认为确切匹配或下一个最小的项目（ arte_type设置为1或省略）。

XMATCH函数默认为精确匹配（ Match_mode设置为0或省略）。

近似匹配的不同行为

当match_mode / match_type参数设置为1：

• 匹配搜索确切匹配或下一个最小的匹配。要求查找阵列应按升序排序。
• XMatch搜索确切的匹配或下一大匹配。不需要任何分类。

当match_mode / match_type参数设置为-1：

• 匹配搜索确切的匹配或下一大匹配。需要按降序对查找阵列进行排序。
• XMatch搜索确切的匹配或下一个最小的匹配。不需要任何分类。

通配符搜索

要查找与XMatch的部分匹配，您需要将Match_mode参数设置为2。

匹配功能没有特殊的通配符匹配模式选项。在大多数情况下，您将其配置为精确匹配（ Match_type设置为0），这也适用于通配符搜索。

搜索模式

与新的Xlookup函数一样，XMatch具有特殊的search_mode参数，该参数允许您定义搜索方向：

• 1或省略（默认） - 搜索首先搜索。
• -1-反向搜索最后一级。

并选择二进制搜索算法，该算法在分类数据上非常快速有效。

• 2-对数据排序上升的二进制搜索。
• -2-二进制搜索数据排序的下降。

二进制搜索，也称为半间隔搜索或对数搜索，是一种特殊的算法，通过将其与数组的中间元素进行比较，可以在数组中找到查找值的位置。二进制搜索比常规搜索要快得多，但仅在排序列表上正确工作。在未分类的数据上，它可能会返回错误的结果，这看起来可能很正常。

匹配的语法根本不能提供搜索模式参数。

XMatch本地处理阵列

与其前身不同，XMATCH函数是为Dynamic Excel设计的，并在本地处理阵列，而无需按CTRL Shift Enter 。这使得公式更容易构建和编辑，尤其是在一起使用一些不同的功能时。只需比较以下解决方案：

• 案例敏感公式：xmatch |匹配
• 比较匹配项的两个列或列表：XMatch |匹配

xMatch和匹配可用性

Xmatch是一个新功能，仅在Microsoft 365和Excel 2021中可在Excel中使用。

匹配功能可在Excel 2007的Excel 365版本中获得。

这就是在Excel中使用XMATCH函数的方法。我感谢您阅读，并希望下周在我们的博客上见到您！

练习工作簿下载

excel xmatch公式示例（.xlsx文件）

以上是excel xMatch函数与公式示例的详细内容。更多信息请关注PHP中文网其他相关文章！