Match函數 | 完美Excel

(1 人投票, 平均: 4.00 out of 5)

引言：本文及相關的一系列文章都是在contextures Blog的30 Excel Functions in 30 Days系列的基礎上整理而成。系統地學習這些函數確實是一項挑戰，能夠堅持下來一定會獲益匪淺。然而，這些文章都採用了很好的描述方法，將函數的用法及常用示例集中在一起，方便理解，也對實際應用很有幫助。contextures Blog也是一個非常好的Excel學習網站，作者更新很快，分享的內容實用，思路很清晰，文章很簡潔但點中實質。

MATCH函數返回指定值在數組中的位置，如果在數組中沒有找到該值則返回#N/A。數組可以已經排序或沒有排序，並且MATCH函數不區分大小寫。

什麼情況下使用MATCH函數?

MATCH函數返回指定值在數組中的位置，其結果可以被其他函數使用，例如INDEX函數或VLOOKUP函數。例如：

MATCH函數語法

MATCH函數的語法如下：

MATCH(lookup_value,lookup_array,[match_type])

MATCH陷阱

MATCH函數返回所查找項的位置，而不是值。如果需要獲取值，那麼與其他函數，如INDEX，一起使用。

示例1:在未排序的列表中查找數據項

對於未排序的列表，可以使用0作為match_type參數，以查找完全匹配的值。如果查找文本並使用0作為參數，那麼可以在查找值中包括通配符。本例中，可以輸入月名或帶有通配符的部分名字，查找列表中該月的位置。

=MATCH(D2,B3:B7,0)

可以輸入數組作為lookup_array參數來代替數組引用。如下面的公式所示，單元格D5中輸入要查找的月名，在MATCH函數的第二個參數中輸入3個月份名。如果在單元格D5中輸入的最後一個月名，例如是Oct，那麼結果將是#N/A。

=MATCH(D5,{「Jan」,」Feb」,」Mar」},0)

示例2: 將學生成績修改為字母

本例中，與CHOOSE函數一起來獲取字母成績。match_type是-1，因為成績以降序排列。當參數match_type是-1時，結果為大於或等於查找值的最小值。本例中，查找值是54，不在成績列表中，因此返回60所在的位置。因為60在成績列表中的位置是4，所以在CHOOSE函數選項中的第4個值是結果，即單元格C6，其值為D。

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

示例3: 在VLOOKUP函數中創建靈活的列選擇

要使VLOOKUP公式更靈活，可以使用MATCH函數來查找列號，而不是在公式中硬編碼。本例中，用戶可以在單元格H1中選擇區域，作為VLOOKUP函數的值。然後，可以在單元格H2中選擇月份，MATCH函數返回該月份所在的列。

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

示例4:與INDEX函數一起查找最接近的匹配項

MATCH函數也可以和INDEX函數一起使用。本例中，MATCH函數用於查找最接近正確數值的猜測。1.ABS函數返回每項猜測和正確數值的絕對差。2.MIN函數查找最小的差值。3.MATCH函數在差值列表中查找最小的差值。如果有多個相同的值，那麼返回第一個值。4.INDEX函數返回名字列表中該位置的名字。

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

MATCH函數示例

相關文章

標籤：Match函數Category: Excel函數與公式| 評論|2,204 次閱讀 在VBA中使用工作表公式查找值2011年03月31日, 8:21 上午

(1 人投票, 平均: 1.00 out of 5)

問題：

在G列中查找A列中的值，如果找到則將G列中相應行對應的H列中的值複製到該值在A列中相應行的B列中。

例如，單元格A2中的「磚基礎」在單元格G3中，則將單元格H3中的值複製到B2中。結果如下：

這樣的數據也許會有成千上萬上，如果使用VBA程序來處理，將大大節約時間以及減小出錯的機率。首先定義一個動態的名稱，以便G列中添加項目時能夠自動更新。名稱：LookupRange

