VLOOKUP函數基本使用方法及常見問題解析

(2013-11-19 21:04:51)

轉載▼

VLOOKUP，應該是Excel里使用頻率僅次於SUM函數的吧……

所以，這裡優先介紹一下VLOOKUP函數的基本使用方法，以及常見錯誤的簡單解析。

VLOOKUP主要的功能是什麼呢，什麼情況下用這個傳說中的函數？

VLOOKUP在Excel里，屬於查找/索引函數，就是根據你提供的條件，去數據列表/資料庫里把相對應的東西查找並返回來，這也說明一點，那就是使用這個函數的前提是必須有可以能用來查找的數據列表或者資料庫。

深刻了解了這一點，以後有需要查找的問題，首先就要想到VLOOKUP函數，同類的查找函數還是LOOKUP、HLOOKUP，而VLOOKUP是Vertical_LOOKUP的簡寫，HLOOKUP是Horizontal_LOOKUP的簡寫，以區分他們不同的使用環境和情況，常用還是VLOOKUP。

看圖說話，如下圖，A、B、C、D列是我們預先做好的數據列表（資料庫），現在我們希望在F4單元格里輸入名稱，然後右邊的G4、H4單元格就能自動返回該名稱相對應的CODE、PART。

很多Excel新手雖然不知道有查找函數這一回事，但都有這個設想，至於用什麼方法完全沒有概念。

如下，G4單元格只需要將下面的公式複製並粘貼進去，G4單元格就可以顯示「AT002」了：

=VLOOKUP(F4,B:D,2,FALSE)

-------------------

語法介紹：下面我們詳細分析一下VLOOKUP函數的語法和各個參數應該注意的內容。

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

如上為VLOOKUP函數的語法規則，我們用什麼函數，都必須以其語法規則為準則書寫對應的參數，而不是按自己想當然的方式把自己認為可以的東西寫到參數里去。

如上語法，以逗號為分隔，VLOOKUP函數總共有4個參數：

lookup_value，這是第一參數，表示需要查找的內容。

就像我們前面講的例子，F4單元格的「NAME」，就是我們需要在數據列表或資料庫中查找的內容。

table_array，這是第二參數，表示的是你要從中匹配查找並返回結果的數據區域。

上例中我們使用的是B:D，表示B列到D列的全部區域，這是比較簡單快捷的寫法，而負責任一點的寫法，應該是$B$2:$D$13，因為這個才是我們要查找並返回結果的有效數據區域，如果數據區域下方還有別的數據，那最好還是使用後面用具體區域的寫法。

這個參數里有兩點必須注意的：

1>.數據區域的第一列，必須是你第一參數中指定要查找的內容的那個欄位。

可能你看不明白，這樣來說吧，我們例子要找的是「NAME」，那麼第二參數里指定的區域，必須以NAME那一列為第一列，雖然我們的資料庫是A列到D列，但我們在參數里只能寫B:D，而不能寫A:D，因為A:D的第一列是「NO.」，不是我們要查找的「NAME」……

如果我們現在想根據「NO.」返回後面對應的「NAME」，那就可以以A:D作為數據區域。

這裡也引申出一點，就是VLOOKUP只能從左往右查找，如果想從右往左查找，則需要另外構造數據源或者換函數，新人們使用過程中要遵循這一點，不要想當然了。。

2>. 數據區域里，必須包含你要返回的欄位。

例子中要返回的是「CODE」，而B:D列則包括了C列，所以是成立的，如果只寫B:B，就沒辦法返回我們要的值了。

col_index_num，這是第三參數，表示你要返回結果的值（的欄位），在你第二參數里指定的數據區域中的第幾列。

這個很好理解，例子里我們寫的是「2」，因為以NAME為第一列，CODE則是B:D區域中的第2列，那麼後面H4單元格的公式，我們要返回的是PART，你就應該知道這個公式應該怎麼修改了吧？

有時候可能你會看到別人用COLUMN()放在這個參數里，這又表示什麼意思呢？（作為思考題吧）

[range_lookup]，這是第四參數，表示的是要在資料庫中查找數據的匹配方式，有兩種參數可以選擇，TRUE和FALSE，其中True表示模糊查找，False表示精確查找。

1>.這個參數使用了中括弧，表示該參數是可省略的，而省略的時候，默認使用TRUE，也就是模糊查找方式；

2>.True和False我們簡單描述是模糊與精確的關係，但實際上使用True時，要求你要匹配的列，必須是按「升序」排列好的，比如說上例中如果我們最後的參數用的是True，那麼B列的NAME就首先要按從小到大排序好，不然就匹配不到結果，可能結果正確但那也只是碰巧。

而False則沒有排序的這個要求，你的數據可以是亂序的，因此通常情況下，我們使用的都是False。

-------------------------

加個小插曲，如下圖，先看看下面這5個公式，其區別在於第四參數，你能辨識她們各自代表的查找方式是哪一種嗎？她們又有什麼區別呢？

首先，先科普一個小知識，在Excel表格中（與VBA的定義有區別），False能轉化為數值0，而True能轉化為數值1，所以你可以用0表示False，用非0的數值表示True。以此作為背景，我們再來解釋上面這5個公式函數的含義和區別，這個也經常應用在其他很多公式參數的書寫上。

