excel表格_excel函数公式大全_execl从入门到精通
「Excel实用技巧」Mlookup函数来了！比Vlookup好用多了！2024-08-09 09:15:33
最近总有很多同学提问Vlookup无法查找的问题，今天就翻出原来编写自定义函数MLookup，希望对这些同学有用。
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 Stringarr1 = rg.ValueARR2 = rgsIf VBA.IsArray(arr1) ThenFor Each R In arr1If R <> "" Thencc = cc & R列数 = 列数 + 1End IfNext RElsecc = arr1End IfIf M > 0 Then '非查找最后一个For X = 1 To UBound(ARR2)sr = ""If 列数 > 1 ThenFor q = 1 To 列数sr = sr & ARR2(X, q)Next qElsesr = ARR2(X, 1)End IfIf sr = cc ThenK = K + 1If K = M ThenMlookup = ARR2(X, L)Exit FunctionEnd IfEnd IfNext XElseIf M = -1 Then '查找所有值For X = 1 To UBound(ARR2)sr = ""If 列数 > 1 ThenFor q = 1 To 列数sr = sr & ARR2(X, q)Next qElsesr = ARR2(X, 1)End IfIf sr = cc ThenMlookup = Mlookup & "," & ARR2(X, L)End IfNext XMlookup = Right(Mlookup, Len(Mlookup) - 1)Exit FunctionElse '查找最后一个For X = UBound(ARR2) To 1 Step -1sr = ""If 列数 > 1 ThenFor q = 1 To 列数sr = sr & ARR2(X, q)Next qElsesr = ARR2(X, 1)End IfIf sr = cc ThenMlookup = ARR2(X, L)Exit FunctionEnd IfNext XEnd IfMlookup = ""End FunctionEnd Function
2、当前文件另存为“启用宏的工作簿”格式，然后在这个表格中就可以像一样使用Mlookup函数了。
注：如果你想在电脑中所有Excel文件中使用，可以保存为加截宏。但...在其他电脑上公式就会出错。所以想用这个自定义函数，还是复制代码吧。
Excel解释说明：VBA就是这么帅，需要什么函数就编写它。学会VBA后，你会发现excel变得无所不能！
标签: In函数excel