|替换函数Substitute,用法大全,值得收藏备用!
文章图片
文章图片
查找替换 , 是Excel中的常见操作 , 除了用命令完成之外 , 一些特殊情况下 , 还可以用函数完成 , 此函数就是Substitute , 可以将指定数据中指定字符串的值替换为新值 , 此函数也因此而得名替换函数 。
一、功能及语法结构 。
功能:将字符串中的部分字符串
语法结构:=Substitute(源字符串被替换字符串替换字符串[替换位置
);当省略“替换位置”时 , 默认从第一个位置开始替换 。
基础示例:
目的:将“性别”中的“男”替换为1 , “女”替换为2 。
方法:
在目标单元格中输入公式:=IF(D3=\"男\"SUBSTITUTE(D3\"男\"1)2) 。
解读:
学习到这里 , 部分亲可能会有疑问:要完成上述需求 , 用【查找替换】岂不是更简单?为什么要用Substitute?其实 , 这要从函数的优势说起 , 函数可以构建和数据源之间的动态关联 , 当数据源发生了变化 , 函数可以自动更新获取最新结果 。 换句话说 , 函数具有自动化处理数据的能力 , 而基础操作却无法完成自动化处理的功能 。
二、经典案例解读 。
(一)隐藏手机号中间4位 。
方法:
在目标单元格中输入公式:=SUBSTITUTE(C3MID(C344)\"****\") 。
解读:
使用上述方法可以隐藏手机号中间的4位 , 达到加密的目的 , 那隐藏身份证号码中的出生年月是不是同样的道理呢?但要注意的是此操作是不可逆的 。
(二)对含有单位的值进行求和 。
方法:
在目标单元格中输入公式:=SUMPRODUCT(SUBSTITUTE(H3:H12\"元\"\"\")*1) 。
解读:
1、公式中 , 首先利用Substitute函数将H3:H12区域中的单位“元”替换为空值 , 然后×1 , 强制换换为数值 , 最后用Sumproduct函数对齐求和 。
2、或在目标单元格中输入公式:=SUM(SUBSTITUTE(H3:H12\"元\"\"\")*1) , 并用Ctrl+Shift+Enter填充即可 。
(三)计算文本的数量 。
【|替换函数Substitute,用法大全,值得收藏备用!】
方法:
在目标单元格中输入公式:=LEN(C3)-LEN(SUBSTITUTE(C3\"、\"\"\"))+1 。
解读:
公式中首先用Len函数计算源字符串的长度 , 然后用Substitute函数将分割字符“、”替换为空值 , 并计算长度 , 最后+1修正得到想要的结果 , 如果不理解+1的原因 , 可以查阅一下植树原理 。
- excel|职场办公必备的9个函数公式,你还不掌握吗?那就加班别喊累
- javascript|web前端培训 - 如何设置函数参数的默认值
- 销售额|能求和,还能计数的Sumproduct函数,你真的会用吗?
- 3g|关于Countif函数,其功能不止是计数,还有这几个高级用法
- 华为|美国成绩正式公布,替换华为的后果出现了!
- 英伟达|工作中必备的20个基础函数,示例解读,收藏备用!
- b-2轰炸机|掌握了这个套路,无论用 Excel vlookup 函数查找第几次结果都很轻松
- MySQL|Mysql数据库替换整张表中某字段中指定的部分字符
- excel|10个必须掌握的Excel文本函数,直接套用,方便快捷,收藏备用吧
- PHP|MD5绕过的技巧