[已解决]需要批量替换excel中某一列的数据，replace效率太低，求解决办法

whoistop

问题是这样的现在有一份excel文件需要将其中的一些单元格内容替换成想要的内容
比如A替换成甲，B替换成乙。。。。以此类推，现在使用excel的replace但因为内容太多执行效率太低。请问高手有没有更好的解决办法。

kevinch

附件和你的代码发上来看下，replace不会慢啊

xms77

回复 2# kevinch
可能数据量很大，是很慢的！

magusneo

excel自带的replace函数都慢的话，用au3操作更是无解吧

kevinch

excel在进行Range操作时会频繁更新屏幕，而更新屏幕耗用的时间大大超过计算所耗用的时间
excel是允许操作的时候关闭屏幕刷新的，这样速度差异很大
测试A1:IV640填充数据，替换其中的两个字为另外两个字
未关闭屏幕刷新时用时：60.6875秒
关闭屏幕刷新后用时：12.375秒
很明显的优势对吧
建议楼主还是把附件和代码弄上来看一下，优化可以从多个方面进行的

whoistop

感谢楼上各位，现在太晚了，明天上来讨论。再次感谢各位！

whoistop

改成在数据替换过程中禁用刷屏，时间缩短了一半，基本可用了。有时间，我再试试其他方法。

xowen

怎么写啊？共享一下关闭命令。

whoistop

回复 8# xowen


    $oExcel.Application.ScreenUpdating = False

whoistop

 .Columns(1).Cells.Replace("A","甲")
           .Columns(1).Cells.Replace("B","乙")
           .Columns(1).Cells.Replace("C","丙")
           .Columns(1).Cells.Replace("D","丁")
           .Columns(1).Cells.Replace("E","戊")
           .Columns(1).Cells.Replace("F","己")
           .Columns(1).Cells.Replace("G","庚")

这样的代码如何写得更简洁一些？

kevinch

回复 10# whoistop

如果这类很多的话，最好用个二维数组装起来，然后循环执行

另外应该测试下循环单元格，提取数值，全部替换完成后写回，看下哪个速度快些，可以想象，每次替换A列的内容，虽然excel可以聪明的只在已使用区域内操作，但是替换一次就要循环一遍全部已使用过的A列单元格，所以值得测试一次两种方法，对比效果

kevinch

刚在vba环境里测试了一下，关闭屏幕刷新情况下，A列10000行数据，全部是ABCDEFG，将ABCDEF分别替换成HIJKLM：
整列replace最慢
循环单元格替换完成后写回速度一般
区域读取到数组，全部替换完成后写回速度最快

感兴趣的可以试下下面的vba代码，就不改成au3的了

Sub test()
Dim Arr(1 To 6, 1 To 2), N%, I&, mTime, Rng As Range, Str$, Result$, ArrT
Arr(1, 1) = "A"
Arr(2, 1) = "B"
Arr(3, 1) = "C"
Arr(4, 1) = "D"
Arr(5, 1) = "E"
Arr(6, 1) = "F"
Arr(1, 2) = "H"
Arr(2, 2) = "I"
Arr(3, 2) = "J"
Arr(4, 2) = "K"
Arr(5, 2) = "L"
Arr(6, 2) = "M"
Columns(1).Clear
[a1:a10000] = "ABCDEFG"
Application.ScreenUpdating = False
mTime = Timer
'直接替换A列
For N = LBound(Arr) To UBound(Arr)
    Columns(1).Replace Arr(N, 1), Arr(N, 2)
Next N
Result = "直接替换A列用时：" & Format(Timer - mTime, "0.000000") & vbNewLine
mTime = Timer
'直接替换A列已使用区
For N = LBound(Arr) To UBound(Arr)
    Intersect(Columns(1), ActiveSheet.UsedRange).Replace Arr(N, 2), Arr(N, 1)
Next N
Result = Result & "直接替换A列已使用区用时：" & Format(Timer - mTime, "0.000000") & vbNewLine
mTime = Timer
'循环替换单元格
For Each Rng In [a1].Resize(Cells(Rows.Count, 1).End(3).Row)
    Str = Rng.Value
    For N = LBound(Arr) To UBound(Arr)
        Str = Replace(Str, Arr(N, 1), Arr(N, 2))
    Next N
    Rng = Str
Next Rng
Result = Result & "循环替换单元格用时：" & Format(Timer - mTime, "0.000000") & vbNewLine
mTime = Timer
'读取到数组，经变量中转替换
ArrT = [a1].Resize(Cells(Rows.Count, 1).End(3).Row).Value
For I = LBound(ArrT) To UBound(ArrT)
    Str = ArrT(I, 1)
    For N = LBound(Arr) To UBound(Arr)
        Str = Replace(Str, Arr(N, 2), Arr(N, 1))
    Next N
    ArrT(I, 1) = Str
Next I
[a1].Resize(Cells(Rows.Count, 1).End(3).Row).Value = ArrT
Result = Result & "数组经变量中转替换用时： " & Format(Timer - mTime, "0.000000") & vbNewLine
mTime = Timer
'读取到数组，不经变量中转替换
ArrT = [a1].Resize(Cells(Rows.Count, 1).End(3).Row).Value
For I = LBound(ArrT) To UBound(ArrT)
    For N = LBound(Arr) To UBound(Arr)
        ArrT(I, 1) = Replace(ArrT(I, 1), Arr(N, 1), Arr(N, 2))
    Next N
Next I
[a1].Resize(Cells(Rows.Count, 1).End(3).Row).Value = ArrT
Result = Result & "数组不经变量中转替换用时：" & Format(Timer - mTime, "0.000000")
Application.ScreenUpdating = True
MsgBox Result
End Sub

whoistop

回复 12# kevinch


    读取后替换再写回有考虑过，只是暂时可用后就放下了，有空再改。谢谢各位！

whoistop

回复 12# kevinch

碰到问题了，au3没找到好的数组内容替换方法，可否指教。谢谢！

kevinch

vba里也没有，逐个替换的