Excel表格如何批量将旧值替换为对应新值

针对「1500条旧-新值映射规则、待处理列包含旧值前缀的字符串」的场景，无需逐行手动查找替换，可按自身Excel版本选择以下方案，全流程耗时不超过1分钟。

方案1：XLOOKUP动态公式（适配Excel 365/2021及以上版本） #

• 预处理步骤：
  1. 先对映射规则表做防误匹配处理：插入辅助列，输入公式=LEN(旧值单元格)计算每个旧值的长度，按辅助列降序排序后删除辅助列，保证更长的旧值优先被匹配，避免短值误匹配（比如旧值同时存在6531和653111时，优先匹配更长的653111，不会提前替换短值导致内容残留）。
     这步是必做操作，否则会出现短值优先匹配导致的替换错误
• 公式写法：在待处理列相邻的空白数据行（比如D2单元格）输入以下公式，根据实际单元格范围替换参数即可：
  =REDUCE(C2, FILTER($A$2:$A$1501, $A$2:$A$1501<>""), LAMBDA(init, old, IF(ISNUMBER(SEARCH(old, init)), SUBSTITUTE(init, old, XLOOKUP(old, $A$2:$A$1501, $B$2:$B$1501)), init)))
• 后续操作：
  1. 公式输入后按回车，向下填充整列得到替换结果
  2. 选中所有公式结果，右键复制后选择「选择性粘贴-值」，替换原待处理列的内容即可。

方案2：Power Query批量处理（适配所有带数据选项卡的Excel版本，无公式依赖） #

• 操作流程：
  1. 分别选中「旧-新值映射表」「待处理产品列」的任意单元格，点击「数据」选项卡→「从表格/区域」，加载到Power Query编辑器，确认数据包含标题行。
  2. 在映射表查询界面，添加自定义列输入Text.Length([旧值])，按该列降序排序后删除辅助列，完成防误匹配的预处理。
  3. 回到产品表查询界面，添加自定义列，用List.Accumulate函数遍历映射表完成替换，核心M逻辑代码为：

     替换后文本 = List.Accumulate(
       Table.ToRecords(映射表查询名),
       当前产品文本,
       (state, current) => if Text.Contains(state, current[旧值]) then Text.Replace(state, current[旧值], current[新值]) else state
     )
     

  4. 点击「关闭并上载」，替换后的内容会自动生成新工作表，1500条映射规则的处理耗时通常在10秒以内。

方案3：VBA宏批量执行（适配所有Excel版本，适合需要重复操作的场景） #

• 操作流程：
  1. 按下Alt+F11打开VBA编辑器，右键点击当前工作簿名→「插入」→「模块」
  2. 粘贴以下代码，根据实际的工作表名、单元格范围修改参数：

     Sub 批量按映射表替换()
         Dim mappingRng As Range, targetRng As Range, cell As Range, dic As Object
         ' 按实际修改：映射表的旧值、新值所在范围
         Set mappingRng = ThisWorkbook.Sheets("映射表").Range("A2:B1501")
         ' 按实际修改：待处理的产品数据范围
         Set targetRng = ThisWorkbook.Sheets("产品表").Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
         Set dic = CreateObject("Scripting.Dictionary")
         ' 导入映射关系到字典
         For Each cell In mappingRng.Columns(1).Cells
             If cell.Value <> "" Then dic(cell.Value) = cell.Offset(0, 1).Value
         Next
         ' 自动按旧值长度降序排序，避免短值误匹配
         Dim keysArr, i As Long, j As Long, tempKey As String
         keysArr = dic.keys
         For i = LBound(keysArr) To UBound(keysArr)
             For j = i + 1 To UBound(keysArr)
                 If Len(keysArr(i)) < Len(keysArr(j)) Then
                     tempKey = keysArr(i)
                     keysArr(i) = keysArr(j)
                     keysArr(j) = tempKey
                 End If
             Next j
         Next i
         ' 执行批量替换
         Application.ScreenUpdating = False
         For Each cell In targetRng.Cells
             For i = LBound(keysArr) To UBound(keysArr)
                 If InStr(cell.Value, keysArr(i)) > 0 Then
                     cell.Value = Replace(cell.Value, keysArr(i), dic(keysArr(i)))
                 End If
             Next i
         Next cell
         Application.ScreenUpdating = True
         MsgBox "替换完成"
     End Sub
     

  3. 按F5运行宏，等待执行结束即可，上千条待处理数据的执行时间通常在数秒内。
• 注意：运行宏前请先备份原数据，避免范围选择错误导致内容被覆盖。

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