MLOOKCUP怎么安装，我用的是WPS？

MLOOKCUP怎么安装，我用的是WPS？

MLookup是自定义函数你可以根据你的需要定制一个查找函数比如：=Mlookup(查找内容，查找区域，返回值所在的列数，第N个)这个函数是用于查找某列第N次出现的数值也有增强版，增加了逆序查找和模糊查找功能=MLOOKUP(rg,rgs,L,M,P)rg：是查找值，可以是单元格或数据区域，如B2或B2:C2。rgs：是数据区域，尽量缩小范围，写成B2:D9这种形式，不要用B:D，否则可能造成卡顿。L：是第几列，正数表示从左往右查询，负数表示从右往左查询。M：是第几个重复值，0是最后一个重复值，-1表示合并重复值，用英文逗号分隔。P：为0或省略是精确查找，为1是模糊查找，也就是查找值类似"*"&A1&"*"，仅支持单个单元格。如出现错误值，返回空白。这是增强版代码：Function MLOOKUP(rg, rgs As Range, L As Integer, M As Integer, Optional P As Integer = 0)Dim arr1, arr2, arr3, columnn 'columnn是列数Dim R, n, K, X, cc, sr As Stringarr1 = rg.ValueIf L > 0 Then arr2 = rgsIf L < 0 Thenarr3 = rgsarr2 = rgs.Offset(0, L).Resize(UBound(arr3), UBound(arr3, 2) - L) 'UBound(arr3, 2)是arr3的列数，rgs需要扩展范围，将左侧L列加入其中，如原来是B2:B3，L是-1，那么扩展后就是A2:B3End IfIf VBA.IsArray(arr1) ThenFor Each R In arr1If R <> "" Thencc = cc & R '查找值为多个单元格合并columnn = columnn + 1End IfNext RElsecc = arr1End IfIf M > 0 And L > 0 Then '非查找最后一个For X = 1 To UBound(arr2) 'x是数组的行数sr = ""If columnn > 1 ThenFor q = 1 To columnn 'q是数组中列的范围sr = sr & arr2(X, q)Next qElsesr = arr2(X, 1)End IfIf P = 0 And sr = cc ThenK = K + 1If K = M ThenMLOOKUP = arr2(X, L)Exit FunctionEnd IfEnd IfIf P = 1 And sr Like "*" & cc & "*" ThenK = K + 1If K = M ThenMLOOKUP = arr2(X, L)Exit FunctionEnd IfEnd IfNext XElseIf M > 0 And L < 0 Then '非查找最后一个For X = 1 To UBound(arr2) 'x是数组的行数sr = ""If columnn > 1 ThenFor q = 1 To columnn 'q是数组中列的范围，查找值是合并的，sr就是指查找值sr = sr & arr2(X, q - L) 'rgs已经拓展，查找列所在的位置发生变化，需要加上L列，因L是负数，使用-L转换为正数Next qElsesr = arr2(X, 1 - L) '查找值所处的位置，从拓展范围后的rgs数组的最左侧算起，End IfIf P = 0 And sr = cc Then '查找值是单个K = K + 1If K = M ThenMLOOKUP = arr2(X, 1)Exit FunctionEnd IfEnd IfIf P = 1 And sr Like "*" & cc & "*" Then '查找值是单个K = K + 1If K = M ThenMLOOKUP = arr2(X, 1)Exit FunctionEnd IfEnd IfNext XElseIf M = -1 And L > 0 Then '查找所有值For X = 1 To UBound(arr2)sr = ""If columnn > 1 ThenFor q = 1 To columnnsr = sr & arr2(X, q)Next qElsesr = arr2(X, 1)End IfIf P = 0 And sr = cc ThenMLOOKUP = MLOOKUP & "," & arr2(X, L)End IfIf P = 1 And sr Like "*" & cc & "*" ThenMLOOKUP = MLOOKUP & "," & arr2(X, L)End IfNext XMLOOKUP = Right(MLOOKUP, Len(MLOOKUP) - 1)Exit FunctionElseIf M = -1 And L < 0 Then '查找所有值For X = 1 To UBound(arr2)sr = ""If columnn > 1 ThenFor q = 1 To columnnsr = sr & arr2(X, q - L)Next qElsesr = arr2(X, 1 - L)End IfIf P = 0 And sr = cc ThenMLOOKUP = MLOOKUP & "," & arr2(X, 1)End IfIf P = 1 And sr Like "*" & cc & "*" ThenMLOOKUP = MLOOKUP & "," & arr2(X, 1)End IfNext XMLOOKUP = Right(MLOOKUP, Len(MLOOKUP) - 1)Exit FunctionElse '查找最后一个If L > 0 And M = 0 ThenFor X = UBound(arr2) To 1 Step -1sr = ""If columnn > 1 ThenFor q = 1 To columnnsr = sr & arr2(X, q)Next qElsesr = arr2(X, 1)End IfIf P = 0 And sr = cc ThenMLOOKUP = arr2(X, L)Exit FunctionEnd IfIf P = 1 And sr Like "*" & cc & "*" ThenMLOOKUP = arr2(X, L)Exit FunctionEnd IfNext XEnd IfIf L < 0 And M = 0 ThenFor X = UBound(arr2) To 1 Step -1sr = ""If columnn > 1 ThenFor q = 1 To columnnsr = sr & arr2(X, q - L)Next qElsesr = arr2(X, 1 - L)End IfIf P = 0 And sr = cc ThenMLOOKUP = arr2(X, 1)Exit FunctionEnd IfIf P = 1 And sr Like "*" & cc & "*" ThenMLOOKUP = arr2(X, 1)Exit FunctionEnd IfNext XEnd IfEnd IfMLOOKUP = ""End Function