如何将Vlookup/Index Match转为数组式VBA，提升6000+行匹配效率？

问题分析 #

你的原代码效率低下的核心原因：

• 循环中反复读写单元格，Excel的单元格交互是性能瓶颈
• 每次调用Application.Match都要重新扫描Page1的整列，6000+行的重复扫描会大幅增加耗时

优化方案：字典+数组实现 #

利用VBA的Dictionary对象实现O(1)时间复杂度的快速查找，同时用数组一次性读写数据，彻底避免循环操作单元格，能将处理速度提升几十倍甚至上百倍。

Sub FastCheckReferenceExists()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim dict As Object
    Dim arrPage1 As Variant, arrPage2 As Variant
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long
    
    ' 关闭Excel后台操作，提升运行速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Set ws1 = ThisWorkbook.Worksheets("Page1")
    Set ws2 = ThisWorkbook.Worksheets("Page2")
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 获取Page1参考编号列的最后一行（假设参考编号在C列）
    lastRow1 = ws1.Cells(ws1.Rows.Count, "C").End(xlUp).Row
    ' 将Page1的参考编号批量加载到数组
    arrPage1 = ws1.Range("C2:C" & lastRow1).Value
    
    ' 将所有参考编号存入字典（键唯一，查找效率极高）
    For i = LBound(arrPage1) To UBound(arrPage1)
        If Not dict.Exists(arrPage1(i, 1)) Then
            dict(arrPage1(i, 1)) = True
        End If
    Next i
    
    ' 获取Page2待匹配数据的最后一行（参考编号在D列，状态写入E列）
    lastRow2 = ws2.Cells(ws2.Rows.Count, "D").End(xlUp).Row
    ' 将Page2的D列（待匹配值）和E列（结果列）加载到数组
    arrPage2 = ws2.Range("D2:E" & lastRow2).Value
    
    ' 批量处理匹配逻辑
    For i = LBound(arrPage2) To UBound(arrPage2)
        If dict.Exists(arrPage2(i, 1)) Then
            arrPage2(i, 2) = "Yes"
        Else
            arrPage2(i, 2) = "No"
        End If
    Next i
    
    ' 将处理后的结果数组写回Page2的E列
    ws2.Range("E2:E" & lastRow2).Value = Application.Index(arrPage2, 0, 2)
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    ' 释放对象内存
    Set dict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

优化细节说明 #

• 字典对象：把Page1的参考编号存入字典后，每次查找仅需常数时间，替代了原代码中反复调用Match的全列扫描操作
• 数组操作：一次性读取和写入数据，彻底避免循环中逐行读写单元格的性能损耗
• 关闭后台操作：暂时禁用屏幕更新、事件触发和自动计算，减少不必要的资源消耗

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir