点击蓝字【秋叶 Excel】👆
发送【交流】
立即进秋叶 Excel 读者快乐交流群！
![]()
本文作者：小花
本文编辑：竺兰
留言区获取练习文件
👇👇👇
Excel 函数的魅力就在于，每一个简单的函数吃透了，就一定有别样的精彩。SUBTITUTE 函数就是一个典型的例子，高手眼中，它可不只是一个文本替换函数。与 REPLACE 函数替换某一文本字符串中指定位置处的任意文本不同，SUBSTITUTE 函数可以在某一文本字符串中替换任意指定的文本。举个例子，要将房源编码「1 号住宅楼-101」简化为「1-101」，我们需要将房源编码中的「号住宅楼」删除，使用 SUBSTITUTE 函数将「号住宅楼」替换为空即可实现。SUBSTITUTE 函数的第三个参数为空，表示将旧文本替换空；第四个参数省略，表示将 A2 文本中的所有「号住宅楼」旧文本全部替换为空，其最终效果就是删除旧文本。了解了 SUBSTITUTE 函数的基本用法后，我们再来看看，替换指定文本这一基本功能是如何被用到极致的？以上案例中，SUBSTITUTE 将旧文本替换为空即为实现删除指定文本，那如果将旧文本替换为新文本+旧文本，是否可以实现插入功能呢？答案是肯定的。例如，我们使用 SUBSTITUTE 函数将「-」替换为「-B2-」，从而实现在部门和姓名中插入职位。=SUBSTITUTE(A2,"-","-"&B2&"-")
同样的思路，如果将指定旧文本替换为换行符，便可以实现在指定文本或符号处自动换行显示。这里需要说明的是，换行符无法直接输入到函数中，因此我们使用 CHAR(10)来获得换行符。PS：CHAR 函数的作用是根据代码数字制定的字符，数字 10 对应的字符就是换行符。=SUBSTITUTE(A2,"，",CHAR(10))
在填列名单时，有些人习惯于将全部人名填到一个单元格中，用特定符号隔开，这就增加了计算人数的难度。
还好有 SUBSTITUTE 函数，只需将特定符号替换为空，再计算替换前后的文本字符数之差，就可轻松计算人数。=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1
LEN 函数可以计算出文本中的字符个数，观察名单文本特点不难发现，替换前后的字符格式之差+1 即为人数。工作中，我们经常遇到将数值和单位填到同一单元格内的情况，此时求和就变得非常困难了。但如果你会 SUBSTITUTE 的升级应用，那将不再是问题。举个例子，如下图，我们需要根据销售面积和价格都带单位的数据求出销售金额，此时我们就需要使用 SUBSTITUTE 将指定单位文本去除后再进行求和。=SUMPRODUCT(SUBSTITUTE(B2:B6,"平",)*SUBSTITUTE(C2:C6,"万",))
SUMPRODUCT 引导两个数组乘积和运算，两个数组均由 SUBSTITUTE 函数计算得来。SUBSTITUTE 分别将面积单位「平」和价格单位「万」替换为空，即删除单位，得到两个文本型数字组成的数组，在 SUMPRODUCT 函数中，乘法可以将文本型数字转化为数值型数组，从而实现相乘并求和。如果上述五种应用你都能掌握，那么恭喜你，SUBSTITUTE 函数你已几乎可以轻松拿捏。以下谈到的神级应用，功力不深者切勿挑战哦，以免误伤脑细胞！![]()
以下案例中，我们要从包含成绩的混合文本中提取三科成绩最高分，需要用到 MAX 函数、 ROW 函数与 SUBSTITUTE 函数来构建数组公式。{=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 函数的一些研究心得，包括：❸ 烧脑级：混合文本多数字取最大值、罗列型数字求和你学会了吗？你觉得哪个用法让你心动，哪个用法让你眼晕，不妨留言与我们交流吧！![]()
欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。现在进群，还会掉落各种学习资源，助力大家提升办公效率![]()