五种SUBSTITUTE函数用法,总有一个奇奇怪怪~ HI,大家好,我是星光。 今天给大家分享一个函数,叫做SUBSITUTE。它是Excel文本处理中最常使用的函数之一,可以将字符串中的旧值替换为新值,类似于基础操作里的查找与替换功能,因此又被称为函数中的查找替换机。
1 丨 基本语法 SUBSTITUTE函数的基本语法如下: SUBSTITUTE(源字符串,旧文本,新文本,[替换第几个旧文本]) 其中第4个参数[替换第几个旧文本]是可以省略的,绝大部分情况下也都用不上。 举个简单的小例子。
如上图所示的数据,需要将B列的二班,替换为一班,C2单格输入以下公式,向下复制填充即可。 公式中B2是源字符串,’二班’是被替换的旧值,’一班’是替换成的新值——是不是很简单? 有朋友可能会说,这事我用查找替换就可以完成,为什么要用函数? 相比于基础操作,函数的优势在于它可以构建和数据源之间的动态关联,当数据源发生了变化,函数可以自动更新结果;另外,也可以作为一个子函数,将计算结果嵌套在其它函数中,完成远比基操复杂的计算目标。 换而言之,函数具有小规模自动化处理数据的能力,而基础操作自身并不具备这种能力。当基操具备这种能力时,它就——变成了PowerQuery
打个响指,给大家循序渐进举几个例子,分享下SUBSTITUTE函数的常用技巧和套路。 2 丨 自古深情留不住 只有套路得人心 1 ▎隐藏手机号中间5位 如下图所示的数据,需要将B列的手机号隐藏中间5位,结果如C列所示。
C2单格输入公式如下: =SUBSTITUTE(B2,MID(B2,4,5),’*’) 公式先用MID(B2,4,5)函数从B2单格第4个字符开始取5个长度的结果,然后再使用SUBSTITUTE函数把这部分替换为5个星号。 2 ▎对含单位的数据进行求和 如下图所示的数据,需要在B7单格对B2:B6区域的人数统计求和。由于区域内的人数并非数值,无法直接使用SUM函数。
B7单格输入公式如下: =SUMPRODUCT( SUBSTITUTE(B2:B6,’人’,”)*1) 公式先运行: SUBSTITUTE(B2:B6,’人’,”) 将区域内的单位’人’,替换为空,结果如下: {‘3′;’7′;’8′;’2′;’6’} 文本函数返回的结果必然是文本,因此这些数值也都属于文本型数值,使用数学运算*1,强制转换为数值,最后再使用SUMPRODUCT函数统计求和。 3 ▎混合文本中计算人员个数 如下图所示的数据,B列是参会人员名单,现在需要在C列统计各班参会人数。
C2单格输入以下公式: =LEN(B2)-LEN(SUBSTITUTE(B2,’、’,”)) 1 LEN(B2)取得B2字符串的长度。 LEN(SUBSTITUTE(B2,’、’,)) 1,替换掉人名之间的分隔符,也就是顿号,然后再用LEN计算该字符串的长度。最后加1,是因为最后一个人名没有顿号,需要补上。如此一来,每个分隔符就代表一个人名。 用A2数值原有的长度减去被替换掉人名分隔符的长度,也就是人名的个数。 4 ▎混合文本中计算整数最大值 如下图所示的数据,A2单格是各班人数的数据,现在需要在B2单格计算各班中最多的人数。
B2单格输入以下数组公式: =MAX((SUBSTITUTE(A2,ROW(1:99),”)<>A2)*ROW(1:99)) SUBSTITUTE(A2,ROW(1:99),)<>A2部分,以A2为源字符串,依次将数值1到99替换为空,再把替换后的结果和源字符串进行比较,如果不相等,则证明A2中存在该数值,返回逻辑值TRUE,否则返回逻辑值FALSE。 最后将这组逻辑值TRUE和FALSE,分别乘以被替换的数值(1..99),其中TRUE*数值,返回数值自身;FALSE*数值,返回0;用MAX函数从中取得最大值,即为结果。 5 ▎计算数据最大连续次数 如下图所示的数据,C列是星光足球队的战绩,需要在F2单格统计连胜的最大次数。
F2单格公式如下: =MAX( (SUBSTITUTE(CONCAT(C2:C15), REPT(‘胜利’,ROW(1:14)),”) <>CONCAT(C2:C15)) *ROW(1:14)) PHONETIC(C2:C15)函数将C列战绩合并成一个字符串,作为SUBSTITUTE函数的源文本。 REPT(‘胜利’,ROW(1:14)),将胜利重复1..14次,14是最大可能的连胜次数。 此时思路回到了案例4。SUBSTITUTE函数将REPT函数的结果,在源字符串里替换掉,然后和源字符串比较是否相等。如果源字符串存在替换值,则和原值不相等,返回逻辑值TRUE,否则返回逻辑值FALSE。 最后依然把这一组逻辑值TRUE或FALSE,分别乘以ROW(1:14),用MAX函数从中取得最大值。 6 ▎将数据按指定分隔符拆分为 如下图所示的数据,B列是多个爱好合并在一起,现在需要把它们按分隔符’/’拆分,结果如C:F列所示。
C2单格输入以下公式,向下向右复制填充: =TRIM(MID(SUBSTITUTE($B2,’/’,REPT(‘ ‘,100)),COLUMN(A1)*100-99,100)) SUBSTITUTE($B2,’/’,REPT(‘ ‘,100))部分,将源字符串B2单格数据中的分隔符’/’替换为100个空格。这样就将不同爱好分别划分到了100个空格构建的小房间里。 然后再使用MID函数,按顺序从小房间里取出爱好,最后使用TRIM函数消除多余的空格。该套路我们有一个专门的单章来讲述,可参考函数系列教程文本处理篇的「如何按指定间隔符拆分数据」 有朋友可能会想,函数这么复杂,我还是用分列吧……谁不想回到小时候过无忧无虑的日子呢
嘿!没了,挥一挥胳膊,不带走一根头发。有啥问题可以在VIP会员群中提问交流↓👇↓ 挥挥手,咱们明天再见。 本文由“Excel星球”首发。
2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/78589.html