fbpx

VLOOKUP要光榮退休了?這個Excel新函數好用到爆!!

VLOOKUP 函數是超好用的函數(誰用誰知道),職場高效辦公離不開它~
不知不覺,它已經陪我們度過了 34 個春秋,它讓千千萬萬人免於加班脫發之苦,但係……
最近microsoft官方放了個大招,發布最新函數:XLOOKUP
和 VLOOKUP 相比,只是字母從 V 變成了 X,功能卻強大了好幾倍!

u-237687579-592232518-fm-26-gp-0

接下來,我們通過 5 個常見的需求,看看 XLOOKUP 函數到底厲害喺邊~
 

01

普通查找

 
VLOOKUP 主要用途就是查詢匹配數據。
XLOOKUP 的用法也是一樣,but 它還可以看做是 LOOKUP 家族的合體版!!
LOOKUP 家族:VLOOKUP、HLOOKUP、LOOKUP、INDEX、MATCH。(可見合體版有多強大……)
舉個例子!
這裡我們要根據「姓名」,查詢每個人的「在職狀態」

20191115-1

用 XLOOKUP 來做,簡單到爆~
在 J4 單元格中輸入公式:
=XLOOKUP(I4,C4:C19,E4:E19)
公式係咩意思呢?解釋一下:
=XLOOKUP(要查找的值,查找的區域,返回的區域)
對應上圖一起來看,是不是好了解一點 ~
XLOOKUP 函數的優勢就在於,在確定要查找的區域、返回的區域時,我們可以直接選中一整列,比如這裡就選中了 C 列、E 列數據~
這樣公式只需 3 個參數就搞定啦!
————————————————————-
but 用 VLOOKUP,我們需要寫 4 個參數……
而且!要一次性選中整個區域,再來挨個數數,看返回的區域在整個區域中排第幾位……
公式如下:
公式如下:
=VLOOKUP(I4,C3:E19,3,0)
解釋一下就是:
=VLOOKUP(要查找的數值,查找的區域,查找返回列,查找模式)
這樣一比較,你是不是發現 XLOOKUP 函數在普通查找中表現更友好一些?
 

02

多條件查找

寫到呢到 , 路人小 E 跑尼投訴:

車,不就是少了一個參數,值得把 XLOOKUP 吹上天嗎?你個喜新厭舊的壞人!

那我們再來看一個案例,還是根據「姓名」,查詢「在職狀態」。

20191115-2

但是……眼神好的同學一定發現啦,數據中有兩個同名的「秋葉」,直接查找一定會出錯!

所以,要同時根據「部門」和「姓名」,來查找在職狀態~

先用 XLOOKUP 函數,把公式寫出來給你們看:

20191115-3

什麼意思呢?思路是這樣的:
❶ 把查詢結果中,「部門 I 列」和「姓名 J 列」合並,一起作為「要查找的值」:
=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)
❷ 選擇「查找區域」時,也把「部門 B 列」和「姓名 C 列」合並起來查找:
=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)
❸ 最後,選擇「返回的區域」為:
=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)
我們會發現,XLOOKUP 函數居然可以直接用&符號,把列合並起來!這也太方便了吧!
————————————————-

但同樣的思路,用 VLOOKUP 就復雜了……
光說這公式,我就先暈低………….
=VLOOKUP(I4&J4,IF({1,0},B4:B19&C4:C19,E4:E19),2,0)
參數 1,用&符號,把部門和姓名合並在了一起。
參數 2,用 IF 函數,構建查找列 B4:B19&C4:C19 和返回列 E4:E19。
參數 3,返回第 2 列。
參數 4,精確查找。
參數 2 的寫法,實在是看不懂啊。這裡還構建了一個動態區域:
IF( {1,0} , B4:B19&C4:C19 , E4:E19)
這段公式相當於構建了下面的數據:

20191115-4

 

看不明白吧?看不明白就對了!
這不就說明了,XLOOKUP 更好用嘛!
 

03

反向查找
 
前面一個案例,相信大家已經感受到 XLOOKUP 的簡單、好用了,但是距離無敵上天,仲差d。
再來看這個需求,要根據「姓名」查詢「部門」。

20191115-5

