本教程介紹了新的Excel XMatch函數(shù)，并顯示了與求解一些常見任務(wù)相比它比匹配更好。

在Excel 365中，添加了XMATCH函數(shù)以取代匹配功能。但是，在您開始升級(jí)現(xiàn)有公式之前，了解新功能的所有優(yōu)勢(shì)以及它與舊功能的不同是明智的。

總而言之，XMATCH函數(shù)與匹配相同，但更靈活和健壯。它可以在垂直和水平陣列中查找，搜索第一至持久的次數(shù)或最后一級(jí)，找到精確的，近似和部分匹配，并使用更快的二進(jìn)制搜索算法。

Excel XMatch函數(shù)

Excel中的XMATCH函數(shù)返回值在陣列或一系列單元格中的相對(duì)位置。

它具有以下語法：

在哪里：

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

Lookup_array （必需） - 搜索的數(shù)組或單元格范圍。

match_mode （可選） - 指定要使用的匹配類型：

• 0或省略（默認(rèn)） - 確切匹配
• -1-確切的匹配或下一個(gè)最小的值
• 1-確切的匹配或第二大價(jià)值
• 2-通配符匹配（*，？）

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

• 1或省略（默認(rèn)） - 從第一個(gè)到最后一個(gè)搜索。
• -1-最后到第一個(gè)以相反順序搜索。
• 2-二進(jìn)制搜索上升。需要Lookup_array以升序排序。
• -2-二進(jìn)制搜索下降。需要Lookup_array以降序排序。

二進(jìn)制搜索是一種更快的算法，可在排序的數(shù)組上有效工作。有關(guān)更多信息，請(qǐng)參閱搜索模式。

哪個(gè)Excel版本具有XMatch？

XMATCH函數(shù)僅在Microsoft 365和Excel 2021的Excel中可用。在Excel 2019，Excel 2016及更早版本中，不支持此功能。

excel中的基本Xmatch公式

為了了解該函數(shù)的能力，讓我們構(gòu)建一個(gè)最簡(jiǎn)單的XMATCH公式，僅定義了前兩個(gè)必需的參數(shù)，并將可選的參數(shù)定義為默認(rèn)值。

假設(shè)您有一個(gè)按大小排名的海洋清單（C2：C6），您希望找到特定海洋的排名。要完成它，只需將海洋的名稱（例如印第安人）作為查找價(jià)值，而名稱的整個(gè)列表則作為查找陣列：

=XMATCH("Indian", C2:C6)

為了使公式更靈活，請(qǐng)輸入某些單元中感興趣的海洋，例如F1：

=XMATCH(F1, C2:C6)

結(jié)果，您將獲得一個(gè)XMatch公式，可以在垂直陣列中查找。輸出是陣列中查找值的相對(duì)位置，在我們的情況下，該位置與海洋等級(jí)相對(duì)應(yīng)：

類似的公式也適用于水平陣列。您需要做的就是調(diào)整Lookup_Array參考：

=XMATCH(B5, B1:F1)

excel xmatch函數(shù) - 要記住的事物

要有效地在工作表中使用XMatch并防止意外結(jié)果，請(qǐng)記住以下3個(gè)簡(jiǎn)單事實(shí)：

• 如果查找陣列中有兩個(gè)或多個(gè)查找值的出現(xiàn)，則如果將search_mode參數(shù)設(shè)置為1或省略，則返回第一個(gè)匹配的位置。將search_mode設(shè)置為-1，該功能以相反順序搜索，并返回最后一場(chǎng)匹配的位置，如本示例所示。
• 如果找不到查找值，則會(huì)發(fā)生＃N/A錯(cuò)誤。
• XMATCH函數(shù)本質(zhì)上是對(duì)病例敏感的，無法區(qū)分字母案例。要區(qū)分小寫和大寫字符，請(qǐng)使用此對(duì)案例敏感的Xmatch公式。

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

以下示例將幫助您對(duì)XMATCH功能及其實(shí)際用途有更多了解。

確切的匹配與近似匹配

Xmatch的匹配行為由可選的Match_mode參數(shù)控制：

• 0或省略（默認(rèn)） - 公式僅搜索精確匹配。如果找不到確切的匹配，則返回＃N/A錯(cuò)誤。
• -1-公式首先搜索精確匹配，然后搜索下一個(gè)較小的項(xiàng)目。
• 1-公式首先搜索精確匹配，然后搜索下一個(gè)較大的項(xiàng)目。

現(xiàn)在，讓我們看看不同的匹配模式如何影響公式的結(jié)果。假設(shè)您想找出某個(gè)區(qū)域（例如80,000,000公里² ）位于所有海洋中。

精確匹配

如果將0用于match_mode ，則會(huì)獲得＃n/a錯(cuò)誤，因?yàn)樵摴秸也坏酵耆扔诓檎抑档闹担?/p>

=XMATCH(80000000, C2:C6, 0)

下一個(gè)最小的項(xiàng)目

如果將-1用于match_mode ，則公式將返回3，因?yàn)樽罱咏檎抑档钠ヅ渲禐?0,560,000，并且是查找數(shù)組中的第3^個(gè)項(xiàng)目：

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

