移動版 - 最新教程 - 熱門教程 - 推薦教程 - 熱門資源 分享高質量的原創Office教程、Word教程、Excel教程、PPT教程。

XLOOKUP函數與VLOOKUP函數用法比較

發布時間:2019-09-21 來源:Office辦公助手(www.wowhua.com)

本文從9個方面解讀XLOOKUP函數與VLOOKUP/LOOKUP函數之間的用法差異,包括:基礎語法、橫向查找、縱向查找、一對多、返回多值、反向查找、多條件查找、模糊查找、近似查找、匹配最后一個等等。

1、XLOOKUP基礎語法

在學習任何一個函數之前,需要了解這個函數的基礎語法,從微軟官方的幫助文檔里找到了這個函數的參數語法,共計有5個參數,跟LOOKUP的參數非常接近,但是使用起來會更加簡單了一些。

XLOOKUP函數

其中第1~3個參數跟LOOKUP的參數非常接近,都是將「查找區域」和「結果區域」全部獨立出來了,跟VLOOKUP的「選擇區域」就有所差異,拆分出來會讓函數更加靈活。

第4個參數match_mode表示匹配類型,可以使用「精確匹配」「通配符匹配」「2種近似匹配」,默認為0表示精確匹配。

第5個參數search_mode是新增的一個參數,表示搜索模式,在傳統的VLOOKUP/LOOKUP系列函數中,搜索只能從上往下,并且尋找第2個、最后1個數值的時候會非常麻煩。而XLOOKUP就直接引進了這個參數,默認為1表示從上往下開始搜索,-1表示從下往上搜索,這2個會用的比較多一些。

至此,XLOOKUP的語法就初步了解了,接下來來實操下這個XLOOKUP函數有多強大!

2、基礎縱向查詢

例如下圖查找“工號的電腦銷售額”,在基礎操作上VLOOKUP和XLOOKUP沒有什么太大的差異,寫法都非常簡單,只是XLOOKUP將選擇區域和返回區域拆分出來單獨寫了而已。

XLOOKUP函數

來總結下XLOOKUP基礎縱向查詢的套路:

XLOOKUP函數

結論:雙方平均(VLOOKUP公式會簡潔一點點,不過差異不大)

3、基礎橫向查詢

既然能縱向查詢,那么橫向查詢其實也是可以的。然而在VLOOKUP中,如果要實現橫向查詢會非常麻煩,又是需要構建虛擬數組。在這里我們使用INDEX+MATCH來代替VLOOKUP實現「基礎橫向查詢」。

而使用XLOOKUP就非常簡單了,所有操作都跟縱向查詢沒有任何差異,XLOOKUP會自動識別是什么方向,例如將上面的數據橫放了,變成這個樣子:

XLOOKUP函數

在橫向查詢中XLOOKUP完爆VLOOKUP,XLOOKUP的兩個方向查詢用法一模一樣,沒有任何差異,太智能了。

來總結下XLOOKUP橫向查詢的公式套路:

XLOOKUP函數

結論:XLOOKUP完爆VLOOKUP,因為VLOOKUP實現非常麻煩。

4、反向匹配查詢

在前面的語法中,我們說到XLOOKUP將「查找區域」和「結果區域」全部獨立出來了,所以在這里的話,反向匹配對于XLOOKUP來說沒有什么障礙,相反VLOOKUP就需要去構建一個IF虛擬數組來實現了。

來感受下2個函數的用法:

XLOOKUP函數

VLOOKUP是通過IF({1,0},XXX,XXX)的方式構建一個虛擬數組來實現這個功能的,因為檢索關鍵字必須在選擇區域的第一列,對于新手來說理解非常不友好,而且復雜的數組公式還會消耗大量的計算機資源,XLOOKUP依然是這么牛逼完爆,格式沒有什么變化,輕松完成反向查詢:

XLOOKUP函數

結論:XLOOKUP完爆VLOOKUP,基礎語法即可實現。

5、多條件查找

多條件查找是很多VLOOKUP的初學者的噩夢,需要寫非常復雜的IF數組公式,而且又要注意定位引用的方式,而XLOOKUP使用起來就非常舒服了,只需要將多個條件利用&符號拼接起來就ok。

