2021-05-12 14:32:11
Vlookup函數的使用技巧及應用範例
2020-07-14 14:34:22
平時工作中經常需要對Excel表格中資料進行查詢呼叫，VLOOKUP函數是工作中使用頻率超高的查詢函數之一。本文完整詳盡的介紹了VLOOKUP函數的使用方法，並結合範例深入的講解了Vlookup函數的應用技巧。
1、VLOOKUP函數語法解析
VLOOKUP 基礎用法是搜尋某個單元格區域的第一列，然後返回該區域相同行上任何單元格中的值。
VLOOKUP 中的 V 表示垂直方向。當比較值位於所需查詢的資料的左邊一列時，可以使用VLOOKUP
語法結構：
VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])
解讀：
VLOOKUP(找什麼，在哪找，找到後返回其右側對應的第幾列資料，精確還是模糊查詢)
這樣一看是不是清晰多了?
具體的語法說明大家按F1看幫助文件吧，本文就不再詳細解讀了。
需要說明的一點是，Excel中的幫助資訊也有錯誤，比如在插入函數功能中VLOOKUP第四引數的說明就是錯的，大家注意不要被誤導哦!如下圖：
紅框部分幫助錯誤，應改為：如果為FALSE或0，精確匹配，如果為TRUE或忽略，大致匹配。
詳細解讀：
http://www.officezhushou.com/vlookup/5523.html
2、VLOOKUP函數單條件查詢
根據單條件進行資料查詢是最基礎也是最常見的需求了。
看如下案例，工作中的資料來源為A:B兩列，分別放置業務員姓名和對應的銷售額，當需要按照業務員查詢其對應的銷售額時，就要用到VLOOKUP函數了。
表中黃色區域為公式所在位置，以E2單元格公式為例
=VLOOKUP(D2,$A$2:$B$12,2,0)
這個公式是標準的VLOOKUP函數的基礎應用方法，每個引數都沒有變形，所以很方便初學者獲悉這個函數最原始的含義和作用。
第一引數：找什麼(或者說按什麼查詢)，按業務員查詢，所以輸入D2
第二引數：在哪找，資料來源區域在A:B列，所以輸入$A$2:$B$12
第三引數：找到後返回第幾列，我們要查詢的是銷售額，銷售額位於B列，即第二引數中的第二列，所以輸入2
第四引數：這裡要精確查詢，所以輸入0
翻譯過來就是
=VLOOKUP(要查詢的業務員,包含業務員和其銷售額的資料來源區域,找到後返回第2列,精確查詢)
詳細解讀：
http://www.officezhushou.com/vlookup/5525.html
3、 VLOOKUP函數多條件查詢
如果有多個條件要同時滿足怎麼辦?
其實很簡單，可以在資料來源左側建立一個輔助列，將多個條件用&符號連線起來作為條件查詢列。
如果資料來源左側不允許插入列，或者想直接用一個公式搞定多條件查詢，自然也有辦法啦，下面結合一個案例來介紹這種方法。
看如下案例，工作中的資料來源為A:C兩列，分別放置水果、產地和對應的銷售額，當需要同時按照水果和產地查詢其對應的銷售額時，就要用到VLOOKUP函數的多條件查詢技巧了。
表中黃色區域為公式所在位置，以G2單元格公式為例
輸入以下陣列公式，按<Ctrl+Shfit+Enter>組合鍵結束輸入。
=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$12&$B$2:$B$12,$C$2:$C$12),2,0)
注意：這個公式是陣列公式，如果直接按Enter鍵輸入會返回#N/A錯誤值。
新人一定搞不懂啥叫陣列公式呢?這裡科普一下吧。
Excel中的公式分普通公式和陣列公式。
普通公式不多說啦就是大家最常用的，輸入公式後直接按Enter結束輸入。
陣列公式是為了應對一些比較複雜的計算，需要對一組或者多組資料執行多項計算，返回一個值或者一組值的公式，這樣的公式輸入完成後需要按<Ctrl+Shfit+Enter>組合鍵結束輸入，公式兩側會自動出現一對大括號{}，標識這個公式是要按照陣列運算模式來計算的。
當滑鼠定位在公式所在單元格進入編輯狀態時，大括號就消失了，當我們按ESC鍵退出編輯狀態時大括號就又重新出現了。
詳細解讀：
http://www.officezhushou.com/vlookup/5525.html
4、VLOOKUP函數查詢返回多列資料
單條件查詢會了，多條件查詢也學了，都是輸入一個公式然後向下複製填充一列的形式，如果有多列資料需要根據查詢值調取對應資料，那麼如何輸入一個公式就能搞定一個區域的資料呼叫呢?
這個案例告訴你答案。
下圖中左側表格是資料來源區域，需要在右側區域根據業務員姓名調取對應的科目成績，黃色區域是需要填寫公式的區域。
這個案例中，觀察到右側的科目順序和資料來源一致，都是從數學到體育，如果用最笨的方法一列一列寫公式固然可以實現目的，但當需要查詢的列很多時無疑是一項大工程。
這裡給出一個簡單實用的公式，選中H2:K5單元格區域，輸入以下公式後按<Ctrl+Enter>組合鍵。
注意是組合鍵同時按下，而不要只按Enter鍵哦!
=VLOOKUP($G2,$A$2:$E$12,COLUMN(B1),0)
詳細解讀：
http://www.officezhushou.com/vlookup/5526.html
5、VLOOKUP函數從右向左查詢
工作中免不了遇到資料來源中要調取的資料在查詢值所在列的左側，知道VLOOKUP函數的常規用法是從左往右找，那麼遇到這種需要從右往左查詢的問題如何解決呢?
下面的表格中，需要按照給出的編號查詢對應的業務員姓名，黃色區域輸入公式。
看到這個場景，有的小夥伴或許說，雖然VLOOKUP函數只能從左往右找，那乾脆把資料來源裡面的編號列改到左邊不就行了嗎?
想的沒錯，這樣是可以實現的，但以下幾種情況下還是學會一個公式一步到位的比較好：
1、當資料來源格式不允許改動時
2、當這項工作出現的頻率較高，你懶得每次都要重新改資料來源，想一勞永逸時
3、當你想除了基礎用法之外，多學點高逼格用法時，哈哈
所以說技多不壓身，多學一手，何樂而不為呢?
F2單元格輸入以下公式後向下複製填充。
=VLOOKUP(E2,IF({1,0},$B$2:$B$12,$A$2:$A$12),2,0)
詳細解讀：
http://www.officezhushou.com/vlookup/5528.html
6、VLOOKUP函數按資料所處區間劃分等級
工作中除了精確查詢外，模糊匹配也經常遇到。結合下面這個案例來學習模糊匹配技巧。
下面表格中左側是資料來源區域，需要按照等級劃分規則，將成績劃分到其對應的等級中。
等級劃分規則如下：
[0,60): D
[60,80): C
[80,90): B
[90,100]: A
看到這裡，很多小夥伴一定想到用IF多條件巢狀來解決，沒錯，使用IF是可以實現的，比如這兩個公式都能實現我們的需求。
=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")))
但是當劃分規則更多時，編輯公式的時候需要一層一層的巢狀，用IF書寫公式簡直變成了體力活。
有沒有更簡便的辦法呢?當然，只要學會VLOOKUP模糊匹配技巧就可以了。
用以下公式就可以實現IF多層條件巢狀同樣的結果了。
=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)
詳細解讀：
http://www.officezhushou.com/vlookup/5529.html
7、VLOOKUP函數使用萬用字元模糊查詢
當在工作中遇到需要只根據查詢值的一部分進行查詢時，記得可以利用萬用字元的特性來實現。
下面結合一個案例來介紹。
表格中左側是資料來源，需要查詢業務員名字中帶“強”的人的銷售額。
萬用字元星號*通配任意個字元，問號?通配單一字元，這個案例中模糊查詢的規則是只要名字中帶“強”就可以，所以我們需要使用“*強*”這種形式，支援“強”字出現在任意位置。
E2公式為：
=VLOOKUP("*強*",$A$2:$B$12,2,0)
詳細解讀：
http://www.officezhushou.com/vlookup/5530.html
8、VLOOKUP函數多層級條件巢狀查詢
遇到多層級條件巢狀查詢，很多人第一時間想到的是IF多條件巢狀，還有些高手想到的是LOOKUP函數查詢，其實VLOOKUP函數也可以搞定。
比如下面這個案例，要根據會員的消費金額查詢其所處的會員等級。
當消費金額處在兩級會員等級之間時，按較低一級的等級算，比如消費金額3333，處於會員等級三級和四級之間，那麼該會員屬於三級會員，只有達到5000消費金額後才算四級會員。
E2輸入以下公式，向下填充。
=VLOOKUP(D2,$A$2:$B$8,2)
詳細解讀：
http://www.officezhushou.com/vlookup/5531.html
9、VLOOKUP函數按指定次數重複資料
工作中一些複雜場景會遇到按指定次數重複資料的需求，如下圖所示。
D列黃色區域是由公式自動生成的重複資料，當左側的資料來源變動時，D列會按照指定的重複次數自動更新。
這裡使用的是一個陣列公式，以D2為例，輸入以下陣列公式後按<Ctrl+Shfit+Enter>結束輸入。
=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUBTOTAL(9,OFFSET(A$2,,,ROW($1:$3))),B$2:B$4),2,),D3)&""
這個思路和方法都很讚，轉給朋友們分享一下吧~
詳細解讀：
http://www.officezhushou.com/vlookup/5532.html
10、VLOOKUP函數返回查詢到的多個值
都知道VLOOKUP的常規用法下，當有多個查詢值滿足條件時，只會返回從上往下找到的第一個值，那麼如果需要VLOOKUP函數一對多查詢時，返回查詢到的多個值，有辦法實現嗎?答案是肯定的。
結合案例來看。
下面表格中左側是資料來源，當右側D2單元格選擇不同的著作時，需要黃色區域返回根據D2查詢到的多個值。
在這裡，先給出遇到這種情況最常用的一個陣列公式
E2單元格輸入以下陣列公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。
=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""
這是經典的一對多查詢時使用的INDEX+SMALL+IF組合。
用VLOOKUP函數的公式，我也給出，E2輸入陣列公式，按<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,))
詳細解讀：
http://www.officezhushou.com/vlookup/5533.html
11、VLOOKUP函數在合併單元格中查詢
合併單元格，這個東東大家在工作中太常見了吧。
在工作中盡量避免合併單元格，尤其是在資料處理過程中。但這並不能避免跟合併單元格打交道，因為資料來源來自的渠道太多了，遇到了合併單元格也不能影響到資料處理和分析過程。
下面結合一個案例，介紹合併單元格中如何使用VLOOKUP函數查詢。
注意到左側的班級列包含多個合併單元格且都是3行一合併，右側的查詢是根據班級和名次進行雙條件查詢。注意是從合併單元格中查詢哦。
最簡便的辦法是在資料來源左側做個輔助列，將合併單元格拆分並填充，這就回歸到前面介紹過的多條件查詢的用法了。這個案例不建立輔助列，也不改動資料來源結構，直接使用公式進行資料提取。
G2輸入以下公式
=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,),,3),2,)
詳細解讀：
http://www.officezhushou.com/vlookup/5534.html
12、VLOOKUP函數提取字串中的數值
工作中有時會遇到從一串文字和數值混雜的字串中提取數值的需求，如果字串比較多而且經常變動，與其每次都手動提取數值，就不如寫好一個公式實現自動提取。當資料來源更新時，公式結果還能自動刷新。
下面的案例中，可以看到字串中包含的數值各式各樣，有整數也有一位小數、兩位和多位小數，還有百分比數值，使用公式都可以一次性批次提取(百分號提取出來預設按照小數形式顯示，可以設定格式改變顯示方式)。
首先給出陣列公式，在B2輸入以下陣列，按<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)
詳細解讀：
http://www.officezhushou.com/vlookup/5535.html
13、VLOOKUP函數轉換資料行列結構
VLOOKUP函數不光能查詢呼叫資料，還可以用來轉換資料來源的佈局，比如將行資料轉換為多行多列的區域資料，如下面案例。
資料來源位於第二行，要將這個1行20列的行資料轉換為黃色區域所示的4行5列的布局。
選中P5:T8單元格區域，輸入以下區域陣列公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。
=VLOOKUP("*",$A$2:$T$2,((ROW(1:4)-1)*5+COLUMN(A:E)),0)
詳細解讀：
http://www.officezhushou.com/vlookup/5536.html
14、VLOOKUP函數疑難排解提示
在使用VLOOKUP函數的過程中，很容易遭遇公式返回錯誤值的困境，下面這些錯誤值總結了最常見的問題，介紹產生錯誤原因的同時還給出了排除錯誤值的方法。
詳細解讀：
http://www.officezhushou.com/vlookup/5537.html
相關文章