位置:首頁 > 軟件操作教程 > 辦公軟件 > Excel > 問題詳情

Vlookup函數(shù)的使用技巧及應用

提問人:周麗平發(fā)布時間:2021-08-16

1、VLOOKUP函數(shù)語法解析

VLOOKUP 基礎用法是搜索某個單元格區(qū)域的第一列,然后返回該區(qū)域相同行上任何單元格中的值。

VLOOKUP 中的 V 表示垂直方向。當比較值位于所需查找的數(shù)據(jù)的左邊一列時,可以使用VLOOKUP

語法結構:

VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])

解讀:

VLOOKUP(找什么,在哪找,找到后返回其右側對應的第幾列數(shù)據(jù),精確還是模糊查找)

這樣一看是不是清晰多了?

具體的語法說明大家按F1看幫助文檔吧,本文就不再詳細解讀了。

需要說明的一點是,Excel中的幫助信息也有錯誤,比如在插入函數(shù)功能中VLOOKUP第四參數(shù)的說明就是錯的,大家注意不要被誤導哦!如下圖:

image.png

紅框部分幫助錯誤,應改為:如果為FALSE或0,精確匹配,如果為TRUE或忽略,大致匹配。


2、VLOOKUP函數(shù)單條件查找

根據(jù)單條件進行數(shù)據(jù)查找是最基礎也是最常見的需求了。

看如下案例,工作中的數(shù)據(jù)源為A:B兩列,分別放置業(yè)務員姓名和對應的銷售額,當需要按照業(yè)務員查找其對應的銷售額時,就要用到VLOOKUP函數(shù)了。

image.png

表中黃色區(qū)域為公式所在位置,以E2單元格公式為例

=VLOOKUP(D2,$A$2:$B$12,2,0)

這個公式是標準的VLOOKUP函數(shù)的基礎應用方法,每個參數(shù)都沒有變形,所以很方便初學者獲悉這個函數(shù)最原始的含義和作用。

第一參數(shù):找什么(或者說按什么查找),按業(yè)務員查找,所以輸入D2

第二參數(shù):在哪找,數(shù)據(jù)源區(qū)域在A:B列,所以輸入$A$2:$B$12

第三參數(shù):找到后返回第幾列,我們要查找的是銷售額,銷售額位于B列,即第二參數(shù)中的第二列,所以輸入2

第四參數(shù):這里要精確查找,所以輸入0

翻譯過來就是

=VLOOKUP(要查找的業(yè)務員,包含業(yè)務員和其銷售額的數(shù)據(jù)源區(qū)域,找到后返回第2列,精確查找)


3、 VLOOKUP函數(shù)多條件查找

如果有多個條件要同時滿足怎么辦?

其實很簡單,可以在數(shù)據(jù)源左側創(chuàng)建一個輔助列,將多個條件用&符號連接起來作為條件查找列。

如果數(shù)據(jù)源左側不允許插入列,或者想直接用一個公式搞定多條件查找,自然也有辦法啦,下面結合一個案例來介紹這種方法。

看如下案例,工作中的數(shù)據(jù)源為A:C兩列,分別放置水果、產(chǎn)地和對應的銷售額,當需要同時按照水果和產(chǎn)地查找其對應的銷售額時,就要用到VLOOKUP函數(shù)的多條件查找技巧了。

image.png

表中黃色區(qū)域為公式所在位置,以G2單元格公式為例

輸入以下數(shù)組公式,按<Ctrl+Shfit+Enter>組合鍵結束輸入。

=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$12&$B$2:$B$12,$C$2:$C$12),2,0)

注意:這個公式是數(shù)組公式,如果直接按Enter鍵輸入會返回#N/A錯誤值。

新人一定搞不懂啥叫數(shù)組公式呢?這里科普一下吧。

Excel中的公式分普通公式和數(shù)組公式。

普通公式不多說啦就是大家最常用的,輸入公式后直接按Enter結束輸入。

數(shù)組公式是為了應對一些比較復雜的計算,需要對一組或者多組數(shù)據(jù)執(zhí)行多項計算,返回一個值或者一組值的公式,這樣的公式輸入完成后需要按<Ctrl+Shfit+Enter>組合鍵結束輸入,公式兩側會自動出現(xiàn)一對大括號{},標識這個公式是要按照數(shù)組運算模式來計算的。

當鼠標定位在公式所在單元格進入編輯狀態(tài)時,大括號就消失了,當我們按ESC鍵退出編輯狀態(tài)時大括號就又重新出現(xiàn)了。


4、VLOOKUP函數(shù)查找返回多列數(shù)據(jù)

單條件查找會了,多條件查找也學了,都是輸入一個公式然后向下復制填充一列的形式,如果有多列數(shù)據(jù)需要根據(jù)查找值調(diào)取對應數(shù)據(jù),那么如何輸入一個公式就能搞定一個區(qū)域的數(shù)據(jù)調(diào)用呢?

