比較XLOOKUP函數(shù)與VLOOKUP函數(shù)用法
1、XLOOKUP基礎(chǔ)語(yǔ)法
在學(xué)習(xí)任何一個(gè)函數(shù)之前,需要了解這個(gè)函數(shù)的基礎(chǔ)語(yǔ)法,從微軟官方的幫助文檔里找到了這個(gè)函數(shù)的參數(shù)語(yǔ)法,共計(jì)有5個(gè)參數(shù),跟LOOKUP的參數(shù)非常接近,但是使用起來(lái)會(huì)更加簡(jiǎn)單了一些。
其中第1~3個(gè)參數(shù)跟LOOKUP的參數(shù)非常接近,都是將「查找區(qū)域」和「結(jié)果區(qū)域」全部獨(dú)立出來(lái)了,跟VLOOKUP的「選擇區(qū)域」就有所差異,拆分出來(lái)會(huì)讓函數(shù)更加靈活。
第4個(gè)參數(shù)match_mode表示匹配類型,可以使用「精確匹配」「通配符匹配」「2種近似匹配」,默認(rèn)為0表示精確匹配。
第5個(gè)參數(shù)search_mode是新增的一個(gè)參數(shù),表示搜索模式,在傳統(tǒng)的VLOOKUP/LOOKUP系列函數(shù)中,搜索只能從上往下,并且尋找第2個(gè)、最后1個(gè)數(shù)值的時(shí)候會(huì)非常麻煩。而XLOOKUP就直接引進(jìn)了這個(gè)參數(shù),默認(rèn)為1表示從上往下開始搜索,-1表示從下往上搜索,這2個(gè)會(huì)用的比較多一些。
至此,XLOOKUP的語(yǔ)法就初步了解了,接下來(lái)來(lái)實(shí)操下這個(gè)XLOOKUP函數(shù)有多強(qiáng)大!
2、基礎(chǔ)縱向查詢
例如下圖查找“工號(hào)的電腦銷售額”,在基礎(chǔ)操作上VLOOKUP和XLOOKUP沒有什么太大的差異,寫法都非常簡(jiǎn)單,只是XLOOKUP將選擇區(qū)域和返回區(qū)域拆分出來(lái)單獨(dú)寫了而已。
來(lái)總結(jié)下XLOOKUP基礎(chǔ)縱向查詢的套路:
結(jié)論:雙方平均(VLOOKUP公式會(huì)簡(jiǎn)潔一點(diǎn)點(diǎn),不過差異不大)
3、基礎(chǔ)橫向查詢
既然能縱向查詢,那么橫向查詢其實(shí)也是可以的。然而在VLOOKUP中,如果要實(shí)現(xiàn)橫向查詢會(huì)非常麻煩,又是需要構(gòu)建虛擬數(shù)組。在這里我們使用INDEX+MATCH來(lái)代替VLOOKUP實(shí)現(xiàn)「基礎(chǔ)橫向查詢」。
而使用XLOOKUP就非常簡(jiǎn)單了,所有操作都跟縱向查詢沒有任何差異,XLOOKUP會(huì)自動(dòng)識(shí)別是什么方向,例如將上面的數(shù)據(jù)橫放了,變成這個(gè)樣子:
在橫向查詢中XLOOKUP完爆VLOOKUP,XLOOKUP的兩個(gè)方向查詢用法一模一樣,沒有任何差異,太智能了。
來(lái)總結(jié)下XLOOKUP橫向查詢的公式套路:
結(jié)論:XLOOKUP完爆VLOOKUP,因?yàn)閂LOOKUP實(shí)現(xiàn)非常麻煩。
4、反向匹配查詢
在前面的語(yǔ)法中,我們說(shuō)到XLOOKUP將「查找區(qū)域」和「結(jié)果區(qū)域」全部獨(dú)立出來(lái)了,所以在這里的話,反向匹配對(duì)于XLOOKUP來(lái)說(shuō)沒有什么障礙,相反VLOOKUP就需要去構(gòu)建一個(gè)IF虛擬數(shù)組來(lái)實(shí)現(xiàn)了。
來(lái)感受下2個(gè)函數(shù)的用法:
VLOOKUP是通過IF({1,0},XXX,XXX)的方式構(gòu)建一個(gè)虛擬數(shù)組來(lái)實(shí)現(xiàn)這個(gè)功能的,因?yàn)闄z索關(guān)鍵字必須在選擇區(qū)域的第一列,對(duì)于新手來(lái)說(shuō)理解非常不友好,而且復(fù)雜的數(shù)組公式還會(huì)消耗大量的計(jì)算機(jī)資源,XLOOKUP依然是這么牛逼完爆,格式?jīng)]有什么變化,輕松完成反向查詢:
結(jié)論:XLOOKUP完爆VLOOKUP,基礎(chǔ)語(yǔ)法即可實(shí)現(xiàn)。
5、多條件查找
多條件查找是很多VLOOKUP的初學(xué)者的噩夢(mèng),需要寫非常復(fù)雜的IF數(shù)組公式,而且又要注意定位引用的方式,而XLOOKUP使用起來(lái)就非常舒服了,只需要將多個(gè)條件利用&符號(hào)拼接起來(lái)就ok。
徹底告別復(fù)雜公式:
相信很多同學(xué)看到了VLOOKUP多條件查詢都是一臉懵逼,IF到底是什么鬼,怎么老是出現(xiàn)?其實(shí)這里還是構(gòu)建了一個(gè)虛擬數(shù)組,數(shù)組公式對(duì)于新手來(lái)說(shuō)理解起來(lái)的確太困難。
將絕對(duì)定位去掉,我們來(lái)看下XLOOKUP公式究竟有多簡(jiǎn)潔,絲毫不拖泥帶水:
=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)
總結(jié)XLOOKUP多條件查詢公式套路:
結(jié)論:XLOOKUP再次完爆VLOOKUP,簡(jiǎn)潔速度快!
6、模糊查詢匹配
VLOOKUP和XLOOKUP均支持模糊匹配,在Excel中使用模糊匹配需要用到通配符(*、?、~),這次兩個(gè)函數(shù)不相上下,XLOOKUP只需要將第4個(gè)參數(shù)修改成2表示通配符匹配即可。
兩個(gè)函數(shù)不相上下,因?yàn)檫@個(gè)功能比較簡(jiǎn)單,總結(jié)下XLOOKUP的模糊查詢公式套路:
結(jié)論:XLOOKUP和VLOOKUP不相上下,因?yàn)槎急容^基礎(chǔ)
7、匹配最后一個(gè)值
在某些情況下,我們需要找到記錄里的最后一條數(shù)據(jù),而恰好XLOOKUP的最后一個(gè)參數(shù)是搜索模式,只要我們將第5個(gè)參數(shù)search_mode修改成-1,就會(huì)倒序查找,這樣就能找到最后一個(gè)數(shù)值了,非常簡(jiǎn)單。
而VLOOKUP本身實(shí)現(xiàn)匹配最后一個(gè)值非常麻煩,這里我們使用LOOKUP來(lái)代替:
LOOKUP的寫法就非常難理解了,又是用0除,又是做邏輯符號(hào)判斷等于的。對(duì)于新手實(shí)在太不友好,相比之下XLOOKUP的寫法就非常簡(jiǎn)單了。直接將搜索模式一改就ok了,這個(gè)功能在人事應(yīng)用中非常廣,找到最后一次打卡時(shí)間和第一次打卡時(shí)間,非常簡(jiǎn)單。
套路總結(jié):
結(jié)論:XLOOKUP完爆VLOOKUP/LOOKUP,寫法簡(jiǎn)潔,計(jì)算快
8、查找多個(gè)值
查找多個(gè)值無(wú)論是利用VLOOKUP還是LOOKUP實(shí)現(xiàn)起來(lái)都非常麻煩,因?yàn)樾枰獦?gòu)建一個(gè)IF虛擬數(shù)組,而XLOOKUP就很便捷了。還記得我們的第一個(gè)參數(shù)叫“檢索關(guān)鍵字”么?我們只需要將這個(gè)參數(shù)選中想要查找的多個(gè)值就ok了。
當(dāng)然的話,一般查找多個(gè)值會(huì)對(duì)這些值做一個(gè)聚合運(yùn)算,例如找到最大值、平均值、最小值等等。例如下方找到3個(gè)員工的銷售額最大值,就非常簡(jiǎn)單:
而VLOOKUP又是要構(gòu)建一個(gè)非常復(fù)雜的數(shù)組公式,甚至還用到了T函數(shù),理解起來(lái)真是太吃力了??偨Y(jié)套路:
結(jié)論:XLOOKUP再次完爆VLOOKUP,寫法非常簡(jiǎn)單。
9、查找返回多列
上面一個(gè)是查找多個(gè)值,對(duì)這個(gè)命題擴(kuò)展下,就可以得到返回多列。在XLOOKUP函數(shù)中返回多列套路也非常簡(jiǎn)單,只需要將「結(jié)果區(qū)域」選擇多列就ok。而VLOOKUP需要使用ROW或者COLUMN函數(shù)才能實(shí)現(xiàn)。
例如想找到某個(gè)工號(hào)的平均銷售額,這里需要同時(shí)返回電腦和手機(jī)的銷售額:
整體來(lái)說(shuō)XLOOKUP函數(shù)比VLOOKUP函數(shù)理解起來(lái)更加簡(jiǎn)潔,因?yàn)閂LOOKUP函數(shù)使用了ROW作為輔助函數(shù),對(duì)于新手來(lái)說(shuō),理解起來(lái)需要一定的門檻,總結(jié):
當(dāng)然的 XLOOKUP函數(shù)的用法還有非常多種,例如還可以通過修改第4個(gè)參數(shù)實(shí)現(xiàn)「近似匹配-包含/不包含最小值」,還可以利用XLOOKUP函數(shù)代替MATCH+INDEX實(shí)現(xiàn)篩選功能,甚至連Offset這個(gè)動(dòng)態(tài)構(gòu)數(shù)函數(shù)都可以代替。
繼續(xù)查找其他問題的答案?
-
Excel 2019如何使用復(fù)制、粘貼命令插入Word數(shù)據(jù)?(視頻講解)
2021-01-257次播放
-
Excel 2019如何制作數(shù)據(jù)透視表?(視頻講解)
2021-01-2227次播放
-
Excel 2019如何設(shè)置相對(duì)引用?(視頻講解)
2021-01-2210次播放
-
2021-01-2212次播放
-
Excel 2019如何使用命令復(fù)制公式?(視頻講解)
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開始選項(xiàng)卡進(jìn)入篩選模式?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置指定數(shù)據(jù)的篩選?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置自定義排序?(視頻講解)
2021-01-222次播放
點(diǎn)擊加載更多評(píng)論>>