Excel中VLOOKUP函數(shù)的應(yīng)用分析

劉模群

摘要：闡述了VLOOKUP函數(shù)的語法結(jié)構(gòu)，分析了其參數(shù)的使用規(guī)則，對這些參數(shù)使用中出現(xiàn)的問題給出了解決辦法，并用實例加以了實現(xiàn)。

關(guān)鍵詞：VLOOKUP函數(shù)；精確/模糊查找；出錯處理

中圖分類號：TP317 文獻標識碼：A 文章編號：1009-3044（2016）36-0195-03

Application Analysis of VLOOKUP Function in Excel

LIU Mo-qun

（School of Computer & Information Engineering， Changzhou Institute of Technology， Changzhou 213032， China）

Abstract： The grammatical structure of VLOOKUP function is elaborated in this article. The author analyzed the rule of how to use the parameter， proposed some methods to solve the problems aroused the process， and proved it by some examples.

Key words： VLOOKUP function； Exact/fuzzy search； error handling

1 背景

使用Excel不僅可以制作各類表格，而且可以計算、管理和分析表格數(shù)據(jù)。Excel強大的統(tǒng)計、分析功能來自于它的公式和函數(shù)。如果你是單位的財務(wù)人員，可能會被無窮的數(shù)據(jù)折磨得頭昏腦漲。有一個單位會計負責材料、成本，每個月總有那么二天會坐在電腦桌前忙著查找復(fù)制數(shù)據(jù)，更新產(chǎn)品的最新單價。在從其他部門拷貝過來的產(chǎn)品新單價表格里，輸入產(chǎn)品編號點擊查找，然后右鍵復(fù)制其最新的價格，再將其粘到自己的表格里，循環(huán)往復(fù)周而復(fù)始。后來采用VLOOKUP函數(shù)，這項工作就從二天減少到了半個小時。在實際工作中，很多人體會到了使用VLOOKUP函數(shù)的好處，但還是聽到有人抱怨說VLOOKUP有的時候好用，有的時候不好用。其實是他沒有完全搞清楚VLOOKUP的使用規(guī)則，才導(dǎo)致達不到他的查找目標。正是基于這個原因，有必要詳細地分析一下VLOOKUP函數(shù)。

2 函數(shù)的語法結(jié)構(gòu)

VLOOKUP是一個垂直查詢函數(shù)。給定一個查找的目標，搜索指定單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何指定單元格的值。其函數(shù)格式為：

VLOOKUP（Lookup_value，Table_array，Col_index_num，[Range_lookup]）

2.1 參數(shù)說明

Lookup_value查找目標是必選參數(shù)。指要在表格或區(qū)域的第1列搜索到的目標值，就是你指定的要查找的內(nèi)容或單元格引用。

Table_array查找范圍是必選參數(shù)。指定了從哪個范圍中進行查找，一般是指要查找的數(shù)據(jù)所在的單元格區(qū)域，也可以從一個常量數(shù)組或內(nèi)存數(shù)組中查找。

Col_index_num返回值的列號是必選參數(shù)。它是一個整數(shù)值，指出了最終返回數(shù)據(jù)所在的列號。需要注意的是，它是“返回值”在第二個參數(shù)給定的區(qū)域中的列號。

Range_lookup精確/模糊查找是可選參數(shù)。它是一個邏輯值，取值為True或者False，指定希望VLOOKUP查找精確匹配值還是近似匹配值，最后一個參數(shù)是決定函數(shù)精確和模糊查找的關(guān)鍵。如果該參數(shù)為True或被省略，則返回近似匹配值；如果為False，那么將只查找精確匹配值。

2.2 必選參數(shù)使用注意事項

下面以實例來解析一下這四個參數(shù)的使用。

例1：如圖1所示，要求根據(jù)表二中的圖書名稱，查找圖書名稱所對應(yīng)的定價。

第二個參數(shù)“查找范圍”是很容易出錯的地方，必須引起足夠的重視。首先查找區(qū)域的第一列必須是查找目標要搜索的值，本例中查找表二的“圖書名稱”，那么在表一中選定的查找區(qū)域的第一列就必須是圖書名稱，而不能從圖書編號列開始選取查找區(qū)域。即應(yīng)該是$B$3：$C$10，而不能是$A3：$C$10，因為查找的“圖書名稱”不在$A3：$C$10區(qū)域的第一列。其次該查找區(qū)域一定要包含返回值所在的列，本例中要返回的值是定價，表一的C列定價一定要包括在這個查找范圍內(nèi)。

第三個參數(shù)是指查找區(qū)域范圍內(nèi)的第幾列，不是所在工作表中的列號。本例中我們要返回的是“定價”，它是第二個參數(shù)查找范圍$B$3：$C$10的第2列，如果寫成工作表中的列號3就是錯誤的。如果本例中要查找的是圖書名稱所對應(yīng)的出版社，則第2個參數(shù)應(yīng)該設(shè)置為$B$3：$D$10，第3個參數(shù)的值應(yīng)該設(shè)置為3，因為“出版社”在該區(qū)域的第3列中。

3 精確/模糊查找

Range_lookup可選參數(shù)如果指定值是0或FALSE就表示精確查找，如果查找區(qū)域的第1列中有兩個或多個值與Lookup_value匹配，則使用第1個找到的值。而值為1或TRUE時則表示模糊查找，這種情況下如果找不到精確匹配值，則返回小于Lookup_value的最大值。該參數(shù)缺省值為模糊查找，精確即完全一樣，模糊即近似匹配的意思。

3.1 對話框提示中的問題

在使用該函數(shù)的時候我發(fā)現(xiàn)一個問題，就是微軟的Excel中該函數(shù)參數(shù)提示是有問題的。如圖2所示是在Excel2010中，使用VLOOKUP函數(shù)自動彈出的對話框，對話框中明顯指出該參數(shù)“如果為TRUE或忽略，精確匹配”。

為了驗證究竟哪一個說法正確，只能實際上機操作去探究。從上機實踐中就會發(fā)現(xiàn)，該參數(shù)為FALSE是精確匹配，所以我個人認為軟件中的提示是有偏差的。很多人在使用該函數(shù)時總是抱怨時常出錯，這與軟件“提示不當”應(yīng)當有很直接的關(guān)系。

3.2 模糊查找

Range_lookup參數(shù)為FALSE時，因為是精確查找，所以不需要對Table_array查找范圍的第1列中的值進行排序。但是如果該參數(shù)設(shè)置為模糊查找，那么就必須按升序排列Table_array查找區(qū)域第1列中的值，否則VLOOKUP函數(shù)可能無法返回正確的值。

如圖3所示，它是大氣特征表“密度”、“粘度”和“溫度”的對應(yīng)關(guān)系，這些值是在1個大氣壓下或海平面0攝氏度對空氣的測定。

例2：要求根據(jù)表二中的大氣密度，查找表一中大致“密度”所對應(yīng)的“粘度”。

公式中搜索A列中的值1，在A列中由于找不到等于1的精確值，所以使用近似匹配找到小于1的最大值0.946，然后返回同一行中B列的值2.17。單元格F4中的公式也是使用近似匹配搜索A列中的值2，在A列中找到小于等于2的最大值，即最后一行的1.29，然后返回同一行中B列的值1.71。

4 多列查找與函數(shù)的出錯處理

4.1 多列查找時公式的復(fù)制

VLOOKUP函數(shù)的第3個參數(shù)是查找返回數(shù)據(jù)所在的列號，如果我們需要查找返回多列數(shù)據(jù)值時，這個列數(shù)值需要一個一個的更改，例如返回Table_array查找區(qū)域第2列中的值，Col_index_num參數(shù)設(shè)置為2，如果需要返回Table_array第3列中的值，就需要把Col_index_num參數(shù)修改為3，以此類推……，如果需要返回若干列的值就會非常麻煩。那么是否可以讓第3個參數(shù)自動改變呢？向右復(fù)制公式時2自動變?yōu)?、4、5等，這就需要用到COLUMN函數(shù)。

在EXCEL中有一個函數(shù)COLUMN，它可以返回一引用的列號，比如：

=COLUMN（B1） 返回值為2

=COLUMN（C1） 返回值為3

