EXCEL高级函数应用-SUBSTITUTES 函数

office

**WPS SUBSTITUTES 函数：专属批量替换神器，文本处理效率翻倍！

在文本处理场景中，“批量替换多个字符” 是高频刚需 —— 比如将 “千克 / 克 / 吨” 统一转为 “kg/g/t”、把菜品名称按对照表批量更新、给序号自动添加换行符。过去要么用 Excel 的SUBSTITUTE嵌套多层公式（繁琐易出错），要么手动逐次替换（效率极低）。而 WPS 表格在 2024 年 4 月版本（16894 版）中新增的SUBSTITUTES 函数，作为 WPS 专属功能，支持 “多对多批量替换”，仅需一行公式就能完成多个字符的对应替换，堪称文本规范化的 “效率核武器”。今天就带大家吃透这个 WPS 独有的实用函数，彻底告别重复替换的繁琐！

一、核心认知：SUBSTITUTES 与 SUBSTITUTE 的关键区别

在学习前必须明确：SUBSTITUTES（复数）是 WPS 专属函数，SUBSTITUTE（单数）是 Excel 与 WPS 共有的基础替换函数，二者核心差异体现在 “替换维度” 上，这也是SUBSTITUTES的效率核心。

对比维度	WPS SUBSTITUTES 函数（专属）	Excel/WPS SUBSTITUTE 函数（通用）
核心能力	支持多对多批量替换（多个旧文本对应多个新文本）	仅支持一对一替换（单个旧文本对应单个新文本）
参数特性	旧文本、新文本可填数组（如 {“a”;“b”} 对应 {“x”;“y”}）	旧文本、新文本仅支持单个值
操作效率	一行公式完成 N 个替换，无需嵌套	N 个替换需嵌套 N 层公式（如 SUBSTITUTE (SUBSTITUTE (…))）
适用场景	批量字符替换、跨列对照表替换、多格式统一	单个字符替换、指定次数替换
兼容性	仅 WPS 16894 及以上版本支持，Excel 无此函数	全版本兼容（Excel 2003+、WPS 全版本）

关键提醒：若在 Excel 中输入SUBSTITUTES，会返回 #NAME? 错误；在低版本 WPS 中使用，需先升级到 2024 年 4 月及以后版本（可通过 “帮助 - 关于 WPS Office” 查看版本号）。

二、吃透基础：SUBSTITUTES 函数的语法与参数

SUBSTITUTES函数的核心是 “将目标字符串中的多个旧文本，按对应关系批量替换为多个新文本”，语法简洁但参数支持数组特性，理解 “数组对应规则” 是关键。

1. 基本语法

SUBSTITUTES(字符串, 原字符串, \[新字符串], \[替换序号])

前 2 个参数为必选项，后 2 个为可选项；
返回结果为 “文本型数据”：完成批量替换后的完整字符串，未匹配的内容保持不变；若未找到任何原字符串，返回原文本。

2. 参数详细说明

结合 “菜品名称批量更新” 场景（将 “番茄 / 土豆 / 黄瓜” 替换为 “西红柿 / 马铃薯 / 青瓜”），参数含义拆解如下，重点标注 “数组用法” 这一核心特性：

参数名称	作用解释	通俗举例（菜品替换场景）	是否必选	关键注意事项
字符串	要进行替换的 “目标文本”（单元格引用、直接文本或公式结果）	1. 单元格引用（A2，内容 “番茄炒土豆”）；2. 直接文本（“黄瓜汤”）	是	支持多单元格区域引用（如 A2:A10），返回对应数组结果（需 WPS 支持动态数组）
原字符串	要被替换的 “旧文本集合”（可填单个文本或垂直数组，多个旧文本用 {} 分隔）	批量替换则填数组：{“番茄”;“土豆”;“黄瓜”}	是	1. 数组需用 {} 包裹，元素用；分隔（垂直数组）；2. 区分大小写（如 “Tom” 不替换 “tom”）；3. 支持单元格区域引用（如 B2:B4，对应 3 个旧文本）
[新字符串]	替换用的 “新文本集合”（可填单个文本、垂直数组或省略）：省略则默认替换为空文本 ""	对应数组：{“西红柿”;“马铃薯”;“青瓜”}	否	1. 数组长度必须与 “原字符串” 数组一致（否则多余元素不替换）；2. 省略时相当于 “批量删除” 原字符串；3. 支持特殊字符（如换行符 CHAR (10)）
[替换序号]	指定替换 “第几次出现的原字符串”（正整数，仅对单个原字符串有效）	替换第一次出现的 “番茄”，则填 1	否	当 “原字符串” 为数组时，此参数无效（批量替换默认替换所有出现的原字符串）

核心数组规则演示：

若原字符串={"a";"b"}，新字符串={"x";"y"}，则函数会自动按 “位置对应” 替换：第 1 个旧文本 “a”→第 1 个新文本 “x”，第 2 个旧文本 “b”→第 2 个新文本 “y”，实现 “一对多” 精准匹配。

