MATCH:函數(shù)哲學(xué)家,找巨人做伴。新出道必學(xué)!
?
作者:壹仟伍佰萬、小雅來源:部落窩教育發(fā)布時間:2019-01-30 21:54:44點擊:9904
今天是函數(shù)課堂第2課,學(xué)習(xí)MATCH函數(shù)。MATCH函數(shù)新人必學(xué),為什么?一個是因為大名鼎鼎的VLOOKUP+MATCH是工作中的常客,另一個是因為MATCH的人生哲學(xué)“自己弱那就找個巨人做伙伴”值得借鑒!
上期給大家介紹了Excel三大家族之一的邏輯家族族長——IF函數(shù)。今天我們來聊一聊另外一個家族——查找家族!說起查找家族,相信大家首先想到的就是VLOOKUP函數(shù)。這位大哥打拼沙場多年,早已名揚萬里!相信接觸過Excel的小伙伴都多少略有耳聞。然而今天我們要說的是它身邊得力小弟——MATCH函數(shù)!
這個小弟呀,雖然單拎出來能力不咋的,但是跟著大哥組隊,那效果可是杠杠的!
我們先來認(rèn)識認(rèn)識他吧
一、MATCH是誰?
MATCH是查找定位函數(shù),它返回的并不是數(shù)據(jù)本身,而是該數(shù)據(jù)在單列或單行中的位置。類似于排隊報數(shù),站在第幾個,MATCH就報幾號!(注意:它只支持單列或單行數(shù)據(jù)查找哦~)
函數(shù)結(jié)構(gòu):MATCH(找啥,在哪兒找[單行或單列],查找類型)
查找類型:3種。分別用0、1、-1代表。0表示精確查找,1表示升序查找,-1表示降序查找。
二、MATCH基本用法
1.精確查找
舉個栗子
我們想知道“張三”在“姓名”這列區(qū)域排第幾位。
公式:
=MATCH(B3,B$2:B$8,0)
公式解析:
找啥:找“張三”,所以是B3單元格
在哪兒找:在姓名列B2:B8中找。為了防止向下填充公式姓名列區(qū)域變動,需要用$將它固定住,即B$2:B$8。
查找類型:0表示精確查找。精確查找不需要排序。
2.升序查找
升序查找就是查找小于等于查找值的最大值然后返回其所在位置。要求數(shù)據(jù)必須升序排列。
同樣舉個栗子
我們想知道不大于60的有幾個。
首先對成績按升序進行排列。
然后在D3中輸入公式:=MATCH(60,B20:B40,1)
確定后就得到了人數(shù)8人。很顯然升序排列后,返回的是小于等于60的最后一個數(shù)值的位置數(shù);也可以理解為統(tǒng)計了凡是不大于60的包括所有等于60的數(shù)值的個數(shù)。
3.降序查找
降序查找就是查找大于等于查找值的最小值然后返回其所在位置。要求必須降序排列。
同樣舉個栗子
我們想知道不小于60的有幾個。接著上面的,首先降序排列數(shù)據(jù)。
然后在E3中輸入公式:=MATCH(60,B20:B40,-1)
確定后得到不小于60的有14人。
得到兩個很顯然的結(jié)果:
(1)降序后,升序查找就出錯了。所以升序查找就必須升序排列;反過來,降序查找就必須降序排列。
(2)降序查找,返回的是大于60中的最小數(shù)或者第一個等于60的數(shù)的位置數(shù);也可以理解為統(tǒng)計了所有大于60的包括第一個等于60的數(shù)值的個數(shù)。這一點與升序篩選不同:如果存在與查找值相同的數(shù)值,升序定位到等于查找值的最后一個數(shù)值,而降序定位到等于查找值的第一個數(shù)值。
明白了MATCH是誰和基本用法,估計大家都會認(rèn)為MATCH有點雞肋:就用來返回位置數(shù),跟我想要查找具體的值相差很遠呀。
正因為這樣,日常工作中MATCH函數(shù)單獨出場幾乎么有。MATCH并不氣餒,為了贏得自己在函數(shù)界的一席之地,它采用了一項有效策略——同巨人結(jié)伴共舞!因此有了大名鼎鼎的VLOOKUP+MATCH組合、INDEX+MATCH組合。
三、與巨人共舞
1. VLOOKUP+MATCH組合
下面是一張成績明細(xì)表,我們需要找到“元菁米、王慧、廉楓、余邁”這幾個人的總分、平均分和等級。
如果單用VLOOKUP函數(shù),我們需要頻繁的修改第三參數(shù)。當(dāng)查總分的時候,在P2單元格輸入公式:
=VLOOKUP(O2,A2:M142,11,0)
而要查平均分的時候,就需要修改第三參數(shù)為12,公式變?yōu)椋?/span>
=VLOOKUP(O2,A2:M142,12,0)
如此使用很麻煩,那怎么能省事呢?
MACTH抓緊機會向VLOOKUP推薦了自己,用自己查詢“總分”“平均分”“等級”在A1:M1行中的位置數(shù)取代第三參數(shù),就可以不需要手動修改。這時查找總分的公式就變成:
=VLOOKUP($O2,$A$2:$M$142,MATCH(P$1,$A$1:$M$1,0),0)
然后右拉填充再下拉填充公式就完成了查詢。如下:
可能有伙伴看到過我們前面的文章《會用Column嗎?它讓公式不那么笨?!?,說用COLUMU取代第三參數(shù)更簡單:
=VLOOKUP($O2,$A$2:$M$142,COLUMN(K1),0)
說得沒錯,當(dāng)前查詢值連續(xù)排列的,并且排列順序與成績明細(xì)排列一致,用COLUMN更簡單。如果是按下面的兩個表查詢呢?
很顯然COLUMN就不適合了,但MATCH完全勝任。
2. INDEX+MATCH組合
還是查成績,如下:
我們單用INDEX查詢成績的話,何叢良的數(shù)學(xué)成績查詢公式:=INDEX(A2:D9,5,2),物理成績查詢公式:= INDEX(A2:D9,5,4)。
INDEX查詢就是以指定的查詢區(qū)域為坐標(biāo)系,通過行坐標(biāo)和列坐標(biāo)查詢所需數(shù)值。何叢良成績的查詢區(qū)域是A2:D9,數(shù)學(xué)成績位于第5行和第2列的交叉點上,所以公式就是INDEX(A2:D9,5,2)。物理成績位于第5行第4列的交叉點上,所以公式是INDEX(A2:D9,5,4)。
通過這樣輸入行數(shù)、列數(shù)的方式查詢太笨拙,不實用。因此MATCH又見縫插針地向INDEX推薦了自己。MATCH可以根據(jù)條件查出定位值,取代人工輸入行數(shù)、列數(shù)。成績查詢公式變成:
=INDEX($A$2:$D$9,MATCH($F3,$A$2:$A$9,0),MATCH(G$2,$A$2:$D$2,0))
當(dāng)然這里也可以用VLOOKUP+MATCH組合,公式:
=VLOOKUP($F3,$A$2:$D$9,MATCH(G$2,$A$2:$D$2,0),0)
那VLOOKUP+MATCH組合與INDEX+MATCH組合有何區(qū)別呢?這個問題在后續(xù)的函數(shù)課堂中我們將談到。有興趣的伙伴可以自己先琢磨琢磨。
怎么樣,大哥和小弟的組合是不是很棒呢?所以說MATCH函數(shù)不愧為函數(shù)中的哲學(xué)家!今天的函數(shù)課就到此為止,我們下期再見。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
部落窩函數(shù)課堂1《IF函數(shù):剝洋蔥》
IF函數(shù)與CHOOSE函數(shù)的區(qū)別《 Excel大擂臺:CHOOSE函數(shù)巔峰對決IF函數(shù),你投誰的票》
VLOOKUP函數(shù)應(yīng)用實例《Vlookup快速核對人員的薪資變動》
讓公式更靈活的COLUMN函數(shù)《會用Column嗎? 它讓公式不那么笨》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!