四虎精品8848ys一区二区,久久久午夜精品

12個案例！帶你從入門到進階全面解析函數(shù)新貴XLOOKUP

2020.09.12

作者：看見星光

微博：EXCELers / 知識星球：Excel

前段時間微軟在365版本中更新了一個新函數(shù)，叫做XLOOKUP。一時間鬧得沸沸揚揚，很多人宣稱這家伙將徹底淘汰VLOOKUP，甚至有人說微軟即將把VLOOKUP拋棄了……胡扯的沒有天際。

做商品數(shù)據(jù)分析的朋友都知道，一個產(chǎn)品能否完全代替甚至淘汰另外一個產(chǎn)品，不但要看它功能強弱，還要看版本迭代、用戶習慣、操作簡易性等因素。LOOKUP函數(shù)、INDEX+MATCH組合，每一個都號稱功能比VLOOKUP強大百倍，但這么多年過去了，VLOOKUP不還是活的好好的？

——打個響指，不過我們今天倒無意討論VLOOKUP生存還是死亡的問題，而是分享一下XLOOKUP到底有多強大，強大到一出生，很多江湖老司機就激揚失色，紛紛跟風宣揚VLOOKUP即將被淘汰的論調。

照例先說一下XLOOKUP的語法，它有六個參數(shù)，成功超越大哥大OFFSET，成為參數(shù)最多的函數(shù)之一。

=XLOOKUP(查找值,查找范圍,結果范圍,[容錯值],[匹配方式],[查詢模式])

參數(shù)看起來很多，不過只有前三個是必須的，后面均可省略。

下面我們舉12個例子+兩道練習題，由易入難、從簡到繁、從入門到進階，讓大家對XLOOKUP的作用和運算方式有一個全面的了解。

……

1）單條件查詢

如下圖所示，B:D列是數(shù)據(jù)明細，需要根據(jù)F列姓名查詢相關電話號碼。

公式如下▼

G2輸入公式▼
=XLOOKUP(F2,B:B,D:D)

F2是查找值，B列是查找范圍，D列是結果范圍，公式的意思也就是在B列查找F2，找到后返回D列對應的結果。

2）容錯查詢

如下圖所示，B:D列是數(shù)據(jù)明細，需要根據(jù)F列姓名查詢相關電話號碼，但和上一個案例所不同的是，如果查無結果，需要返回指定值：查無結果。

公式如下▼

G2輸入公式▼
=XLOOKUP(F2,B:B,D:D,'查無')

XLOOKUP的第4參數(shù)可以指定容錯值，當查無結果時避免返回錯誤值#N/A，省去了外圍再嵌套一個IFERROR函數(shù)。

3）模糊條件查詢

如下圖所示，A:B列是數(shù)據(jù)明細，需要根據(jù)F列姓名的簡稱查詢相關特長。這是一個模糊查詢的示例，比如查找星光，對應的結果為看見星光。

公式如下▼

E2輸入公式▼
=XLOOKUP('*'&D2&'*',A:A,B:B,'查無',2)

XLOOKUP的查找值是'*'&D2&'*'，*是通配符，可以代替0到多個字符串，'*'&D2&'*'也就指包含D2的字符串。

但和VLOOKUP所不同的是，XLOOKUP默認不支持通配符匹配，只有將第5參數(shù)設置為常數(shù)2時，才支持通配符匹配。

XLOOKUP的第5參數(shù)可以指定匹配方式，包含了精確匹配、區(qū)間匹配以及通配符匹配等。

4）區(qū)間查詢

如下圖所示，F(xiàn):G列是評分標準，60以下不及格，80以下及格等，需要根據(jù)該評分標準，對C列的成績計算評級。

公式如下▼

D2輸入公式▼
=XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,'',-1)

XLOOKUP第5參數(shù)為-1，指定了匹配方式是'精確匹配或下一個較小的項'，比如查找84，找不到精確匹配，則尋找比它小的項，也就是80，然后取其對應結果:'良好'。

這兒的XLOOKUP等同于LOOKUP函數(shù)▼

=LOOKUP(C2,F:G)

但和LOOKUP所不同的是，XLOOKUP函數(shù)不要求查找區(qū)域首列數(shù)據(jù)升序排列，即便把F:G列的數(shù)據(jù)打亂了，也不妨礙它尋找'精確匹配或下一個較小的項'的計算規(guī)則▼

除此之外，XLOOKUP還支持'精確匹配或下一個較大的項'的計算規(guī)則▼

=XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,'',1)

第5參數(shù)指定值為1，比如查找80，找不到精確匹配，則尋找比它大的項，也就是90。

5）查詢符合條件的最后一個結果

如下圖所示，A:C列是數(shù)據(jù)明細，其中日期字段升序排列。需要根據(jù)E列姓名查詢相關銷售額，但和前面案例所不同的是，它需要查找每個人最后一次銷售額，也就是符合條件的最后一條記錄。

公式如下▼

F2輸入公式▼
=XLOOKUP(E2,B:B,C:C,'查無',0,-1)

