关注【秋叶 Excel】👆发送【插件】
免费领 Excel 必备 4 大插件
本文作者：小花
本文编辑：竺兰
Excel 函数的魅力就在于，每一个简单的函数吃透了，就一定有别样的精彩。
SUBTITUTE 函数就是一个典型的例子，高手眼中，它可不只是一个文本替换函数。
与 REPLACE 函数替换某一文本字符串中指定位置处的任意文本不同，SUBSTITUTE 函数可以在某一文本字符串中替换任意指定的文本。
现在关注【秋叶 Excel】
发送关键词【模板】
免费领 100+ 实用表格模板！
👇👇👇
基础应用：删除指定文本

举个例子，要将房源编码「1 号住宅楼-101」简化为「1-101」，我们需要将房源编码中的「号住宅楼」删除，使用 SUBSTITUTE 函数将「号住宅楼」替换为空即可实现。
B2 单元格公式如下：
=SUBSTITUTE(A2,"号住宅楼",)
👉 公式说明：
SUBSTITUTE 函数的第三个参数为空，表示将旧文本替换空；第四个参数省略，表示将 A2 文本中的所有「号住宅楼」旧文本全部替换为空，其最终效果就是删除旧文本。
了解了 SUBSTITUTE 函数的基本用法后，我们再来看看，替换指定文本这一基本功能是如何被用到极致的？
初级应用：插入字符
以上案例中，SUBSTITUTE 将旧文本替换为空即为实现删除指定文本，那如果将旧文本替换为新文本+旧文本，是否可以实现插入功能呢？答案是肯定的。
例如，我们使用 SUBSTITUTE 函数将「-」替换为「-B2-」，从而实现在部门和姓名中插入职位。
C2 单元格公式如下：
=SUBSTITUTE(A2,"-","-"&B2&"-")
加强应用：文本换行
同样的思路，如果将指定旧文本替换为换行符，便可以实现在指定文本或符号处自动换行显示。
这里需要说明的是，换行符无法直接输入到函数中，因此我们使用 CHAR(10)来获得换行符。
PS：CHAR 函数的作用是根据代码数字制定的字符，数字 10 对应的字符就是换行符。
B2 单元格公式：
=SUBSTITUTE(A2,"，",CHAR(10))
进阶应用：混合文本计数
在填列名单时，有些人习惯于将全部人名填到一个单元格中，用特定符号隔开，这就增加了计算人数的难度。

还好有 SUBSTITUTE 函数，只需将特定符号替换为空，再计算替换前后的文本字符数之差，就可轻松计算人数。
例如，如下图，求每组的名单人数。
C2 单元格公式如下：
=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1
👉公式说明：
LEN 函数可以计算出文本中的字符个数，观察名单文本特点不难发现，替换前后的字符格式之差+1 即为人数。
高阶应用：去单位求和

工作中，我们经常遇到将数值和单位填到同一单元格内的情况，此时求和就变得非常困难了。但如果你会 SUBSTITUTE 的升级应用，那将不再是问题。
举个例子，如下图，我们需要根据销售面积和价格都带单位的数据求出销售金额，此时我们就需要使用 SUBSTITUTE 将指定单位文本去除后再进行求和。
B8 单元格公式如下：
=SUMPRODUCT(SUBSTITUTE(B2:B6,"平",)*SUBSTITUTE(C2:C6,"万",))
▲ 左右滑动查看

👉 公式说明：
SUMPRODUCT 引导两个数组乘积和运算，两个数组均由 SUBSTITUTE 函数计算得来。
SUBSTITUTE 分别将面积单位「平」和价格单位「万」替换为空，即删除单位，得到两个文本型数字组成的数组，在 SUMPRODUCT 函数中，乘法可以将文本型数字转化为数值型数组，从而实现相乘并求和。
神级应用：最大值提取

如果上述五种应用你都能掌握，那么恭喜你，SUBSTITUTE 函数你已几乎可以轻松拿捏。
以下谈到的神级应用，功力不深者切勿挑战哦，以免误伤脑细胞！
以下案例中，我们要从包含成绩的混合文本中提取三科成绩最高分，需要用到 MAX 函数、 ROW 函数与 SUBSTITUTE 函数来构建数组公式。
C2 单元格公式如下：
{=MAX((SUBSTITUTE(B2,ROW(1:150),)<>B2)*ROW(1:150))}
▲ 左右滑动查看
👉 公式说明：
❶ ROW(1:150)返回一个 1-150 的数组，
❷ SUBSTITUTE 将 B2 文本中数字 1 到 150 分别替换为空（即删除该数字）。
❸ 对 1-150 中的任一数字来说，如果 B2 中的文本不包含该数字，则文本保持不变，逻辑判断式SUBSTITUTE(B2,ROW(1:150),)<>B2 不成立，返回 FALSE，即为 0，0*ROW(1:150)=0；
反之，B2 文本中的该数字被删除，则逻辑判断式：SUBSTITUTE(B2,ROW(1:150),)<>B2 成立，返回 TRUE，即为 1。
❹ 1*ROW(1:150）=ROW(1:150），即返回该数字本身。
于是，B2 文本中包含的所有数字都被提取出来，此时用 MAX 就可以轻松提取最大值。
注意，该公式只能用于提取最大值，将 MAX 替换为 MIN 并不能正确提取最小值，且该公式输入后需按【Ctrl+Shift+Enter】来完成数组运算。
SUBTITUTE 的高难度应用还有很多，比如混合文本拆分、求最大连续次数，就连下图这样的罗列型数字求和，也可以用 SUBSTITUTE 函数来实现。
可以说，研究有多深入，SUBSTITUTE 函数就有多精彩！
=EVALUATE(SUBSTITUTE(文本求和!B2,"、","+"))
▲ 左右滑动查看
公式说明：哈哈，没有说明，不妨请你开动脑筋琢磨一下，再留言与我们交流吧！
以上，就是小花对 SUBSTITUTE 函数的一些研究心得，包括：
❶ 基操级：从替换、删除、插入到换行
❷ 强化级：文本计数和去单位求和
❸ 烧脑级：混合文本多数字取最大值、罗列型数字求和
你学会了吗？你觉得哪个用法让你心动，哪个用法让你眼晕，不妨留言与我们交流吧！
📑
秋叶家 5 月专享福利来袭——《3 天 Excel 集训体验营》。
专为职场人准备，全部基于职场真实表格案例设计，超实用 Excel 技巧集合教学。
从此不愿再为表格而加班，那就行动起来！3 天搞定表格难题！
《秋叶 Excel 3 天集训营》
想提升，趁现在！
福利名额 仅限前 100
不再被加班支配，不再为表格发愁！
免费赠送【35 个常用函数说明】哦！
赶紧扫码预约吧！
👇👇👇
▲ 报名成功自动弹出班主任二维码，切勿提前退出
若未能添加，请联系公众号客服获取
🎁

为感谢一直以来各位小伙伴的支持， 秋叶 PPT 开设读者群啦！ 欢迎大家扫描下方二维码加入哦！
群内不仅有海量 Office 资料包，还有各种职场干货分享、红包掉落等福利~
赶紧扫码入群，解锁更多惊喜
↓ ↓ ↓
请关注「秋叶 PPT」旗下优质账号
↓ ↓ ↓
继续阅读
阅读原文