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中文網其他相關文章！