二维码 购物车
部落窝在线教育欢迎您!

替换函数SUBSTITUTE的5个应用技巧

 

作者:郅龙来源:部落窝教育发布时间:2021-10-08 10:10:15点击:298

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

Hello大家好,今天给大家介绍一下SUBSTITUTE函数,它可以将数据中的旧值替换为新值,与我们熟悉的“查找替换”功能相似。很多小伙伴可能会说,简单的查找替换就能解决的问题,我为什么还要学习用复杂的函数?相比Excel的基础操作,函数可以构建和数据源之间的动态关联,当我们的数据源发生变化和,函数会自动更新结果,但基础操作并没有。所以我们今天就通过5个案例一起来学习一下SUBSTITUTE函数吧~

 

今天给大家介绍一个可以进行查找替换的函数---SUBSTITUTE函数,SUBSTITUTE函数的基础语法是:SUBSTITUTE (要替换的文本,旧文本,新文本,[替换第几个])。最后一个参数,[替换第几个],是可以省略的,如果要替换的文本存在多个的话,省略这个参数表示替换全部。

 

先通过一个示例来掌握SUBSTITUTE函数的基本用法。

 

示例1:将单元格里的“付款”替换成“账期”

公式为=SUBSTITUTE(B2,"付款","账期")

 

 

这个公式省略了最后一个参数,所以将单元格里的所有“付款”都换成“账期”。如果只想替换第一个“付款”,公式需要修改为:=SUBSTITUTE(B2,"付款","账期",1)

 

 

如果原来是“预付款”不进行替换,公式可以修改为:=SUBSTITUTE(B2,"天付款","天账期",1)

 

 

通过这个例子相信大家对于SUBSTITUTE函数的基本用法应该明白了。不过在实际应用中,单独使用SUBSTITUTE函数的机会很少,基本上都是和其他函数组合使用的,下面的几个例子都是组合套路,非常实用。

 

示例2SUBSTITUTE组合MID加密手机号

这里所说的加密就是将手机号的中间四位显示成*,公式为:

=SUBSTITUTE(A2,MID(A2,4,4),"*****")

 

 

公式的原理很简单,MID(A2,4,4)是从手机号的第4位开始提取4个数字,用SUBSTITUTE函数把这部分内容换成"*****",从而实现了手机号加密。

 

示例3SUMPRODUCT组合SUBSTITUTE实现带单位的数字求和

公式为:=SUMPRODUCT(--SUBSTITUTE(A2:A13,"",""))

 

 

首先用SUBSTITUTE(A2:A13,"","")将区域中数据的单位“元”替换为空,因为SUBSTITUTE函数得到的结果是文本格式,所以前面用两个负号将替换后的数据变成数值,最后用SUMPRODUCT函数对这一组数字求和。(注意:数字加单位是一种不规范的用表习惯,如果确实需要加单位可以用自定义格式实现。)

 

示例4LEN组合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使用案例是非常典型的常见用法,希望大家能够好好利用,简化自己的工作提高工作效率。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。