標籤:

這也許是史上最好最全的VLOOKUP函數教程

Excel不加班,讓你不再因為Excel問題而加班。如果你有Excel問題不能解決可以私聊我,我將用心替你解決。如果你喜歡盧子寫的原創文章,請分享給你的朋友,讓更多人一起進步!

函數中最受歡迎的有三大家族,一個是以SUM函數為首的求和家族,一個是以VLOOKUP函數為首的查找引用家族,另外一個就是以IF函數為首的邏輯函數家族。根據二八定律,學好這三大家族的函數,就能完成80%的工作。

現在一起來學習VLOOKUP函數,讓關於查找的煩惱一次全解決!

1、根據番號精確查找俗稱。

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

VLOOKUP函數語法:

=VLOOKUP(查找值,查找區域,返回查找區域第N列,查找模式)

VLOOKUP函數示意圖。

2、屏蔽錯誤值錯誤值查找。

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

VLOOKUP函數如果查找不到對應值會顯示錯誤值#N/A,這個看起來很不美觀。這時可以在外面加個容錯函數IFERROR,如果是2013版本那就更好,可以用IFNA函數,這個是專門處理#N/A這種錯誤值。

=IFERROR(VLOOKUP(D2,A:B,2,0),"")

=IFNA(VLOOKUP(D2,A:B,2,0),"")

函數語法:

=IFERROR(表達式,錯誤值要顯示的結果)

說白了就是將錯誤值顯示成你想要的結果,不是錯誤值就返回原來的值。IFNA函數的作用也是一樣,只是IFERROR函數是針對所有錯誤值,而IFNA函數只針對#N/A。

3、按順序返回多列對應值。

通過上面的例子,我們知道可以通過更改第3參數,返回各項對應值如:

=VLOOKUP($A13,$A$1:$F$10,2,0)

=VLOOKUP($A13,$A$1:$F$10,3,0)

如果項目少,更改幾次參數也沒什麼,但項目多時,肯定不方便。如圖 5?103所示,可以通過ROW、COLUMN產生行列號,從而得到1,2,……,n的值。

=VLOOKUP($A13,$A$1:$F$10,COLUMN(B1),0)

因為這裡是同一行產生序號,所以用COLUMN函數。

4、按不同順序返回對應值。

這回看來只能手動更改第3參數了,COLUMN完全派不上用場。

NO!每當你覺得操作繁瑣時,就要停下來思考,也許Excel本身存在這個功能,只是自己一時想不到或者不知道而已。列號不管千變萬化,在數據源的位置始終不變,利用這個特點可以去搜索一下看看有什麼函數可以解決。

在「搜索函數」文本框輸入:位置,單擊「轉到」按鈕,就會出現跟位置有關的函數,查看每個函數的說明,找到我們需要的,如MATCH函數,返回符合特定值特定順序的項在數組中的相應位置,單擊「確定」按鈕。

在彈出的「函數參數對話框」中嘗試填寫相應的參數,每個參數的作用下面都有相關說明,填寫後會出現計算結果3,也就是訂單數在區域中是第3列。嘗試下更改第1參數為C12(俗稱),計算結果是2,也就是區域中第2列。經過嘗試,知道這個函數是我們要找的那個函數,單擊「取消」按鈕,返回工作表。

在單元格再做最後一次驗證。

到這一步已經十拿九穩了,將公式設置為:

=VLOOKUP($A13,$A$1:$F$10,MATCH(B$12,$A$1:$F$1,0),0)

5、根據番號逆序俗稱。

幫助提到VLOOKUP函數只能按首列查找,不能逆向查找,既然如此,那就得想辦法將非首列的區域轉換成首列。怎麼轉換區域呢,這時IF函數就派上用場。一步步來了解IF函數的轉換。

看看好友傳遞如何趣聊IF函數,吃貨的福音。

IF函數其實只有一個條件來判斷是否符合條件,返回FALSE和TRUE兩種結果。

當菜只有分甜的或鹹的2種口味時,甜味是紅燒肉,鹹味是醬油肉。

