头部广告

Excel中的替换神技,替换函数Substitute用法大全,值得学习收藏

在使用WPS时对Excel数据进行查找替换是常见操作,除了我们常用的查找替换命令,我们有时对于一些特殊情况,我们就需要使用到Substitute函数,主要是用于将指定数据中指定字符串的值替换为新值。今天就跟大家一下Substitute函数的常见用法和使用技巧,希望对大家有所帮助。

第一:Substitute函数介绍

Substitute函数其实就是表格文本替换函数,主要是用于将指定数据中指定字符串的值替换为新值。

语法

SUBSTITUTE(text,old_text,new_text,instance_num)

■Text 为需要替换其中字符的文本,或对含有文本的单元格的引用。

■Old_text 为需要替换的旧文本。

■New_text 用于替换 old_text 的文本。

■Instance_num 为一数值,用来指定以 new_text 替换第几次出现的 old_text。

如果指定了 instance_num,则只有满足要求的 old_text 被替换;

否则将用 new_text 替换 TEXT 中出现的所有 old_text。

第二:经典用法解读

(一)文本替换

如下图所示将A1单元格中的“赵飞”替换成“赵云”,只需在目标单元格中输入公式:=SUBSTITUTE(A1,"赵飞","赵云")

1.jpg


2.gif


(二)去掉字符的空格

如下图所示将A1单元格中的姓名中的空格去掉,只需在目标单元格中输入公式:=SUBSTITUTE(A1," ","")

3.gif


(三)将“性别”中的“男”替换为2,“女”替换为1。

如下图所示,我们将表格中的性别这一列中的“男”替换为2,“女”替换为1,只需在目标单元格中输入公式:=IF(B2="女",SUBSTITUTE(B2,"女",1),2)

4.jpg


5.gif


(四)隐藏手机号中间4位以"*"号显示

有时为了保护个人手机信息不被泄露,常常需要把手机号码中间部分隐藏,这时我们就可以使用Substitute函数隐藏手机号中间的4位,达到加密的目的,具体操作如下

在目标单元格中输入公式:=SUBSTITUTE(D2,MID(D2,4,4),"****")

6.jpg


7.gif


解读:

上面的公式使用到了SUBSTITUTE()函数和MID(),MID()函数返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

MID()函数语法:

MID(text,start_num,num_chars)

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1,以此类推。

先通过MID(D2,4,4)获取手机号从左侧第四位开始,然后去四位数,最后通过SUBSTITUTE()函数把取得的数值都替换"****"。

那么同样的道理,也可以通过此方法隐藏身份证号码中的出生年月或者其它制定信息,但要注意的是此操作是不可逆的。

(五)对带单位的数值进行求和

我们在平时使用WPS办公时,经常会遇到需要对带有单位的数值进行求和,这时如果直接使用求和公式的话就无法处理,我们就可以通过SUBSTITUTE()函数在进行求和。

在求和单元格中输入公式=SUMPRODUCT(SUBSTITUTE(D2:D8,"件","")*1),按Enter回车键即可获求和。

7.jpg


8.gif


解读:

SUMPRODUCT函数公式介绍

SUMPRODUCT(array1,array2,array3, ...)

Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

说明

■数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。

■函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

同样先使用SUBSTITUTE(D2:D8,"件","")就是把数值中的单位替换成空,然后*1后使用SUMPRODUCT函数进行数组相乘求和。

(六)统计文本数量

统计文本数量的情况我们经常用在统计人数这块,如下图所示每个部门参会人员使用"、"隔开,我们想统计每个部门有多少人,我们可以使用Len函数搭配Substitute函数实现。

在目标单元格中输入公式:=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1

9.jpg


10.gif


解读:

1、公式中首先用Len函数计算源字符串的长度,Len(B2)代表计算B2的总长度。

2、LEN(SUBSTITUTE(B2,"、",""))代表把人员清单里面的"、"全部替换为空,并计算长度。

3、最后+1修正得到想要的结果,如果不理解+1的原因,可以查阅一下植树原理。


底部广告