SUBSTITUTES 函数¶
1. 函数概述¶
在 Excel 的日常工作中，文本替换是一个非常高频的操作。基础的 SUBSTITUTE 函数虽然好用，但一次只能替换一种字符。如果我们需要将一段文本中的“A换成1”、“B换成2”、“C换成3”，使用传统方法需要写出层层嵌套的“洋葱式”公式，既难以阅读又容易出错。
SUBSTITUTES 函数（注意是复数形式）的出现彻底解决了这个问题。它支持数组化的批量替换，允许用户一次性传入一组“旧字符”和一组“新字符”，并且保留了对替换位置的控制能力（即第 4 个参数）。它是现代 Excel 高效清洗数据的必备利器。
2. 基础语法¶
与基础函数不同，SUBSTITUTES 的参数设计全面支持数组（区域）引用。
3. 参数说明¶
SUBSTITUTES 函数包含 4 个参数，前三个用于定义“把什么改成什么”，第四个用于控制“改第几个”。
| 参数顺序 | 参数名称 | 必填/选填 | 说明 |
|---|---|---|---|
| 1 | text | 必填 | 需要进行处理的原始文本或单元格引用。 |
| 2 | old_text | 必填 | 旧字符列表。可以是一个单元格区域（如 A1:A3）或常量数组（如 {"A","B"}），列出所有需要被替换的内容。 |
| 3 | new_text | 必填 | 新字符列表。对应 old_text 的替换内容。其数量通常应与旧字符列表保持一致。 |
| 4 | [instance_num] | 选填 | 指定替换的次序。 • 省略：替换所有匹配到的旧字符。 • 数字 n：对于列表中的每一组替换规则，只替换第 n 次出现的那一个。 |
4. 基础用法示范¶
下面通过三个经典场景，展示 SUBSTITUTES 函数的强大之处。
场景一：多对多批量替换（翻译/代码转换）¶
假设 A2 单元格内容为 "Red, Green, Blue"，我们需要根据对照表将其转换为中文。
- 旧字符区域 (D2:D4)：
{"Red"; "Green"; "Blue"} - 新字符区域 (E2:E4)：
{"红"; "绿"; "蓝"}
| 公式 | 结果 | 说明 |
|---|---|---|
=SUBSTITUTES(A2, D2:D4, E2:E4) |
"红, 绿, 蓝" |
一次性完成了三次替换，无需嵌套。 |
场景二：多对一批量清理（去除干扰字符）¶
假设 A5 单元格内容为 "138-0000.8888 电话"，我们需要同时去除 -、. 和 空格。
- 旧字符区域：
{"-"; "."; " "} - 新字符区域：
{"" ; "" ; ""}(全部为空)
| 公式 | 结果 | 说明 |
|---|---|---|
=SUBSTITUTES(A5, {"-","."," "}, {"","",""}) |
"13800008888电话" |
将列表中的所有干扰符号全部替换为空。 |
场景三：指定位置替换（使用第 4 参数）¶
这是该函数比普通批量替换更高级的地方。
假设 A8 单元格内容为 "A-A, B-B, C-C"。我们希望只替换每组字符的第一个，保留第二个。
- 规则：把
A换成1，B换成2，C换成3。 - 要求：只替换第 1 次出现的字符。
| 公式 | 结果 | 说明 |
|---|---|---|
=SUBSTITUTES(A8, {"A","B","C"}, {"1","2","3"}, 1) |
"1-A, 2-B, 3-C" |
因为第4参数设为 1，所以只有每对字符中的第一个被改变了，第二个保持原样。 |
5. 总结¶
SUBSTITUTES 函数是 Excel 文本处理逻辑的一次重要升级。它不仅仅是节省了公式的长度，更重要的是它引入了“参数化”的处理思维：
- 逻辑解耦：将“替换规则”与“原始数据”分离，规则可以存放在单独的表格中维护。
- 极简高效：用一个函数替代了过去可能需要嵌套十层的复杂公式。
- 精准控制：通过第 4 参数，它继承了原版 SUBSTITUTE 函数的精准度，既能批量处理，又能指定位置。
掌握这个函数，面对复杂的文本清洗任务时，你将不再需要手动反复查找替换，真正实现办公自动化。