（分享）函数技巧之文本处理

hxqzccct · 发表于 2007-8-25 07:03

目标字符在字符串中N次出现的位置   下面的两个公式可以计算字符"a"在A1单元格的字符串中第2次出现的位置   =FIND("a",A1,FIND("a",A1)+1)  =FIND("々",SUBSTITUTE(A1,"a","々",2))     下面两个公式可以计算字符"a"在A1单元格的字符串中第3次出现的位置.   =FIND("a",A1,FIND("a",A1,FIND("a",A1)+1)+1)   =FIND("々",SUBSTITUTE(A1,"a","々",3))    思路解析:    FIND函数嵌套使用,从目标字符第一次出现位置的后一个字符开始找,得到第2次出现位置.如果要计算第三次,第四次或者更多次出现的位置,简单地使用FIND函数嵌套的方法就显得非常笨拙.   可以利用SUBSTITUTE函数先将N次出现的目标字符替换为特殊符号"々",在用FIND函数在替换后的字符串中进行查找,这样就很灵活了

[此贴子已经被作者于2007-8-25 9:14:14编辑过]

hxqzccct · 发表于 2007-8-25 07:25

计算字符出现的次数   在实际应用中,经常要统计某个字符或字符串出现的次数,一般分为以下两种情况.

[此贴子已经被作者于2007-8-27 8:18:34编辑过]

hxqzccct · 发表于 2007-8-25 07:25

1:某字符在某单元格内出现的次数   例:A1中存放着字符串"你好我好大家好",求"好"字出现的次数.  =LEN(A1)-LEN(SUBSTITUTE(A1,"好",""))思路解析:   使用SUBSTITUTE函数在内存中创建一个新字符串,在该字符串中将原有文本中的"好"字全部替换为空文本,然后用原有文本的长度减去新字符串的长度,得到"好"字出现的次数.   如果需要统计的目标字符不只1个,则公式结果须除以所求字符串的长度.如统计"你好"出现的次数,则公式应改为:=(LEN(A1)-LEN(SUBSTITUTE(A1,"你好","")))/LEN("你好")   SUBSTITUTE函数区分大写和小写,且第三个参数new_text可简化.如求字符"a"和"A"的个数,则用以下3个公式均能得到正确结果.=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"A",),"a",))=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",))=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",))   下面的公式可用于计算A1单元格的单词个数.考虑到A1的字符串可能存在多余空格,先用TRIM函数将它们除去,然后在求删除单词间多余空格后剩下的空格个数,加1后即为单词个数.  =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",))=1 附件再13楼

[此贴子已经被作者于2007-8-27 8:19:25编辑过]

hxqzccct · 发表于 2007-8-25 07:26

包含某字符的单元格个数   在附件Sheet2中,B3:B7存放教师职务工资的档次名称,要统计助理级教师人数,可用公式:=COUNTIF(B3:B7,"*助*")或数组公式:{=COUNT(FIND("助",B3:B7))}  数组公式中使用FIND查找函数,如果查找到结果就返回数值,否则返回#VALUE!错误.  由于COUNT函数可以直接过滤错误值,所以直接求得结果即可.  若目标字符为英文且要求区分大小写,则必须使用后一个公式.  附件再13楼

[此贴子已经被作者于2007-8-27 8:21:05编辑过]

hxqzccct · 发表于 2007-8-25 07:26

提取字符串常用函数  从原有文本中截取一部分用于形成新的文本是常见的文本运算。主要的文本截取函数有LEFT函数，MID函数和RIGHT函数，它们的语法分别是：LEFT(text,num_chars)MID(text,start_num,num_shars)RIGHT(text,num_chars)  下面的公式将分别提取A1单元格的首个字符和前3个字符。=LEFT(A1)=LEFT(A1,3)  下面的公式将分别提取A1单元格的尾子字符和最后三个字符。=RIGHT(A1)=RIGHT(A1,3)   下面的公式将截取A1单元格的第7～12个字符。=MID(A1,7,6)   以上三个文本截取函数，最后一个参数都是要提取的字符个数，如果输入的个数超过目标文本的实际字符数，函数将按实际字符返回结果，而不返回错误。   假设A1单元格的内容为“ABCDEFG”，共有7个字符，下面两个公式返回的结果相同，都是"ABCDEFG"  =LEFT(A1,7)  =LEFT(A1,255)而下面两个公式返回的结果都是"DEFG"=MID(A1,4,4)=MID(A1,4,LEN(A1))    MID函数和RIGHT函数的特点也与此相同。利用这个特点，如果须要返回某个字符串指定位置后的所有字符，就不用在公式中指定有几个字符。   下面的公式将返回由A1单元格中的每一个字符组成的垂直数组，假设A1单元格中的文本是"ABCD",则下面的公式返回数组{"A";"B";"C";"D"}.{=MID(A1,{1;2;3;4},1)}    思路解析：    从A1的第1，2，3，4个字符位置分别截取1个字符，如果A1的字符串长度不固定，则须要组合运用ROW函数和INDIRECT函数来返回代表位置的数组，如下面的数组公式。  {=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)}

[此贴子已经被作者于2007-8-27 8:29:37编辑过]

hxqzccct · 发表于 2007-8-25 07:26

