给SUBSTITUTE函数加个“S”,性能开挂,实现批量替换!

职场   职场   2024-09-18 08:49   河北  


Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能
Super Excel Man
用1%的Excel基础搞定99%的职场问题
做一个超级实用的Excel公众号
Excel是门手艺玩转需要勇气
数万Excel爱好者聚集地


    



不得不说金山WPS表格对于国人的使用习惯还是研究的相当不错的,今天我们就来讲解一个金山WPS表格最新版本拥有的而微软Office Excel没有的函数。


这个函数就是SUBSTITUTES函数,不要看得太快,注意SUBSTITUTES函数比我们常用的替换函数SUBSTITUTE多一个字母“S”。在英语中一般名词后加上一个后缀字母“S”,代表该名词的复数,也就是多个的意思。同样的道理,SUBSTITUTE函数是单个替换文本的函数,加上一个“S”变成SUBSTITUTES后,就可以实现批量替换文本的效果了。


以前我们要想实现批量替换,可能要使用到VBA代码或者Power Query编辑器等方式,现在我们只需要一个简单的函数即可解决。

如下图所示

原内容中包含“红色”、“黄色”、“黑色”等内容,我们有一个替换内容的对照表,即红色用英文Red替换,黄色用英文Yellow替换,黑色用英文Black替换,替换完成后的数据放置到A7:A9区域内。这是一个典型的批量替换案例。



在以前没有出现SUBSTITUTES函数之前,我们只能使用SUBSTITUTE函数,假如对照表中有100个需要替换的内容,我们则需要嵌套100次SUBSTITUTE函数,使用效率非常的低下。


SUBSTITUTE函数将字符串中的部分字符替换为新字符

基本参数

SUBSTITUTE(待处理的字符, 旧文本, 新文本,[替换第几次出现的旧文本])


比如说我们需要先替换A2:A4数据源中的所有内容“红色”变为“Red”,则需要输入如下函数公式:

=SUBSTITUTE(A2:A4,C2,D2)


即将A2:A4数据源中,将旧内容C2单元格数据替换为新内容D2单元格的数据。


这样只能完成对“红色→Red”的替换



我们需要在上一步SUBSTITUTE(A2:A4,C2,D2)替换的数组溢出结果基础上再次进行对“黑色→Black”的替换:

=SUBSTITUTE(SUBSTITUTE(A2:A4,C2,D2),C3,D3)


这样只能完成对“红色→Red”和“黑色→Black”的替换



最后我们需要在上一步SUBSTITUTE(SUBSTITUTE(A2:A4,C2,D2),C3,D3)替换的数组溢出结果基础上再次进行对“黄色→Yellow”的替换:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A4,C2,D2),C3,D3),C4,D4)


这样最终完成对“红色→Red”和“黑色→Black”和“黄色→Yellow”的全部替换。这样只嵌套了三个需要替换的内容,函数公式就如此的长了,显得非常的复杂繁重



那么有了SUBSTITUTES批量替换函数后,就非常的简单了,简直一气呵成

=SUBSTITUTES(A2:A4,C2:C4,D2:D4)


SUBSTITUTES函数只比SUBSTITUTE函数多个一个S,实用性确比其强千万倍。因为它可以实现批量替换。


SUBSTITUTES(替换的字符,旧字符,新字符,替换的序号)

参数乍一看没什么区别,但是旧字符和新字符均支持数组的。


本例中使用SUBSTITUTES函数将A2:A4区域内的内容,把C2:C4区域的旧内容批量替换成为D2:D4区域内的新内容。



其实重复学习Excel,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。


 

 

知识点精彩超链接点击阅读

VLOOKUP的新搭档HSTACK函数,实现逆向查询
一个小小的VSTACK函数,就能实现多工作表合并
VLOOKUP最新用法,提取混合内容中的手机号
带超链接的VLOOKUP函数,让查询体验起飞
最新方法来了:按指定的次数重复数据
合并工作表,用两个函数就搞定啦
比VLOOKUP还好用的多条件查找
点击谁就筛选谁,实现筛选自动化
以“数字”作为分隔符,进行分列
点击谁,就对谁自动条件求和
更多实用内容请在号内历史文章搜索 

Excel情报局
Excel表格爱好者,分享一些日常的积累。做一个职场人都能看懂的表格技巧公众号。多学一个Excel小技巧,会让你在职场中多一分底气与自信。(同名视频号)
 最新文章