三、实战场景：SUBSTITUTES 函数的 7 大核心应用

SUBSTITUTES的价值体现在 “批量处理 + 简化公式”，下面结合 WPS 官方案例与实际工作场景，用 7 个示例覆盖 “批量替换、删除、格式统一、跨列匹配” 等需求，每个示例均标注 “WPS 专属优势”，对比传统方法凸显效率。

示例 1：基础应用 —— 多字符批量替换（单位规范化）

需求：在 “物资表” A2:A100 列（如 “5 千克 / 3 克 / 2 吨”）中，将 “千克”→“kg”、“克”→“g”、“吨”→“t”，统一单位格式。

传统操作（SUBSTITUTE 嵌套）：

\=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "千克", "kg"), "克", "g"), "吨", "t")

问题：替换 N 个字符需嵌套 N 层，公式冗长且修改困难（新增单位需加一层嵌套）。

SUBSTITUTES 公式（WPS 专属，一行搞定）：

\=SUBSTITUTES(A2, {"千克";"克";"吨"}, {"kg";"g";"t"})

解析：

原字符串与新字符串均为 3 个元素的数组，按位置对应替换；
结果：“5 千克”→“5kg”，“3 克”→“3g”，“2 吨”→“2t”，新增 “毫克” 仅需在数组中加"毫克";"mg"，无需修改公式结构；
优势：公式长度与替换数量无关，后期维护成本极低。

示例 2：批量删除 —— 省略新字符串（清理特殊符号）

需求：在 “订单表” A2:A100 列（如 “ORD-2025#001 - 北京”）中，批量删除 “-”“#”“ORD” 三个字符，得到 “2025001 北京”。

传统操作（SUBSTITUTE + 嵌套）：

\=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "#", ""), "ORD", "")

问题：删除字符越多，嵌套层数越多，易遗漏或输错。

SUBSTITUTES 公式（WPS 专属，省略新字符串）：

\=SUBSTITUTES(A2, {"-";"#";"ORD"})

解析：

省略新字符串参数，默认将原字符串数组中的元素替换为空文本（即删除）；
结果：“ORD-2025#001 - 北京”→“2025001 北京”，一步完成多字符删除；
拓展：若需保留部分字符，仅需调整原字符串数组（如仅删除 “-” 和 “#”，则数组改为 {"-";"#"}）。

示例 3：跨列匹配 —— 引用替换对照表（动态更新）

需求：在 “菜单表” A2:A100 列（原菜品）中，按 B2:C4 的 “替换对照表”（B 列 = 原菜品，C 列 = 新菜品）批量更新菜品名称，对照表新增时自动同步。

传统操作（VLOOKUP+IFERROR）：

\=IFERROR(VLOOKUP(A2, B:C, 2, FALSE), A2)

问题：仅支持单个单元格匹配，需下拉公式；对照表新增时需确保区域覆盖（如 B:C 改为 B:Z）。

SUBSTITUTES 公式（WPS 专属，区域引用）：

\=SUBSTITUTES(A2, B2:B4, C2:C4)

解析：

直接引用 B2:B4（原菜品区域）和 C2:C4（新菜品区域）作为数组，无需手动输入 {}；
结果：若 A2=“番茄炒蛋”，B2=“番茄”，C2=“西红柿”，则返回 “西红柿炒蛋”；
优势：对照表新增行（如 B5=“土豆”，C5=“马铃薯”）时，仅需将区域改为 B2:B5、C2:C5，公式结构不变；支持多单元格批量输出（输入=SUBSTITUTES(A2:A100, B2:B4, C2:C4)，自动溢出结果）。

示例 4：格式统一 —— 批量添加换行符（文本排版）

需求：在 “说明表” A2:A100 列（如 “1. 封面 2. 目录 3. 正文”）中，在序号 “2.”“3.” 前添加换行符，实现自动分段（WPS 官方社区经典案例）。

传统操作（手动换行 + 复制）：

双击单元格进入编辑模式，按 “Alt+Enter” 在 “2.” 前换行；
重复操作至所有序号，耗时且易漏改。

SUBSTITUTES 公式（WPS 专属，结合 SEQUENCE）：

\=LET(a, SEQUENCE(9,,2)&".", SUBSTITUTES(A2, a, CHAR(10)\&a))

解析：

用SEQUENCE(9,,2)生成 2-9 的序号，连接 “.” 得到a={"2.";"3.";..."9."}（原字符串数组）；
CHAR(10)表示换行符，CHAR(10)&a生成 “换行 + 序号” 的新字符串数组；
结果：“1. 封面 2. 目录 3. 正文”→“1. 封面 \n2. 目录 \n3. 正文”（自动分段）；
优势：支持任意序号范围（如 1-100，仅需改SEQUENCE(100)），配合 LET 函数简化重复表达式，公式更简洁。

示例 5：嵌套求值 —— 批量替换并计算（数据提取）