下一個(gè)最大的項(xiàng)目

如果將1用于match_mode ，則公式將輸出2，因?yàn)樽罱咏钠ヅ渲荡笥诓檎抑凳?5,133,000，這是查找數(shù)組中的2^個(gè)項(xiàng)目：

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

下圖顯示了所有結(jié)果：

如何將Excel中的部分文本與通配符匹配

XMATCH函數(shù)具有針對(duì)通配符的特殊匹配模式： Match_mode參數(shù)設(shè)置為2。

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

• 問號(hào)（？）匹配任何單個(gè)字符。
• 星號(hào)（*）匹配任何字符序列。

請(qǐng)記住，通配符僅處理文本而不是數(shù)字。

例如，要查找以“南方”開頭的第一個(gè)項(xiàng)目的位置，公式為：

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

或者，您可以在某些單元格中鍵入通配符表達(dá)式，例如F1，并為Lookup_value參數(shù)提供單元格參考：

=XMATCH(F1, B2:B6, 2)

使用大多數(shù)Excel功能，您將使用Tilde（?）將星號(hào)（?*）或問號(hào)（?？）視為字面角色，而不是通配符。使用XMatch，不需要Tilde。如果您不定義通配符匹配模式，則XMatch會(huì)假設(shè)？ *是常規(guī)字符。

例如，以下公式將搜索A2：A7的范圍，以確切地使用星號(hào)字符：

=XMATCH("*", A2:A7)

xMatch反向搜索以查找最后一場(chǎng)比賽

如果查找數(shù)組中有幾次查找值出現(xiàn)，則有時(shí)可能需要獲得最后一次發(fā)生的位置。

搜索方向是控制XMatch命名s??earch_mode的第4^個(gè)參數(shù)。要以相反的順序搜索，IE在垂直數(shù)組中從底部到頂部，在水平數(shù)組中從右到左，應(yīng)將search_mode設(shè)置為-1。

在此示例中，我們將返回特定查找值的最后一個(gè)記錄的位置（請(qǐng)參閱下面的屏幕截圖）。為此，設(shè)置參數(shù)如下：

• Lookup_value- H1中的目標(biāo)銷售員
• Lookup_array-銷售員名稱：C2：C10
• match_mode是0或省略（確切的匹配）
• search_mode是-1（最后一開始）

將四個(gè)論點(diǎn)匯總在一起，我們得到了這個(gè)公式：

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

返回勞拉（Laura）進(jìn)行的最后一次交易的數(shù)量：

如何比較Excel中的兩列以進(jìn)行比賽

要比較匹配項(xiàng)的兩個(gè)列表，您可以將xMatch函數(shù)與if and isna一起使用：

例如，要比較b2：b10中的列表2與a2：a10中的列表1的列表，該公式采用以下表格：

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

在此示例中，我們僅識(shí)別匹配，因此if函數(shù)的值是一個(gè)空字符串（“”）。

輸入上面的公式在最上方的單元格（在我們的情況下C2）中，按Enter ，然后將“溢出”自動(dòng)“溢出”（稱為溢出范圍）：

該公式如何工作

在公式的核心中，Xmatch函數(shù)從列表1中的列表2搜索一個(gè)值。如果找到一個(gè)值，則返回其相對(duì)位置，否則＃n/a錯(cuò)誤。在我們的情況下，xMatch的結(jié)果是以下數(shù)組：

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

此數(shù)組被“饋送”到ISNA函數(shù)，以檢查＃N/A錯(cuò)誤。對(duì)于每個(gè)＃N/A錯(cuò)誤，ISNA返回true；對(duì)于任何其他值 - false。結(jié)果，它產(chǎn)生以下邏輯值數(shù)組，其中True的代表不匹配，而False表示匹配：

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

上面的數(shù)組用于IF函數(shù)的邏輯測(cè)試。根據(jù)您如何配置最后兩個(gè)參數(shù)，公式將輸出相應(yīng)的文本。在我們的情況下，這是一個(gè)非匹配（ value_if_true ）的空字符串（“”），匹配項(xiàng)（ value_if_false ）的“列表1中的匹配”。

筆記。該公式僅在支持動(dòng)態(tài)陣列的Excel 365和Excel 2021中起作用。如果您使用Excel 2019，Excel 2016或更早版本，請(qǐng)查看其他解決方案：如何比較Excel中的兩列。

在Excel中索引XMatch

Xmatch可以與索引函數(shù)結(jié)合使用，以從與查找值關(guān)聯(lián)的另一列中檢索一個(gè)值，就像索引匹配公式一樣。通用方法如下：

邏輯非常簡(jiǎn)單，易于遵循：

XMATCH函數(shù)計(jì)算查找陣列中查找值的相對(duì)位置，并將其傳遞到索引的row_num參數(shù)?；谛刑?hào)，索引函數(shù)從您指定的任何列返回一個(gè)值。

例如，要查找E1中的海洋區(qū)域，您可以使用此公式：

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

索引xMatch xMatch以執(zhí)行2維查找