盲人吃飯時,看不到是什麼菜。當別人問盲人:「你現在吃的什麼菜? 是鹹的嗎?如果是鹹的,就是醬油肉,如果不是鹹的就是紅燒肉。」(給定判斷條件:鹹味)盲人剛好在吃紅燒肉,於是就咂吧著嘴說:「恩,好吃,不是鹹的!是紅燒肉」(根據提問的要求,不符合鹹的)假如要是盲人當時是在吃醬油肉呢,一定回答;「是的,鹹的,是醬油肉」(條件為真,是!TRUE)。盲人根據口感,結合提問者說的條件,就知道自己吃的是紅燒肉還是醬油肉了。

把這段話用公式來寫:

=IF(A1="鹹的",A2,B2)

翻譯:是鹹的嗎?要是(TRUE),就是醬油肉,要是不是鹹的(FALSE),就是甜的紅燒肉。

A1="鹹的"這個條件也可以直接換成TRUE或者FALSE。

=IF(TRUE,A2,B2)

因為滿足條件,所以返回A2的對應值醬油肉。

=IF(FALSE,A2,B2)

因為不滿足條件,所以返回B2的對應值紅燒肉。

其實TRUE=1,FALSE=0,所以可以直接用1跟0表示。

=IF(1,A2,B2)

=IF(0,A2,B2)

IF函數不止可以返回1個單元格的值,也可以返回多個單元格的值。

=IF({1,0},A2,B2)

=IF({0,1},A2,B2)

選擇兩個單元格輸入,按Ctrl Shift Enter三鍵結束。條件為{1,0},返回A2:B2的對應值順序不變;條件為{0,1},返回A2:B2的對應值,順序對換。也就是說通過改變1跟0的位置,可以調換兩單元格的前後位置。

看到這裡,知道IF函數通過改變1,0可以調換單元格的順序,如果要改變區域的順序也是可以實現的。

用IF函數重新構造的新區域,是多單元格數組公式,記得按Ctrl Shift Enter三鍵結束,否則出錯。

新區域:

=IF({1,0},B2:B10,A2:A10)

所以公式可以變成:

=VLOOKUP(A13,新區域,2,0)

兩個公式合併,大功告成。

=VLOOKUP(A13,IF({1,0},$B$2:$B$10,$A$2:$A$10),2,0)

6、根據俗稱跟訂單號兩個條件查詢完成情況。

正常情況下VLOOKUP函數是不能多條件查詢,通過IF函數的學習,我們知道IF函數可以重新構造區域,這裡就再次用IF構成一個區域。

新區域:

=IF({1,0},A2:A9&C2:C9,E2:E9)

所以公式可以變成:

=VLOOKUP(A12&B12,新區域,2,0)

兩個公式合併,大功告成,記得按Ctrl Shift Enter三鍵結束。

=VLOOKUP(A12&B12,IF({1,0},$A$2:$A$9&$C$2:$C$9,$E$2:$E$9),2,0)

7、根據俗稱的第一個字元查找番號。

=VLOOKUP(D2&"*",A:B,2,0)

星號(*)是通配符,代表所有字元,問號(?)代表一個字元。D2&"*"就是開頭包含D2的意思。

8、根據區域判斷成績的等級。

藉助輔助列的話,很容易查詢等級,只需將VLOOKUP函數的第四參數設置為1或者省略即可。

=VLOOKUP(E2,A:C,3)

如果不用輔助列,估計很多人看到這條公式就得哭了,得結合前面所有函數知識才能完成,有興趣的朋友可以自己去研究。

=VLOOKUP(E2,IF({1,0},--LEFT(B$2:B$5,FIND("-",B$2:B$5)-1),C$2:C$5),2)

前陣子無意間發現了IMREAL函數,所以不用輔助列的數組公式可以稍微簡單一點。

=VLOOKUP(E2,IF({1,0},IMREAL(B$2:B$5&"i"),C$2:C$5),2)

IMREAL函數是計算複數的實部係數的函數,作用就是提取區間的下限。

通過這8個疑難,基本上的查詢問題都能夠解決。

開心嗎?一下搞定8大疑難!

盧子簡介:20萬讀者支持的《Excel效率手冊 早做完,不加班》系列叢書作者,十年的Excel職場經驗,精通Excel函數和數據透視表。

推薦閱讀:

從上往下織簡單的寶寶短袖開衫外套教程(千絲萬縷的愛手工)
毛線棉鞋(教程)--稱為新款棉鞋
從上往下織的詳細教程!你都會嗎?
圖文教程:工筆花卉設色技法(1)
PS教程:黑白照片的調色技巧

TAG:函數 | 教程 |