用自定义函数VLOOKUPS扩展VLOOKUP函数的功能

周威

摘 要：Excel有很强的数据处理功能，利用其内置的函数可以帮助我们高效、快速的完成日常工作。文章先是介绍了VLOOKUP 函数的格式、功能、基本用法，然后采用VBA编程自定义函数VLOOKUPS，解决了VLOOKUP 函数遇到的难题，扩展了VLOOKUP 函数的功能。

关键词：VLOOKUP;VLOOKUPS;自定义函数;查找

VLOOKUP函数是Excel中的一个纵向查找函数，它与Sum、If、Countif等函数一样在我们的日常工作中都有广泛应用。例如可以用来核对数据，在多个表格之间快速导入数据等。VLOOKUP的功能是按列查找，返回该列所需查询序列对应的值。还有一个HLOOKUP函数功能与之相同，只是按行查找而已。VLOOKUP虽然好用但也不是万能的，有些情况下VLOOKUP也无能为力，比如，数据区域有多个符合条件的值，VLOOKUP只能查到第一个满足条件的值。其实我们可以通过自定义函数VLOOKUPS来解决这个问题。下面我们通过一个实例来说明。

1 VLOOKUP的语法格式

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

参数说明：

1）Lookup_value：为需要在数据表中进行查找的数值。可以是为数值、引用或文本字符串。当vlookup函数第一参数省略查找值时，表示用0查找。

2）Table_array：为需要在其中查找数据的数据表区域。为避免公式复制时出错通常该区域用绝对地址表示。

3）col_index_num：要返回的数据位于第二个参数所表示的区域中的列数。比如，要返回第一列数据，则该参数为1，要返回第二列数据时就为2，以此类推。如果该参数小于1，那么函数就返回错误值#VALUE！如果参数大于 第二个参数表示区域的列数则返回错误值#REF！

4）Range_lookup：指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为FALSE或0，则返回精确匹配，如果找不到，则返回错误值#N/A。如果为TRUE或1（如省略，则默认为1），函数VLOOKUP将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于查找值的最大数值。应注意VLOOKUP函数在进行近似匹配时的查找规则是从第一个数据开始匹配，没有匹配到一样的值就继续与下一个值进行匹配，直到遇到大于查找值的值，此时返回上一个数据（近似匹配时应对查找值所在列进行升序排列）。

实际工作中有人在使用VLOOKUP函数时经常出错，明明有这个数，但找不到或者查找出错误值。所以，仍然需要注意以下几点。

1）在写完第一行并得到正确结果后先不要急着按填充柄向下拖拉，要先看一下复制公式时要查找的数和查找的范围是否有变化，一般查找的数是要变化的，但查找范围通常是不变的，所以在公式中查找范围最好使用绝对地址表示，以免出错。

2）注意第四个参数是否正确。

3）注意查找目标是否在查找区域的第一列。

4）注意查找目标与第一列中的匹配值格式是否一致？不一致时，则必须先转为一致。如果文本格式转数值格式，可以用乘1或加0的方法，如果是数值格式转文本格式则可以用TEXT函数或&””连一个空转换。

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

6）如果确实目标区域没有查找目标，但不想出现错误值，可以用IFERROR函数。

2 VLOOKUP的基本用法

例1：如图1所示，根据E列的职工号，在A：B列查找返回部门名称填入F列。在F2单元格中输入函数为：=VLOOKUP（E2，$A$2：$C$16，2，0）。

公式说明：

参数1：要查找的数据，即职工号（S003）。

参数2：在A2：C16单元格区域查。为保证其他各行查找时的区域相同，所以要使用绝对地址。

参数3：部门名称在A2：C16区域的第2列。

参数4：采用精确查找，所以该参数为0。

3 自定义函数VLOOKUPS

VLOOKUP函数只能返回第一个找到的对应。例如，假设有如图2所示的数据：要查找编号为“S1002”所对应的商品，使用VLOOKUP函数，在E2单元格输入=VLOOKUP（D2，$A$2：$B$6，2，0），结果只返回了最先出现的“电视机”。那么如何能查找到所有内容呢？现在我们自己来写一个自定义函数实现这个功能。因为查找到的多个结果都是字符，所以我们可以将其拼接在一起。

使用VBA创建自定义函数VLOOKUPS。在Excel工作表中按【alt】+【F11】（如果是笔记本有FN键 ，还需要同时按FN）会打开VBE窗口，在窗口中单击“插入”/“模块”。把下面的代码复制粘贴到右侧的空白区域中，如图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，”/”），前三个参数与VLOOKUP是一样的含义，最后一个参数是分隔符，结果如图4所示。

4 结束语

通过编写自定义函数解决了VLOOKUP只能查到第一个满足条件的值这个问题，扩展了VLOOKUP的功能。但需要注意的是VLOOKUPS是一个自定义函数，并不在函数列表中，需要在使用前在模块中自已定义。另外，需要将当前文件另存为“启用宏的工作簿”格式才可以（扩展名为.xlsm）。

参考文献

[1]神龙工作室.Excel函数应用500例[M].人民邮电出版社，2006.

[2]HomeE.Excel函数与公式实战技巧精粹[M].人民邮电出版社，2008.