=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1)

然後，編寫代碼如下：

Sub CopyData() Dim lLastRowA As Long Dim rngA As Range Dim rngValueA As Range Dim lRow As Long On Error Resume Next "列A中有數據的最後一行的行號 lLastRowA = Cells(Rows.Count, 1).End(xlUp).Row "設置需要處理的數據區域 Set rngA = Range("A2:" & "A" & lLastRowA) "遍歷需要處理的數據區域 For Each rngValueA In rngA "使用工作表函數查找數據所在的行並返回行號 lRow = Application.WorksheetFunction.Match(rngValueA, [LookupRange], 0) + 1 "如果找到則進行相應的操作 If lRow > 0 Then Range("B" & rngValueA.Row) = Range("H" & lRow) lRow = 0 End If NextEnd Sub

本來也可以使用兩個循環來解決問題，也就是在裡面再使用一個For Each循環遍歷G列中的內容，但使用工作表公式使得程序代碼更簡潔，也更快捷。

相關文章

標籤：Match函數, WorksheetFunction對象Category: VBA| 評論|1,558 次閱讀 Excel中區分大小寫的查找2009年10月17日, 9:13 上午

(3 人投票, 平均: 5.00 out of 5)

在Excel中，需要查找表中的數值時，通常使用Lookup系列的查找函數，但是這些函數不區分大小寫。例如下圖所示的查找問題，我們需要得到的結果是6，但是VLOOKUP函數給我的結果卻是5。

Microsoft Excel MVP Peo Sjoblom給出了一個解決方法，使用了數組公式：

{=INDEX(B1:B6,MATCH(1,–EXACT(A1:A6,D1),0))}

結果如下圖所示。

註解：

補充:還可以使用下面的公式:

=LOOKUP(1,1/EXACT(A1:A6,D1),B1:B6)

相關文章

標籤：EXACT函數, Index函數, Match函數, VLookup函數, 函數技巧, 數組公式Category: Excel函數與公式| 評論|3,895 次閱讀 SUMIF函數、COUNTIF函數、INDEX和MATCH函數實現條件求和及組合查找2009年07月3日, 1:33 下午

(2 人投票, 平均: 4.50 out of 5)

問題的提出如下圖1所示，在工作表的A列、B列和C列中存放著一些統計數據，即每天每間隔一小時的數據，現在要求在F列求出每天所對應數據的平均值，在G列中獲取每天12時的數據。

圖1：原始數據使用SUMIF函數和COUNTIF函數求每天所對應數據的平均值第1步：選擇單元格F2，輸入公式=SUMIF($A$2:$A$73,E2,$C$2:$C$73)/COUNTIF($A$2:$A$73,E2)。第2步：選擇單元格F2，下拉至所有單元格（或雙擊右下角的填充柄）。也可以使用名稱來簡化公式。例如，將列A中的數據命名為Date，列B中的數據命名為Time，列C中的數據命名為Data。此時的公式為：=SUMIF(Date,E2,Data)/COUNTIF(Date,E2)使用INDEX函數和MATCH函數實現查找每天12時的數據第1步：將列E中的數據命名為FindDate，在單元格I1中輸入12：00並將其命名為FindTime。第2步：選擇單元格區域G2：G4，輸入公式=INDEX(Data,MATCH(FindDate&FindTime,Date&Time,0))，並按下Ctrl+Shift+Enter組合鍵，即輸入數據公式。最終的結果如下圖2所示。

圖2：最終結果以上示例給出了具體步驟，若數據區域有變化，作相應的調整即可。示例下載：更詳細的示例下載：相關文章

標籤：CountIf函數, Index函數, Match函數, SumIf函數, 函數技巧, 工作表函數Category: Excel函數與公式| 3 評論|9,528 次閱讀 顯示滿足條件的所有數據—VLookup函數、IF函數、Row函數、Small函數、Index函數、Match函數、IFERROR函數、表結構的組合使用2009年03月20日, 1:26 下午

