我正在使用 VLookup 函数来查找列中存在的多个值。这种方法效果很好，但需要花费很多时间，因为 Excel 工作表中有 100,000 行。
有什么办法可以加快这段代码吗？
该代码基本上查找列中的特定值并获取偏移量。简单 VLookup 与此之间的区别在于，如果有多行具有相同的查找值，则它会获取所有元素。
Function VLookupAll(ByVal lookup_value As String, _
ByVal lookup_column As Range, _
ByVal return_value_column As Long, _
Optional seperator As String = ", ") As String
Dim i As Long
Dim result As String
For i = 1 To lookup_column.Rows.Count
If Len(lookup_column(i, 1).Text) <> 0 Then
If lookup_column(i, 1).Text = lookup_value Then
result = result & (lookup_column(i).Offset(0, return_value_column).Text & seperator)
End If
End If
Next
If Len(result) <> 0 Then
result = Left(result, Len(result) - Len(seperator))
End If
VLookupAll = result
End Function
这比简单循环快大约 20-30 倍（在包含 20k 个值的列上进行测试，其中有 3 个与正在搜索的值匹配）。
'rng: a single-column range to search for matches
'val: the value to match on
'col: offset from match in rng to concatenate values from (similar
' to the matching VLOOKUP argument)
Function MultiLookup(rng As Range, val As String, col As Long)
Dim i As Long, v, s
Dim r As Long
r = rng.Cells.Count
v = Application.Match(val, rng, 0)
s = ""
Do While Not IsError(v)
s = s & IIf(s <> "", ",", "") & rng.Cells(v).Offset(0, col - 1).Value
r = r - v
Set rng = rng.Offset(v, 0).Resize(r, 1)
v = Application.Match(val, rng, 0)
Loop
MultiLookup = s
End Function
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
