Excelers(网名:看见星光)
SUBSTITUTE函数是Excel常用的文本函数之一,类似于基础操作的查找替换功能,当然,两者亦有不同之处。
SUBSTITUTE函数的用法是:
SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])
最后一个参数,[替换第几个], 是可以省略的。
今天通过几个小栗子,和大家分享下SUBSTITUTE函数的常用技巧和套路。
例子一:将数据中的某个值替换为另一个值
比如将B列数据里的二班,替换为一班。
C2输入公式,并向下复制填充:
=SUBSTITUTE(B2,'二班','一班')
imgLoading这里没啥好解释的了,就是将B列中单元格中的“二班”全部替换为“一班”。
imgLoading例子二:隐藏手机号的中间5位
C2输入公式,并向下复制填充:
=SUBSTITUTE(B2,MID(B2,4,5),'*****')
imgLoading先使用MID函数取得B列号码中的中间五位,再用字符串“*****”替换掉这部分内容。
imgLoading例子三:含单位的数据求和
B7输入公式:
=SUMPRODUCT(SUBSTITUTE(B2:B6,'人',)*1)
imgLoading先用SUBSTITUTE替换掉B列单元格中的“人”,得到文本型数字,乘以1后变成可以计算的数值,再用SUMPRODUCT函数进行数组求和。
imgLoading例子四:数据分列,将B列数据按顿号进行分列。
C2单元格输入公式横向拖动,并向下复制填充。
=TRIM(MID(SUBSTITUTE($B2,'、',REPT(' ',100)),COLUMN(A1)*100-99,100))
imgLoadingREPT(' ',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函数将多余空格删除掉,OK了。
imgLoading例子五:混合文本中,计算人数个数
imgLoadingB2公式:
=LEN(A2)-LEN(SUBSTITUTE(A2,'、',)) 1
LEN(A2)取得A2字符串的长度。
LEN(SUBSTITUTE(A2,'、',)) 1,替换掉人名之间的间隔符,也就是顿号,再用LEN计算该值的长度,最后加1,是因为最后一个人名没有顿号,需要补上。
用A2数值原有的长度减去被替换掉人名之间间隔符的长度,也就是人名的个数。
imgLoading例子六:混合文本中,计算数值最大值。
B2数组公式:
=MAX((SUBSTITUTE(A2,ROW($1:$98),)<>A2)*ROW(1:98))
SUBSTITUTE(A2,ROW($1:$98),)<>A2
依次将数值1到98从A2替换为空,然后把替换后的结果和被替换值(1-98)进行比较 ,如果不相等,则证明A2中存在该数值。
最后将证明的结果,真和假,也就是逻辑值TRUE和FALSE,乘以被替换的值(1-98),用MAX函数从中取得最大值。
imgLoading例子七:计算某个值在某个范围的最大连续次数
imgLoadingB2数组公式:
=MAX((SUBSTITUTE(PHONETIC(A2:A9),REPT('A',ROW(1:9)),)<>PHONETIC(A2:A9))*ROW(1:9))
PHONETIC(A2:A9)
将A2:A9的文本值黏合成一个值,以便SUBSTITUTE函数进行操作。
REPT('A',ROW(1:9))
把“A”重复1到9次。
SUBSTITUTE(PHONETIC(A2:A9),REPT('A',ROW(1:9)),)<>PHONETIC(A2:A9)
思路回到示例6,SUBSTITUTE函数将REPT函数的结果,在PHONETIC函数结果里替换掉,然后和PHONETIC函数原值进行比较。如果后者存在替换值,则被替换掉,和原值不相等,返回FALSE,否则返回TRUE。
最后依然把逻辑值TRUE和FALSE,乘以ROW(1:9),用MAX函数从中取得最大值。
imgLoading转载请注明出处,谢谢。更多精彩,请持续关注呗。
联系客服