(11 人投票, 平均: 4.36 out of 5)

一個簡單的示例：查找Excel工作表中的重複數據記得一位網友曾問：要求找出Excel工作表中的重複數據並顯示在工作表相應的單元格中。我給出了一個數組公式供參考，但不是太符合要求，因為這個數組公式雖然找出了重複數據，但是如果將數組公式向下複製時超出了出現重複數據的數量，會在相應單元格中顯示錯誤。不久，這位朋友獲得了更好的一個公式。這個公式非常好，完美地解決了這類問題，因此，我將其轉貼於此，供有興趣的朋友參考。先看看下圖：

在列A和列B中存在一系列數據（表中只是示例，可能還有更多的數據），要求找出某人（即列A中的姓名）所對應的所有培訓記錄（即列B中的數據）。也就是說，在單元格E1中輸入某人的姓名後，下面會自動顯示這個人所有的培訓記錄。我們知道，Excel的LOOKUP系列函數能夠很方便地實現查找，但是對於查找後返回一系列的結果，這類函數無能為力，因此只能聯合其它函數來實現。這裡，在方法一中使用了INDEX函數、SMALL函數、IF函數和ROW函數，在方法二中還使用了Excel 2007中新增的IFERROR函數。方法一：

方法二：

示例文檔下載：

一個複雜的示例：查找不同工作表中的數據並顯示滿足條件的所有數據對於VLOOKUP函數來說，其主要缺點是僅能返回與查找條件相匹配的單條數據，不能夠返回與某條件相匹配的所有數據，但其優勢是能夠相當容易地獲取與所給條件相匹配的第一條數據，如下圖1所示。

圖1：使用VLOOKUP函數返回包含某顧客姓名相應的行中的數據是很容易的方式在Excel 2007中使用結構化引用，VLOOKUP函數不僅容易使用，而且可讀性也很強。在示例中，將表Table1中第一列的值與單元格A9中的值相匹配，並從表中第3列返回值。但是，不能返回表中第二個與「Dan」相匹配的值。事實上，很多時候我們都會碰到這樣的情況，我們想要返回與條件相匹配的所有值，但是VLOOKUP函數只能返回滿足條件的第一個值。下面，讓我們看看如何從表中獲取滿足條件的所有數據。首先，準備一些要操作的數據。我們以Office自帶的Northwind示例資料庫的一部分數據來演示，將其中的部分數據導入Excel表中，如下圖2、圖3、圖4所示。

圖2：導入到工作表Customers中的「Customer」表並命名為「tblCustomers」

圖3：導入到工作表Orders中的「Orders」表並命名為「tblOrders」

圖4：導入到工作表Details中的「OrderDetails」表並命名為「tblDetails」當然，上述數據都不需要自已手工輸入，只需從Northwind資料庫中導入即可。現在，希望選擇訂單號（Order Number）後，能夠顯示該訂單的詳細信息，如下圖5所示。

