有一个Excel函数叫：省时一整天，你却连我的名字都不知道…
编按
Hello大家好，今天给大家介绍一下SUBSTITUTE函数，它可以将数据中的旧值替换为新值，与我们熟悉的“查找替换”功能相似。很多小伙伴可能会说，简单的查找替换就能解决的问题，我为什么还要学习用复杂的函数？相比Excel的基础操作，函数可以构建和数据源之间的动态关联，当我们的数据源发生变化和，函数会自动更新结果，但基础操作却不会。所以我们今天就通过5个案例一起来学习一下SUBSTITUTE函数吧~
今天给大家介绍一个可以进行查找替换的函数---SUBSTITUTE函数，SUBSTITUTE函数的基础语法是：SUBSTITUTE (要替换的文本,旧文本,新文本,[替换第几个])。
最后一个参数，[替换第几个]，是可以省略的，如果要替换的文本存在多个的话，省略这个参数表示替换全部。
先通过一个示例来掌握SUBSTITUTE函数的基本用法。
扫码入群，下载Excel练习文件，同步操作
示例1：将单元格里的“付款”替换成“账期”
公式为=SUBSTITUTE(B2,"付款","账期")。
这个公式省略了最后一个参数，所以将单元格里的所有“付款”都换成“账期”。如果只想替换第一个“付款”，公式需要修改为：=SUBSTITUTE(B2,"付款","账期",1)。
如果是“预付款”不进行替换，公式可以修改为：=SUBSTITUTE(B2,"天付款","天账期",1)。
通过这个例子相信大家对于SUBSTITUTE函数的基本用法应该明白了。不过在实际应用中，单独使用SUBSTITUTE函数的机会很少，基本上都是和其他函数组合使用的，下面的几个例子都是组合套路，非常实用。
示例2：SUBSTITUTE组合MID加密手机号
这里所说的加密就是将手机号的中间四位显示成*，公式为：
=SUBSTITUTE(A2,MID(A2,4,4),"*****")。
公式的原理很简单，MID(A2,4,4)是从手机号的第4位开始提取4个数字，用SUBSTITUTE函数把这部分内容换成"*****"，从而实现了手机号加密。
示例3：SUMPRODUCT组合SUBSTITUTE实现带单位的数字求和
公式为：=SUMPRODUCT(--SUBSTITUTE(A2:A13,"元",""))。
首先用SUBSTITUTE(A2:A13,"元","")将区域中数据的单位“元”替换为空，因为SUBSTITUTE函数得到的结果是文本格式，所以前面用两个负号将替换后的数据变成数值。
最后用SUMPRODUCT函数对这一组数字求和。
（注意：数字加单位是一种不规范的用表习惯，如果确实需要加单位可以用自定义格式实现。）
示例4：LEN组合SUBSTITUTE统计一个单元格内的人数
公式为：=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1。
在这个公式中，LEN(B2)取得B2单元格中字符串的长度。LEN(SUBSTITUTE(B2,"、",))+1的意思是用LEN计算不含顿号的字符串长度。在这个例子中，人名之间的间隔符是顿号，最后加1，是因为最后一个人名没有顿号。
用B2原有的长度减去被替换掉人名之间间隔符的长度，也就是人数。使用这个公式要注意，每个名字之间的分隔符必须是一样的，否则统计结果就会出错。
示例5：五个函数联手实现数据分列
将示例4中存在于一个单元格的多个人物分开，每个单元格只存放一个人物，公式需要用到五个函数。
公式为：=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))。
这个公式的原理比较复杂，篇幅所限仅做简要解释。
REPT(" ",100)：先使用REPT函数，将空格重复100次，得到100个空格；
SUBSTITUTE($B2,"、",REPT(" ",100))：使用SUBSTITUTE函数将姓名中的的间隔符号顿号替换为100个空格；
MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)：再使用MID函数，依次从带有空格的新字符串中的第1、第101、第201位……截取长度为100的字符。
这样得到的字符串是带有多余空格的，因此再使用TRIM函数将多余空格删除掉。如果实在不好理解会套用即可。
今天分享的五个SUBSTITUTE使用案例是非常典型的常见用法，希望大家能够好好利用，简化自己的工作提高工作效率。
最新文章
-
揭秘：伤寒汗出病解的方式有哪些
6分钟前 -
洗髓修炼中的：找病、攻病、翻病、愈病！
21分钟前 -
（原创）颤证的经方治疗案。。。
27分钟前 -
中医教你：看指甲上的月牙诊病
39分钟前 -
中药方论（24）
42分钟前 -
仅一味中药就可以降血糖 ，一味中药的单药方！
44分钟前 -
解郁安神颗粒：抑郁症、焦虑症都是因为肝的疏泄功能出现了失调
55分钟前 -
有一种痛苦叫牙疼，有种止疼药叫艾灸！收藏起来，以备不时之需！
1小时前
有话要说...