一 : 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函数的应(www.61k.com］用实例
第一题：substitute函数基础应用
实例如下图所示。源数据为A5单元格。本题实现的效果就是变换不同的参数，将源数据中的“笑”字替换为“看今朝”3个字。
C5单元格的公式，实现的就是B5单元格的效果。其中，第4个参数，省略，就表示源数据中所有“笑”字都替换掉。如果只是替换源数据中第1个“笑”，即B6单元格的效果，只需把第四个参数写1即可。同样，如果要替换第两个笑，即B7单元格的效果，就把第4参数写2即可。
如果源数据中有多处数据需要替换。比如源数据有3个笑，需要替换其中的2和三个笑字，那么就需要嵌套函数。关于嵌套层数，在Excel2003及以前的版本，最多允许7层嵌套，在Excel 2007中允许使用64层嵌套。
B8、B9单元格的效果，前面包含空格，可以使用LEN函数测试出空格数，比如：=LEN(B8)-4，得到1，前面有一个空格。这里的4，代表笑看今朝四个字符。
C9单元格的公式，用到了REPT函数。REPT函数就是按照给定的次数重复显示文本。可以通过此函数来不断地重复显示某一文本字符串，对单元格进行填充。比如，要重复显示十个空格，可以输入=REPT("",10)。
其实空格是没有实际意义，为什么要举这个列子呢，是因为有的时候输入不规范。比如一次性把A1单元格的所有空格取消，可以输入公式：=SUBSTITUTE(A1,"","")。
第二题：substitute函数进阶应用实例
下图所示的是1个单位的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列人员名单中的“、”符号比人名少1个，利用substitute函数把它替换成空,然后计算。前面再加上IF来判断没有人名的情况就显示为空。
第四，计算12月份的总金额。
公式为：=SUMPRODUCT(--SUBSTITUTE(E5:E31,"元",""))&"元"
公式分析：从上面的工作表中的数据可以发现金额后面都有个“元”字，这时，利用sum是不能正确求和的。其实，用SUBSTITUTE(E5:E31,"元","")将数字提取提取出来，然后即可求和了。
二 : SUBSTITUTE函数的常见用法
SUBSTITUTE函数的常见用法，SUBSTITUTE函数的用法是：
SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])
今天我们通过几个例子，熟悉一下SUBSTITUTE函数的常用技巧和套路。
例子一
将数据中的某个值，替换为另一个值
比如将B列数据里的二班，替换为一班。
=SUBSTITUTE(B2,"二班","一班")
这里没啥好解释的了，就是将B列中单元格中的“二班”全部替换为“一班”。
例子二
隐藏手机号的中间5位
=SUBSTITUTE(B2,MID(B2,4,5),"*****")
先使用MID函数取得B列号码中的中间五位，再用“*****”替换掉这部分内容。
例子三
含单位的文本值求和
=SUMPRODUCT(SUBSTITUTE(B2:B6,"人",)*1)
先用SUBSTITUTE替换掉B列单元格中的“人”，得到文本型数字，乘以1后变成可以计算的数值，再用SUMPRODUCT求和。
例子四
数据分列，将B列数据按顿号进行分列。
=TRIM(MID(SUBSTITUTE($B2,"、",R[www.61k.com）EPT(" ",100)),COLUMN ))
先使用REPT函数，将空格重复100次，得到100个空格。
再使用SUBSTITUTE函数将姓名中的的间隔符号(、)顿号替换为100个空格。
再使用MID函数，依次从这个带有空格的新字符串中的第1、第101、第201位……开始截取长度为100的字符。
这样得到的字符串是带有多余空格的，因此再使用TRIM函数将多余空格删除掉，OK了。
三 : excel中substitute函数有什么作用?
下面给大家讲解excel常用函数substitu的使用方法。
1、如图所示，打开文档，屏蔽手机号码的后四位。这里参数Instance_num省略，替换掉所有的old_text(旧文本)，蔽手机号码也可以使用replace函数。Replace函数的含义
2、提示：用新字符串替换旧字符串，而且替换的位置和数量都是指定的
3、如图所示，substitute函数经常用来去掉文本之间的空格
相关推荐：
excel怎么用函数计算复利终值和现值?
excel中怎么使用函数计算某个月的最后一天日期?
怎么用Excel查看员工上班迟到状况? IF和TIME函数计算出上下班状态的方法
四 : SUBSTITUTE函数怎么用
SUBSTITUTE函数怎么用
SUBSTITUTE实际上是字符串替换函数,功能是将一个字符串中的部分字符串用新的字符串替代.函数的表达式为：
SUBSTITUTE(text,old_text,new_text,instance_num)
text 是准备做内容替换的单元格；old_text是准备替换掉的内容；new_text是要替换后的内容；最后一个参数是指准备替换掉的内容在原来的字符串中第几次出现,这个参数也可以不写,表示准备替换的字符串全部替换,如果填写了具体的数字n,表示要将第n次出现的字符串替换掉.
举例如下,在A1中输入数据345464
在B1输入=SUBSTITUTE(A1,"4","Q"),显示3Q5Q6Q
在C1输入=SUBSTITUTE(A1,"4","Q",1),显示3Q5464
在D1输入=SUBSTITUTE(A1,"4","Q",2),显示345Q64
在E1输入=SUBSTITUTE(A1,"4","Q",3),显示34546Q
在F1输入=SUBSTITUTE(A1,"4","Q",4),显示345464
五 : excel中substitute函数有什么作用？
下面给大家讲解excel常用函数substitu的使用方法。
。www.61k.com”1、如图所示，打开文档，屏蔽手机号码的后四位。这里参数Instance_num省略，替换掉所有的old_text(旧文本)，蔽手机号码也可以使用replace函数。Replace函数的含义
2、提示：用新字符串替换旧字符串，而且替换的位置和数量都是指定的
3、如图所示，substitute函数经常用来去掉文本之间的空格
61阅读| 精彩专题| 最新文章| 热门文章| 苏ICP备13036349号-1