如何在Excel中通过映射表批量替换（不使用VBA）

针对Sheet1 A列带后缀的字符串（如sp_abc_Rick），结合Sheet2的原名称-替换名称映射表，在Sheet1 B列生成替换结果，无匹配项保持原样，以下是无需VBA的实现方案：

方案1：Excel 365/2021（推荐，支持动态数组） #

在Sheet1的B2单元格输入以下公式，回车后自动溢出到所有行：

=LET(
    original, FILTER(Sheet1!A:A, Sheet1!A:A<>""),
    map_old, Sheet2!A$2:A$101,
    map_new, Sheet2!B$2:B$101,
    REDUCE(original, SEQUENCE(ROWS(map_old)), LAMBDA(a,i, SUBSTITUTE(a, INDEX(map_old,i), INDEX(map_new,i))))
)

说明： #

• FILTER用于排除Sheet1 A列的空行，若不需要可直接用Sheet1!A2:A1001指定固定范围
• map_old和map_new对应Sheet2的原名称列和替换名称列，根据实际数据范围调整（比如Sheet2映射从A1开始就改成Sheet2!A$1:A$100）
• 公式会按顺序遍历所有映射规则，依次替换字符串中的匹配内容；若原名称重复，最后一次替换生效

区分大小写替换（可选）

默认SUBSTITUTE区分大小写，若需要不区分大小写替换，改用以下公式：

=LET(
    original, FILTER(Sheet1!A:A, Sheet1!A:A<>""),
    map_old, Sheet2!A$2:A$101,
    map_new, Sheet2!B$2:B$101,
    REDUCE(original, SEQUENCE(ROWS(map_old)), LAMBDA(a,i, IFERROR(REPLACE(a, SEARCH(INDEX(map_old,i), a, 1), LEN(INDEX(map_old,i)), INDEX(map_new,i)), a)))
)

方案2：旧版Excel（无动态数组支持） #

方法A：辅助列逐步替换 #

1. 在Sheet1插入与Sheet2映射行数相同的辅助列（比如100列）
2. 第一个辅助列（C2）输入：=SUBSTITUTE(A2, Sheet2!$A$2, Sheet2!$B$2)
3. 第二个辅助列（D2）输入：=SUBSTITUTE(C2, Sheet2!$A$3, Sheet2!$B$3)
4. 依次类推，每列公式对应Sheet2的下一行映射规则
5. 最后在Sheet1 B2输入最后一个辅助列的引用（比如=CV2），下拉填充所有行

方法B：嵌套SUBSTITUTE（限64层以内映射） #

若Sheet2映射行数≤64，直接嵌套SUBSTITUTE函数，按Ctrl+Shift+Enter作为数组公式：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, Sheet2!A2, Sheet2!B2), Sheet2!A3, Sheet2!B3), Sheet2!A4, Sheet2!B4)

（继续嵌套至所有映射规则，最多64层；超过64层则分两次嵌套，先替换前64条，再用结果替换剩余规则）

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