EXCEL技巧四十六:substitute函数的运用

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

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

  substitute函数属于文本查找类函数,就是查找某个字符,然后替换成别的字符。

  substitute函数的语法是:SUBSTITUTE(text,old_text,new_text,instance_num)

  其中的参数意义如下:
  Text:为需要替换其中字符的文本,或对含有文本的单元格的引用。
EXCEL技巧四十六:substitute函数的运用
  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个笑字,那么就需要嵌套函数。关于嵌套层数,在Excel2003及以前的版本,最多允许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来判断没有人名的情况就显示为空。

  第四,计算12月份的总金额。

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

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

  

爱华网本文地址 » http://www.aihuau.com/a/25101013/169171.html

更多阅读

EXCEL技巧五十六:text函数应用分享

第一部分:TEXT函数基础介绍  Text函数,有人称它是万能函数。其实,text的宗旨就是将自定义格式体现在最终结果里。Text函数主要是将数字转换为文本。当然,也可以对文本进行一定的处理。  TEXT函数的语法:TEXT(value,format_text) 

今日“天姥论坛”发贴:悟道大和尚四十六了字见性歌

  悟道大和尚驻锡新昌大佛寺十多年,实在是新昌人民之福。但老方丈西去已快三周年了,不知有多少新昌人民还会想到他。今发上大和尚之《三十六了字见性歌》,以志纪念。  缘起:10月19日贵宾报到的晚上,与同道在佛心广场拜见了小老(净友)法

都市聊斋惊悚夜-第四十六夜-痋术 十六夜清心 胧月都市

书名:都市聊斋惊悚夜:鬼话连篇作者:布川鸿内酷第四十六夜 痋术痋(teng)术其实是一种巫术。跟蛊术和降头并列为滇南三大邪术,蛊术和降头术并不新鲜,大多数人都知道,但是疲术却极少有人知道,就算是野史也鲜有提及,痋

声明:《EXCEL技巧四十六:substitute函数的运用》为网友我爱我的祖国分享!如侵犯到您的合法权益请联系我们删除