如何优化Range.Find()匹配Excel两列数据的性能？附VB代码

你的代码运行缓慢的核心原因是嵌套使用Range.Find()，每次调用Find都会遍历sheet2的B列，再加上服务器文件的IO开销，导致仅100-150行数据就耗时很久。以下是针对性的优化方案和算法说明：

一、更快的替代方案：使用字典（Dictionary） #

字典基于哈希表结构，单次查找的时间复杂度为O(1)，能将整体操作复杂度从O(n*m)降至O(n+m)，效率提升显著。同时配合禁用Excel界面更新、事件等操作，可进一步减少服务器文件的IO耗时。

优化后的代码示例 #

Sub MatchAndMergeData()
    Dim sheet1 As Worksheet, sheet2 As Worksheet, sheet3 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, i As Long, j As Long, k As Long
    Dim dict As Object
    Dim arr1 As Variant, arr2 As Variant, arrResult As Variant
    
    ' 初始化工作表（替换为你的实际表名）
    Set sheet1 = ThisWorkbook.Sheets("Sheet1")
    Set sheet2 = ThisWorkbook.Sheets("Sheet2")
    Set sheet3 = ThisWorkbook.Sheets("Sheet3")
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 禁用界面更新和事件，减少IO交互耗时
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' 获取两表数据的最后行号
    lastRow1 = sheet1.Cells(sheet1.Rows.Count, "B").End(xlUp).Row
    lastRow2 = sheet2.Cells(sheet2.Rows.Count, "B").End(xlUp).Row
    
    ' 将sheet2的B列值作为键，对应行号作为值存入字典
    arr2 = sheet2.Range("B1:L" & lastRow2).Value
    For j = 1 To UBound(arr2)
        If Not dict.Exists(arr2(j, 1)) Then
            dict(arr2(j, 1)) = j
        End If
    Next j
    
    ' 读取sheet1的目标数据到数组
    arr1 = sheet1.Range("B1:D" & lastRow1).Value
    ' 预定义结果数组，避免逐行写入工作表
    ReDim arrResult(1 To lastRow1, 1 To 13)
    i = 1
    
    ' 遍历sheet1数据，匹配字典中的键
    For j = 1 To UBound(arr1)
        If dict.Exists(arr1(j, 1)) Then
            ' 写入sheet1的B-D列到结果数组1-3列
            arrResult(i, 1) = arr1(j, 1)
            arrResult(i, 2) = arr1(j, 2)
            arrResult(i, 3) = arr1(j, 3)
            
            ' 写入sheet2的C-L列到结果数组4-13列
            Dim rowNum As Long
            rowNum = dict(arr1(j, 1))
            For k = 2 To 11
                arrResult(i, k + 2) = arr2(rowNum, k + 1)
            Next k
            
            i = i + 1
        End If
    Next j
    
    ' 将结果数组一次性写入sheet3，大幅减少IO操作
    sheet3.Range("A1:M" & i - 1).Value = arrResult
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Set dict = Nothing
End Sub

额外优化细节 #

• 用数组替代直接操作Range：数组是内存级操作，比反复读写服务器上的工作表快得多
• 批量写入结果：先将匹配好的数据存入数组，最后一次性写入目标表，避免多次IO交互
• 禁用界面更新和事件：减少Excel不必要的界面刷新、事件触发，降低额外开销

二、Range.Find()的搜索算法 #

微软官方未公开Range.Find()的具体实现细节，但根据实际测试和行为分析，它采用的是线性搜索（顺序搜索）：从指定起始位置开始，逐个单元格遍历目标区域，直到找到匹配值或遍历完整个区域。

这种算法的单次查找时间复杂度为O(m)（m为目标区域的单元格数量），你的代码对sheet1的每一行都调用一次Find，整体复杂度达到O(n*m)，再加上服务器文件的IO延迟，自然会导致运行缓慢。

内容的提问来源于stack exchange，提问作者Chris_A