wps自带m lookup函数公式吗？
发布网友
发布时间：2022-04-22 15:59
共3个回答
热心网友
时间：2023-11-11 17:19
你可以根据你的需要定制一个查找函数
比如：=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 String
arr1 = rg.Value
If L > 0 Then arr2 = rgs
If L < 0 Then
arr3 = rgs
arr2 = rgs.Offset(0, L).Resize(UBound(arr3), UBound(arr3, 2) - L) 'UBound(arr3, 2)是arr3的列数，rgs需要扩展范围，将左侧L列加入其中，如原来是B2:B3，L是-1，那么扩展后就是A2:B3
End If
If VBA.IsArray(arr1) Then
For Each R In arr1
If R <> "" Then
cc = cc & R '查找值为多个单元格合并
columnn = columnn + 1
End If
Next R
Else
cc = arr1
End If
If M > 0 And L > 0 Then '非查找最后一个
For X = 1 To UBound(arr2) 'x是数组的行数
sr = ""
If columnn > 1 Then
For q = 1 To columnn 'q是数组中列的范围
sr = sr & arr2(X, q)
Next q
Else
sr = arr2(X, 1)
End If
If P = 0 And sr = cc Then
K = K + 1
If K = M Then
MLOOKUP = arr2(X, L)
Exit Function
End If
End If
If P = 1 And sr Like "*" & cc & "*" Then
K = K + 1
If K = M Then
MLOOKUP = arr2(X, L)
Exit Function
End If
End If
Next X
ElseIf M > 0 And L < 0 Then '非查找最后一个
For X = 1 To UBound(arr2) 'x是数组的行数
sr = ""
If columnn > 1 Then
For q = 1 To columnn 'q是数组中列的范围，查找值是合并的，sr就是指查找值
sr = sr & arr2(X, q - L) 'rgs已经拓展，查找列所在的位置发生变化，需要加上L列，因L是负数，使用-L转换为正数
Next q
Else
sr = arr2(X, 1 - L) '查找值所处的位置，从拓展范围后的rgs数组的最左侧算起，
End If
If P = 0 And sr = cc Then '查找值是单个
K = K + 1
If K = M Then
MLOOKUP = arr2(X, 1)
Exit Function
End If
End If
If P = 1 And sr Like "*" & cc & "*" Then '查找值是单个
K = K + 1
If K = M Then
MLOOKUP = arr2(X, 1)
Exit Function
End If
End If
Next X
ElseIf M = -1 And L > 0 Then '查找所有值
For X = 1 To UBound(arr2)
sr = ""
If columnn > 1 Then
For q = 1 To columnn
sr = sr & arr2(X, q)
Next q
Else
sr = arr2(X, 1)
End If
If P = 0 And sr = cc Then
MLOOKUP = MLOOKUP & "," & arr2(X, L)
End If
If P = 1 And sr Like "*" & cc & "*" Then
MLOOKUP = MLOOKUP & "," & arr2(X, L)
End If
Next X
MLOOKUP = Right(MLOOKUP, Len(MLOOKUP) - 1)
Exit Function
ElseIf M = -1 And L < 0 Then '查找所有值
For X = 1 To UBound(arr2)
sr = ""
If columnn > 1 Then
For q = 1 To columnn
sr = sr & arr2(X, q - L)
Next q
Else
sr = arr2(X, 1 - L)
End If
If P = 0 And sr = cc Then
MLOOKUP = MLOOKUP & "," & arr2(X, 1)
End If
If P = 1 And sr Like "*" & cc & "*" Then
MLOOKUP = MLOOKUP & "," & arr2(X, 1)
End If
Next X
MLOOKUP = Right(MLOOKUP, Len(MLOOKUP) - 1)
Exit Function
Else '查找最后一个
If L > 0 And M = 0 Then
For X = UBound(arr2) To 1 Step -1
sr = ""
If columnn > 1 Then
For q = 1 To columnn
sr = sr & arr2(X, q)
Next q
Else
sr = arr2(X, 1)
End If
If P = 0 And sr = cc Then
MLOOKUP = arr2(X, L)
Exit Function
End If
If P = 1 And sr Like "*" & cc & "*" Then
MLOOKUP = arr2(X, L)
Exit Function
End If
Next X
End If
If L < 0 And M = 0 Then
For X = UBound(arr2) To 1 Step -1
sr = ""
If columnn > 1 Then
For q = 1 To columnn
sr = sr & arr2(X, q - L)
Next q
Else
sr = arr2(X, 1 - L)
End If
If P = 0 And sr = cc Then
MLOOKUP = arr2(X, 1)
Exit Function
End If
If P = 1 And sr Like "*" & cc & "*" Then
MLOOKUP = arr2(X, 1)
Exit Function
End If
Next X
End If
End If
MLOOKUP = ""
End Function
热心网友
时间：2023-11-11 17:19
LOOKUP函数我倒是知道，EXCEL上用的，但WPS的函数跟EXCEL函数有的并不套用，需要你单独设计或在网上找模板，通常来说只要是付费用户，大把模板够你用的。
热心网友
时间：2023-11-11 17:20
你不如说说你需要什么需求，最好能提供样例文件
