处理 1.15 亿个细胞的最快方法是什么？

我收到了一项工作任务，其中我要查找 8 位数字并将其替换为来自 2 列表的相应新值......基本上是一个 vlookup，然后用新值替换旧值......

我面临的挑战是...... 2 列表有 882k 行，我尝试替换的单元格约为 1.2 亿（41,000 行 x 3000 列）......

我尝试运行我在某处找到的 vba 代码...

Option Explicit

Sub Replace_Overwrite()
Dim LRow As Long, i As Long
Dim varSearch As Variant

With Sheets("Sheet2")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    varSearch = .Range("A2:B" & LRow)
End With

With Sheets("Sheet1").UsedRange
    For i = LBound(varSearch) To UBound(varSearch)
        .Replace what:=varSearch(i, 1), replacement:=varSearch(i, 2), lookat:=xlWhole
    Next
End With
    
End Sub

我尝试使用它，它运行了 8 个小时，我的工作笔记本电脑崩溃了...... 我不再确定仅使用 MS Excel 是否仍然可行......

我想知道是否有人可以帮助我编写一个可以处理它的代码。如果系统稳定并且工作正常，我可以在周末让我的系统保持打开状态。顺便说一句，它只有 8GB 内存，运行 excel 2013...

为了加快速度，请在内存中执行尽可能多的操作，并尽量减少 VBA 和 Excel 之间的交互（因为这会使速度变得非常慢）。

以下尝试将查找列表读入字典，然后逐列处理数据。

我做了一个测试，创建了 880.000 个查找行和 40.000 x 100 个数据单元。构建字典花费了不到一分钟的时间，处理列每列花费了 3-4 秒。我添加了一个逻辑，即每 10 列后，保存整个工作簿，这增加了处理时间，但确保在崩溃后您可以或多或少地从您离开的地方继续（黄色告诉您在哪里，只需替换1 in for col=1与您要重新启动的列）。

我添加了一些 DoEvents，理论上这会稍微减慢该过程。优点是您可以看到 debug.print 的输出，并且整个 Excel 进程不会在任务管理器中显示为无响应。

为了构建字典，我立即将完整数据读入数组（如果您不熟悉字典：您需要添加对 Microsoft 脚本运行时的引用）。

Function createDict() As Dictionary
    Dim d As New Dictionary
        
    Dim rowCount As Long
    Dim list()
    Debug.Print Now, "Read data from Lookup sheet"
    With ThisWorkbook.Sheets(1)
        rowCount = .Cells(.Rows.Count, 1).End(xlUp).row
        list = .Range("A1:B" & rowCount).Value
    End With
        
    Debug.Print Now, "Build dictionary."
    
    Dim row As Long
    For row = 1 To UBound(list)
        If Not d.Exists(list(row, 1)) Then d.Add list(row, 1), list(row, 2)
        If row Mod 1000 = 0 Then DoEvents
    Next row
    
    Set createDict = d
End Function

如前所述，替换数据是逐列完成的。再次，我一次将整个列读入一个数组，对该数组进行替换，然后将其写回到工作表中。

Sub replaceAll()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim d As Dictionary
    Set d = createDict
    
    Dim row As Long, col As Long
    Dim rowCount As Long, colCount As Long
    With ThisWorkbook.Sheets(2)
        rowCount = .Cells(.Rows.Count, 1).End(xlUp).row
        colCount = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        For col = 1 To colCount
            Debug.Print Now & "processing col " & col
            DoEvents
            
            Dim data
            data = .Range(.Cells(1, col), .Cells(rowCount, col))
            For row = 1 To rowCount
                If d.Exists(data(row, 1)) Then data(row, 1) = d(data(row, 1))
            Next row
            .Range(.Cells(1, col), .Cells(rowCount, col)) = data
            .Cells(1, col).Interior.Color = vbYellow
            
            If col Mod 10 = 0 Then ThisWorkbook.Save
        Next
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

备注：对于如此大量的数据，您应该考虑使用数据库。