本教程解釋瞭如何在公式示例中使用excel中的匹配函數(shù)。它還顯示瞭如何通過使用Vlookup和匹配的動(dòng)態(tài)公式來改善查找公式。

在Microsoft Excel中，有許多不同的查找/參考功能可以幫助您在一系列單元格中找到某個(gè)值，而匹配就是其中之一?；旧希鼧?biāo)識(shí)了項(xiàng)目在一系列單元格中的相對(duì)位置。但是，匹配函數(shù)可以做的遠(yuǎn)遠(yuǎn)超過其純粹的本質(zhì)。

Excel匹配功能 - 語(yǔ)法和使用

Excel中的匹配函數(shù)在一系列單元格中搜索指定值，並返回該值的相對(duì)位置。

匹配函數(shù)的語(yǔ)法如下：

匹配（lookup_value，lookup_array，[match_type]）

Lookup_value （必需） - 您想要找到的值。它可以是數(shù)字，文本或邏輯值以及單元格的參考。

Lookup_array （必需） - 要搜索的單元格範(fàn)圍。

match_type （可選） - 定義匹配類型。它可以是以下值之一：1，0，-1。 Match_Type參數(shù)設(shè)置為0僅返回確切的匹配，而其他兩種類型允許大致匹配。

• 1或省略（默認(rèn)） - 近似匹配（最接近較小）。在查找陣列中找到最大的值，該值小於或等於查找值。需要按升序排序查找陣列，從最小到最大或從A到Z進(jìn)行排序。
• 0-確切匹配。在數(shù)組中找到完全等於查找值的第一個(gè)值。無需分類。
• -1-近似匹配（最接近較大）。找到大於或等於查找值的最小值。查找陣列應(yīng)以降序從最大到最小或從z到A進(jìn)行排序。

為了更好地理解匹配功能，讓我們根據(jù)此數(shù)據(jù)製作一個(gè)簡(jiǎn)單的公式：A列中的學(xué)生名稱及其在B列中的考試成績(jī)，從最大到最小。要找出特定學(xué)生（例如，勞拉）的位置，請(qǐng)使用此簡(jiǎn)單的公式：

=MATCH("Laura", A2:A8, 0)

可選地，您可以將查找值放入某些單元格（在此示例中的E1）中，並在Excel匹配公式中引用該單元格：

=MATCH(E1, A2:A8, 0)

如您在上面的屏幕截圖中看到的那樣，學(xué)生名稱是按任意順序輸入的，因此我們將Match_type參數(shù)設(shè)置為0（精確匹配），因?yàn)橹挥写似ヅ漕愋筒恍枰檎覕?shù)組中的排序值。從技術(shù)上講，比賽公式返回勞拉在該範(fàn)圍內(nèi)的相對(duì)位置。但是，由於分?jǐn)?shù)從最大到最小的分類，所以它也告訴我們，勞拉在所有學(xué)生中的得分排名^第五。

提示。在Excel 365和Excel 2021中，您可以使用XMATCH函數(shù)，該功能是現(xiàn)代，更強(qiáng)大的接班人。

關(guān)於匹配功能，您應(yīng)該知道的4件事

正如您剛剛看到的那樣，在Excel中使用Match很容易。但是，就像幾乎所有其他功能一樣，您應(yīng)該注意的是：

1. 匹配函數(shù)返回?cái)?shù)組中查找值的相對(duì)位置，而不是值本身。
2. 匹配是不敏感的，這意味著它在處理文本值時(shí)不會(huì)區(qū)分小寫和大寫字符。
3. 如果查找數(shù)組包含查找值的幾個(gè)出現(xiàn)，則返回第一個(gè)值的位置。
4. 如果在查找數(shù)組中找不到查找值，則返回＃N/A錯(cuò)誤。

如何在Excel -Formula示例中使用匹配

現(xiàn)在，您知道了Excel匹配功能的基本用途，讓我們討論一些超出基礎(chǔ)知識(shí)的公式示例。

與通配符的部分比賽

像許多其他功能一樣，Match了解以下通配符：

• 問號(hào)（？） - 替換任何一個(gè)字符
• 星號(hào)（*） - 替換任何字符序列

筆記。通配符只能用於匹配公式中，匹配公式設(shè)置為0。

當(dāng)您想匹配整個(gè)文本字符串，而是字符串的某些部分時(shí)，與通配符的匹配公式在情況下很有用。為了說明這一點(diǎn)，請(qǐng)考慮以下示例。

