Vlookup函數詳解,教你真正認識Excel中的函數

  【摘要】

對於經常使用Excel的人來說,對於Vlookup函數肯定不陌生。Vlookup函數經常被我們用於數據的查找、對比,關於該函數我們很多人都會用,可是用的都是相對比較簡單的功能,那麼在本文中,我們對該函數做個非常詳細和深入的了解認識。

【正文】

Vlookup函數是一個查找函數,它是根據給定的一個查詢值,在指定的範圍中返回最後想要找到的那個值。它的基本語法為:

Vlookup(查詢值,查找範圍,顯示序列,匹配參數)

我們以下方的實例為大家介紹以上四個參數的用法及注意事項。

一基本用法

我們希望根據學號,找到對應的姓名、語數英及總分的成績。

Vlookup函數詳解,教你真正認識Excel中的函數

我們根據Vlookup函數的語法,在B9單元格輸入以下公式:=VLOOKUP($A$9,$A$1:$F$6,2,0)。參數說明為:

查詢值:即為我們希望通過指定的查找內容或單元格。在該例中我們希望通過A9單元格的學號「A001」去查找,所以A9單元格作為我們的查詢值。

查找範圍:

在使用vlookup函數的時候需要特別注意,查詢值必須為範圍的第一列。即我們是根據學號來查詢,所以在我們選擇範圍的時候學號要在該範圍的第一列,即從A列開始計算;

需要顯示的值也必須位於查找範圍中。即本例中的姓名也必須要在我們的範圍裡,所以最後我們的範圍定位在A1到F6整個區域。

顯示序列:即需要顯示的值位於查找範圍的第幾列,而且該值必須為數字。我們最終希望返回的是姓名,姓名位於我們查找範圍A1到F6的第2列,所以在此輸入數字「2」

匹配參數:該參數決定Vlookup函數是精確查詢還是模糊查詢,而且該匹配參數最好不要忽略。匹配參數分為以下兩種情況:

0、false——精確查詢

1、true——模糊查詢

隨後,關於語數英及總分的vlookup函數大家就可以自己完成了。

二進階版

2.1返回多列結果

還是上面的Vlookup函數,難道後面的語數英及總分的函數我需要一個個填寫嗎?這樣,似乎有點太累了。其實,我們發現,只需要在第一個Vlookup函數的基礎上,講「顯示序列」的值做變更,即可將公式往後復制計算結果。因此在這裡我們再加入一個新的函數——Match。

函數語法為:match(查詢值,包含查詢值在內的一行或一列,0)。該函數主要是返回指定的值在指定數組區域中的位置,也屬於查找函數之一。

因此,C9單元格的match函數寫法為:=MATCH(B$8,$A$1:$F$1,0)。我們需要通過B8單元格的「姓名」在A1到F1的行標題上返回第幾個值,最後得到的結果是「2」,然後將該match函數嵌入到B9單元格中,即最後B9單元格的公式為:VLOOKUP($A$9,$A$1:$F$6,MATCH(B$8,$A$1:$F$1,0),0)

2.2多條件查詢

簡單的Vlookup函數能做到單一條件查詢,但是如果像以下的例子,需要同時滿足兩個條件的是否可以借助Vlookup函數來完成呢?

Vlookup函數詳解,教你真正認識Excel中的函數

在本例中,需要同時滿足「所在地區」和「企業名稱」兩個條件,那我們可以利用「&」連接符的,將這兩個條件合併為唯一條件,即變成如下的效果:

Vlookup函數詳解,教你真正認識Excel中的函數

如此一來,我們可以在H列輸入如下的vlookup函數:=VLOOKUP(F2&G2,A:D,4,0)即可做到同時滿足兩個條件的查找。

2.3模糊查詢

一般情況下,我們使用vlookup來做到精確查詢,即返回一一對應的唯一值,那vlookup函數還有另外一個非常常用的功能,及模糊查詢的功能。我們在一開始的基本用法中已經跟大家說了vlookup最後的匹配參數可以是精確查詢(0或者False),也可以是模糊查詢(1或者True)。那麼在上面介紹的例子中已經對精確查詢做了詳細介紹,那麼在這一部分,我們將介紹模糊查詢的用法。

那在什麼情況下,使用vlookup的模糊查詢呢?我們看看以下這個例子:

Vlookup函數詳解,教你真正認識Excel中的函數

在本例中,我們需要根據每個訂單號的貨物金額找到對應的優惠折扣,最後計算折後價格。傳統的方法是通過多層嵌套的if函數來完成,可是if函數嵌套實在太麻煩了,而且很容易出現,那麼在這種情況下,我們就可以利用vlookup的模糊查詢功能來做到了。

使用Vlookup的模糊查詢的前提條件是:查詢範圍的第一列必須為升序排列。外加我們已經知道做模糊查詢的時候最後的參數為1或者True,因此,在C2單元格我們就可以輸入以下函數:=VLOOKUP(B2,$F$2:$G$8,2,1),即可快速的計算得到優惠價格。

Vlookup函數詳解,教你真正認識Excel中的函數

Vlookup函數的模糊查詢的規則是:按照查找範圍的第一列,並且是第一列中向下匹配最接近的那個值,返回對應序列的結果。所以在這裡,針對B2單元格的32380向下匹配,最接近的為20000,那麼返回第二列的值即為5%。如此一來,我們就可以利用簡單的vlookup函數來替換多層嵌套的if函數了。

三高階版

3.1反向查找

一般情況下,Vlookup函數只能從左往右查找數據,如果需要從右往左查找,我們一般需要把數據區域進行移動,如以下的例子,希望通過姓名找到對應的學號:

Vlookup函數詳解,教你真正認識Excel中的函數

在這裡我們可以借助if函數的數組功能將A、B兩列的位置重新組合,然後再利用Vlookup函數從左往右查找。

我們在B9單元格輸入如下公式:=VLOOKUP(A9,IF({1,0},B2:B6,A2:A6),2,0)。在Excel中,1相當於True,0相當於False。所以當1時,它會返回if函數的第二個參數真值及B列,反之返回第三個參數即A列。然後再根據數組運算返回的單元格區域為:{“張三”,”A001″;”李四”,”A002″;”王五”,”A003″;”趙六”,”A004″;”田七”,”A005″}。如此一來便可在這個新的區域中查找,然後返回第二列的值便可。