精通 Excel XMATCH 函數：進階查詢終極指南

在進行數據分析時，您經常需要查找項目在清單或儲存格範圍內的相對位置。多年來，這意味著需要依賴複雜的 Excel 函數組合。

在 Excel 中執行模式匹配操作最有效的原生方法是使用 XMATCH 函數。XMATCH 簡單而強大，具有多項功能，可實現精確且靈活的匹配。但是，如果您可以完全繞過公式呢？

在本教學中，我們將介紹 Excel XMATCH 函數的基礎知識，分析其語法，並提供實用範例。我們還將介紹 Excelmatic，這是一個由 AI 驅動的工具，可以透過簡單的語言提示完成相同的任務，提供更快、更直觀的替代方案。最後，我們將比較 XMATCH 與 MATCH，並介紹兩種方法的最佳實踐。

什麼是 XMATCH？

XMATCH 是 Excel 中用於匹配操作的靈活且強大的函數。它允許您執行各種查詢，從垂直和水平搜尋到精確匹配、近似匹配，甚至是部分（萬用字元）匹配。

如果您使用 Excel 有一段時間了，您可能熟悉 MATCH 函數。在 XMATCH 推出之前，MATCH 是執行這些任務的首選函數。然而，MATCH 有其局限性。XMATCH 是一個期待已久的升級，它提供了任意方向的搜尋和多種匹配類型，使其適用於更廣泛的使用場景。

雖然 XMATCH 是一個重要的進步，但數據查詢的下一個演進涉及人工智慧。像 Excelmatic 這樣的工具消除了記憶函數語法的需要，讓您只需陳述您的目標即可。

請注意，XMATCH 僅在 Microsoft 365 版 Excel 和 Excel 2021 中可用。對於使用舊版本的使用者，MATCH 是唯一的內建選項。

現在讓我們來看看 XMATCH 的語法和參數。

XMATCH 語法與參數

以下是 XMATCH 函數的基本語法：

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

該函數有兩個必要參數（lookup_value 和 lookup_array）和兩個可選參數（match_mode 和 search_mode），用於更精確和靈活的操作。

• Lookup_value：您要尋找的值。
• Lookup_array：您要搜尋的陣列或清單。
• Match_mode：指定匹配類型。
  • 0：精確匹配（預設值）。
  • -1：精確匹配或下一個較小的項目。
  • 1：精確匹配或下一個較大的項目。
  • 2：萬用字元匹配，使用 * 和 ? 等字元。
• Search_mode：指定搜尋方向和演算法。
  • 1：從第一個搜尋到最後一個（預設值）。
  • -1：從最後一個搜尋到第一個（反向搜尋）。
  • 2：對按升序排序的資料進行二分搜尋。
  • -2：對按降序排序的資料進行二分搜尋。

雖然功能強大，但記住這些參數及其對應的數字可能很麻煩。這就是 AI 方法提供明顯優勢的地方。

匹配模式：精確度與靈活性

XMATCH 的可選 match_mode 參數允許您執行精確和近似匹配。讓我們看看它是如何工作的，並與更現代的方法進行比較。

精確匹配

精確匹配（match_mode = 0）是預設值。Excel 將尋找與查詢值完全匹配的項目；否則，它會傳回 #N/A 錯誤。這在準確性至關重要時是必需的。

例如，要在 DataCamp 課程目錄的技術清單中查找「Excel」的確切位置，您可以使用公式 =XMATCH(D3, A3:A14, 0)。Excel 正確傳回位置 6。

Excelmatic 方法：直接詢問即可

使用 Excelmatic，您不需要公式。只需上傳您的檔案並用簡單的語言提問：

'Excel' 在技術清單中的位置是什麼？

Excelmatic 理解您的請求並立即提供答案，節省您編寫和除錯公式的時間和精力。

近似匹配

有時您需要靈活的匹配。XMATCH 允許您查找下一個較小（match_mode = -1）或下一個較大（match_mode = 1）的值。

