如何用替换功能批量替换Excel单列中多值为指定文本？

一、公式法（无需宏，兼容大部分Excel版本） #

• 第一步：创建替换映射表
  在空白工作表（比如Sheet2）中，A列输入所有36个需要替换的旧文本值，B列对应输入目标新文本值。多对一的场景直接重复填写新值即可，比如10个旧值对应的B列都写orange。
• 第二步：应用批量替换公式
  假设待处理数据在Sheet1的A列（第1行是表头），在Sheet1的B2单元格输入公式：
  如果你用的是Excel 365/2021及以上版本，用XLOOKUP更简洁：

  =XLOOKUP(A2,Sheet2!$A$2:$A$37,Sheet2!$B$2:$B$37,A2,0)
  

  参数说明：找不到匹配值时保留原单元格内容（最后一个参数A2），精确匹配（0）。
  如果你用的是旧版本Excel，用INDEX+MATCH组合：

  =IFERROR(INDEX(Sheet2!$B$2:$B$37,MATCH(A2,Sheet2!$A$2:$A$37,0)),A2)
  

• 第三步：批量填充公式
  选中B2单元格，鼠标移到单元格右下角，当光标变成十字形时双击，即可自动填充到所有有数据的行。之后如果需要覆盖原列，直接复制B列，右键A列选择「粘贴值」即可。

二、VBA宏法（高效批量替换，适合大量数据） #

• 操作步骤：

  1. 打开Excel，按下Alt+F11打开VBA编辑器。
  2. 右键左侧的工作簿名称，选择「插入」→「模块」。
  3. 将以下代码粘贴到模块窗口中：

  Sub BatchReplaceValues()
      Dim ws As Worksheet
      Dim replaceDict As Object
      Dim lastRow As Long
      Dim i As Long
      
      ' 指定要处理的工作表名称，根据实际修改
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      ' 创建字典存储替换规则
      Set replaceDict = CreateObject("Scripting.Dictionary")
      
      ' ********** 这里添加所有替换规则，格式：replaceDict.Add "旧值", "新值" **********
      replaceDict.Add "旧值1", "orange"
      replaceDict.Add "旧值2", "orange"
      replaceDict.Add "旧值3", "orange"
      ' 继续添加剩下的33个旧值与对应新值的映射，多对一重复添加即可
      replaceDict.Add "旧值11", "apple"
      replaceDict.Add "旧值12", "apple"
      ' ... 直到所有36个规则添加完成
      
      ' 获取A列最后一行数据的行号
      lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      
      ' 批量遍历替换
      For i = 2 To lastRow ' 从第2行开始，若表头在其他行则修改起始行号
          If replaceDict.Exists(ws.Cells(i, "A").Value) Then
              ws.Cells(i, "A").Value = replaceDict(ws.Cells(i, "A").Value)
          End If
      Next i
      
      MsgBox "批量替换完成！"
  End Sub
  

  4. 修改代码中的工作表名称（比如Sheet1），并补充完整所有36条替换规则。
  5. 按下F5运行宏，或者回到Excel界面，点击「开发工具」→「宏」，选择BatchReplaceValues执行。

• 注意事项：

  • 运行宏前建议备份原数据，避免误操作。
  • 保存文件时需选择「Excel 启用宏的工作簿（*.xlsm）」格式，否则宏会丢失。

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