要同時(shí)查找列和行，請(qǐng)將索引與兩個(gè)XMatch函數(shù)一起使用。第一個(gè)Xmatch將獲得行號(hào)，第二個(gè)將檢索列號(hào)：

該公式類似于索引匹配項(xiàng)，除非您可以省略match_mode參數(shù)，因?yàn)樗J(rèn)為精確匹配。

例如，要在特定月份（G2）中檢索給定項(xiàng)目（G1）的銷售號(hào)碼，該公式為：

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

其中b2：d8是排除行和列標(biāo)題的數(shù)據(jù)單元，A2：A8是項(xiàng)目列表，B1：D1是月份的名稱。

案例敏感的XMATCH公式

如前所述，Excel Xmatch函數(shù)是通過設(shè)計(jì)對(duì)大小寫的。為了區(qū)分文本案例，請(qǐng)將XMatch與確切函數(shù)結(jié)合使用：

以相反順序從最后到第一個(gè)搜索：

下面的示例顯示了此通用公式。假設(shè)您在b2：b11中有一個(gè)對(duì)病例敏感的產(chǎn)品ID的列表。您正在尋找在E1中找到該項(xiàng)目的相對(duì)位置。 E2中的案例敏感公式與此一樣簡(jiǎn)單：

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

該公式如何工作：

確切的功能將查找值與查找數(shù)組中的每個(gè)項(xiàng)目進(jìn)行比較。如果比較的值完全相等（包括字符案例），則該函數(shù)將返回true，否則為false。這個(gè)邏輯值數(shù)組（其中true代表精確匹配）用于XMatch的Lookup_array參數(shù)。并且由于查找值是正確的，因此XMATCH函數(shù)返回第一個(gè)找到的精確匹配或最后一個(gè)精確匹配的位置，具體取決于您如何配置search_mode參數(shù)。

XMATCH與Excel中的比賽

Xmatch被設(shè)計(jì)為更強(qiáng)大，更通用的匹配替代品，因此這兩個(gè)功能具有很多共同點(diǎn)。但是，存在基本差異。

不同的默認(rèn)行為

匹配函數(shù)默認(rèn)為確切匹配或下一個(gè)最小的項(xiàng)目（ arte_type設(shè)置為1或省略）。

XMATCH函數(shù)默認(rèn)為精確匹配（ Match_mode設(shè)置為0或省略）。

近似匹配的不同行為

當(dāng)match_mode / match_type參數(shù)設(shè)置為1：

• 匹配搜索確切匹配或下一個(gè)最小的匹配。要求查找陣列應(yīng)按升序排序。
• XMatch搜索確切的匹配或下一大匹配。不需要任何分類。

當(dāng)match_mode / match_type參數(shù)設(shè)置為-1：

• 匹配搜索確切的匹配或下一大匹配。需要按降序?qū)Σ檎谊嚵羞M(jìn)行排序。
• XMatch搜索確切的匹配或下一個(gè)最小的匹配。不需要任何分類。

通配符搜索

要查找與XMatch的部分匹配，您需要將Match_mode參數(shù)設(shè)置為2。

匹配功能沒有特殊的通配符匹配模式選項(xiàng)。在大多數(shù)情況下，您將其配置為精確匹配（ Match_type設(shè)置為0），這也適用于通配符搜索。

搜索模式

與新的Xlookup函數(shù)一樣，XMatch具有特殊的search_mode參數(shù)，該參數(shù)允許您定義搜索方向：

• 1或省略（默認(rèn)） - 搜索首先搜索。
• -1-反向搜索最后一級(jí)。

并選擇二進(jìn)制搜索算法，該算法在分類數(shù)據(jù)上非常快速有效。

• 2-對(duì)數(shù)據(jù)排序上升的二進(jìn)制搜索。
• -2-二進(jìn)制搜索數(shù)據(jù)排序的下降。

二進(jìn)制搜索，也稱為半間隔搜索或對(duì)數(shù)搜索，是一種特殊的算法，通過將其與數(shù)組的中間元素進(jìn)行比較，可以在數(shù)組中找到查找值的位置。二進(jìn)制搜索比常規(guī)搜索要快得多，但僅在排序列表上正確工作。在未分類的數(shù)據(jù)上，它可能會(huì)返回錯(cuò)誤的結(jié)果，這看起來可能很正常。

匹配的語法根本不能提供搜索模式參數(shù)。

XMatch本地處理陣列

與其前身不同，XMATCH函數(shù)是為Dynamic Excel設(shè)計(jì)的，并在本地處理陣列，而無需按CTRL Shift Enter 。這使得公式更容易構(gòu)建和編輯，尤其是在一起使用一些不同的功能時(shí)。只需比較以下解決方案：

• 案例敏感公式：xmatch |匹配
• 比較匹配項(xiàng)的兩個(gè)列或列表：XMatch |匹配

xMatch和匹配可用性

Xmatch是一個(gè)新功能，僅在Microsoft 365和Excel 2021中可在Excel中使用。

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

這就是在Excel中使用XMATCH函數(shù)的方法。我感謝您閱讀，并希望下周在我們的博客上見到您！

練習(xí)工作簿下載

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

以上是excel xMatch函數(shù)與公式示例的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！