百万级地址数据清洗：Excel卡顿问题优化方案咨询

使用64位Excel处理70万行地址数据，数据格式不统一（如部分条目为“41 st”，部分为“41st st”），已编写以下公式进行标准化处理：

1. 单元格D7提取带后缀的街道编号：
   =IFERROR(TEXTAFTER(TEXTBEFORE(A7," Ave",1,1,0,0)," <br>",1,1,0,TEXTAFTER(TEXTBEFORE(A7," St",1,1,0,0)," <br>",1,1,0,TEXTAFTER(TEXTBEFORE(A7," CT",1,1,0,0)," <br>",1,1,0,TEXTAFTER(TEXTBEFORE(A7," CIR",1,1,0,0)," <br>",1,1,0,TEXTAFTER(TEXTBEFORE(A7," St CIR",1,1,0,0)," <br>",1,1,0,TEXTAFTER(TEXTBEFORE(A7," Ave CIR",1,1,0,0)," <br>",1,1,0,"")))))),"")
2. 单元格E7匹配无后缀编号：
   =IFERROR(VLOOKUP(D7,$L$1:$M$302,1,FALSE),IFERROR(VLOOKUP(D7,$K$1:$L$302,2,FALSE),D7))
3. 单元格F7替换标准化内容：
   =IFERROR(IF(I7=1,TRIM(SUBSTITUTE(A7," "&D7," "&E7,1)),TRIM(SUBSTITUTE(A7," "&D7," "&D7,1))),"")

运行时Excel长时间卡顿无响应，目前仅能分段处理但效率极低。已采取优化措施：单独文件、关闭自动保存、设置手动计算，设备配置为i7-9700K+32GB内存+SSD，文件本地存储（同步OneDrive但自动保存已关）。现咨询：如何优化公式效率？有无更适合的替代工具？或高效分段处理方法？

1. 简化提取逻辑，减少嵌套层级 #

原D列公式多层嵌套TEXTAFTER+TEXTBEFORE，每一层都会重复扫描单元格内容，70万行下计算量指数级增长。可改用正则表达式一次性匹配所有街道类型，减少扫描次数：

• 用REGEXEXTRACT（Excel 365/2021支持）先提取街道前缀部分，再处理<br>分割内容：
  =IFERROR(TEXTAFTER(REGEXEXTRACT(A7,"(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)")," <br>",,REGEXEXTRACT(A7,"(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)")), "")
  该公式仅扫描单元格内容1-2次，远低于原公式的多次嵌套扫描。

2. 替换VLOOKUP为XLOOKUP，优化匹配效率 #

原E列双层VLOOKUP可替换为XLOOKUP，匹配效率更高且无需嵌套：
=IFERROR(XLOOKUP(D7,$L$1:$L$302,$L$1:$L$302,IFERROR(XLOOKUP(D7,$K$1:$K$302,$L$1:$L$302,D7))),D7)
同时确保查找区域为静态范围（如$L$1:$M$302），避免Excel扫描整列空值。

3. 简化替换公式，消除冗余计算 #

原F列中TRIM(SUBSTITUTE(A7," "&D7," "&D7,1))属于冗余操作，直接保留原内容即可：
=IFERROR(IF(I7=1,TRIM(SUBSTITUTE(A7," "&D7," "&E7,1)),A7),"")
减少不必要的SUBSTITUTE和TRIM调用。

4. 改用动态数组批量计算 #

如果使用Excel 365，将公式改为动态数组形式，一次性计算整列，避免逐单元格计算的开销：

• D列动态数组公式：
  =IFERROR(TEXTAFTER(REGEXEXTRACT(A:A,"(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)")," <br>",,REGEXEXTRACT(A:A,"(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)")), "")
• E列动态数组公式：
  =IFERROR(XLOOKUP(D:D,$L$1:$L$302,$L$1:$L$302,IFERROR(XLOOKUP(D:D,$K$1:$K$302,$L$1:$L$302,D:D))),D:D)

1. Power Query #

Excel内置的Power Query是大规模数据处理的首选，效率远高于普通公式：

• 导入数据到Power Query编辑器，用替换值+正则表达式批量标准化地址；
• 用合并查询替代VLOOKUP完成匹配，后台以高效算法执行；
• 处理完成后直接加载回Excel，70万行数据通常几分钟内完成。

2. Python（Pandas库） #

熟悉代码的话，Pandas处理效率更高，70万行数据通常几十秒内完成：

import pandas as pd

# 读取主数据
df = pd.read_excel("地址数据.xlsx")
# 提取街道编号并处理<br>分割
df["D"] = df["A"].str.extract(r"(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)", expand=False)[0]
df["D"] = df["D"].str.split(" <br>").str[-1]
# 读取匹配表
match_df = pd.read_excel("匹配表.xlsx", usecols=["K","L","M"])
# 构建匹配字典
match_dict = {}
for _, row in match_df.iterrows():
    match_dict[row["K"]] = row["L"]
    match_dict[row["L"]] = row["L"]
# 匹配获取标准化编号
df["E"] = df["D"].map(match_dict).fillna(df["D"])
# 替换地址内容
df["F"] = df.apply(lambda row: row["A"].replace(f" {row['D']}", f" {row['E']}") if row["I"] == 1 else row["A"], axis=1)
# 导出结果
df.to_excel("标准化地址.xlsx", index=False)

3. VBA宏 #

编写VBA宏批量处理，通过字典实现O(1)查找，避免单元格交互开销：

Sub 标准化地址()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim matchDict As Object
    Dim i As Long
    Dim addr As String, num As String, newNum As String
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set matchDict = CreateObject("Scripting.Dictionary")
    
    ' 加载匹配表到字典
    For i = 1 To 302
        matchDict(ws.Cells(i, "K").Value) = ws.Cells(i, "L").Value
        matchDict(ws.Cells(i, "L").Value) = ws.Cells(i, "L").Value
    Next i
    
    ' 关闭屏幕更新和自动计算
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' 批量处理数据
    For i = 7 To lastRow
        addr = ws.Cells(i, "A").Value
        ' 正则提取街道编号
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.*?)( Ave| St| CT| CIR| St CIR| Ave CIR)"
            .Global = False
            If .Test(addr) Then
                num = .Execute(addr)(0).SubMatches(0)
                If InStr(num, " <br>") > 0 Then num = Split(num, " <br>")(UBound(Split(num, " <br>")))
                ws.Cells(i, "D").Value = num
                ' 匹配标准化编号
                newNum = IIf(matchDict.Exists(num), matchDict(num), num)
                ws.Cells(i, "E").Value = newNum
                ' 替换地址内容
                ws.Cells(i, "F").Value = IIf(ws.Cells(i, "I").Value = 1, Trim(Replace(addr, " " & num, " " & newNum, 1)), addr)
            End If
        End With
    Next i
    
    ' 恢复设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

如果坚持使用Excel公式，可通过以下方式优化分段处理：

• 拆分工作表：将70万行拆分为多个工作表（如每个10万行），分别计算后合并，降低单工作表计算压力；
• 分步计算并转值：计算完D列后，复制D列粘贴为值，再计算E列；E列完成后同样粘贴为值，最后计算F列，避免公式依赖导致的重复计算；
• 定向计算：选中F列目标行，按F9只计算当前及以上单元格，避免全列计算；
• 关闭界面刷新：通过VBA设置Application.ScreenUpdating = False和Application.Calculation = xlCalculationManual，处理完成后再恢复，减少界面刷新开销。

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