Excel表格通过引用替换重复字符串，SUBSTITUTE函数如何正确使用

SUBSTITUTE函数仅支持单组字符串的逐一定义替换，处理多组重复值时需要多层嵌套，不仅编写难度高，大体积表格运行时也会出现卡顿、匹配错误的问题，因此不适合这类场景，可根据你的实际需求选择以下方案：

场景1：有预设替换对照表，整单元格匹配替换 #

如果你已经提前整理好了「原重复字符串-对应引用内容」的对照关系，适合用查找类函数批量处理：

• 预设替换对照表放在Sheet2的A:B列，A列为待替换的原字符串，B列为对应的引用内容（如=Sheet1!$C$3等自定义引用）
• 在待处理列的相邻空白列第一行，输入对应公式：
  支持XLOOKUP的新版Excel：
  =IFERROR(XLOOKUP(A1, Sheet2!$A:$A, Sheet2!$B:$B, A1), A1)
  无XLOOKUP的旧版Excel：
  =IFERROR(VLOOKUP(A1, Sheet2!$A:$B, 2, FALSE), A1)
• 公式下拉填充整列后，选中该列复制，右键粘贴为值即可覆盖原内容。

场景2：无预设对照表，替换为内容首次出现的单元格引用 #

如果要把所有重复内容自动替换为该内容第一次出现的单元格引用，不需要手动整理对照表：

• 在待处理列的相邻空白列第一行输入公式：
  =IF(COUNTIF($A$1:A1, A1)>1, ADDRESS(MATCH(A1, $A:$A, 0), COLUMN(A:A), 4), A1)
  公式中最后一位参数为4时返回相对引用（如A1），改为1时返回绝对引用（如$A$1），可按需调整。
• 下拉填充后粘贴为值即可完成替换。

场景3：单元格内部分字符串匹配替换 #

如果需要替换的是单元格内的部分重复片段，而非整个单元格内容，推荐用Power Query处理，大表运行效率远高于函数：

1. 选中待处理的表格区域，点击「数据」选项卡→「从表格/区域」导入Power Query编辑器
2. 提前将替换规则对照表也导入Power Query，命名为替换规则表
3. 回到待处理表的查询界面，点击「添加列」→「自定义列」，输入公式：
   List.Accumulate(Table.ToRows(替换规则表), [你要处理的列名], (state, current) => Text.Replace(state, current{0}, current{1}))
4. 处理完成后点击「关闭并上载」，即可将结果导回Excel，后续数据更新时只需右键刷新即可自动重新执行替换。

注意事项 #

• 所有替换操作前请先备份原表格，避免误操作丢失原始数据
• 部分匹配替换时，建议将更长的待匹配字符串放在替换规则表的靠前位置，避免短字符串先被替换后导致长字符串无法正常匹配。

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