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的追求，千万不能断舍离哦！