徹底告別復雜公式:

XLOOKUP函數

相信很多同學看到了VLOOKUP多條件查詢都是一臉懵逼,IF到底是什么鬼,怎么老是出現?其實這里還是構建了一個虛擬數組,數組公式對于新手來說理解起來的確太困難。

將絕對定位去掉,我們來看下XLOOKUP公式究竟有多簡潔,絲毫不拖泥帶水:

=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)

總結XLOOKUP多條件查詢公式套路:

XLOOKUP函數

結論:XLOOKUP再次完爆VLOOKUP,簡潔速度快!

6、模糊查詢匹配

VLOOKUP和XLOOKUP均支持模糊匹配,在Excel中使用模糊匹配需要用到通配符(*、?、~),這次兩個函數不相上下,XLOOKUP只需要將第4個參數修改成2表示通配符匹配即可。

XLOOKUP函數

兩個函數不相上下,因為這個功能比較簡單,總結下XLOOKUP的模糊查詢公式套路:

XLOOKUP函數

結論:XLOOKUP和VLOOKUP不相上下,因為都比較基礎

7、匹配最后一個值

在某些情況下,我們需要找到記錄里的最后一條數據,而恰好XLOOKUP的最后一個參數是搜索模式,只要我們將第5個參數search_mode修改成-1,就會倒序查找,這樣就能找到最后一個數值了,非常簡單。

而VLOOKUP本身實現匹配最后一個值非常麻煩,這里我們使用LOOKUP來代替:

XLOOKUP函數

LOOKUP的寫法就非常難理解了,又是用0除,又是做邏輯符號判斷等于的。對于新手實在太不友好,相比之下XLOOKUP的寫法就非常簡單了。直接將搜索模式一改就ok了,這個功能在人事應用中非常廣,找到最后一次打卡時間和第一次打卡時間,非常簡單。

套路總結:

XLOOKUP函數

結論:XLOOKUP完爆VLOOKUP/LOOKUP,寫法簡潔,計算快

8、查找多個值

查找多個值無論是利用VLOOKUP還是LOOKUP實現起來都非常麻煩,因為需要構建一個IF虛擬數組,而XLOOKUP就很便捷了。還記得我們的第一個參數叫“檢索關鍵字”么?我們只需要將這個參數選中想要查找的多個值就ok了。

當然的話,一般查找多個值會對這些值做一個聚合運算,例如找到最大值、平均值、最小值等等。例如下方找到3個員工的銷售額最大值,就非常簡單:

XLOOKUP函數

而VLOOKUP又是要構建一個非常復雜的數組公式,甚至還用到了T函數,理解起來真是太吃力了??偨Y套路:

XLOOKUP函數

結論:XLOOKUP再次完爆VLOOKUP,寫法非常簡單。

9、查找返回多列

上面一個是查找多個值,對這個命題擴展下,就可以得到返回多列。在XLOOKUP函數中返回多列套路也非常簡單,只需要將「結果區域」選擇多列就ok。而VLOOKUP需要使用ROW或者COLUMN函數才能實現。

例如想找到某個工號的平均銷售額,這里需要同時返回電腦和手機的銷售額:

XLOOKUP函數

整體來說XLOOKUP函數比VLOOKUP函數理解起來更加簡潔,因為VLOOKUP函數使用了ROW作為輔助函數,對于新手來說,理解起來需要一定的門檻,總結:

XLOOKUP函數

當然的 XLOOKUP函數的用法還有非常多種,例如還可以通過修改第4個參數實現「近似匹配-包含/不包含最小值」,還可以利用XLOOKUP函數代替MATCH+INDEX實現篩選功能,甚至連Offset這個動態構數函數都可以代替。

更多相關閱讀

微軟宣布為Excel用戶提供XLOOKUP函數,解決了大多數VLOOKUP函數問題

Xlookup函數來了,比Vlookup函數好用10倍

XLOOKUP函數簡介及實際用法

Excel更新了超級查找函數XLOOKUP函數

    相關教程
    專題教程推薦
    熱門資源下載
    色就色 综合偷拍区,色综合天天综合网,色就色 综合偷拍区欧美