這個案例告訴你答案。

下圖中左側表格是數(shù)據(jù)源區(qū)域,需要在右側區(qū)域根據(jù)業(yè)務員姓名調(diào)取對應的科目成績,黃色區(qū)域是需要填寫公式的區(qū)域。

image.png

這個案例中,觀察到右側的科目順序和數(shù)據(jù)源一致,都是從數(shù)學到體育,如果用最笨的方法一列一列寫公式固然可以實現(xiàn)目的,但當需要查找的列很多時無疑是一項大工程。

這里給出一個簡單實用的公式,選中H2:K5單元格區(qū)域,輸入以下公式后按<Ctrl+Enter>組合鍵。

注意是組合鍵同時按下,而不要只按Enter鍵哦!

=VLOOKUP($G2,$A$2:$E$12,COLUMN(B1),0)


5、VLOOKUP函數(shù)從右向左查找

工作中免不了遇到數(shù)據(jù)源中要調(diào)取的數(shù)據(jù)在查找值所在列的左側,知道VLOOKUP函數(shù)的常規(guī)用法是從左往右找,那么遇到這種需要從右往左查找的問題如何解決呢?

下面的表格中,需要按照給出的編號查找對應的業(yè)務員姓名,黃色區(qū)域輸入公式。

image.png

看到這個場景,有的小伙伴或許說,雖然VLOOKUP函數(shù)只能從左往右找,那干脆把數(shù)據(jù)源里面的編號列改到左邊不就行了嗎?

想的沒錯,這樣是可以實現(xiàn)的,但以下幾種情況下還是學會一個公式一步到位的比較好:

1、當數(shù)據(jù)源格式不允許改動時

2、當這項工作出現(xiàn)的頻率較高,你懶得每次都要重新改數(shù)據(jù)源,想一勞永逸時

3、當你想除了基礎用法之外,多學點高逼格用法時,哈哈

所以說技多不壓身,多學一手,何樂而不為呢?

F2單元格輸入以下公式后向下復制填充。

=VLOOKUP(E2,IF({1,0},$B$2:$B$12,$A$2:$A$12),2,0)


6、VLOOKUP函數(shù)按數(shù)據(jù)所處區(qū)間劃分等級

工作中除了精確查找外,模糊匹配也經(jīng)常遇到。結合下面這個案例來學習模糊匹配技巧。

下面表格中左側是數(shù)據(jù)源區(qū)域,需要按照等級劃分規(guī)則,將成績劃分到其對應的等級中。

image.png

等級劃分規(guī)則如下:

[0,60): D

[60,80): C

[80,90): B

[90,100]: A

看到這里,很多小伙伴一定想到用IF多條件嵌套來解決,沒錯,使用IF是可以實現(xiàn)的,比如這兩個公式都能實現(xiàn)我們的需求。

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=60,"C","D")))

=IF(B2<60,"D",IF(B2<80,"C",IF(B2<90,"B","A")))

但是當劃分規(guī)則更多時,編輯公式的時候需要一層一層的嵌套,用IF書寫公式簡直變成了體力活。

有沒有更簡便的辦法呢?當然,只要學會VLOOKUP模糊匹配技巧就可以了。

用以下公式就可以實現(xiàn)IF多層條件嵌套同樣的結果了。

=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)


7、VLOOKUP函數(shù)使用通配符模糊查找

當在工作中遇到需要只根據(jù)查找值的一部分進行查找時,記得可以利用通配符的特性來實現(xiàn)。

下面結合一個案例來介紹。

表格中左側是數(shù)據(jù)源,需要查找業(yè)務員名字中帶“強”的人的銷售額。

image.png

通配符星號*通配任意個字符,問號?通配單一字符,這個案例中模糊查找的規(guī)則是只要名字中帶“強”就可以,所以我們需要使用“*強*”這種形式,支持“強”字出現(xiàn)在任意位置。

E2公式為:

=VLOOKUP("*強*",$A$2:$B$12,2,0)


8、VLOOKUP函數(shù)多層級條件嵌套查找

遇到多層級條件嵌套查找,很多人第一時間想到的是IF多條件嵌套,還有些高手想到的是LOOKUP函數(shù)查找,其實VLOOKUP函數(shù)也可以搞定。

比如下面這個案例,要根據(jù)會員的消費金額查找其所處的會員等級。

image.png

當消費金額處在兩級會員等級之間時,按較低一級的等級算,比如消費金額3333,處于會員等級三級和四級之間,那么該會員屬于三級會員,只有達到5000消費金額后才算四級會員。

E2輸入以下公式,向下填充。

=VLOOKUP(D2,$A$2:$B$8,2)


9、VLOOKUP函數(shù)按指定次數(shù)重復數(shù)據(jù)

工作中一些復雜場景會遇到按指定次數(shù)重復數(shù)據(jù)的需求,如下圖所示。

