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