Excel012-Vlookup不為人知的秘密

  ▲本期分享技能請觀看

Excel012-Vlookup不為人知的秘密

之前有小夥伴問我怎麼進行兩張表的查詢,例如如何在A表中根據某個字段查詢B表的相關信息,或者怎麼確定A表的內容是否在B表中存在,鑒於很多小夥伴問這個問題,今天就單獨來跟大家聊聊這個查詢函數Vlookup,它能讓您的查詢工作事半功倍。

Excel012-Vlookup不為人知的秘密

讓我們先來了解一下Vlookup函數的結構,四個參數分別如下:

①查誰?就是我要查詢的內容是什麼。這裡可以是數字,文本甚至是單元格引用。

②在哪查?就是我要在什麼範圍進行查詢。查詢的範圍第一列一定是包含查詢內容的。例如我要查詢「張三」的身份證號碼,那查詢的範圍第一列一定要是「姓名」這一列。

③返回值列號。例如我要查詢「張三」的身份證號碼,那查詢的範圍從「姓名」這一列開始數到「身份證號碼」這一列的列數就是返回值的列號。

④怎麼查?就是查詢的方式,這裡我們分為精確查詢和模糊查詢。

Excel012-Vlookup不為人知的秘密

當查詢的對象在被查詢區域有且只有一個值時可以使用精確查詢,查詢出該對象所對應的精確值。這裡我們用一個員工信息動態查詢的案例來看看如何用Vlookup進行精確查詢。

員工信息動態查詢頁面如下:

Excel012-Vlookup不為人知的秘密

員工花名冊頁面如下:

Excel012-Vlookup不為人知的秘密

具體演示步驟如下:

1、在員工信息動態查詢頁面D4單元格使用數據驗證製作【姓名】的下拉菜單,具體製作步驟參見Excel004 -多級菜單,就這麼簡單。

2、在員工信息動態查詢頁面F4單元格輸入公式:=VLOOKUP($D4,員工花名冊!$B$:$J$,2,0),我們通過四個參數來分析一下這個公式:

①查誰?查詢「馬祥君」。

②在哪查?在員工花名冊頁面的B列到J列查詢。

③返回值列號。F4要查詢的是員工編號,從B列【姓名】開始數,工號位於第二列,所以返回值列號是2。

④怎麼查?因為「馬祥君」在員工花名冊頁面中只有一條信息,所以我們採用精確查詢,精確查詢參數為0。

3、了解了公式的具體含義,我們分別將【身份證號碼】、【部門】、【入職日期】、【聯繫方式】也用Vlookup函數進行查詢即可。只要更換第三個參數「返回值列號」即可。

奉上動態演示:

Excel012-Vlookup不為人知的秘密

看了上面的演示您學會了麼?這個要多操作才能熟能生巧哦!

Excel012-Vlookup不為人知的秘密

學會如何進行精確查詢之後我們來看看如何進行模糊查詢,當查詢的對象包含在被查詢區域區間範圍時適用此方法。

我們來看看具體的演示案例:如何用Vlookup函數在A表的C列根據B表的等級參考查詢出員工考核成績相對應的等級?

Excel012-Vlookup不為人知的秘密

我們可以看到,A 表中要查詢的成績,很多在B表中都不存在,但是A表中的成績包含在B表的區間範圍內,那麼我們就能通過模糊查詢來查找到對應的等級,切記B表必須按照升序排列。具體方法如下:

在C3單元格輸入公式:=VLOOKUP($B3,$F:$G,2,1),我們通過四個參數來分析一下這個公式:

①查誰?查詢【考核成績】「64」。

②在哪查?在B表的F列到G列查詢。

③返回值列號。要查詢員工等級,從F列【成績】開始數,【等級】位於第二列,所以返回值列號是2。

④怎麼查?因為【考核成績】「64」在B表中不存在,但包含在>60的範圍內,所以我們採用模糊查詢,模糊查詢參數為1。

奉上動態演示:

Excel012-Vlookup不為人知的秘密

是不是很簡單,只要改變最後一個參數就能有另外一種用途。

Excel012-Vlookup不為人知的秘密

不知道大家有沒有發現,不管是精確查詢還是模糊查詢,Vlookup只能查找到第一條信息,如果說在被查詢的表裡同一個對象有一條以上的信息,Vlookup不能全部查找出來。這真是Vlookup的一個bug啊,沒關係,今天給大家帶來了一個自定義函數Vlookups,可以幫助彌補這個缺憾。

Excel012-Vlookup不為人知的秘密

我們先來研究一下Vlookups函數的四個參數:

①查誰?和Vlookup一樣,不再贅述。

②在哪查?這個範圍是指查詢對象所在的列。

③查第幾個?希望查詢相同對象的第幾個信息。

④返回值列號。和Vlookup一樣,不再贅述。

我們來看看下面的案例:如何把A表中一對多的信息轉換成B表中一對一的信息?

Excel012-Vlookup不為人知的秘密

具體操作如下:

1、導入自定義函數模塊。ALT F11 調出VBA 編輯器 →在工程窗口右擊選擇【導入文件】→選擇VBA 模塊文件(自定義函數VLOOKUPS.bas)→關閉VBA 編輯器。

2、在F-I 列添加輔助列寫上1,2,3,4,目的是為了寫公式的時候引用。

3、在F3單元格輸入公式:=vlookups($E3,$A:$A,F$2,2),我們通過四個參數來分析一下這個公式:

①查誰?查詢存貨編碼「B90-A545」。

②在哪查?存貨編碼位於A表的A列。

③查第幾個?我們通過引用上面的F2單元格的值來查詢第一個。

④返回值列號?產品系統號位於從存貨編號開始數的第二列,所以返回值列號為2。

4、將公式向右填充,即可查詢存貨編碼「B90-A545」的第二個、第三個、第四個產品系統號。

5、將公式向下填充即可查詢所有存貨編碼的所有產品系統號。

6、用連接符將查詢出的產品系統號連接起來即可。

奉上動態演示:

Excel012-Vlookup不為人知的秘密

記得一定要先導入模塊再使用這個函數哦,這個函數是自定義函數,不包含在Excel默認的函數裡。

今天的內容就分享到這裡,非常感謝大家一直以來對布衣公子的關注和支持!如果您有任何PPT和Excel技能方面的問題,您可以給公子留言,有可能您的問題就會在下一個影片中出現!