首页
学习
活动
专区
工具
TVP
发布

Excel自编写MLookup查找函数的高级应用

很多朋友都知道VLOOKUP函数,它是查找数据常用到,但它有一定的局限性。比如:只能查找第一个符合条件的值,无法任意位置查找和多条件查找等。于是,本文用VBA编写了一个功能强大的Mlookup函数。

函数解析:

=Mlookup(查找内容,查找区域,返回值所在的列数,第N个)

语法说明:

查找内容:除了单个值外,还可以选取多个单元格,进行多条件查找。

查找区域:同VLOOKUP

返回值的在列数:同VLOOKUP

第N个:值为1就返回第1个符合条件的,值为2就返回第2个符合条件的....当值为0值时,返回最后1个符合条件的值,值为-1时返回所有查找结果并用逗号连接(新增功能)。

实例:

示例表

1、查找电视的最后一次进货数量,=MLOOKUP(A12,A1:C8,3,0)

查询表1

2、查找电视的第三次进货数量,=MLOOKUP(A14,A1:C8,3,3)

查询表2

3、查找47寸电视的最后一次进货数量,=MLOOKUP(A12:B12,A1:D8,4,0)

查询表3

4、根据产品名称型号实现筛选功能=MLOOKUP($B$10:$B$11,$A$1:$D$8,4,A14)

查询表4

5、实现多结果查询,=MLOOKUP(A11,B$1:C$8,2,-1)

查询5

Mlookup函数使用方法:

Mlookup要想在你的表格中也能使用,需要按下面的步骤操作。按alt+F11(键盘上如果有FN键 ,还需要同时按FN)会打开VBE窗口,在窗口中点插入 - 模块。把下面的代码复制粘贴到右侧的空白区域中。

添加代码

代码:

Function Mlookup(rg, rgs As Range, L As Integer, M As Integer)

Dim arr1, ARR2, 列数

Dim R, n, K, X, cc, sr As String

arr1 = rg.Value

ARR2 = rgs

If VBA.IsArray(arr1) Then

For Each R In arr1

If R "" Then

cc = cc & R

列数 = 列数 + 1

End If

Next R

Else

cc = arr1

End If

If M > 0 Then '非查找最后一个

For X = 1 To UBound(ARR2)

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

K = K + 1

If K = M Then

Mlookup = ARR2(X, L)

Exit Function

End If

End If

Next X

ElseIf M = -1 Then '查找所有值

For X = 1 To UBound(ARR2)

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Mlookup = Mlookup & "," & ARR2(X, L)

End If

Next X

Mlookup = Right(Mlookup, Len(Mlookup) - 1)

Exit Function

Else '查找最后一个

For X = UBound(ARR2) To 1 Step -1

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Mlookup = ARR2(X, L)

Exit Function

End If

Next X

End If

Mlookup = ""

End Function

当前文件另存为“启用宏的工作簿”格式,

启用宏工作簿

然后在这个表格中就可以象本文一样使用Mlookup函数了。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20190907A07IT500?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券