圖5：當選擇某訂單號後，會顯示該訂單的詳細信息下面是圖5顯示的界面的主要設計過程。步驟1 在工作表相應的單元格中輸入下列字元：單元格B1：Order Number單元格C3：Order Information單元格C4：Customer單元格F4：Order Date單元格F5：Status單元格F6：Salesperson單元格F7：Ship Date單元格C10：Order Details單元格C11：Product單元格D11：Quantity單元格E11：Unit Price單元格F11：Discount單元格G11：Total Price單元格H11：Status ID步驟2 創建包含所有訂單IDs的一個命名區域，然後使用該名稱在數據有效性中創建訂單號的下拉列表。為此，單擊「公式—定義名稱」，在「新建名稱」對話框中輸入：名稱：OrderIds引用位置：=tblOrders[ID]註：數據有效性不能夠引用不同工作表中的單元格區域，除非為該區域定義名稱。步驟3 選擇單元格D1，將其命名為rngOrderId。步驟4 選擇單元格D1，單擊「數據—數據有效性」，在「允許」中選擇「序列」，在「來源」框中輸入「=OrderIds」。步驟5 選擇單元格C5，輸入下列函數：=VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#標題], 0), FALSE)步驟6 與單元格C5中的函數相似，設置剩餘單元格的查找函數。C6：=VLOOKUP($C$5,tblCustomers,MATCH(「Address」,tblCustomers[#標題],0), FALSE)C7：=VLOOKUP($C$5, tblCustomers, MATCH(「City」,tblCustomers[#標題],0), FALSE) & 「, 」 & VLOOKUP($C$5, tblCustomers, MATCH(「State」,tblCustomers[#標題],0), FALSE) & 」 」 & VLOOKUP($C$5, tblCustomers, MATCH(「Zip」,tblCustomers[#標題],0), FALSE)H4：=VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#標題], 0), FALSE)H5：=VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#標題], 0), FALSE)H6：=VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#標題], 0), FALSE)H7：=VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#標題], 0), FALSE)至此，完成了我們的界面的上半部分，如圖6所示。

圖6：已完成的查找界面接下來，讓我們看看後面將要使用的公式中的一些函數。

好了，先讓我們來看看已經完成的函數，如下圖7中的單元格C12所示（注意，這是一個數組公式，其左右兩側的花括弧不需要自已輸入。在輸完公式後按下Ctrl+Shift+Enter組合鍵即可）。

圖7：數組公式用於返回與特定值相匹配的第N項這個公式看起來很複雜，為便於閱讀，將其書寫如下：{=IFERROR(INDEX(tblDetails,SMALL(IF(tblDetails[Order ID]=rngOrderId,ROW(tblDetails[Order ID])-ROW(tblDetails[#標題])),ROW(1:1)),MATCH(C$11, tblDetails[#標題], 0)),「」)}首先，看看SMALL函數，該函數接受一組值並從中返回第N小的值。SMALL(IF(tblDetails[Order ID]=rngOrderId,ROW(tblDetails[Order ID])-ROW(tblDetails[#標題])),ROW(1:1)),本例中，數組的值由IF函數確定。特別地，如果Details表中某行的Order ID與在下拉列表中選擇的Order ID相等，那麼添加該行的行號到數組值中。通過使用ROW函數，Excel能夠自動調整公式來向下填充單元格。並且，通過使用結構化引用，使得工作表數據能夠自動適應源數據的變化。然後，再看看INDEX函數，該函數接受一個二維數據並返回數組中指定行列位置的值。INDEX(tblDetails,SMALL(IF(tblDetails[Order ID]=rngOrderId,ROW(tblDetails[Order ID])-ROW(tblDetails[#標題])),ROW(1:1)),MATCH(C$11, tblDetails[#標題], 0)),我們已經知道，本例中的SMALL函數返回與輸入的Order ID相匹配的Order Details列表中ROW(Nth)的值，這裡是第一個值即行號。接著需要獲取列號，即使用MATCH函數，本例中該函數接受C11的值並查找在Details表中有相同名稱的列。最後使用的是IFERROR函數，如果包含的計算式發生錯誤，使用空字元串（」」）替換錯誤值。現在，選擇單元格C12，拖動其右下角的填充柄至單元格H12。選擇單元格區域C12：H12，拖動填充句柄向下10行或更多。現在，將顯示滿足該訂單號的所有信息，並且當我們改變Order ID時，將會自動更新相關信息。

圖8：完成的界面表，使用數組公式向下填充以獲取所有相關信息示例文檔下載：相關文章

標籤：IFERROR函數, IF函數, Index函數, Match函數, Row函數, Small函數, VLookup函數, 表Category: Excel函數與公式| 11 評論|27,021 次閱讀

推薦閱讀：