用自定義函數(shù)VLOOKUPS擴展VLOOKUP函數(shù)的功能

周威

摘 要：Excel有很強的數(shù)據(jù)處理功能，利用其內置的函數(shù)可以幫助我們高效、快速的完成日常工作。文章先是介紹了VLOOKUP 函數(shù)的格式、功能、基本用法，然后采用VBA編程自定義函數(shù)VLOOKUPS，解決了VLOOKUP 函數(shù)遇到的難題，擴展了VLOOKUP 函數(shù)的功能。

關鍵詞：VLOOKUP;VLOOKUPS;自定義函數(shù);查找

VLOOKUP函數(shù)是Excel中的一個縱向查找函數(shù)，它與Sum、If、Countif等函數(shù)一樣在我們的日常工作中都有廣泛應用。例如可以用來核對數(shù)據(jù)，在多個表格之間快速導入數(shù)據(jù)等。VLOOKUP的功能是按列查找，返回該列所需查詢序列對應的值。還有一個HLOOKUP函數(shù)功能與之相同，只是按行查找而已。VLOOKUP雖然好用但也不是萬能的，有些情況下VLOOKUP也無能為力，比如，數(shù)據(jù)區(qū)域有多個符合條件的值，VLOOKUP只能查到第一個滿足條件的值。其實我們可以通過自定義函數(shù)VLOOKUPS來解決這個問題。下面我們通過一個實例來說明。

1 VLOOKUP的語法格式

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

參數(shù)說明：

1）Lookup_value：為需要在數(shù)據(jù)表中進行查找的數(shù)值。可以是為數(shù)值、引用或文本字符串。當vlookup函數(shù)第一參數(shù)省略查找值時，表示用0查找。

2）Table_array：為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表區(qū)域。為避免公式復制時出錯通常該區(qū)域用絕對地址表示。

3）col_index_num：要返回的數(shù)據(jù)位于第二個參數(shù)所表示的區(qū)域中的列數(shù)。比如，要返回第一列數(shù)據(jù)，則該參數(shù)為1，要返回第二列數(shù)據(jù)時就為2，以此類推。如果該參數(shù)小于1，那么函數(shù)就返回錯誤值#VALUE！如果參數(shù)大于 第二個參數(shù)表示區(qū)域的列數(shù)則返回錯誤值#REF！

4）Range_lookup：指明函數(shù)VLOOKUP查找時是精確匹配，還是近似匹配。如果為FALSE或0，則返回精確匹配，如果找不到，則返回錯誤值#N/A。如果為TRUE或1（如省略，則默認為1），函數(shù)VLOOKUP將查找近似匹配值，也就是說，如果找不到精確匹配值，則返回小于查找值的最大數(shù)值。應注意VLOOKUP函數(shù)在進行近似匹配時的查找規(guī)則是從第一個數(shù)據(jù)開始匹配，沒有匹配到一樣的值就繼續(xù)與下一個值進行匹配，直到遇到大于查找值的值，此時返回上一個數(shù)據(jù)（近似匹配時應對查找值所在列進行升序排列）。

實際工作中有人在使用VLOOKUP函數(shù)時經常出錯，明明有這個數(shù)，但找不到或者查找出錯誤值。所以，仍然需要注意以下幾點。

1）在寫完第一行并得到正確結果后先不要急著按填充柄向下拖拉，要先看一下復制公式時要查找的數(shù)和查找的范圍是否有變化，一般查找的數(shù)是要變化的，但查找范圍通常是不變的，所以在公式中查找范圍最好使用絕對地址表示，以免出錯。

2）注意第四個參數(shù)是否正確。

3）注意查找目標是否在查找區(qū)域的第一列。

4）注意查找目標與第一列中的匹配值格式是否一致？不一致時，則必須先轉為一致。如果文本格式轉數(shù)值格式，可以用乘1或加0的方法，如果是數(shù)值格式轉文本格式則可以用TEXT函數(shù)或&””連一個空轉換。

5）注意返回的列是否正確。

6）如果確實目標區(qū)域沒有查找目標，但不想出現(xiàn)錯誤值，可以用IFERROR函數(shù)。

2 VLOOKUP的基本用法

例1：如圖1所示，根據(jù)E列的職工號，在A：B列查找返回部門名稱填入F列。在F2單元格中輸入函數(shù)為：=VLOOKUP（E2，$A$2：$C$16，2，0）。

公式說明：

參數(shù)1：要查找的數(shù)據(jù)，即職工號（S003）。

參數(shù)2：在A2：C16單元格區(qū)域查。為保證其他各行查找時的區(qū)域相同，所以要使用絕對地址。

參數(shù)3：部門名稱在A2：C16區(qū)域的第2列。

參數(shù)4：采用精確查找，所以該參數(shù)為0。

3 自定義函數(shù)VLOOKUPS

VLOOKUP函數(shù)只能返回第一個找到的對應。例如，假設有如圖2所示的數(shù)據(jù)：要查找編號為“S1002”所對應的商品，使用VLOOKUP函數(shù)，在E2單元格輸入=VLOOKUP（D2，$A$2：$B$6，2，0），結果只返回了最先出現(xiàn)的“電視機”。那么如何能查找到所有內容呢？現(xiàn)在我們自己來寫一個自定義函數(shù)實現(xiàn)這個功能。因為查找到的多個結果都是字符，所以我們可以將其拼接在一起。

使用VBA創(chuàng)建自定義函數(shù)VLOOKUPS。在Excel工作表中按【alt】+【F11】（如果是筆記本有FN鍵 ，還需要同時按FN）會打開VBE窗口，在窗口中單擊“插入”/“模塊”。把下面的代碼復制粘貼到右側的空白區(qū)域中，如圖3所示。

代碼如下：

Option Explicit

Function vlookups（rng1 As Range， rng2 As Range， col As Byte， sep As String）

Dim time

time = Timer

Dim region， dict

Set rng1 = rng1（1）

Set dict = CreateObject（“Scripting.Dictionary”）

region = Intersect（rng2， ActiveSheet.UsedRange）

Dim target As String， r As Long

For r = LBound（region， 1） To UBound（region， 1）

If region（r， 1） = rng1.Value Then

target = region（r， col）

If Not dict.Exists（target） Then dict.Add target， “”

End If

Next

vlookups = Join（dict.Keys（）， sep）

Debug.Print （（Timer - time） * 1000） & “ ms”

End Function

原理就是遍歷所查找的內容，將找到的內容依次存入字典，然后使用指定的分隔符esp拼接在一起，此時輸入=VLOOKUP S（D2，A2：B6，2，”/”），前三個參數(shù)與VLOOKUP是一樣的含義，最后一個參數(shù)是分隔符，結果如圖4所示。

4 結束語

通過編寫自定義函數(shù)解決了VLOOKUP只能查到第一個滿足條件的值這個問題，擴展了VLOOKUP的功能。但需要注意的是VLOOKUPS是一個自定義函數(shù)，并不在函數(shù)列表中，需要在使用前在模塊中自已定義。另外，需要將當前文件另存為“啟用宏的工作簿”格式才可以（擴展名為.xlsm）。

參考文獻

[1]神龍工作室.Excel函數(shù)應用500例[M].人民郵電出版社，2006.

[2]HomeE.Excel函數(shù)與公式實戰(zhàn)技巧精粹[M].人民郵電出版社，2008.