Excel教程:SUBSTITUTE函数解决文本单元格的判断和计算,你会吗?

来源:互联网 作者:

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


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

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


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


一、如何构建函数组合

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


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


 


大家先来理顺一下思路:

①以A1和B1为例,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,"不包含","不包含") ”,就可以得到最终的结果了。



二、应用实例。


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



这个问题的断舍离思路:

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


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


下面,开始分步写函数。

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



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



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



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



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

资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 现代古风通用PPT
  • 故宫国风演示文档PPT
  • 古风水墨简约模板PPT
  • 古风中国风环保主题PPT
  • 古风工作总结汇报模板PPT
  • 古风古典雅致通用模板PPT
  • 扁平插画手绘中小学生安全教育日PPT
  • 小学交通安全教育课件PPT
  • 小学交通安全教育课件PPT
  • 可爱卡通幼儿教学课件模板PPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2021 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。