周威

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

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

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

1 VLOOKUP的語(yǔ)法格式

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

參數(shù)說(shuō)明：

1）Lookup_value：為需要在數(shù)據(jù)表中進(jìn)行查找的數(shù)值?？梢允菫閿?shù)值、引用或文本字符串。當(dāng)vlookup函數(shù)第一參數(shù)省略查找值時(shí)，表示用0查找。

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

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

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

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

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

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

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

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

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

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

2 VLOOKUP的基本用法

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

公式說(shuō)明：

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

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

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

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

3 自定義函數(shù)VLOOKUPS

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

使用VBA創(chuàng)建自定義函數(shù)VLOOKUPS。在Excel工作表中按【alt】+【F11】（如果是筆記本有FN鍵 ，還需要同時(shí)按FN）會(huì)打開(kāi)VBE窗口，在窗口中單擊“插入”/“模塊”。把下面的代碼復(fù)制粘貼到右側(cè)的空白區(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

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

4 結(jié)束語(yǔ)

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

參考文獻(xiàn)

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

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