Excel Match函數的使用方法的8個實例，含與Sum、OffSet、Indirect組合實現提取數據和對變化區域動態跨表求

在 Excel 中，Match函數用于返回查找值在引用單元格中的相對位置。它共有三個參數，第一個參數為查找值，第二參數為查找區域，第三個參數為匹配選項；匹配選項可以設置為精確匹配和模糊匹配，其中模糊匹配需要排序。

Match函數常與Index、Sum、OffSet、Indirect函數組合使用，其中 Index + Match 或 Indirect + Match 組合都能實現返回行列對應的多個值（即提取數據），OffSet + Match 組合能返回大于等于查找值的最小值對應的某列的值，Sum + OffSet + Indirect + Match 組合實現對變化區域動態跨表求和。以下是Match函數的使用方法的8個實例。

一、Excel Match函數語法

1、表達式：MATCH(LookUp_Value, LookUp_Array, [Match_Type])

中文表達式：MATCH(查找值, 查找區域, [匹配類型])

2、說明：

（1）Match_Type 為可選項，它有三個取值，分別為 1、0 和 -1，如果省略 Match_Type，默認取 1。

A、如果 Match_Type 省略或取 1，則查找小于等查找值的最大值，并且查找區域要按升序排序，否則可能返回不正確的值。

B、如果 Match_Type 取 0，則查找第一個完全等于查找值的值，查找區域不需要排序。

C、如果 Match_Type 取 -1，則查找大于等于查找值的最小值，查找區域要按降序排序，否則可能返回不正確的值。

（2）當 Match_Type 為 0 且查找值是文本，可在查找值中使用通配符“問號 (?) 和星號 (*)”；問號表示任意一個字符，星號表示任意一個或一串字符；如果要查找問號或星號，需要在它們前加轉義字符（~）；例如：查找問題應該這樣寫：~?，查找星號應該這樣寫：~*。

（3）如果Match函數找不到值，將返回錯誤值 #N/A；另外，查找文本時，Match函數不區分大小寫。

二、Excel Match函數的使用方法及實例

（一）Match_Type 省略或取 1 的實例

1、假如要查找員工表中“姓名”為“黃子辛”的位置。雙擊 A9 單元格，把公式 =MATCH("黃子辛",A2:A8) 復制到 A9，按回車，返回錯誤值 #N/A；框選 A2:A8，選擇“數據”選項卡，單擊“升序”圖標，打開“排序提醒”窗口，保持默認選項“擴展選定區域”，單擊“確定”，則表格按“姓名”升序排列；再次雙擊 A9，按回車，返回“黃子辛”在 A2:A8 的位置 1；雙擊 A9，給公式添加第三個參數 1，按回車，也返回 1；操作過程步驟，如圖1所示：

圖1

2、公式說明：

A、公式 =MATCH("黃子辛",A2:A8) 中，“黃子辛”為查找值，A2:A8 為查找區域，它省略了參數 Match_Type，當沒有對 A 列按升序排序時，返回錯誤值 #N/A，當排序后才能返回“黃子辛”在 A2:A8 中的位置 1，當給公式加上參數 Match_Type（設置為 1）后，同樣返回 1，說明參數 Match_Type 省略或取 1 時，查找區域需要按升序排序且查找小于等于查找值的最大值。

B、另外，查找值在查找區域中的位置從選定區域開始算起，這里的選定區域從第二行開始算起，因此返回 1。

（二）Match_Type 取 0 的實例

1、同樣以查找員工表中“姓名”為“黃子辛”的位置為例。雙擊 A9 單元格，把公式 =MATCH("黃子辛",A2:A8,0) 復制到 A9，按回車，返回 3；雙擊 A9，把公式后面的 0 刪除，按回車，也返回 3；操作過程步驟，如圖2所示：

圖2

2、公式說明：

A、當參數 Match_Type 取 0 時，在不按升序排序時，仍能正確返回“黃子辛”在 A2:A8 中的位置 3，說明 Match 取 0 時，查找區域不用排序。

B、公式 =MATCH("黃子辛",A2:A8,0) 與 =MATCH("黃子辛",A2:A8,) 能返回相同的值，說明省略第三個參數 Match_Type 時，如果第二個參數后有逗號（,），Match_Type 默認取 0；如果第二個參數后沒有逗號，Match_Type 默認取 1，這一點要注意。

（三）Match_Type 取 -1 的實例

