二维码 购物车
部落窝在线教育欢迎您!

Excel实用案例:SUBSTITUTE嵌套函数对文本单元格的判断和计算

 

作者:阿硕来源:部落窝教育发布时间:2021-05-27 11:26:45点击:160

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:
SUBSTITUTE替换函数,在文本单元格数据的判断、比较、计算中常常起到至关重要的作用。下面,将用两个工作实例,全面分析,怎么用SUBSTITUTE函数和其它函数写出正确的嵌套函数,其中的公式逻辑又是怎样的……

 

近年来,断舍离、极简主义等文化在国内相当流行。笔者特意去百度了一下“断舍离”,顾名思义其意思是:“断”指的是断绝不需要的东西,“舍”指的是舍弃多余之物,“离”指的是脱离对物质的迷恋。

说的简单粗暴点,就是把多余的东西“咔嚓”掉。下面要讲的这个函数组合,就充满了断舍离的精神,它可以通过舍弃一些内容来达到最终效果,是不是很神奇呢?

 

今天,笔者将通过一个小伙伴提出的问题,来和大家讲讲这个函数组合的精髓所在,然后再通过一个广泛应用的案例,领略这个断舍离函数组合的妙用。

 

一、如何构建函数组合

小伙伴的数据如下图,A列保存的是文本格式的二位数,B列保存的是文本格式的三位数。

 

问题:判断A列每个单元格内二位数的每一位数字,是否全部被包含在B列对应单元格内的三位数中(与数据出现的先后顺序无关)。即如果A列中每一位数字均出现在B列中,则判断结果为“包含”;否则,判断结果为“不包含”。

 

表格

中度可信度描述已自动生成

 

大家先来理顺一下思路:

①以A1B1为例,A1中的数据为“66”,它的第一位和第2位数字均为“6”。需要在B1中,根据A1中的两位数字来进行“断舍离”。

②第一次断舍离是舍去A1中第一位数“6”,于是,B1中的数据将变成“05”;第二次再对“05“进行一次断舍离,这次要舍去的是A1中第二位数“6”,但是,经过第一次断舍离后的数据是“05”,它里面不包含“6”,所以想舍也没的舍了,这时数据保持不变,依然为“05”。大家可以发现,在经过两次断舍离之后,B1中剩余数据的长度为2

③如果大家逐一判断A列中数据经过两次断舍离后剩余的数据长度,就可以找出一个规律:若B列中剩余数据的长度为2或者为3,则A列数据不包含在B列中;若B列中剩余数据的长度为1,则A列数据包含在B列中。

 

搞清楚了思路,现在来用函数分步实现:

Step.1 制作辅助列。首先通过LEFT函数获取一下A列中数据的第一位数字,在C1中输入“=LEFT(A1,1)”,可以得到A列中数据的第一位数字(注意:第二参数可省略)。

 

 

Step.2 第一次断舍离,从A列单元格中舍去辅助列C列的内容。在D1中输入“=SUBSTITUTE(B1,C1,,1)”,即得到结果。

 

 

函数讲解:

SUBSTITUTE函数的语法为:SUBSTITUTE(Text,Old_text,New_text,[Instance_num])

①Text参数为需要替换其中字符的文本,即为B1单元格。

②Old_text参数为需要被替换掉的老文本,即为C1单元格(亦是A1中第一位数字)。

③New_text参数为用于替换Old_text的文本,即为替换成的新文本。此参数若省略不写,则默认为替换成空值,如上图。

④Instance_num参数用来指定以新文本替换第几次出现的老文本,在D1单元格所写的函数中,由于替换的是第一次出现的“6“,所以将这个参数的值设为“1”。(注:如果缺省,则意味着用新文本替换Text中出现的所有老文本。)

 

Step.3 制作辅助列E列。通过RIGHT函数获取一下A1单元格中的第二位数,E1中输入“=RIGHT(A1,1)”,得到的结果如下(注意:第二参数可省略)。

 

 

Step.4 第二次断舍离,从A列单元格中舍去辅助列D列的内容。在F1中输入“=SUBSTITUTE(D1,E1,,1) ”即得到结果。

 

敲黑板:在这一次的断舍离中,SUBSTITUTE函数的第一参数是D1单元格中的数据,即经过第一次断舍离之后的数据。

 

 

这时,大家可以看到——只有当A列数据中的两个数字都出现在B列对应的单元格中时,F列中的数据才是一位数。

 

Step.5 判断A列单元格内容是否全部被包含于B列。

通过LEN计算一下F1中数据的位数,在G1中输入“=LEN(F1)”,即判断A列单元格内容有多少个数字被包含于B列。

 

 

再对G列中的数据进行一个IF判断,在H1中输入“=IF(G1=1,"包含","不包含") ”,就OK了。

 

 

Step.6 将上述函数嵌套一下,在I1中输入“=IF(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,1),,1),RIGHT(A1,1),,1)=1,"不包含","不包含") ”,就可以得到最终的结果了。

 

 

二、应用实例

 

如下图所示,B2B6单元格中的数据是参会人员的名单,在每个人名之间,用中文输入法下的顿号(、)分隔,现在需要统计每一天的参会人数。

 

 

这个问题的断舍离思路:

先批量去掉所有的顿号,再计算去除顿号后的数据的长度,然后计算数据原始长度和去除顿号后的数据长度的差值,这样算出来的刚好是顿号的数量,最后,用顿号的数量再加1,就行了。

 

为什么要加1呢?因为如果用1个顿号,可以分隔2个姓名;用2个顿号,则可以分隔3个人,以此类推,人数始终比顿号的数量多1

 

下面,开始分步写函数。

Step.1 C2中输入“=SUBSTITUTE(B2,"",)”。敲黑板:在此处的函数中,第三参数省略,意味着用空格来替代顿号;第四参数省略,意味着替换掉所有的顿号。

 

表格

描述已自动生成

 

D2E2中分别输入“=LEN(B2)”、“=LEN(C2)”,得到如下结果。

 

 

F2中输入“=D2-E2+1”,即得到每天参会的人员总数。

 

 

最后,大家将函数嵌套一下,就得到一个终极的公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"",))+1”,如下图。

 

 

好了,亲爱的小伙伴们,今天的内容,你们学会了吗?函数可以断舍离,但是对于Excel的追求,千万不能断舍离哦!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel函数经典案例:substitute函数应用

Excel怎么汇总货品清单中的文本数据?

Power Query的数据替换技巧比Excel函数更万能!

如何对单元格内的x箱x条x包批量求和

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。