VBA常用小代码305:数据模糊匹配查询

诸君好，我们今天分享的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函数的语法和用法，上期我们讲过了。链接：

原文中此处为链接，暂不支持采集

。

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帮助文件~

嗯……再见……提前说晚安……