海信|substitute函数的用法,附带几个经典使用案例

海信|substitute函数的用法,附带几个经典使用案例

文章图片

海信|substitute函数的用法,附带几个经典使用案例

文章图片

海信|substitute函数的用法,附带几个经典使用案例

文章图片

海信|substitute函数的用法,附带几个经典使用案例

在Excel中 , 有个字符串函数 substitute , 它的功能非常强大 , 可以实现很多让你意想不到的功能 , 今天我们就来讲解下它的应用案例 。
语法:
SUBSTITUTE(要替换的文本 , 旧文本 , 新文本 , 替换第几个)
比如要把“我得你得他得”中的所有“得”替换为“的” 。
=SUBSTITUTE(“我得你得他得”\"得\"\"的\") , 结果为:“我的你的他的” 。
但是如果想替换第2个“得”
=SUBSTITUTE(“我得你得他得”\"得\"\"的\" , 2) 结果为:“我得你的他得”
一、隐藏关键信息 , 比如隐藏电话号码或者身份证中间的位数变为\"*\"号 , 为什么审核方便 , 我用名字代替 , 实际上隐藏电话也是如此 。

公式为:=SUBSTITUTE(A2MID(A221)\"*\")
如果姓名这里是电话号码隐 , 电话号码是11位 , 所以公式改为
=SUBSTITUTE(A2MID(A244)\"****\")

二 , 替换相同的内容 , 比如成绩列中有缺考 , 它不是分数 , 我们给他替换成0分 。

公式为:=SUBSTITUTE(B2\"缺考\"0)
要学习更多的函数知识 , 可以查阅我的专栏 。 专栏中对很多Excel常见函数进行了详细的讲解 。

Excel函数进阶与提升Excel技术控购买专栏三 , 计算被逗号分隔的人数len+substitute的使用


公式为:=LEN(C2)-LEN(SUBSTITUTE(C2\" , \"\"\"))+1
原理特别简单 , 我们通过观察可以发现那些别逗号分隔的人名 , 逗号的数量+1就是总人数 。
所以我们只要求出逗号的个数就行了 。
LEN(C2)计算出人名+逗号的长度
LEN(SUBSTITUTE(C2\" , \"\"\")) 把逗号替换成了空值 , 就生下人名 , 所以计算出了人名的长度 。
LEN(C2)-LEN(SUBSTITUTE(C2\" , \"\"\"))两者相减就是逗号的数量 , 然后加上1 , 不就是人数吗 。
四、带单位数字求和

=SUM(SUBSTITUTE(B2:B14\"分\"\"\")*1)
注意这个公式因为是数组公式 , 所以使用ctrl shift 回车确认
如果你不想ctrl shift 回车确认 , 可以用下面的公式
=SUMPRODUCT(SUBSTITUTE(B2:B14\"分\"\"\")*1)
为什么要乘以1 , 是因为要把文本转成数值 。
以上呢 , 就是substitute函数的4个用法 , 你还有substitute函数的其他用法吗 , 欢迎在评论区留言讨论 。
【海信|substitute函数的用法,附带几个经典使用案例】