在下面的範例中，我們尋找最接近 15 的課程數量。

• 使用 match_mode = -1，公式傳回位置 6（對應 12 門課程，即最接近的較小數字）。
• 使用 match_mode = 1，它傳回位置 5（對應 16 門課程，即最接近的較大數字）。

Excelmatic 方法：簡單直接

無需記住哪個數字（-1 或 1）對應哪種操作，您可以直接詢問 Excelmatic：

查找最接近 15 但不超過的課程數量的位置。

或者：

查找課程數量為 15 或下一個較高值的位置。

Excelmatic 解讀您的意圖並提供正確的結果，而您無需查閱函數的文件。

萬用字元匹配

XMATCH 的一個強大功能是當 match_mode 設定為 2 時，可以使用萬用字元。這適用於文字資料。

• ?：匹配任何單一字元。
• *：匹配任何字元序列。

在下面的範例中，我們使用公式 =XMATCH("E*", A3:A14, 2) 來查找第一個以「E」開頭的技術。

Excelmatic 方法：自然語言模式

使用 Excelmatic，您可以用自然語言描述模式：

第一個以 'E' 開頭的技術的位置是什麼？

或者對於更複雜的模式：

查找第一個有三個字母的技術的位置。

這比使用萬用字元和特定匹配模式來建構公式要直觀得多。

XMATCH 搜尋模式：方向與效率

XMATCH 還允許您控制搜尋方向，這對於查找時間序列中的最新條目等任務非常有用。

反向搜尋

要執行反向搜尋（從最後一個到第一個），請將 search_mode 參數設定為 -1。在下面的範例中，我們使用公式 =XMATCH("Peter", B3:B14, 0, -1) 查找使用者「Peter」完成的最後一門課程。

Excelmatic 方法：清晰是關鍵

為什麼要記住 -1 表示反向搜尋？使用 Excelmatic，您的意圖很明確：

查找 Peter 完成的最後一門課程的位置。

Excelmatic 根據您的簡單語言指令處理邏輯，無論是查找第一次、最後一次還是第 N 次出現。

二分搜尋

對於非常大的、已排序的資料集，XMATCH 可以使用二分搜尋演算法（search_mode = 2 用於升序，-2 用於降序）以獲得極快的速度。然而，這要求資料必須預先排序。Excelmatic 會自動優化其搜尋過程，無需您指定演算法或預先排序資料。

XMATCH 的實際應用與範例

XMATCH 功能強大，但將其與其他函數結合使用可能會導致複雜的巢狀公式。讓我們看看 AI 方法如何簡化這些進階任務。

使用 INDEX/XMATCH 進行二維查詢

一個常見的任務是在特定行和列的交集處查找值。使用傳統的 Excel，這需要將 INDEX 與兩個 XMATCH 函數結合使用。

在下面的範例中，公式 =INDEX(C3:F14, XMATCH(I2, A3:A14, 0), XMATCH(I3, C2:F2, 0)) 查找「Q2」中「Excel」課程的註冊使用者數量。

Excelmatic 方法：直接獲得答案，而非座標

使用 Excelmatic，您可以跳過查找行號和列號的中間步驟，直接詢問最終答案：

Q2 中 Excel 課程的註冊使用者數量是多少？

Excelmatic 理解您的資料之間的關係，執行二維查詢，並提供您需要的值。這就像詢問路線與瞬間傳送到目的地之間的區別。

多欄位查詢

如果您需要找到符合多個條件的行怎麼辦？使用 XMATCH，這涉及使用布林邏輯的複雜陣列公式，例如 =XMATCH(1, (C3:C14<1000)*(D3:D14<1000)*(E3:E14<1000)*(F3:F14<1000), 0)。這個公式很難編寫，更難除錯。

Excelmatic 方法：描述您想要的內容

忘記布林陣列。只需向 Excelmatic 描述條件：

查找第一個在每個季度註冊使用者都少於 1000 的技術。

