昨天讲人民币阿拉伯数字转中文大写的公式,最后用两个SUBSTITUTE函数替换“零角”为“零”,“零分”为“整”。今天,就有朋友问韩老师SUBSTITUTE函数的具体用法,那韩老师就来总结一下。 功能 在某一文本字符串中替换指定的文本。 语法SUBSTITUTE(text, old_text, new_text, [instance_num]) SUBSTITUTE 函数语法具有下列参数:
特点 1、区分大小写和全角半角:当text中没有包含 old_text指定的字符串时,函数结果与text相同; 2、当第三个参数为空文本或是省略,而只保留参数前的逗号时,相当于将 old_text替换; 3、当第四个参数省略,text中与 old_text相同的文本将被替换; 4、如果第四个参数有指定,比如“2”,则只有第二次出现的old_text被替换。 以上四个特点举例如下: 公式:=SUBSTITUTE(D1,'及','合'),把“不及格”统一改为“不合格”: 比如:火车票上的身份证号码从第11位开始隐藏4位,实现公式:=SUBSTITUTE(C2,MID(C2,11,4),'****') 其中: text:C2,身份证所在单元格; old_text::MID(C2,11,4),用MID函数从身份证号码的第11位取4位; new_text::“****”。 公式为:=LEN(D2)-LEN(SUBSTITUTE(D2,6,)) 其中: LEN(D2):D2字符串的长度; LEN(SUBSTITUTE(D2,6,)):替换掉了6以后字符串的长度。 在F11单元格输入公式: {=(AVERAGE(--SUBSTITUTE(F2:F10,'分',)))} (CTRL SHIFT ENTER结束) 其中: {=--SUBSTITUTE(F2:F10,'分',)},公式内“--”称为“减负运算”,{=SUBSTITUTE(F2:F10,'分',)}的结果是一串文本,前面加一个“-”,是通过取负数将文本转换成数值,再加一个“-”,即负负得正。 “--减负运算”常用于公式中把文本转换为数字。 比如下图表中的员工姓名和业绩挤在一个单元格里,要求统计业绩最大值。 在C2单元格输入公式: {=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))},(TRL SHIFT RNTER结束)。 {=ROW($1:$100)}: 返回值是1-100组成的数组{1;2;3;4;5;6;7……98;99;100} {=SUBSTITUTE(B2,ROW($1:$100),)}: 将B2内的文本依次删除1~100数值以后,返回100组文本组成的数组,如下图: 关于SUBSTITUTE函数的用法参考昨天的文章:Excel | 数据输入不规范,部分带数量单位,怎么计算平均值?。 {=SUBSTITUTE(B2,ROW($1:$100),)<>B2}: 返回值是一组TURE与FALSE组成的100个逻辑值数组,将删除了数字后的文本与B2单元格相对比,如果不等于B2返回TURE,如果等于B2返回FALSE。 {=(SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100)}: 将得到的一级逻辑值与1~100数值相乘,TURE相当于1,FALSE相当于0,相乘以后得到的结果是一个数组,该数组由100个数值组成,分别是B2单元格中包含的所有数字和0。 最后用MAX函数对上述数组内的数值求最大值。 |
|