substitute函数(关于SubStitute的用法)-趣虎号

各位老铁们好，相信很多人对substitute函数都不是特别的了解，因此呢，今天就来为大家分享下关于substitute函数以及关于SubStitute的用法的问题知识，还望可以帮助大家，解决大家的一些困惑，下面一起来看看吧！

本文目录

Excel函数公式***关于SubStitute的用法
Excel中substitute函数能否替换多个可能出现的关键字
excel结合实例详细讲解substitute函数的运用
手把手教你透过实例理解substitute函数怎么用

一、Excel函数公式***关于SubStitute的用法

1、在工作中，经常需要对特定字符串进行替换，这时SUBSTITUTE函数便派上用场。

2、其功能在于对指定字符串进行替换，语法为SUBSTITUTE(text, old_text, new_text, [instance_num])。

3、理解起来，可将其视为SUBSTITUTE（字符串，旧字符，新字符，[第几次替换]）。

4、文本参数是必需的，需替换字符的文本或含有文本的单元格引用。

5、旧字符与新字符同样是必需参数，分别代表要替换的文本和替换后的文本。

6、可选参数实例数指定特定的替换事件，如未指定则替换所有符合要求的旧字符。

7、一例，将字符串ExcelHSGS中的HSGS替换为“函数公式”。

8、操作步骤，目标单元格输入公式：=SUBSTITUTE(B3,"HSGS","函数公式")。

9、在工作场景中，若字符串中包含大量空格，使用SUBSTITUTE函数可实现全面替换。

10、二例，解决空格对VLOOKUP函数查找的影响，通过SUBSTITUTE函数替换空格。

11、操作方式，目标单元格输入公式：=VLOOKUP(SUBSTITUTE(F2,"",""),B3:C4,2,0)。

12、解释此公式，=VLOOKUP（要查找的值、查找区域、返回值列号、精确匹配）。

13、其中，要查找的值用SUBSTITUTE(F2,"","")替换，确保正确查找目标。

14、例如，F2单元格内为“梨子”，不含空格，不处理则无法准确查找。

二、Excel中substitute函数能否替换多个可能出现的关键字

1、一个substitute只能替换一个词，要替换多个，可嵌套多个substitute，如：

2、＝SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"办公室",),"写字楼",),"健身房",)

3、就是这样，一层层套一层，比较麻烦的。不过好在SUBSTITUTE如果在要寻找的字符串（或引用单元格中）没找到要替换的内容时，不会像FIND一样报错，而直接跳过。所以多层嵌套只管放心使用，不用担心要找的内容是否存在。

三、excel结合实例详细讲解substitute函数的运用

第一部分：substitute函数的用法介绍

Substitute这个单词就是替换的意思。substitute函数是属于什么函数，如何使用呢?substitute函数有点类似于excel中的查找替换命令，但却更灵活好用。

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。

第二部分：substitute函数的应用实例

第一题：substitute函数基础应用

实例如下图所示。源数据为A5单元格。本题实现的效果就是变换不同的参数，将源数据中的“笑”字替换为“看今朝”三个字。

C5单元格的公式，实现的就是B5单元格的效果。其中，第四个参数，省略，就表示源数据中所有“笑”字都替换掉。如果只是替换源数据中第一个“笑”，即B6单元格的效果，只需把第4个参数写1就可以。同样，如果要替换第2个笑，即B7单元格的效果，就把第4参数写2就可以。

如果源数据中有多处数据需要替换。比如源数据有三个笑，需要替换其中的2和3个笑字，那么就需要嵌套函数。关于嵌套层数，在Excel 2003及以前的版本，最多允许7层嵌套，在Excel 2007中允许使用64层嵌套。

B8、B9单元格的效果，前面包含空格，可以使用LEN函数测试出空格数，比如：=LEN(B8)-4，得到1，前面有1个空格。这里的4，代表笑看今朝4个字符。

C9单元格的公式，用到了REPT函数。REPT函数就是按照给定的次数重复显示文本。可以通过此函数来不断地重复显示某一文本字符串，对单元格进行填充。比如，要重复显示10个空格，可以输入=REPT("",10)。

其实空格是没有实际意义，为什么要举这个列子呢，是因为有时候输入不规范。比如一次性把A1单元格的所有空格取消，可以输入公式：=SUBSTITUTE(A1,"","")。

第二题：substitute函数进阶应用实例

下图所示的是一个单位的12月份出差费用明细表，方便演示，只截取了部分图表。

第一，根据上图，统计D5单元格“杨”出现的次数。

公式分析： substitute函数如果第4参数省略不写，就是把所有需要替换掉的内容替换掉。这里，使用len函数测试有几个字符被替换掉，减少的字符数就是有几个“杨”。

公式为：=LEN(D5)-LEN(SUBSTITUTE(D5,"杨",""))

第二，12月份“陈锡卢”共出现几次。

此题需要使用到sumproduct函数，这个函数功能很强大，求和、计数都可以使用。这个函数与SUM函数很相似。sumproduct函数的用法是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。在以后的讲座还会专门讲解此函数。

公式为：=SUMPRODUCT(--((LEN(D5:D31)-LEN(SUBSTITUTE(D5:D31,"陈锡卢","")))>0))

公式分析：本题我们就是使用sumproduct函数来计数。总字符—替换的字符>0，统计大于0的个数。因为公式判断出来的结果是逻辑值，不能直接求和，因此得先变为数值才行。--的作用是把文本转换为数字，让逻辑值参加运算。另外*1，/1，+0，等都可以让文本参与运算。另外，用LEN(D5:D31)给出12月份这个区域。

第三，在人数C列统计出对应的人数有几个。

公式为：=IF(D5="","",LEN(D5)-LEN(SUBSTITUTE(D5,"、",""))+1)

公式分析：从上面的工作表，可以发现D列人员名单中的“、”符号比人名少一个，利用substitute函数把它替换成空,然后计算。前面再加上IF来判断没有人名的情况就显示为空。

公式为：=SUMPRODUCT(--SUBSTITUTE(E5:E31,"元",""))&"元"

公式分析：从上面的工作表中的数据可以发现金额后面都有个“元”字，这时，利用sum是不能正确求和的。其实，用SUBSTITUTE(E5:E31,"元","")将数字提取提取出来，然后就可以求和了。