vlookup函數相信大家都非常的熟悉,平常就是用它來查找下數據,其實對于數據合并,數據提取這樣的問題我們也能使用vlookup函數來解決,今天跟大家盤點下vlookup的9種用法,帶你徹底解決工作中的數據查詢類問題,可以直接套用
1.常規用法常規方法相信大家都非常的熟悉,在這裡我們想要查找西瓜的銷售額,隻需要将公式設置為:=VLOOKUP(E2,A2:C8,3,0)即可,這樣的話就能查找想要的結果
2.核對兩列順序錯亂數據
如下圖,我們想要核對順序錯亂的數據,隻需要将公式設置為:=E4-VLOOKUP(D4,$A$3:$B$9,2,0),在這裡如果結果不是0,就是差異的數據
它其實利用的也是vlookup的常規用法,将表1的考核得分引用到表2中,然後再用表2的考核得分減一下即可
3.多條件查詢
使用vlookup查找數據的時候,如果遇到重複的查找值,函數僅僅會返回第一個查找的結果,比如在這裡我們要查找銷售部王明的考核得分,僅僅用王明來查找數據就會返回75分這個結果,因為它在第一個位置,這個時候就需要增加一個條件來查找數據才能找到精确的結果,隻需要将公式設置為:=VLOOKUP(E3&F3,IF({1,0},A1:A10&B1:B10,C1:C10),2,0)然後按ctrl shift 回車三鍵填充公式即可
在這裡利用連接符号将姓名與部門連接在一起,随後再利用if函數構建一個二維數組就能找到正确的結果
4.反向查找
當我們使用vlookup來查找數據的時候,它僅僅隻能查找數據區域右邊的數據,而不能查找左邊的數據,比如在這裡我們想要通過工号來查找姓名,因為姓名在工号的左邊所以查找不到,這個時候我們就需要将函數設置為:=VLOOKUP(G2,IF({1,0},B2:B10,A2:A10),2,0)然後按ctrl shift 回車三鍵填充公式即可
這個與多條件查詢十分的相似,我們都是利用if函數構建了一個二維數組來達到數據查詢的效果
5.關鍵字查詢
在這裡我們需要用到一個通配符,就是一個星号它代表任意多個字符,我們需要利用連接符号将星号分别連接在關鍵字的前後作為查找值,這樣的話就能達到根據關鍵字查找數據的效果公式為:=VLOOKUP("*"&E2&"*",A1:A10,1,0)
6.一對多查詢
首先我們需要先在數據的最左側構建一個輔助列,A2單元格輸入公式為:=(B2=$G$2) A1,然後點擊回車向下填充,這的話每遇到一個2班就會增加1,此時我們的查找值就變為了從1開始的序列,隻需要将公式設置為:=VLOOKUP(ROW(A1),$A$1:$D$10,3,0)向下填充即可
7.區間查詢
所謂的區間查詢就是某一個區間對應一個固定的數值,如下圖我們想要計算銷售提成的系數,首先需要先構建一個數據區域,将每個區間的最小值提取出來對應該區間的系數,然後進行升序排序,随後我們直接使用vlookup函數的近似匹配來引用結果即可,公式為:=VLOOKUP(B2,$E$11:$F$16,2,1)
8.提取固定長度的數字
如下圖,我們想要将工号提取出來,也可以使用vlookup來解決,隻需要将公式設置為:=VLOOKUP(0,{0,1}*MID(A2,ROW($1:$20),5),2,0),然後按ctrl shift 回車向下填即可
工号的長度都是5位,所以在這裡我們利用MID(A2,ROW($1:$20),5)來提取5個字符長度的數據,然後将這個結果乘以0與1,來構建一個二維數組
9.合并同類項
Vlookup也可以用于合并同類項,隻不過過程比較複雜,我們需要使用兩次公式,首先我們将公式設置為:=B2&IFERROR("、"&VLOOKUP(A2,A3:$C$10,3,0),""),然後拖動公式至倒數第二個單元格中,随後我們在旁邊的單元格中再次使用vlookup函數将結果引用過來,公式為:=VLOOKUP(E3,A:C,3,0)至此合并完畢
以上就是今天分享的9個vlookup的常見用法,學會它們,幾乎能搞定工作中所有的數據查找類問題
我是Excel從零到一,關注我,持續分享更多Excel技巧
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!