image.png

D列黃色區(qū)域是由公式自動生成的重復數(shù)據(jù),當左側的數(shù)據(jù)源變動時,D列會按照指定的重復次數(shù)自動更新。

這里使用的是一個數(shù)組公式,以D2為例,輸入以下數(shù)組公式后按<Ctrl+Shfit+Enter>結束輸入。

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUBTOTAL(9,OFFSET(A$2,,,ROW($1:$3))),B$2:B$4),2,),D3)&""

這個思路和方法都很贊,轉(zhuǎn)給朋友們分享一下吧~


10、VLOOKUP函數(shù)返回查找到的多個值

都知道VLOOKUP的常規(guī)用法下,當有多個查找值滿足條件時,只會返回從上往下找到的第一個值,那么如果需要VLOOKUP函數(shù)一對多查找時,返回查找到的多個值,有辦法實現(xiàn)嗎?答案是肯定的。

結合案例來看。

下面表格中左側是數(shù)據(jù)源,當右側D2單元格選擇不同的著作時,需要黃色區(qū)域返回根據(jù)D2查找到的多個值。

image.png

在這里,先給出遇到這種情況最常用的一個數(shù)組公式

E2單元格輸入以下數(shù)組公式,按<Ctrl+Shift+Enter>組合鍵結束輸入。

=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""

這是經(jīng)典的一對多查找時使用的INDEX+SMALL+IF組合。

用VLOOKUP函數(shù)的公式,我也給出,E2輸入數(shù)組公式,按<Ctrl+Shift+Enter>組合鍵結束輸入。

=IF(COUNTIF(A$2:A$11,D$2)<ROW(A1),"",VLOOKUP(D$2&ROW(A1),IF({1,0},A$2:A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)),A$2:A$11),B$2:B$11),2,))


11、VLOOKUP函數(shù)在合并單元格中查找

合并單元格,這個東東大家在工作中太常見了吧。

在工作中盡量避免合并單元格,尤其是在數(shù)據(jù)處理過程中。但這并不能避免跟合并單元格打交道,因為數(shù)據(jù)源來自的渠道太多了,遇到了合并單元格也不能影響到數(shù)據(jù)處理和分析過程。

下面結合一個案例,介紹合并單元格中如何使用VLOOKUP函數(shù)查找。

image.png

注意到左側的班級列包含多個合并單元格且都是3行一合并,右側的查找是根據(jù)班級和名次進行雙條件查找。注意是從合并單元格中查找哦。

最簡便的辦法是在數(shù)據(jù)源左側做個輔助列,將合并單元格拆分并填充,這就回歸到前面介紹過的多條件查找的用法了。這個案例不創(chuàng)建輔助列,也不改動數(shù)據(jù)源結構,直接使用公式進行數(shù)據(jù)提取。

G2輸入以下公式

=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,),,3),2,)


12、VLOOKUP函數(shù)提取字符串中的數(shù)值

工作中有時會遇到從一串文本和數(shù)值混雜的字符串中提取數(shù)值的需求,如果字符串比較多而且經(jīng)常變動,與其每次都手動提取數(shù)值,就不如寫好一個公式實現(xiàn)自動提取。當數(shù)據(jù)源更新時,公式結果還能自動刷新。

下面的案例中,可以看到字符串中包含的數(shù)值各式各樣,有整數(shù)也有一位小數(shù)、兩位和多位小數(shù),還有百分比數(shù)值,使用公式都可以一次性批量提取(百分號提取出來默認按照小數(shù)形式顯示,可以設置格式改變顯示方式)。

image.png

首先給出數(shù)組公式,在B2輸入以下數(shù)組,按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)


13、VLOOKUP函數(shù)轉(zhuǎn)換數(shù)據(jù)行列結構

VLOOKUP函數(shù)不光能查找調(diào)用數(shù)據(jù),還可以用來轉(zhuǎn)換數(shù)據(jù)源的布局,比如將行數(shù)據(jù)轉(zhuǎn)換為多行多列的區(qū)域數(shù)據(jù),如下面案例。

數(shù)據(jù)源位于第二行,要將這個1行20列的行數(shù)據(jù)轉(zhuǎn)換為黃色區(qū)域所示的4行5列的布局。

image.png

選中P5:T8單元格區(qū)域,輸入以下區(qū)域數(shù)組公式,按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP("*",$A$2:$T$2,((ROW(1:4)-1)*5+COLUMN(A:E)),0)


14、VLOOKUP函數(shù)疑難解答提示

在使用VLOOKUP函數(shù)的過程中,很容易遭遇公式返回錯誤值的困境,下面這些錯誤值總結了最常見的問題,介紹產(chǎn)生錯誤原因的同時還給出了排除錯誤值的方法。

image.png


繼續(xù)查找其他問題的答案?

回復(0)
返回頂部