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