从完整路径中提取文件名    应用实例：假设A1单元格中包含“E:\My Documents\函数\Sample\文本处理.xls",下面的公式将去除路径，返回文件名”文本处理.xls".=MID(A1,FIND("\",SUBSTITUTE(A1,"\","/",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))        该公式主要利用上上一技巧找到最后一个“\“的位置，得到仅包含文件名的字符串。   另一种更简洁的方法是，利用REPLACE函数将最后一个斜划线\及之前的字符替换为空，如下面的公式.=REPLACE(A1,1,FIND("/",SUBSTITUTE(A1,"\","/",LEN(A1)-LEN(SUBSTITUTE(A1,"\",)))),)  假如在B1单元格中用上述公式得到的”文本处理，XLS",要去掉，XLS的文件扩展名，可以用下面的公式完成。＝LEFT(B1,FIND(".",B1)-1) 附件再15楼

[此贴子已经被作者于2007-8-27 8:31:05编辑过]

hxqzccct · 发表于 2007-8-25 07:26

以附件中SHEET2的表格为例，要将A列中的内容按文本和数字分开放在B C列。         方法1：用LEN和SUBSTITUTE计算数字的个数来作为截取字符长度。D2=RIGHT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},))))B2==LEFT(A2,LEN(A2)-(SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))))或者C2＝SUBSTITUTE(A2,B2,"")     方法2：用FIND在A2找到第一个数字的位置并减去1来作为截取字符长度。因为A2不一定包含0～9这些数字，所以用ISNUMBER函数将错误值过滤掉=LEFT(A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2)))-1=RIGHT(A2,LEN(A2)-MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2)))+1)    以上公式是有一定缺陷的，只能针对文本与数字分别连续存放且文本串在前数字串在后的字符串，如果目标字符串中的文本与数字是交融在一起的，则需要对公式做进一步改善。 附件再15楼

[此贴子已经被作者于2007-8-27 8:31:25编辑过]

hxqzccct · 发表于 2007-8-25 07:26

提取连续的汉字   1.取得固定在左（或右）边的连续汉字      沿用附件SHEET2中的表格为例，图中A4，A5单元格中的文本由汉字和数字组成，而且汉字是连续的，此时可用双字节文本函数与单字节函数计算的差值来作为截取字符的长度。   使用LENB函数返回汉字的公式：＝LEFT(A4,LENB(A4)-LEN(A4))使用SEARCHB函数返回汉字的公式： =LEFT(A4,SEARCHB（"?",A4)/2)返回数字的公式：=RIGHT(A4,2*LEN(A4)-LENB(A4))    2.取得任意位置的连续汉字    假设A3单元格的内容为"We热爱Excel px"下面的公式将返回"热爱"两个汉字。  方法1{=MID(A3,MATCH(TRUE,CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))>255,),LENB(A3)-LEN(A3))}思路解析：     由于非全角字符的ASCI码都是介于0～255之间的数值，因此，该公式主要通过MATCH函数和CODE函数组合应用来实现，定位到第一个全角字符的位置，在取得汉字的个数，最后提取汉字。  方法2   首先定义名称DATA，用于替换多余的空格：=SUBSTITUTE(!$A3," ","")  取得公式：{=MID(DATA,MATCH(" ",MIDB(DATA,ROW(INDIRECT("1:"&LENB($A3))), 1),),LENB($A3)-LEN($A3))}    对上述公式进行容错优化以后的公式为:=MID(DATA,MATCH(" ",MIDB(DATA&" ",ROW(INDIRECT("1:"&LENB($A3) +1)),1),),LENB($A3)-LEN($A3)+(LENB($A3)=LEN($A3)))     思路解析：   方法2和方法1的区别在与，方法2中利用了"MIDB函数取全角字符为" "(半角空格)"的技巧，该公式主要通过查找(半角空格)来定位首个汉字位置，在根据汉字字符个数取得结果。    后面的优化公式中进行了容错处理（注意优化公式中底纹处公式代码），如果DATA为空（或者原字符串中没有全角字符），公式直接返回空字符，而不会返回错误。  注意！   以上公式只能在所有全角字符连续排列情况下才能用。否则公式无法得出正确结果  附件再15楼

[此贴子已经被作者于2007-8-27 8:31:59编辑过]

hxqzccct · 发表于 2007-8-25 07:27

文本合并符号的妙用   文本合并符"&"将符号两边的内容作为文本加以链接，除了比CONCATENATE函数更加简洁灵活，没有30个参数限制外，还有多种妙用。下面介绍几个应用实例。

[此贴子已经被作者于2007-8-27 8:32:58编辑过]

hxqzccct · 发表于 2007-8-25 07:27

链接名字与姓氏    假设A1单元格中包含文本"Jacky"，B1单元格中包含文本"Chou",下面的公式返回"Jack Chou".=A1&" "&Ｂ1　链接注释文字和计算结果   设置一个考勤表的动态标题，可以自动显示出当前月份。   ="xxx公司xx部门"&MONTH(NOW())&"月考勤表"

[此贴子已经被作者于2007-8-26 18:32:00编辑过]

		自动登录	找回密码
密码			注册

[函数] （分享）函数技巧之文本处理

浏览过的版块