处理1.15亿个细胞的最快方法？
我接到了一项工作任务，我要找到并用来自 2 列表的相应新值替换 8 位数字……基本上是一个 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 ram btw，运行 excel 2013 ......
回答
为了加快速度，在内存中尽可能多地做，并尽量减少 VBA 和 Excel 之间的交互（因为这会让事情变得很慢）。
以下尝试将查找列表读入字典，然后逐列处理数据。
我做了一个测试，创建了 880.000 个查找行和 40.000 x 100 个数据单元。构建字典只用了不到一分钟，处理每列需要 3-4 秒。我添加了一个逻辑，在每 10 列之后保存整个工作簿，这增加了处理时间，但确保在崩溃后您或多或少可以继续离开的地方（黄色告诉您在哪里，只需将1in替换for col=1为要重新启动的列）。
我添加了一些 DoEvents，理论上会稍微减慢这个过程。优点是可以看到 debug.print 的输出，并且整个 Excel 过程不会在任务管理器中显示为无响应。
为了构建字典，我立即将完整数据读入数组（如果您不熟悉字典：您需要添加对 Microsoft Scripting Runtime 的引用）。
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
一句话：对于如此大的数据量，您应该考虑使用数据库。