XLOOKUP的第6參數(shù)可以指定查詢方式，默認是從前往后找~找到即止；此外也可以從后往前找~找到即止；如果數(shù)據(jù)源有排序，還可以執(zhí)行二分法查找。

本例是尋找符合查詢條件的最后一條記錄，需要從后往前找~找到即止，也就是將第6參數(shù)設置為-1。

6）二分法查詢

如下圖所示，A:C列是數(shù)據(jù)源，其中姓名列有升序排序，現(xiàn)在需要根據(jù)E列姓名查詢相關電話號碼。

公式如下▼

F2輸入公式▼
=XLOOKUP(E2,A:A,C:C,'查無',0,2)

第6參數(shù)指定值為2，查找方式是升序排序情況下的二分法查找。

這里也可以使用公式▼

=XLOOKUP(E2,A:A,C:C,'查無')

兩者相比有何不同呢？

主要是查詢方式的區(qū)別。后者是從前往后找，雖然說找到即止，但效率也不是很高。后者是二分法查找，效率非常高。

比如查詢看見星光，前者要從第1行開始遍歷，找到第10行才找到結果，它需要找10次。而后者折半查找，只需要找3次就可以了。數(shù)據(jù)量越大后者的效率優(yōu)勢就越高——不過后者要求查詢范圍需排序處理。

7）橫向查詢

如下圖所示，A:D列是數(shù)據(jù)明細，需要根據(jù)F1指定的科目查詢對應的成績。

公式如下▼

F2輸入公式▼
=XLOOKUP(F1,B1:D1,B2:D2)

當查詢范圍是一個橫向區(qū)域時，XLOOKUP也就可以像HLOOKUP一樣，實現(xiàn)橫向數(shù)據(jù)查詢。

8）多列數(shù)據(jù)查詢

如下圖所示，A:D列是數(shù)據(jù)明細，需要根據(jù)F列的姓名，查詢對應的特長、電話和得分等多列數(shù)據(jù)。

公式如下▼

G2輸入公式▼
=XLOOKUP($F2,$A:$A,B:D)

當結果范圍是一個多行多列的區(qū)域時，XLOOKUP可以根據(jù)查詢范圍的行列特性，返回一個多行或多列的結果區(qū)域。本例中查找范圍是單列(A列)，結果范圍是B:D列，因此返回B:D列多列結果。

9）交叉表查詢

如下圖所示，A:D列是數(shù)據(jù)明細，需要根據(jù)F列的姓名，查詢對應的電話、特長和得分等多列數(shù)據(jù)。和上面的案例所不同的是，結果表的字段排列順序和數(shù)據(jù)源不一致，也就是通常所說的交叉表查詢了。

公式如下▼

G2輸入公式▼
=XLOOKUP($F2,$A$2:$A$11,XLOOKUP(G$1,$B$1:$D$1,$B$2:$D$11))

公式使用了兩個XLOOKUP函數(shù)。

先說XLOOKUP(G$1,$B$1:$D$1,$B$2:$D$11)。

上面解釋過，當結果范圍是一個多行多列的區(qū)域時，XLOOKUP可以根據(jù)查詢范圍的行列特性，返回一個多行或多列的結果區(qū)域。本例中查找范圍是單行($B$1:$D$1)，結果范圍是$B$2:$D$11，因此返回一個多行單列數(shù)據(jù)。

比如查找G1的值為'電話'，則返回C2:C11。以此作為第2個XLOOKUP的結果范圍。

10）多條件查詢

如下圖所示，A:C列是數(shù)據(jù)明細，需要根據(jù)E列的年和F列的姓名，查詢對應的得分。

公式如下▼

G2輸入公式▼
=XLOOKUP(E2&F2,$A$2:$A$11&$B$2:$B$11,$C$2:$C$11)

XLOOKUP支持數(shù)組運算，本例中查找值為E2&F2，查找范圍是年字段&姓名字段，即$A$2:$A$11&$B$2:$B$11▼

11）區(qū)域查詢

如下圖所示，A:B列是數(shù)據(jù)明細，A列日期升序排列。需要查詢E1單元格指定開始日期和E2單元格指定結束日期之間的金額合計。

公式如下▼

E3輸入公式▼
=SUM(XLOOKUP(E1,A:A,B:B):XLOOKUP(E2,A:A,B:B))

和VLOOKUP不同，和INDEX函數(shù)相同，XLOOKUP返回的不是一個單純的值，而是一單元格引用；因此XLOOKUP(E1,A:A,B:B)返回的是B4單元格的引用，XLOOKUP(E2,A:A,B:B)返回B8單元格的引用，B4:B8也就是目標金額區(qū)域，最后使用SUM函數(shù)求和即可。

12）動態(tài)表查詢

如下圖所示，一張工作簿包含了2017年、2018年、2019年等多張工作表，現(xiàn)在需要根據(jù)B1單元格指定的工作表名稱，在其中查詢A列相關人名的得分。

公式如下▼