这一刻突然觉得好熟悉,像昨天今天同时在放映……
诸君好,我们今天分享的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帮助文件~
嗯……再见……说晚安……
联系客服