1、假如要在水果銷量表中查找銷量 2050 和 2000 的位置。雙擊 D8 單元格，把公式 =MATCH(2050,D2:D8,-1) 復制到 D8，按回車，返回錯誤值 #N/A；選中 D8，按 Delete 鍵把公式刪除；選擇“數據”選項卡，單擊“降序”圖標把表格按“銷量”降序排序；再次把公式 =MATCH(2050,D2:D8,-1) 復制到 D8，按回車，返回 2050 在 D2:D8 中的位置 3；雙擊 D8，把 2050 改為 2000，按回車，同樣返回 3；操作過程步驟，如圖3所示：

圖3

2、公式說明：

A、當 Match_Type 取 -1 時，在未對查找區域“銷量”列排序前，公式返回錯誤值 #N/A，當按“降序”排序后，能返回 2050 在 D2:D8中的正確位置 3，說明 Match_Type 取 -1，查找區域必須按降序排序；當把 2050 改為 2000 后，由于 D2:D8 中沒有 2000，所以查找大于等于 2000 的最小值，即 2050，最后返回該值的位置。

（四）查找值使用通配符問號（?）或星號（*）的實例

1、假如要查找以任意一個字或三個字開頭且以“瓜”結尾的水果名稱在 A 列中的位置。雙擊 D8 單元格，把公式 =MATCH("?瓜",A2:A7,0) 復制到 D8，按回車，返回 2；雙擊 D8，把 "?瓜" 改為 "???瓜"，按回車，返回 4；再次雙擊 D8，把 "???瓜" 改為 "*桃"，按回車，返回 5；操作過程步驟，如圖4所示：

圖4

2、公式說明：

公式 =MATCH("?瓜",A2:A7,0) 中的查找值 "?瓜" 有一個問號，它表示任意一個字，找到的是“香瓜”，因此返回位置 2；"???瓜" 表示由任意三個字開頭且以“瓜”結尾，因此找到“青皮西瓜”；"*桃" 表示以任意一個或多個字開頭且以“桃”結尾，因此找到“獼猴桃”。使用通配符除以上幾種查找方式外，還可以組合出多種查找方式，在前面的篇章中已經介紹過多次。

三、Excel Match函數的使用方法擴展實例

（一）Index + Match 組合使用實例

Index + Match 有兩種組合，一種為 =Index(, Match(,,))，另一種為 =Index(, Match(,,),Match(,,))，具體請查看《Excel Index函數的使用方法及與Match、Small、If配合返回行列對應的多個值和一對多、多對多查找》一文。

（二）Indirect + Match 組合實現返回行列對應的多個值（提取數據）實例

1、雙擊 B10 單元格，把公式 =INDIRECT("r"&MATCH($A10,$A:$A,)&"c"&COLUMN(),0) 復制到 B10，按回車，返回“漿果”；選中B10，把鼠標移到 B10 右下角的單元格填充柄上，鼠標變為十字架后，按住左鍵，往右拖，則提取出“香蕉”的所有數據；把鼠標移到 D10 右下角單元格填充柄上，按住左鍵往下拖，則提取“香瓜”的所有數據；雙擊 B13，把公式 =INDEX($A:$D,MATCH($A13,$A:$A,),MATCH(B,$A:$D,)) 復制到 B13，按回車，同樣返回“漿果”，再用往右拖和往下拖的方法提取剩余數據；操作過程步驟，如圖5所示：

圖5

2、公式說明：

（1）公式 =INDIRECT("r"&MATCH($A10,$A:$A,)&"c"&COLUMN(),0)

A、$A10 表示絕對引用列相對引用行，往右拖時，A10 不會變為 B10、C10、……；往下拖時，A10 會變為 A11、A12、……；$A:$A以數組形式返回 A1:A7 中的“名稱”。

B、MATCH($A10,$A:$A,) 用于返回 A10（香蕉）在 A1:A7 中的位置，結果為 4；COLUMN() 用于返回公式所在列列號，當公式在 B10 時，返回 2。

C、則公式變為 =INDIRECT("r"&4&"c"&2,0)，& 是連接符，在這里用于把字符（或字符串）與數字連接起來，進一步計算變為 =INDIRECT(r4c2,0)，r4c2 中 r 表示行、c 表示列，r4c2 表示第4行第2列，即 B4；參數 0 表示單元格引用被解釋為 r1b1；最后用 Indirect 返回 r4c2 的引用，即返回“漿果”。

（2）公式 =INDEX($A:$D,MATCH($A13,$A:$A,),MATCH(B,$A:$D,)) 與 =INDIRECT("r"&MATCH($A10,$A:$A,)&"c"&COLUMN(),0) 作用一樣，所不同的是 Index + Match 在公式中要引用表格的列名（如 B），而 Indirect + Match 不需引用；Index + Match 的解釋請參考上面的“Index + Match 組合使用實例”。

