今天給大家分享的Excel函數(shù)是XLOOKUP，先說一下它的基本語法。它有六個參數(shù)，成功超越大哥大OFFSET，成為參數(shù)最多的函數(shù)之一。

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

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

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

1）單條件查詢

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

公式如下：

G2輸入公式▼

=XLOOKUP(F2,B:B,D:D)

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

2）容錯查詢

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

公式如下：

G2輸入公式▼

=XLOOKUP(F2,B:B,D:D,"查無")

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

3）模糊條件查詢

如下圖所示，A:B列是數(shù)據(jù)明細(xì)，需要根據(jù)F列姓名的簡稱查詢相關(guān)特長。這是一個模糊查詢的示例，比如查找技巧，對應(yīng)的結(jié)果為常用技巧。

公式如下：

E2輸入公式▼

=XLOOKUP("*"&D2&"*",A:A,B:B,"查無",2)

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

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

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

4）區(qū)間查詢

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

公式如下：

D2輸入公式▼

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

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

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

=LOOKUP(C2,F:G)

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

除此之外，XLOOKUP還支持’精確匹配或下一個較大的項(xiàng)’的計(jì)算規(guī)則

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

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

5）查詢符合條件的最后一個結(jié)果

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

公式如下：

F2輸入公式▼

=XLOOKUP(E2,B:B,C:C,"查無",0,-1)

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

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

6）二分法查詢

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

公式如下：

F2輸入公式▼

=XLOOKUP(E2,A:A,C:C,"查無",0,2)

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

這里也可以使用公式：

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

兩者相比有何不同呢？

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

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

7）橫向查詢

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

公式如下：

F2輸入公式

=XLOOKUP(F1,B1:D1,B2:D2)

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

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

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

公式如下：

G2輸入公式

=XLOOKUP($F2,$A:$A,B:D)

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

9）交叉表查詢

如下圖所示，A:D列是數(shù)據(jù)明細(xì)，需要根據(jù)F列的姓名，查詢對應(yīng)的電話、特長和得分等多列數(shù)據(jù)。和上面的案例所不同的是，結(jié)果表的字段排列順序和數(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)。

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

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

10）多條件查詢

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

公式如下：

G2輸入公式

=XLOOKUP(E2&F2,$A$2:$A$11&$B$2:$B$11,$C$2:$C$11)

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

11）區(qū)域查詢

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

公式如下：

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也就是目標(biāo)金額區(qū)域，最后使用SUM函數(shù)求和即可。

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

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

公式如下：

B4輸入公式

=XLOOKUP(A4,INDIRECT($B$1&"!A:A"),INDIRECT($B$1&"!B:B"))

公式使用INDIRECT函數(shù)根據(jù)B1單元格指定的工作表名稱構(gòu)建引用范圍，其中查找范圍是指定表的A列，結(jié)果范圍是指定表的B列。

最后留兩道練習(xí)題：

1）多列數(shù)據(jù)源區(qū)域查詢

如下圖所示，A1:F4是數(shù)據(jù)源，需要據(jù)此查詢A8:A10單元格人名對應(yīng)的特長信息。

2）動態(tài)引用圖片

上文講過，XLOOKUP和INDEX函數(shù)一樣，返回的是單元格引用，那么它就可以像INDEX一樣，實(shí)現(xiàn)動態(tài)引用圖片的功能。

實(shí)現(xiàn)效果如下圖所示：

推薦閱讀：字符提取，幾個方法隨你選

最新評論