首先，公式1、2、3這三個公式代表的含義和查找方式是一樣的，也就是FALSE的精確查找方式；

後面，公式4、5兩個公式，使用的則是TRUE的模糊查找方式。

有上面的小知識作為解讀的依據，其實就不難明白了，更多疑惑的應該是公式3和公式4，為什麼差距這麼大呢？

首先，公式4已經完全省略了第四個參數，參考語法規則，省略第四參數的情況下，按True進行模糊匹配，這樣說你應該容易理解也沒有異議吧？

那麼第3條公式呢？我們可以注意到，公式3的最後面有一個逗號，這個逗號有跟沒有是有本質上的區別的，有的話就表示我們需要使用第四參數，而不是省略，而後面沒有數值，Excel就按「空值」來處理，而空值轉化為具體的數值，就是0，所以又回到公式2的情況。

這是Excel的智能，也是我們的苦惱，因為有時候當你用VLOOKUP要返回的那一列的值是空的時候，VLOOKUP公式給你提供的結果會是「0」，而不是空白單元格。

語法介紹完了，示例公式里為什麼那樣寫也基本上解讀完了，現在，你試一下自己書寫H4單元格的公式吧！

有興趣可以用你自己書寫的公式回復這條博客哦……

------------------------------

錯誤處理：下面我們說說常見的一些錯誤，並按下面羅列的可能原因按順序排查即可：

1.#N/A - 「數據缺失錯誤」，最直接的解讀就是告訴你，在你指定的數據區域的第一列里，找不到你第一參數說的那個值

-看一下你第二參數指定的區域的第一列，是不是你第一參數要查找的值所在的那一列，有時候可能你選錯區域了；

-第四參數有沒有使用錯誤？如果是True的話，數據匹配區域第一列要按「升序」排列哦；

-第二參數引用的區域，所使用的單元格引用方式是否正確，如果未用絕對引用，下拉填充過程中，查找區域會逐漸變小哦；

-用Excel的「查找」功能，手動在數據列表裡查找一下你第一參數要找的值，看是不是存在；

-確認一下查找值與數據列表的值的數據格式是否一致，有可能你提供的是文本，而資料庫里是數值，或者相反；

-確認一下查找值與數據列表裡的值是否完全一致，有時候一方的值的後面會有多餘的空格或者其他不可見字元；

2. #REF! -「引用無效錯誤」，表示你第三參數指定要返回的第幾列，壓根就沒包括在你第二參數指定的區域里

-怎麼修改就應該一目了然了吧？

--------------------------

錯誤屏蔽：某些情況下，我們寫的公式檢查沒有錯，但數據列表裡確實不存在要查找的值，那就鐵定會返回#N/A錯誤，畢竟我們都喜歡整潔，不想表格里零零落落分布著#N/A錯誤，那可怎麼辦呢，有沒有辦法在公式里屏蔽這樣的錯誤呢？

答案是肯定的，一般人會直接教你用=IF(ISERROR(原公式),"",原公式)，或者=IF(ISNA(原公式),"",原公式)，這什麼意思呢？就是用ISERROR函數，判斷你「原公式」返回的結果是不是錯誤值，如果是就顯示""（空值），也就是什麼都不顯示，如果沒有錯誤，就顯示原公式的結果。

拿上面的例子來說，我們處理後的結果應該是：

=IF(ISNA(VLOOKUP(F4,B:D,2,FALSE)),"",VLOOKUP(F4,B:D,2,FALSE))

當然，我會覺得這樣的公式太長，所以一般會教人使用=IF(COUNTIF(),原公式,"")，這種方式樣寫出來的公式就是：

=IF(COUNTIF(B:B,F4),VLOOKUP(F4,B:D,2,FALSE),"")

而自從07版新增IFERROR函數之後，我們在xlsx格式的文檔中寫容錯公式就可以更簡潔了，直接寫為：

=IFERROR(原公式,"")

如：=IFERROR(VLOOKUP(F4,B:D,2,FALSE),"")

----------------------------

另外說一下，源數據里NO.5和NO.12的NAME是一樣的，如果我們同樣使用「Michel」來查詢後面的值，返回的只會是排在前面的NO.5的記錄哦，這一對一查找也算是VLOOKUP的一個特性，因此用VLOOKUP查找時有多種查詢關鍵值時最好是使用如號碼、證件號等有唯一性的關鍵詞，而姓名、地區等可能有重名的則不能為首選，不然會徒勞無功哦。

至於一對多的查找，那需要用複合函數嵌套，如典型的INDEX+IF+SMALL+MATCH+ROW~~~

語法和使用其實很簡單，但要注意的小細節也非常多，這裡也沒辦法一一列舉，比如說如何確認查找值與被查找值的數據類型是否一致，當我們希望從右往左查詢時怎麼變換？這都需要其他方面的綜合經驗，因此需要多用、甚至多犯錯才能吸收，才能運用自如。

VLOOKUP的姐妹函數HLOOKUP語法和使用均是一樣的，是當你的源數據列表的形式是「轉置」的形式時使用，騷年，加油吧……VLOOKUP是把利器啊，靈活運用可以大大提高工作效率，比如說對比兩列數據是否一致！

推薦閱讀：