（三）OffSet + Match 組合使用實例

1、假如要返回大于等于查找值的最小值對應的某列的值，例如要查找價格為 3.5 或 3.8 對應的“名稱”。先把 C 列按“降序”排序（方法在上面“Match_Type 取 -1 的實例”已經介紹），雙擊 B10 單元格，把公式 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 復制到 B10，按回車，返回“獼猴桃”；雙擊 C10，把公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 復制到 C10，按回車，也返回“獼猴桃”；雙擊 A10，把 3.5 改為 3.8，單擊一下 B10（或按 Ctrl + S 保存），B10 的內容變為“香蕉”，C10 的內容變為錯誤值 #N/A；操作過程步驟，如圖6 所示：

圖6

2、公式說明：

A、=OFFSET(A1,MATCH(A10,C2:C7,-1),) 中 MATCH(A10,C2:C7,-1) 用于返回 A10（3.5）在 C2:C7 中的位置 3；則公式變為 =OFFSET(A1,3,)，然后用 OffSet 返回距 A1 三行 0 列的單元格中的值，正好是 A5 中的“獼猴桃”。

B、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 與 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 能返回相同的值僅限于查找范圍中有查找值，例如 A10 為 3.5 時，C 列中有 3.5；當把 3.5 改為 3.8，OffSet + Match 能返回正確的值，而 VLookUp + Choose 返回 #N/A，說明 OffSet + Match 能查找大于等于查找值的最小值，而 VLookUp + Choose 卻不能；另外，OffSet + Match 比 VLookUp + Choose 效率高。

C、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 在《Excel VLookUp函數的使用方法，含逆向查找、一對多查找和近似匹配實例》一文中已經有類似介紹，如果不理解可以參考。

（四）Sum + OffSet + Indirect + Match 組合實現對變化區域動態跨表求和實例

1、假如要匯總每個月的女裝和男裝的銷量，女裝銷量與男裝銷量分別在一個表，要把它們匯總到“匯總”表。當前工作表為“服裝銷量女裝”，單擊“服裝銷量男裝”切換到該表，單擊“匯總”表切換到該表；雙擊 B2 單元格，把公式 =SUM(OFFSET(INDIRECT("服裝銷量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服裝銷量"&$A2&"!$C$2:$H$2"),))) 復制到 B2，按回車，返回“4月”的“女裝”銷量匯總結果2407；選中 B2，用往右拖的方法返回“女裝”剩余月份的銷量匯總結果，再用往下拖的方法返回“男裝”各月的銷量匯總結果；操作過程步驟，如圖7所示：

圖7

2、公式 =SUM(OFFSET(INDIRECT("服裝銷量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服裝銷量"&$A2&"!$C$2:$H$2"),))) 說明：

A、"服裝銷量"&$A2&" 用于組合工作表名稱，每個工作表都以“服裝銷量”開頭，A2 中為“女裝”，因此，公式執行時，"服裝銷量"&$A2&" 變為“服裝銷量女裝”。當往下拖時， A2 變為 A3，"服裝銷量"&$A2&" 變為 "服裝銷量"&$A3&"，即“服裝銷量男裝”。

B、$B$3:$B$7 以數組形式返回 B3 至 B7 中的值，INDIRECT("服裝銷量"&$A2&"!$B$3:$B$7") 把字符轉為對單元格的引用，它返回“服裝銷量女裝!$B$3:$B$7”。

C、$C$2:$H$2 以數組形式返回 C2 至 H2 中的值，即返回 C2 至 H2 的列名；INDIRECT("服裝銷量"&$A2&"!$C$2:$H$2") 返回“服裝銷量女裝!$C$2:$H$2”。

D、MATCH(B$1,INDIRECT("服裝銷量"&$A2&"!$C$2:$H$2"),) 變為 MATCH(B$1,服裝銷量女裝!$C$2:$H$2,)，接著用 Match 返回 B1（4月）在 C2 至 H2 中位置 2。

E、則公式變為 =SUM(OFFSET(服裝銷量女裝!$B$3:$B$7,,2,)))，進一步計算用 OffSet 返回“服裝銷量女裝!$D3$3:$D$7”，是怎么返回的呢？執行到 OffSet 時，首取出 B3，然后返回與 B3 相隔兩列的單元格，即返回 D3；第二次取出 B4，返回與 B4 相隔兩列的單元格 D4；其它的以此類推，因此最后返回“服裝銷量女裝!$D3$3:$D$7”；則公式變為 =SUM(服裝銷量女裝!$D$3:$D$7)，最后用 Sum 對 D3:D7 求和，所以返回 2407。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。