這不僅更簡單，而且可讀性更高，出錯的可能性更低。

動態陣列篩選

您可以結合 XMATCH、ISNUMBER 和 FILTER 來建立動態篩選器。這個過程涉及建立輔助欄或複雜的巢狀公式來決定保留哪些行。

Excelmatic 方法：輕鬆篩選

篩選是 Excelmatic 的核心優勢之一。只需陳述您的條件：

篩選表格，僅顯示由 Sam 和 Peter 完成的課程。

Excelmatic 將立即為您生成篩選後的表格，無需任何輔助欄或多步驟公式建構。

XMATCH 與 MATCH：主要差異

XMATCH 被設計用來取代 MATCH。以下是為熟悉舊函數的使用者提供的快速比較：

常見陷阱與解決方案

無論是使用 XMATCH 還是 Excelmatic，以下是一些提示：

• 檢查資料類型：XMATCH 對資料類型敏感（例如，文字與數字）。請確保您的資料是乾淨的。Excelmatic 通常更寬容，但良好的資料衛生習慣總是最好的。
• 處理 #N/A 錯誤：如果未找到匹配項，XMATCH 會傳回 #N/A 錯誤。您可以將其包裝在 IFNA 或 IFERROR 函數中以顯示自訂訊息。使用 Excelmatic，您會得到像「未找到匹配項」這樣清晰、易讀的回應。
• 善用匹配模式：如果您堅持使用公式，請了解不同的 match_mode 和 search_mode 選項，以釋放 XMATCH 的全部潛力。如果您使用 Excelmatic，請專注於盡可能清晰地表述您的問題。

效能考量

在處理大量、已排序資料集的原始計算速度方面，精心設計的 XMATCH 二分搜尋速度極快。然而，對於絕大多數現實世界的商業場景，瓶頸不是函數的執行速度，而是人類編寫、測試和除錯公式所花費的時間。

這就是 Excelmatic 提供巨大效能提升的地方。使用簡單的語言提示而不是建構複雜的公式所節省的時間，可以用分鐘甚至小時來衡量，特別是對於多步驟任務。

結論

XMATCH 對於任何認真的 Excel 使用者來說都是一個強大且必不可少的函數。它比舊有的 MATCH 函數有顯著改進，可以處理各種查詢場景。

然而，數據分析的世界正在發展。像 Excelmatic 這樣的AI 驅動工具代表了下一次飛躍。透過允許您使用自然語言提問，它們消除了函數和公式的陡峭學習曲線，減少了錯誤，並顯著加快了您的工作流程。

對於那些喜歡精心設計完美公式的人來說，XMATCH 是一個值得掌握的神奇工具。對於那些希望盡快從數據中獲得洞察的人來說，像 Excelmatic 這樣的 AI 助手是明確的選擇。

準備好簡化您的數據查詢並跳過複雜的公式了嗎？立即免費試用 Excelmatic，體驗 AI 驅動的數據分析如何改變您的工作流程。

Excel XMATCH 常見問題解答

XMATCH 有什麼用途？

XMATCH 是一個 Excel 函數，用於查找項目在陣列或範圍內的相對位置。它用於各種查詢任務，從簡單的搜尋到構成更複雜公式的基礎。

XMATCH 和 MATCH 有什麼區別？

XMATCH 是 MATCH 的現代繼任者。其主要優勢包括更安全的精確匹配預設值、內建的反向和二分搜尋模式，以及對萬用字元匹配的支援。

我可以在所有版本的 Excel 中使用 XMATCH 嗎？

不行，XMATCH 僅在 Microsoft 365 版 Excel 和 Excel 2021 中可用。舊版本不支援它。

有沒有比使用 XMATCH 更簡單的方法來進行查詢？

有。像 Excelmatic 這樣的 AI 工具允許您透過輸入簡單的語言請求來執行相同的查詢任務。這消除了記憶函數語法、參數和複雜巢狀結構的需要，對大多數使用者來說，這使得操作更快、更直觀。