【Excel VBA】如何處理數據模糊查詢與匹配?

如下圖所示，根據A:C列的數據源信息，查詢E列人名相應的考試成績，如果查無結果，則返回空白。

此類問題常用的解決方法有三種。一種是單元格的Find方法，一種是VBA函數instr，還有一種則是搭配通配符的like語句。

1，Find方法：

Sub RngFind()
    Dim Rng As Range, cll As Range
    Dim arr, i&
    Set Rng = Range("b1:c" & Cells(Rows.Count, 2).End(xlUp).Row)
    '數據源賦值Rng
    arr = Range("e1:f" & Cells(Rows.Count, 5).End(xlUp).Row)
    '查詢區域裝入數組arr
    For i = 2 To UBound(arr) '遍歷查詢區域
        Set cll = Rng.Find(arr(i, 1), lookat:=xlPart) 'xlpart模糊匹配模式
        If Not cll Is Nothing Then
        '如果有查找到相應單元格則cll必然非nothing，那麼
            arr(i, 2) = cll.Offset(0, 1)
            '對查找到的單元格通過offset偏移取值
        Else
            arr(i, 2) = "" '否則返回空文本
        End If
    Next
    With Range("e1:f" & Cells(Rows.Count, 5).End(xlUp).Row)

        .NumberFormat = "@"
        '設置單元格文本格式，避免文本數值變形
        .Value = arr
        '將arr放回單元格區域
    End With
    MsgBox "ok"
End Sub

2，Instr和Like方法：

Sub ArrInstrOrLike()
    Dim arr, brr, i&, j&
    arr = Range("a1:c" & Cells(Rows.Count, 2).End(xlUp).Row)
    '數據源裝入數組arr
    brr = Range("e1:f" & Cells(Rows.Count, 5).End(xlUp).Row)
    '查詢區域裝入數組brr
    For i = 2 To UBound(brr)
    '遍歷查詢區域
        brr(i, 2) = ""
        '清空原結果
        For j = 1 To UBound(arr)
            If InStr(1, arr(j, 2), brr(i, 1), vbTextCompare) Then
                'Instr函數，vbTextCompare不區分字母大小寫，上期我們講過了,此外也可以使用like語句(like語句區分字母大小寫)：
                'If arr(j, 2) Like "*" & brr(i, 1) & "*" Then
                brr(i, 2) = arr(j, 3)
                Exit For '找到結果後，退出遍歷arr
            End If
        Next
    Next
    With Range("e1:f" & Cells(Rows.Count, 5).End(xlUp).Row)
        .NumberFormat = "@" '文本格式，防止文本數值變形
        .Value = brr '將arr放回單元格區域
    End With
    MsgBox "ok"
End Sub

小貼士：

1，Instr函數的語法和用法，上期我們講過了。連結：VBA常用小代碼101：批量改變單元格部分字符格式。

2，LIKE語句區分字母大小寫，例如：MsgBox "MyToToOFFICE" Like "*Office*"，結果為False。

3，Find方法常用於單元格對象,Instr和Like則常用於字符串計算。前者的優勢是可以不指定查詢值在查詢範圍中的具體列數，而是直接使用單元格區域甚至工作表作為查詢區域，例如此例中的Set Rng = Range("b1:c" & Cells(Rows.Count, 2).End(xlUp).Row)。後兩者雖然需要指定查詢條件列……但它們更容易處理多條件模糊匹配查詢的問題。例如將查詢條件修改成班級為Office，姓名為星光的考試成績，使用Instr或Like語句要明顯比Find更為簡單。比如：

If arr(j, 1) & arr(j, 2) Like "*Office*星光*" Then

4，應用於Range對象的Find方法，在區域中查找特定信息，並返回 Range對象，該對象代表用於查找信息的第一個單元格。如果未發現匹配單元格，就返回 Nothing。該方法不影響選定區域或活動單元格。其語法如下：

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SerchFormat)

該方法參數眾多，但常用的不過是What（找誰？）和LookAt(xlWhole精確查詢或 xlPar模糊查詢)，該方法具體可以參考Office幫助文件~

圖文作者：看見星光