APP下载

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

2020-11-30周威

卷宗 2020年22期

周威

摘 要: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.