假設(shè)您在過去一個(gè)月中有區(qū)域經(jīng)銷商及其銷售數(shù)據(jù)列表。您想在列表中找到某個(gè)經(jīng)銷商的相對(duì)位置（按降序銷售量排序），但是您不記得他的名字，儘管您確實(shí)記得一些第一個(gè)字符。

假設(shè)轉(zhuǎn)銷商名稱在A2：A11範(fàn)圍內(nèi)，並且您正在搜索以“汽車”開頭的名稱，則該公式如下：

=MATCH("car*", A2:A11,0)

為了使我們的匹配公式更具用途，您可以在某些單元格中鍵入查找值（在此示例中的E1），並將該單元格與通配符的字符相連，因此：

=MATCH(E1&"*", A2:A11,0)

如下屏幕截圖所示，公式返回2，這是“ Carter”的位置：

要替換查找值中的一個(gè)字符，請(qǐng)使用“？”通配符操作員，這樣：

=MATCH("ba?er", A2:A11,0)

上面的公式將與“貝克”的名稱匹配，並重新重新列出其相對(duì)位置，即5。

病例敏感的匹配公式

如本教程開頭所述，匹配函數(shù)不能區(qū)分大寫和小寫字符。要製作對(duì)案例敏感的匹配公式，請(qǐng)將匹配與精確比較單元格（包括字符案例）的確切函數(shù)結(jié)合使用。

這是匹配數(shù)據(jù)的通用案例敏感公式：

匹配（true，精確（查找數(shù)組，查找值），0）

該公式可與以下邏輯合作：

• 確切的函??數(shù)將查找值與查找數(shù)組的每個(gè)元素進(jìn)行比較。如果比較的單元格完全相等，則該函數(shù)將返回真實(shí)，否則為false。
• 然後，匹配函數(shù)將true（這是其Lookup_value ）與由精確返回的數(shù)組中的每個(gè)值進(jìn)行比較，並返回第一匹配的位置。

請(qǐng)記住，這是一個(gè)陣列公式，需要按CTRL Shift Enter正確完成。

假設(shè)您的查找值在單元格E1中，並且查找陣列為A2：A9，則公式如下：

=MATCH(TRUE, EXACT(A2:A9,E1),0)

以下屏幕截圖顯示了Excel中的案例敏感匹配公式：

比較2列的匹配和差異（ISNA匹配）

檢查兩個(gè)列表中的比賽和差異是Excel中最常見的任務(wù)之一，可以通過多種方式完成。 ISNA/匹配公式是其中之一：

if（isna（匹配（ list1中的1個(gè)值， list2，0 ）），“不在列表1”，“”）

對(duì)於列表1中不存在的列表2的任何值，公式返回“不在列表1 ”。這就是：

• 匹配函數(shù)搜索列表2中列表1的值。如果找到一個(gè)值，它將返回其相對(duì)位置，否則＃n/a錯(cuò)誤否則。
• Excel中的ISNA函數(shù)只能做一件事 - 檢查＃n/a錯(cuò)誤（意味著“不可用”）。如果給定值是＃n/a錯(cuò)誤，則該函數(shù)將返回true，否則為false。在我們的情況下，true意味著列表1中未找到一個(gè)值1（即，匹配返回＃n/a錯(cuò)誤）。
• 因?yàn)槟挠脩魧?duì)於未出現(xiàn)在列表1中的值的值可能會(huì)很困惑，所以您將IF函數(shù)包裹在ISNA周圍以顯示“不在列表1 ”或您想要的任何文本。

例如，要將B中的值與A列中的值進(jìn)行比較，該公式採(cǎi)用以下形狀（其中B2是最高單元格）：

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

如您所記得的那樣，Excel中的匹配功能本身對(duì)細(xì)節(jié)不敏感。為了區(qū)分角色情況，請(qǐng)?jiān)?em>Lookup_array參數(shù)中嵌入確切的函數(shù)，並記住按Ctrl Shift Enter Enter以完成此數(shù)組公式：

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

以下屏幕截圖顯示了兩種行動(dòng)中的公式：

要學(xué)習(xí)其他方法來比較Excel中的兩個(gè)列表，請(qǐng)參見以下教程：如何比較Excel中的2列。

Excel Vlookup和匹配

此示例假設(shè)您已經(jīng)對(duì)Excel Vlookup函數(shù)有一些基本知識(shí)。而且，如果您這樣做的話，您很可能會(huì)遇到其眾多限制（在為什麼Excel Vlookup不起作用的原因中可以找到詳細(xì)的概述），並且正在尋找更強(qiáng)大的替代方案。

