excel中的替换函数

admin 办公软件 2020-04-05 浏览

在excel中，常用的替换函数有replace和substitute函数，这两个函数都可以替换单元格中的部分内容，功能和ctrl+H的功能类似。

1.substitute的参数=substitute（单元格，被替换的字符串，新字符串，指定替换第几个），第四个参数可以省略，表示全部替换。

2.replace函数的参数=replace（单元格，从第几个字符开始替换，替换的字符个数，新字符串）。

从参数可以看出来这两个函数的替换角度是有区别的，前者是直接指定把**替换为**，

后者是从第几个字符开始替换，替换几个字符，替换成什么。这两种思路在工作中根据具体情况选择简单的一种即可。

一。基本的使用方法：

例子：把身份证中的年月日用四个字符串代替

解释以上函数：1.函数=REPLACE(C2,7,8,"****")。表示在C2单元格中，从左数第7位开始，往右数8个字符，把这8个字符替换为****。

2.函数=SUBSTITUTE(C2,MID(C2,7,8),"****")，这个函数中的mid函数表示在C2单元格中，从第7位开始，提取8位字符，返回的结果就是19901203，

然后substitute函数表示把C2单元格中19901203替换为****。

二，利用substitute函数替换字符。(注意substitute中的最后一个参数

在下图中，如果要替换“滚滚长江东逝水，浪花淘尽英雄。”中的两个“滚滚”，

则输入函数=SUBSTITUTE(A2,"滚","")，省略了第四个参数，表示把A2单元格中所有的“滚”字替换为空值。

如果只替换一个“滚”，则输入函数=SUBSTITUTE(A2,"滚","",1)，这里第四个参数没有省略，1表示只替换其中一个“滚”。

三.substitute函数与sumproduct函数结合进行求和。

在下图中，C列中数据不是数值，而是数字加文本组合，利用sum等数值函数无法求和。

此时运用substitute函数与sumproduct函数组合就可以进行求和。函数公式为=SUMPRODUCT(--SUBSTITUTE(C2:C10,"元",""))&"元"。

这个公式中的SUBSTITUTE(C2:C10,"元","")表示把C2到C10单元格的“元”替换为空值，前面--表示减负号，把单元格文本格式变成数值格式，

然后利用sumproduct函数进行求和，最后用连接符加上“元”字。

如果把sumproduct函数改成sum函数的话，就成了数组函数，按下ctrl+shift+enter才能算出正确的结果。

拓展1：sumproduct函数（数组求和函数）

对于sumproduct函数，公式参数特别简单，即=SUMPRODUCT（数组1,数组2,数组3, ……），每个数组之间用逗号隔开，表示数组之间先相乘再求和。

点击这里跳转查看sumproduct函数详细使用方法

四。substitute函数与len函数结合计算单元格重复字符的个数。

在下图中，要如何计算A2单元格中的3重复出现了多少次呢？输入函数公式=LEN(A2)-LEN(SUBSTITUTE(A2,3,""))即可算出3出现了4次。

此处SUBSTITUTE(A2,3,"")，表示把A2单元格中的3全部替换为空值，然后前面加上len函数表示去掉3以后单元格字符串的长度，

而len（A2）表示A2单元格字符串的长度，两个len函数相减就是重复值的个数了。