需求：在 “计算表” A2:A100 列（如 “100/200/300”“500/100/200”）中，将 “/” 依次替换为 “+”“-”“*”，并计算结果。

传统操作（替换 + 手动输入公式）：

用 “查找和替换” 将 “/” 改为 “+”“-”“*”；
在前面加 “=” 转为公式，需逐行操作。

SUBSTITUTES+EVALUATE 公式（WPS 专属，自动求值）：

\=EVALUATE(SUBSTITUTES(A2, {"/";"/";"/"}, {"+";"-";"\*"}, 1))

解析：

SUBSTITUTES将第 1 个 “/”→“+”、第 2 个 “/”→“-”、第 3 个 “/”→“*”；
EVALUATE对替换后的文本（如 “100+200-300”）求值，返回计算结果；
结果：“100/200/300”→100+200-300=0，“500/100/200”→500+100-200=400；
优势：替换与求值一步完成，支持复杂运算符组合，无需手动输入公式。

示例 6：多区域替换 —— 批量处理多列数据（跨列统一）

需求：同时对 “产品表” A2:A100（名称）、B2:B100（规格）两列数据，批量删除 “旧款”“型号” 两个字符。

传统操作（分区域替换）：

选中 A 列，用 “查找和替换” 删除 “旧款”“型号”；
选中 B 列，重复上述操作，需两次处理。

SUBSTITUTES 公式（WPS 专属，多区域引用）：

\=SUBSTITUTES(A2:B100, {"旧款";"型号"})

解析：

第 1 参数为多列区域（A2:B100），函数自动对区域内所有单元格执行替换；
结果：A 列 “旧款手机”→“手机”，B 列 “型号 X1”→“X1”，两列同时完成清理；
优势：支持任意矩形区域（如 A2:C200），一次公式覆盖多列多行，无需分区域操作。

示例 7：反向提取 —— 批量删除数字留文本（内容分离）

需求：在 “数据表” A2:A100 列（如 “苹果 12”“香蕉 5”“橙子 20”）中，批量删除所有数字，仅保留中文文本。

传统操作（复杂嵌套公式）：

\=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(\$1:\$100),1)\*1,"",MID(A2,ROW(\$1:\$100),1)))

问题：公式冗长，需启用迭代计算，新手难以理解。

SUBSTITUTES 公式（WPS 专属，结合 ROW 生成数字数组）：

\=SUBSTITUTES(A2, ROW(\$0:\$9), "")

解析：

ROW($0:$9)生成 0-9 的数字数组（{"0";"1";..."9"}）；
批量删除数组中的所有数字，仅保留非数字字符；
结果：“苹果 12”→“苹果”，“香蕉 5”→“香蕉”，“橙子 20”→“橙子”；
优势：公式极简，无需理解复杂嵌套，支持任意位数数字的删除。

四、总结：SUBSTITUTES 函数的核心优势与注意事项

1. 核心优势（WPS 用户必用理由）

优势维度	具体表现	效率提升对比（10 个字符替换场景）
批量处理能力	一行公式完成 N 个字符替换，无需嵌套	传统方法：需嵌套 10 层公式，耗时 5 分钟；SUBSTITUTES：10 秒搞定
公式简洁性	支持数组与区域引用，避免重复输入	传统公式长度：约 200 字符；SUBSTITUTES 公式长度：约 50 字符
动态扩展性	替换内容新增时，仅需修改数组或区域，公式结构不变	传统方法：需新增嵌套层，易出错；SUBSTITUTES：修改 1 处即可
场景适配性	覆盖批量替换、删除、排版、计算等全场景，配合 LET/EVALUATE 更强大	传统方法：需切换多个函数，操作割裂；SUBSTITUTES：一站式解决

2. 必记注意事项

版本兼容性：仅支持 WPS 16894 及以上版本（2024 年 4 月后更新），低版本需升级（“帮助 - 检查更新”）；
数组格式：原字符串和新字符串数组必须为垂直数组（用；分隔元素），水平数组（用，分隔）会返回错误；
长度匹配：数组长度必须一致（如原字符串有 3 个元素，新字符串也需 3 个元素），否则多余元素不参与替换；
区分大小写：默认区分英文字母大小写（如 “Apple” 不替换 “apple”），若需不区分，可配合 UPPER 函数（SUBSTITUTES(UPPER(A2), UPPER({"Apple";"Banana"}), {"苹果";"香蕉"})）；
性能提示：处理 10 万行以上数据时，建议分区域操作（如 A2:A10000、A10001:A20000），避免卡顿。

作为 WPS 专属的 “批量替换神器”，SUBSTITUTES函数彻底解决了传统文本替换的繁琐问题，尤其适合数据规范化、格式统一、跨列匹配等场景。对于 WPS 用户而言，掌握它能极大提升文本处理效率，让 “批量替换” 从 “耗时任务” 变成 “一秒操作”。建议从基础的多字符替换（示例 1）开始尝试，逐步结合 SEQUENCE、LET 等函数解锁复杂场景，慢慢体会 WPS 专属函数的高效魅力！