Vlookup最令人討厭的缺點(diǎn)之一是，它在在查找表中插入或刪除列後停??止工作。之所以發(fā)生這種情況，是因?yàn)閂lookup根據(jù)您指定的返回列的數(shù)量（索引編號(hào)）提取匹配值。由於公式中的索引號(hào)是“硬編碼”的，因此當(dāng)將新列添加到表中時(shí)，Excel無法將其調(diào)整。

Excel匹配函數(shù)涉及查找值的相對(duì)位置，這使其非常適合col_index_num vlookup的參數(shù)。換句話說，您沒有將返回列指定為靜態(tài)號(hào)碼，而是使用匹配來獲取該列的當(dāng)前位置。

為了使事情更容易理解，讓我們?cè)俅问褂脤W(xué)生的考試成績(jī)（類似於我們?cè)诒窘坛涕_始時(shí)使用的考試），但是這次我們將檢索真正的分?jǐn)?shù)，而不是其相對(duì)位置。

假設(shè)查找值在單元格F1中，則表數(shù)組為$ a $ 1：$ c $ 2（如果您計(jì)劃將公式複製到其他單元格中，則使用絕對(duì)單元格鎖定是一個(gè)好習(xí)慣），該公式如下：

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

3 ^rd參數(shù)（ col_index_num ）設(shè)置為3，因?yàn)槲覀円?em>數(shù)學(xué)分?jǐn)?shù)是表中的3^列列。正如您在下面的屏幕截圖中看到的那樣，此常規(guī)vlookup公式效果很好：

但是，只有插入或刪除列之前：

所以，為什麼要#ref！錯(cuò)誤？因?yàn)?em>col_index_num設(shè)置為3，告訴excel從第三列獲取一個(gè)值，而現(xiàn)在表格數(shù)組中只有2列。

為了防止此類事情發(fā)生，您可以通過包含以下匹配功能來使您的vlookup公式更具動(dòng)態(tài)性：

MATCH(E2,A1:C1,0)

在哪裡：

• E2是查找值，它完全等於返回列的名稱，即您要從中提取值的列（此示例中的數(shù)學(xué)分?jǐn)?shù)）。
• A1：C1是包含表標(biāo)頭的查找陣列。

現(xiàn)在，將此匹配函數(shù)包含在您的Vlookup公式的Col_index_num參數(shù)中，例如：

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

並確保它無可挑剔地工作，無論您添加多少列或刪除??：

在上面的屏幕截圖中，即使我的用戶將其移至工作表中的另一個(gè)位置，我也鎖定了公式正確工作的所有單元。一個(gè)您可以在下面的屏幕截圖中看到，刪除列後，公式正常工作；此外，Excel足夠聰明，可以在這種情況下正確調(diào)整絕對(duì)參考：

Excel Hlookup和Match

以類似的方式，您可以使用Excel匹配功能來改善Hookup公式。一般原理與Vlookup的情況基本相同：您使用匹配函數(shù)獲取返回列的相對(duì)位置，並將該數(shù)字提供給您的hlookup公式的row_index_num參數(shù)。

假設(shè)查找值在單元格B5中，表數(shù)組為b1：h3，返回行的名稱（匹配的查找值）在單元格A6中，並且行標(biāo)頭為a1：a3，完整的公式如下：

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

正如您剛剛看到的那樣，Hlookup/Vlookup＆Match的組合無疑是對(duì)常規(guī)Hlookup和Vlookup公式的改進(jìn)。但是，匹配函數(shù)並不能消除其所有局限性。特別是，Vlookup匹配公式仍然無法查看其左側(cè)，而HlookUp匹配也無法在最上方的任何行中搜索。

為了克服上述（以及其他一些）限制，請(qǐng)考慮使用索引匹配的組合，該組合提供了一種非常強(qiáng)大且通用的方式來在Excel中進(jìn)行查找，在許多方面都優(yōu)於Vlookup和Hlookup。詳細(xì)的指導(dǎo)和公式示例可以在Excel中的索引和匹配中找到 - vookup的更好替代方案。

這就是您在Excel中使用匹配公式的方式。希望本教程中討論的示例對(duì)您的工作有所幫助。我感謝您閱讀，並希望下週在我們的博客上見到您！

練習(xí)工作簿下載

excel匹配公式示例（.xlsx文件）

以上是Excel匹配功能與公式示例的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！