當然，COLUMN（B1）也可以用COLUMN（B2）、COLUMN（B3）等替代。

例3：要求根據(jù)如圖4所示的大氣密度，查找圖3中表一大致“密度”所對應(yīng)的“粘度”和“溫度”。

復(fù)制公式時單元格引用會自動發(fā)生變化，即COLUMN（B1）隨公式向右復(fù)制時會變成COLUMN（C1）、COLUMN（D1） ……，這樣列號2就可以自動轉(zhuǎn)換成數(shù)字3、4……，通過使用COLUMN（B1）轉(zhuǎn)化成可以自動遞增的數(shù)字。由于向右復(fù)制公式時第1個參數(shù)E3會根據(jù)相對位置變成F3，所以“列”要設(shè)置成絕對地址$E3。

4.2 函數(shù)的出錯處理

在進行查找時，如果第三個參數(shù)Col_index_num值小于1，則VLOOKUP返回錯誤值#VALUE??；如果該參數(shù)值大于Table_array的列數(shù)，則VLOOKUP函數(shù)返回錯誤值#REF！。當?shù)谒膫€參數(shù)Range_lookup為FALSE時，進行精確查找，如果找不到精確匹配值，則返回錯誤值#N/A。

有時我們并不希望在文件中出現(xiàn)這些錯誤信息，那如何避免出現(xiàn)這些錯誤值呢？在Excel2003中有一個ISERROR（value）函數(shù)，它用來檢查一個值是否為錯誤（#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或#NULL?。?，返回TRUE或FALSE?？梢岳迷撳e誤處理函數(shù)把錯誤值轉(zhuǎn)換成空值或0，即：

=IF（ISERROR（VLOOKUP（參數(shù)略…））， ""， VLOOKUP（參數(shù)略…） ）

EXCEL2010中提供了一個新函數(shù)IFERROR（value， value_if_error），該函數(shù)的功能是：如果表達式是一個錯誤，則返回value_if_error，否則返回表達式自身的值。該函數(shù)處理起來比ISERROR簡單多了，語句如下：

=IFERROR（VLOOKUP（參數(shù)略…）， ""）

VLOOKUP函數(shù)查找時出現(xiàn)錯誤值，除了上面提到的“實在是沒有所要查找到的值”這個原因外，還有以下幾方面的原因：首先，查找的字符串或被查找的字符中含有空格或看不見的空字符。可以用=號對比驗證一下，如果結(jié)果是FALSE，就表示兩個單元格看上去相同，其實內(nèi)容不同。其次，參數(shù)設(shè)置錯誤。如第二個參數(shù)數(shù)據(jù)源區(qū)域，查找的值不是區(qū)域的第一列，或者需要返回的字段不在區(qū)域里。最后還有可能是數(shù)據(jù)格式不同，如果查找值是文本，而被查找的是數(shù)字類型，就會查找不到。解決方法是將其進行類型轉(zhuǎn)換，文本轉(zhuǎn)換成數(shù)值可以將其*1或/1，數(shù)值轉(zhuǎn)換成文本可以將其&""。

3 結(jié)束語

以上只是對VLOOKUP函數(shù)的應(yīng)用進行了分析，其實Excel提供了大量的、類型豐富的實用函數(shù)，可以通過各種運算符及函數(shù)構(gòu)造出各種公式以滿足各類應(yīng)用的需要。使用者也不僅僅局限于財務(wù)人員，人事檔案人員以及各個行業(yè)各部門的人員也都廣泛地使用著這些函數(shù)，它給我們的工作提供了相當?shù)谋憷瑤臀覀冏龊霉芾矸治?，極大地提高了工作的效率和效果。

參考文獻：

[1] 于雙元. 全國計算機等級考試二級教程——MS Office高級應(yīng)用[M].北京：高等教育出版社，2016.

[2] 馮梅， 吳靜. Vlookup 函數(shù)的應(yīng)用[J].成都航空職業(yè)技術(shù)學(xué)院學(xué)報，2013（4）： 57-59.

[3] 趙春蘭. Excel2010應(yīng)用教程[M].北京：人民郵電出版社，2015.