Excel表一个单元格里有多个值,如何匹配

发布网友发布时间：2022-04-25 13:40

共2个回答

热心网友时间：2022-06-27 15:19

Function MVlookup(str As String, ran As Range, k As Integer)
    Dim kval As Integer, i As Integer, j As Integer, arr, arrk(), arr1(), arr2(), arrval As String, h As Integer, z As Integer, ChrI As Integer
    arr = ran.Value
    kval = k
    For i = 1 To Len(str)
         ChrI = InStr(str, Chr(10))
         h = h + 1
         ReDim Preserve arrk(1 To h)
         If ChrI > 1 Then
             arrk(h) = Mid(str, 1, ChrI - 1)
         ElseIf ChrI = 1 Then
             arrk(h) = "龥"
         ElseIf ChrI = 0 And Len(str) = 0 Then
              arrk(h) = "龥"
              Exit For
         Else
             arrk(h) = str
             Exit For
         End If
         str = Mid(str, ChrI + 1, Len(str))
    Next i
       h = 0
       For i = 1 To UBound(arr)
              For j = 1 To Len(arr(i, 1))
                     ChrI = InStr(arr(i, 1), Chr(10))
                     h = h + 1
                     ReDim Preserve arr1(1 To h)
                     ReDim Preserve arr2(1 To h)
                     If ChrI > 1 Then
                         arr1(h) = Mid(arr(i, 1), 1, ChrI - 1)
                         arr2(h) = arr(i, kval)
                     Else
                         arr1(h) = arr(i, 1)
                         arr2(h) = arr(i, kval)
                         Exit For
                     End If
                     arr(i, 1) = Mid(arr(i, 1), ChrI + 1, Len(arr(i, 1)))
              Next j
       Next i

    For i = 1 To UBound(arrk)
        For j = 1 To UBound(arr1)
              If arr1(j) = arrk(i) And Len(arrk(i)) > 0 Then
                     arrval = arrval & vbCrLf & arr2(j)
                     Exit For
              ElseIf arr1(j) <> arrk(i) And j = UBound(arr1) Then
                     arrval = arrval & vbCrLf
              End If
       Next j
    Next i
    MVlookup = Right(arrval, Len(arrval) - 2)
End Function

来个VBA的，函数的不会；需要先alt+F11，插入模块，复制代码；再输入公式例如=MVlookup(A1,B1:D9,3),单元格格式设置为自动换行。

=MVlookup(查找的值,被查找的区域,返回被查找区域的第几列的值)

热心网友时间：2022-06-27 15:20

可以写过滤匹配规则