使用dict优化VLOOKUP参数完全一致

excel中,大量使用vlookup,打开特别慢,打算优化一下。

对于每次查询,使用“表格-范围-查询词”作为key,缓存结果。

在vba中,新建一个模块,复制即可。

使用:把原来的VLOOKUP换成vl,其他参数都不动


Public dict As Object
Function vl(lookup_value As Range, table_array As Range, col_index_num As Integer, range_lookup)
     Dim ws As Worksheet
     Dim pre_key As String  '关键词前缀
     Dim key As String
     Dim result As Variant
     Dim lastRow As Long
     If dict Is Nothing Then
       Set dict = CreateObject("Scripting.Dictionary")
     End If
     'ws.Range(cellname).Value  命名表格的值
     'table_array.Worksheet.Name & "!" & table_array.Address & "!" & col_index_num 关键词,如=vl(A12,波段记录!A:D,2) ->波段记录!$A:$D!2
     Set ws = table_array.Worksheet 
     pre_key = table_array.Worksheet.Name & "!" & table_array.Address & "!" & col_index_num
     key = pre_key & lookup_value.Value
     If Not dict.exists(key) Then
       dict(key) = Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)
     End If
     vl = dict(key)
End Function

posted @ 2025-02-22 10:22  jetz  阅读(33)  评论(0)    收藏  举报