非常簡單嘛,和按「姓名」查找「在職狀態」沒兩樣啊?!
fine,我先不說 XLOOKUP,沒有對比就沒有傷害,先看 VLOOKUP。
用過 VLOOKUP 同學都知道,它有一個通病:只能從左往右查找。
也就是說這裡需要「姓名」在左邊,「部門」在右邊,才方便查找。
而表格中「部門」在左邊,所以查找起來會很麻煩。
 公式如下:
  •  
=VLOOKUP(I4,IF({1,0},C4:C19,B4:B19),2,0)
 
what the,又是 IF({1,0})的方法,byebye
再看 XLOOKUP,一如既往地簡單優雅:
  •  
=XLOOKUP(I4,C4:C19,B4:B19)
 
XLOOKUP 在選擇時,只需要分別選擇查找列和返回列就行,所以根本不存在左右的問題~
 
再說了,這裡查找「秋葉」的部門時,因為有兩個秋葉(重名)。
 
而 VLOOKUP 默認只能查找到第 1 條記錄,也就是「生產部」。
 
如果我想查找在「客服部」的「秋葉」,要怎麼寫呢?
 
給 XLOOKUP 加個參數「0,1」就可以了:
=XLOOKUP(I5,C5:C20,B5:B20,0,-1)
20191115-6
「0,1」這個參數並不難,來解釋一下:
0 表示匹配模式為精確匹配。
-1 表示從下往上查找;如果輸入 1,表示從上往下查找。
所以填寫 -1,就能找到最下面位於「客服部」的「秋叶」~
 
 

04

一對多查找

現在我們要根據姓名,把員工的全部信息都查找出來,共計 4 列,所以返回值也有 4 個。
如果用 VLOOKUP 函數,為了解決返回列變化的問題,需要結合 COLUMN 函數來寫公式。
=VLOOKUP(I5,C4:G19,COLUMN(B1),0)
20191115140844-7
公式填寫好之後,向右拖動填充即可。
但……如果你不會 COLUMN 函數,解決這個問題最好的方法,就是趕緊關掉 Excel,眼不見為淨。
在這個問題上,XLOOKUP 的處理方式會更高級。
簡簡單單一個公式就搞掂:
  •  
=XLOOKUP(I4,C3:C19,D3:G19)
 
奧秘就在第 3 個參數「D3:G19」上。
參數 3,選擇返回列的時候,把所有需要返回的列,一次性都選上。
聰明的 XLOOKUP 同學,會根據返回列的列數,自動填充相鄰的數據列~
 
高效又簡單,大家快D讚吓我啦!
 
 
 

05

模糊查找

 
這裡我們要根據G列的「績效」,算出I列的「績效評比」結果。

20191115-7

 

評比規則如下:

20191115-8

我猜,很多人遇到這種問題,都會寫長長的 IF 函數吧?
  •  
=IF(N4>=100,"A+",IF(N4>=90,"A",IF(N4>=80,"B",IF(N4>=70,"C",IF(N4>=60,"D","E")))))
▲左右滑動查看完整公式
這種情況其實可以用 VLOOKUP 解決~
在一些績效、提成的計算上,用 VLOOKUP 模糊查找,可以避免反復地寫 IF 函數嵌套。
 
在 I4 列輸入公式:
  •  
=VLOOKUP(G4,$L$4:$M$9,2,1)
 
20191115-9
要注意的是,這種模糊查找方法,要求「績效」列的數字從小到大排序,否則查詢會出錯。

20191115-10

但是!沒錯,但是來了——
 
XLOOKUP 的解決方法,更加簡單易懂,改一下參數就好。
  •  
=XLOOKUP(G4,$L$4:$L$9,M4:M9,-1)
前 3 個參數和 VLOOKUP 道理一樣,重點是第 4 個參數:設置查詢匹配的模式。
 
參數 4 有下面幾種用法
0 表示精確匹配。
 
1 表示精切匹配,或下一個較大的項。如果找不到,就找比查找值大的最接近的值。
 
-1 表示精切匹配,或下一個較小的項。如果找不到,就找比查找值小的最接近的值。
這樣一來,不管「績效」列的數字按什麼順序排,都不會影響我們查找結果!
 
又比 VLOOKUP 函數節省了好幾分鐘呢~
 
(溫馨提示,XLOOKUP 目前只有 Office 365 版本的 Excel 才可以使用。)
希望幫到大家~~
 

Leave a Comment

Your email address will not be published. Required fields are marked *