|
跟李銳學(xué)Excel， 高效工作，快樂生活。 史上最全 MATCH函數(shù) 應(yīng)用教程及案例解析 MATCH函數(shù)是Excel中廣泛應(yīng)用的查找引用函數(shù)，除自身具有返回查找數(shù)據(jù)的相對(duì)位置的功能外，MATCH函數(shù)還能結(jié)合眾多的函數(shù)，在工作中展現(xiàn)Excel的強(qiáng)大威力，比如著名的INDEX+MATCH組合就能輕松搞定很多VLOOKUP的高級(jí)應(yīng)用案例，可見MATCH函數(shù)無疑屬于職場(chǎng)辦公必備函數(shù)。 為了讓大家認(rèn)識(shí)MATCH函數(shù)那些不為人知的強(qiáng)大功能，本文貼合辦公實(shí)際場(chǎng)景，整理了多種MATCH函數(shù)的應(yīng)用方法，除了原理和基礎(chǔ)性講解外，還提供了使用場(chǎng)景介紹，幫助讀者加深理解，便于在自己的實(shí)際工作中直接借鑒和使用。 由于正文字?jǐn)?shù)限制，本教程給出Excel案例和公式解法，對(duì)公式的原理解析和詳細(xì)說明請(qǐng)點(diǎn)擊本文底部的“閱讀原文”獲取。 適用對(duì)象：本文面向的讀者包括所有需要用到查找引用數(shù)據(jù)的用戶，無論是初入職場(chǎng)的應(yīng)屆畢生生，還是在職場(chǎng)拼殺多年的白領(lǐng)精英，都將從本文找到值得學(xué)習(xí)的內(nèi)容。 軟件版本：本文的寫作環(huán)境是Window10家庭版操作系統(tǒng)上的簡(jiǎn)體中文版Excel 2013。 本文絕大多數(shù)內(nèi)容也適用于Excel的早期版本（2010、2007和2003），或者英文版和繁體中文版，所以讀者大不必因自用版本不同而過多擔(dān)心。 本文學(xué)習(xí)要點(diǎn)（強(qiáng)烈推薦收藏本教程） 1、MATCH函數(shù)語法解析及基礎(chǔ)用法 2、MATCH函數(shù)根據(jù)模糊條件查找 3、MATCH函數(shù)查找特殊符號(hào)的方法 4、MATCH函數(shù)提取最后一個(gè)文本數(shù)據(jù)的行號(hào) 5、MATCH函數(shù)按條件提取最后一個(gè)數(shù)據(jù) 6、MATCH函數(shù)統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù) 7、MATCH函數(shù)統(tǒng)計(jì)兩列數(shù)據(jù)的相同個(gè)數(shù) 8、MATCH函數(shù)按多條件計(jì)數(shù)統(tǒng)計(jì) 9、MATCH函數(shù)根據(jù)用餐時(shí)間自動(dòng)判斷餐別 10、MATCH函數(shù)按條件多權(quán)重排序 01MATCH函數(shù)語法解析及基礎(chǔ)用法 MATCH用于返回要查找的數(shù)據(jù)在區(qū)域中的相對(duì)位置。下面介紹她的語法和參數(shù)用法。 語法 MATCH(lookup_value,lookup_array, [match_type]) 用通俗易懂的方式可以表示為 MATCH(要查找的數(shù)據(jù), 查找區(qū)域, 查找方式) MATCH 函數(shù)語法具有下列參數(shù)： 第一參數(shù)：要在lookup_array中匹配的值。例如，如果要在電話簿中查找某人的電話號(hào)碼，則應(yīng)該將姓名作為查找值，但實(shí)際上需要的是電話號(hào)碼。 第一參數(shù)可以為值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。 第二參數(shù)：要搜索的單元格區(qū)域。 第三參數(shù)：可選。數(shù)字 -1、0 或 1。match_type參數(shù)指定 Excel 如何將lookup_value與lookup_array中的值匹配。此參數(shù)的默認(rèn)值為 1。 下表介紹該函數(shù)如何根據(jù) match_type參數(shù)的設(shè)置查找值。 對(duì)于非高級(jí)用戶可以略過這部分直接看后面的示例，因?yàn)?9%的情況下，第三參數(shù)只用0就足以應(yīng)付日常工作需求啦！
MATCH函數(shù)返回匹配值在第二參數(shù)中的位置，而非其值本身。例如，MATCH('b',{'a','b','c'},0)返回 2，即“b”在數(shù)組 {'a','b','c'} 中的相對(duì)位置。 匹配文本值時(shí)，MATCH 函數(shù)不區(qū)分大小寫字母。 如果 MATCH函數(shù)查找匹配項(xiàng)不成功，它會(huì)返回錯(cuò)誤值 #N/A。 看了這么多文字，很多同學(xué)是不是有點(diǎn)暈？ 上兩個(gè)簡(jiǎn)單易學(xué)的示例，讓你秒懂MATCH函數(shù)~ 先來看個(gè)縱向查找定位的案例吧 C2輸入以下公式。 =MATCH('王紅強(qiáng)',A:A,0) 第一參數(shù)：要查找的數(shù)據(jù)，這里是“王紅強(qiáng)” 第二參數(shù)：在哪里查找，這里是在A列查找，所以寫A:A 第三參數(shù)：按完全匹配查找，寫0 連起來就是在A列中查找和“王紅強(qiáng)”完全匹配的數(shù)據(jù)，并返回其位置。 公式結(jié)果為8，表示找到數(shù)據(jù)并且數(shù)據(jù)在第8行。 明白了縱向查找，再來看個(gè)橫向查找定位的案例 C2輸入以下公式。 =MATCH('梨',1:1,0) 第一參數(shù)：要查找的數(shù)據(jù)，這里是“梨” 第二參數(shù)：在哪里查找，這里是在第一行中查找，所以寫1:1 第三參數(shù)：按完全匹配查找，寫0 連起來就是在第一行中查找和“梨”完全匹配的數(shù)據(jù)，并返回其位置。 公式結(jié)果為4，表示找到數(shù)據(jù)并且數(shù)據(jù)在第4列，即D列。 是不是很簡(jiǎn)單，現(xiàn)在你已經(jīng)學(xué)會(huì)了MATCH函數(shù)最基礎(chǔ)的應(yīng)用啦 先休息一下吧，后續(xù)還有更精彩的應(yīng)用案例等著你~ 02MATCH函數(shù)根據(jù)模糊條件查找 上一節(jié)中咱們學(xué)習(xí)了MATCH函數(shù)最基礎(chǔ)的用法（按條件完全匹配查詢），但在工作中很多時(shí)候會(huì)遇到查詢條件并不那么明確，只能根據(jù)部分已知條件模糊查詢。 下面就結(jié)合一個(gè)案例，展示MATCH函數(shù)根據(jù)模糊條件查找的功能。 上圖中A列是各年份的產(chǎn)品批號(hào)，包含2016和2017年，現(xiàn)在要查詢2017年的批號(hào)從哪行開始出現(xiàn)。 在C2單元格輸入以下公式。 =MATCH('*2017*',A:A,0) 03MATCH函數(shù)查找特殊符號(hào)的方法 上一節(jié)教程中，我們學(xué)習(xí)了MATCH函數(shù)按照模糊條件查詢的方法，但其只適用于普通字符的字符串，當(dāng)要查找的數(shù)據(jù)包含一些特殊字符（比如星號(hào)*問號(hào)?波浪符~）時(shí)，原公式結(jié)果就會(huì)出錯(cuò)了。 那么，遇到這幾種特殊符號(hào)，我們?nèi)绾螒?yīng)對(duì)呢？ 下面就結(jié)合一個(gè)實(shí)際案例進(jìn)行講解。 上圖中A列是數(shù)據(jù)源區(qū)域，放置了很多激活碼，由數(shù)字和符號(hào)構(gòu)成，現(xiàn)在需要查找激活碼“*5~?”的位置。 如果使用常規(guī)的查找方法，比如以下公式，會(huì)返回錯(cuò)誤結(jié)果。 錯(cuò)誤公式：=MATCH('*5~?',A:A,0) 那么，應(yīng)該如何修正這個(gè)公式，才能得到正確結(jié)果呢？ 在C2單元格輸入以下公式。 正確公式：=MATCH('~*5~~~?',A:A,0) 04MATCH函數(shù)提取最后一個(gè)文本數(shù)據(jù)的行號(hào) 之前幾節(jié)的學(xué)習(xí)中，我們掌握了MATCH的基本查找方法，根據(jù)模糊條件查找的方法以及查找內(nèi)容包含特殊符號(hào)的處理方法。 今天，咱們一起來看一個(gè)逆向查找提取文本數(shù)據(jù)位置的案例。 上圖中A:B列是數(shù)據(jù)源，放置著員工編號(hào)和業(yè)務(wù)員姓名，要提取B列最后一個(gè)文本所在行號(hào)。 在D2輸入以下公式。 =MATCH(CHAR(1),B:B,-1) 05MATCH函數(shù)按條件提取最后一個(gè)數(shù)據(jù) 之前幾節(jié)教程中，我們掌握了MATCH函數(shù)的各種查找方法，還學(xué)到了逆向提取數(shù)據(jù)位置的處理方法，今天再來學(xué)一招條件查找下的MATCH技術(shù)。 老規(guī)矩，先上案例，對(duì)照講解。 上圖中左邊是數(shù)據(jù)源，包含業(yè)務(wù)員的編號(hào)、姓名和銷售額，現(xiàn)在要查找最后一個(gè)銷售額大于50000的業(yè)務(wù)員所在行號(hào)。 在E2輸入以下數(shù)組公式，按 =MATCH(1,0/(C:C>50000)) 06 MATCH函數(shù)統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù) 在之前的教程里，我們介紹的都是單獨(dú)應(yīng)用MATCH函數(shù)的方法，其實(shí)MATCH函數(shù)跟其他函數(shù)配合使用，可以產(chǎn)生更大威力。 今天咱們先來介紹一個(gè)統(tǒng)計(jì)不重復(fù)值個(gè)數(shù)的方法。 上案例，看下圖 在上圖中左側(cè)是數(shù)據(jù)源，包含值班日期和值班人員的姓名，其中值班人員有重復(fù)，即有的人值班過多天，現(xiàn)在要計(jì)算參與值班的人數(shù)（排重后）。 在D2輸入數(shù)組公式，按 =COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(1:7))) 07MATCH函數(shù)統(tǒng)計(jì)兩列數(shù)據(jù)的相同個(gè)數(shù) 曾經(jīng)有人問過我一個(gè)問題： Excel中哪個(gè)函數(shù)的威力最大？ Excel中的什么功能最強(qiáng)大？ 不知道你遇到這種提問會(huì)如何回答，我除了介紹了一些Excel的強(qiáng)大功能（可以去我的知了問答獲取答案）外，還告訴他，關(guān)鍵在于Excel在誰的手里使用，其實(shí)沒有最牛的功能，只有最牛的人！ 有時(shí)候，看似很簡(jiǎn)單的函數(shù)，只要能靈活運(yùn)用，平凡的函數(shù)也能用出花來。 這節(jié)課程里，咱們就來看一個(gè)比對(duì)兩列數(shù)據(jù)差異的案例，出場(chǎng)的兩位都是大家耳熟能詳?shù)暮瘮?shù)，一個(gè)是COUNT，一個(gè)是MATCH，但別小看他倆，不信你先看看這個(gè)題目自己能否搞的定吧。 上圖中左側(cè)是數(shù)據(jù)源，包括某班級(jí)兩門考試前10名學(xué)生的數(shù)學(xué)成績(jī)和語文成績(jī)，因?yàn)槊靠瞥煽?jī)單獨(dú)排名，所以數(shù)學(xué)的前10名與語文的前10名學(xué)生并不完全一致。 現(xiàn)在要做的是，對(duì)比兩個(gè)科目的前10名學(xué)生，找出相同的個(gè)數(shù)。 先給出公式，下面再分步詳細(xì)解析。 在E2單元格輸入數(shù)組公式，按 =COUNT(MATCH(B2:B11,C2:C11,0)) 08MATCH函數(shù)按多條件計(jì)數(shù)統(tǒng)計(jì) 上一節(jié)中，我們講解了一個(gè)MATCH函數(shù)配合COUNT函數(shù)，輕松對(duì)兩列數(shù)據(jù)進(jìn)行比對(duì)并且統(tǒng)計(jì)其中相同值個(gè)數(shù)的案例。 這一節(jié)中，我們?cè)賮斫榻B一個(gè)這兩個(gè)函數(shù)搭配上場(chǎng)的精彩應(yīng)用。 多條件計(jì)數(shù)的需求在工作中經(jīng)常遇到，處理方式很多，下面就來看這個(gè)案例中MATCH是如何搞定多條件統(tǒng)計(jì)的。 上圖中左側(cè)是數(shù)據(jù)源區(qū)域，包含出庫日期、出庫產(chǎn)品以及出庫的經(jīng)銷商名稱，現(xiàn)在需要統(tǒng)計(jì)其中兩個(gè)經(jīng)銷商（大地集團(tuán)和希望集團(tuán)）的蘋果的出庫次數(shù)之和。 老規(guī)矩，先給出公式，再解析原理。 在E2單元格輸入數(shù)組公式，按 =COUNT(MATCH(B2:B8&C2:C8,'蘋果'&{'大地集團(tuán)','希望集團(tuán)'},0)) 09MATCH函數(shù)根據(jù)用餐時(shí)間自動(dòng)判斷餐別 今天，我們結(jié)合一個(gè)實(shí)際案例，介紹一下靈活應(yīng)用函數(shù)的思路和精彩。 上圖中左側(cè)是數(shù)據(jù)源，黃色區(qū)域是需要輸入公式計(jì)算的單元格。 數(shù)據(jù)源中已知用餐日期和時(shí)間，需要根據(jù)用餐時(shí)間和規(guī)則自動(dòng)判斷餐別。 規(guī)則如下： 還是老規(guī)矩，先給出公式，在分步介紹原理及解析。 在C2輸入以下公式，將公式向右、向下拖拉填充到黃色區(qū)域。 =IF(MATCH($B2*48,{11;23;35})=COLUMN(A1),'√','') 10 MATCH函數(shù)按條件多權(quán)重排序 從前面九節(jié)課的學(xué)習(xí)中，我們已經(jīng)知道，MATCH函數(shù)不但可以提取數(shù)據(jù)位置，而且可以按照條件查詢，比對(duì)兩列數(shù)據(jù)的差異，今天再來結(jié)合一個(gè)案例，展現(xiàn)MATCH函數(shù)按不同權(quán)重多條件排序的技術(shù)。 上圖中左側(cè)是數(shù)據(jù)源區(qū)域，包含各個(gè)代表隊(duì)在某次賽事中取得的金牌、銀牌、銅牌數(shù)量，現(xiàn)在需要根據(jù)各個(gè)代表隊(duì)的獎(jiǎng)牌數(shù)量計(jì)算其總名次。 名次的排名規(guī)則如下： 1、首先按金牌數(shù)量降序排列。 2、金牌數(shù)量一致的代表隊(duì)，比較其銀牌數(shù)量。 3、金牌和銀牌數(shù)量都一致時(shí)，比較銅牌數(shù)量。 其實(shí)這就是一個(gè)分權(quán)重的多條件排序問題。 老規(guī)矩，先給出公式，再解析原理。 選定E2:E8單元格區(qū)域，輸入?yún)^(qū)域單元格數(shù)組公式，按 =MATCH(MMULT(B2:D8*10^{4,2,0},{1;1;1}),LARGE(MMULT(B2:D8*10^{4,2,0},{1;1;1}),ROW(1:7)),0) 李 銳 微軟全球最有價(jià)值專家MVP 新浪微博Excel垂直領(lǐng)域第一簽約自媒體 百度名家，百度閱讀認(rèn)證作者 每日分享職場(chǎng)辦公技巧教程 高效工作，快樂生活！ 微博 @Excel_函數(shù)與公式 |
|
|
