大数跨境
首页
>
【教你一招】Mlookup 函数到底有多利害
>

【教你一招】Mlookup 函数到底有多利害

【教你一招】Mlookup 函数到底有多利害 裕灌音
2017-02-11
2
导读：Vlookup是最常用到的查找函数，但它有很大的局限性。比如：只能查找第一个符合条件的值，无法任意位置查找和

Vlookup是最常用到的查找函数，但它有很大的局限性。比如：只能查找第一个符合条件的值，无法任意位置查找和多条件查找等。于是，兰色用VBA编写了一个功能强大的Mlookup函数。可以实现：


  • 查找第N个

  • 查找最后一个

  • 多条件查找

  • 一对多查找

  • 一对多查找后合并


一、用法介绍


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


语法说明：

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

  • 查找区域：同VLOOKUP

  • 返回值的在列数：同VLOOKUP

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


二、功能演示。


【例】如下图所示的入库表中，要求完成以下查找。


1、查找第2次电视的进货数量。


=Mlookup(A11,A2:D8,4,2)


2、查找电视的最后一次入库数量


=Mlookup(A11,A2:D8,4,0)


3、查找47寸电视的第1次进货数量。


=Mlookup(A11:B11,A2:D8,4,1)

4、实现筛选功能。


=Mlookup($B$10:$B$11,$A$1:$D$8,4,A14)


5、实现多结果查找功能。（把所有符合条件结果用逗号连接起来）


=MLOOKUP(A11,B$1:C$8,2,-1)



三、使用方法


Mlookup要想在你的表格中也能使用，需要按下面的步骤操作。


1、按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

End Function


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


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

注：如果你想在电脑中所有Excel文件中使用，可以保存为加截宏。但...在其他电脑上公式就会出错。所以想用这个自定义函数，还是复制代码吧。


阅读原文↓↓↓↓↓↓


【声明】内容源于网络
0
0
裕灌音
裕灌公司企业文化宣达，重大事件报道，团体活动记录。同时，作为员工提升管理知识的平台以及提升品牌影响力的渠道之一。
内容 213
粉丝 0
裕灌音 裕灌公司企业文化宣达，重大事件报道，团体活动记录。同时，作为员工提升管理知识的平台以及提升品牌影响力的渠道之一。
总阅读72
粉丝0
内容213