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

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

字符串转换技巧三则   英文字母大小写转换   假设A1存放着字符"i love excEL",下面的公式将A1的英文字母全部转为大写。  ＝UPPER(A1)得到"I  LOVE EXCEL"下面的公式将A1的英文字母全部转为小写 =LOWER(A1) 得到"i love excel"下面的公式将A1的英文单词首字母及任何非字母字符之后的首字母转为大写，其余的字母转换成小写=PROPER(A1) 得到"I Love Excel"   附件在三楼

[此贴子已经被作者于2007-8-27 9:26:17编辑过]

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

字符与ANSI字符代码转换    下面的公式将A1单元格中的第一个字符转换为ANSI字符代码。  =CODE(A1) 下面的公式得到大写字母A的ANSI代码65。CODE("A")下面的公式将一个代表字符的ANSI代码转换为字符，如=CHAR(65)得到大写字母A      附件在三楼

[此贴子已经被作者于2007-8-25 7:42:52编辑过]

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

半角与全角的转换   假设A1单元格中的字符是"I L O V E E X C E L "，下面的公式将从A1中的全角（双字节）字符返回半角（单字节）字符，得到“I LOVE EXCEL”。=ASC(A1)反之，若A1存放字符"I Love Excel",则下面的公式将改半角字符为全角字符，返回"I L o v e E x c e l ".=WIDECHAR(A1)

[此贴子已经被作者于2007-8-25 7:42:03编辑过]

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

重复文本技巧二则   函数REPT(text,number_times)用来将特定字符按给定的次数重复填充。

[此贴子已经被作者于2007-8-25 7:18:51编辑过]

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

填充字符  例：要将A列存放的数字转化为10位数的编码，原位数不足的在前面补0。=RIGHT(REPT(0,10)&A1, 10)   而下面的公式可以除了将数字转化以"NO",开头的编码外,还用空格填充不足10位的位数以达到右对齐的显示效果   ="NO."&REPT(" ",10-LEN(A1))&A1    附件在六楼

[此贴子已经被作者于2007-8-25 7:43:28编辑过]

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

制作文本直方图   利用REPT函数,还可以在不创建EXCEL图表的情况下制作一个块状的直方图.     如附件中,在A2:C13中存放着每月计划进度和实际进度的数据,在D2中输入下面的公式并设置格式为百分比得到进度对比数据.   =IF(B2<>0,(C2-B2)/B2,0)或者可以简化为:  =IF(B2,(C2-B2)/B2,0)  在F2,H2中分别输入下面的个公式,可将D列的每个百分点转为一个小方块.如果需要每10个百分点做一个小方块,则将D2*100改为D2*10即可.  =IF(D2<0,REPT("■",-ROUND(D2*100,0)),"")  =IF(D2>0,REPT("■",ROUND(D2*100,0)),"")  将F2,H2的公式向下填充,然后设置F~H列的填充色,字体颜色以及对齐方式,一个漂亮简单的文本直方图就完成了

[此贴子已经被作者于2007-8-25 7:40:32编辑过]

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

字符串比较及清理垃圾字符    在EXCEL中针对某些数据进行查找与引用操作时,表面看起来相同阿字符串,却经常无法查询到相匹配的结果,这往往是因为目标字符串与关键字不符导致的.   假设A1="John  smith",B1="John  Smith",下面的公式将对两个字符串进行比较.=A1=B1   公式返回TRUE.=EXACT(A1,B1)公式返回FALSE,这事因为该函数区分大小写.    若B1="John   smith",则两个公式都将返回FALSE.这事因为B1的两个单词中间有两个空格,而A1只有一个.这种情况常常发生于手工输入时.用TRIM函数将消除字符串中多余的空格,下面两个公式都将返回TRUE.    =A1=TRIM(B1)  =EXACT(A1,TRIM(B1))   另外,从一些数据库软件导入的EXCEL文件中经常夹杂着肉眼难辨认的非打印字符,这些符号的存在更易造成查找引用,统计等下一步有关运算的错误,因此也被称为"垃圾字符".这些字符可以用CLEAN函数清除.  如A1单元格的内容为".你好."两个圆点是非打印字符,用下面的公式可返回"你好".  =CLEAN(A1)

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

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

替换字符    在许多时候,可能需要对某个文本字符串中的部分内容进行替换,除了使用EXCEL的"替换"功能外,还可以使用文本替换函数.常用的文本替换函数为SUBSTITUTE函数和REPLACE函数,它们的语法分别是:   SUBSTITUTE(text,old_text,new_text,instance_num)   REPLACE(old_text,start_num,num_shars,new_text)   如果明确知道目标字符,但是不知道其在字符串中的具体位置,可以使用SUBSTITUTE函数.   例一:A1单元格内容为"教师职别档次",下面的公式将去除文本字符串中的两个空格,返回"教师职别档次".  =SUBSTITUTE(A1" ","")  例二:A1单元格的内容为"我爱EXCELPX,我爱EXCEL!",下面的公式可以将第二个"爱"替换为"喜欢",返回"我爱EXCELPX,我喜欢EXCEL".  =SUBSTITUTE(A1,"爱","喜欢",2)  如果目标字符并不固定,但能够明确知道其在字符串中的具体位置,可以使用REPLACE函数来实现替换.  例三:A1单元格的内容为"EXCEL-PX.com",下面的公式可以用一个空字符串替换第6个字符,即删除"-",返回结果为"EXCELPX.com".=REPLACE(A1,6,1,"")  REPLACE函数还有个妙用,就是可以在字符串的指定位置插入字符.  例四:A1单元格的内容为"A123456789",用下面的公式可以在原字符串第5个字符前插入字符串"abc",得到"A123abc456789".  以上函数还可以通过嵌套,让它们在公式中执行多重替换的任务,如下面的公式可以清除A1单元格中的数字0~7  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,),1,),2,),3,),4,),5,),6,),7,)

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

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

查找字符    精确查找指定字符在一个字符串中的位置是EXCEL函数运用中的一项重要技巧,尤其是在截取字符串,替换字符串等文本处理过程中,精确定位技术更是必不可少.查找字符的主要函数是FIND函数和SEARCH函数,两者的语法完全相同:  FIND(find_text,within_text,start_num)  SEARCH(find_text,within_text,start_num)   主要区别在于:FIND函数支持大小写区分但是不支持通配符,SEARCH函数不支持大小写区分但是支持通配符.

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

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

目标字符在字符串中第一次出现的位置   下面的公式都将返回字符"e"在A1中第一次出现的位置   =FIND("e",A1)  =SEARCH("e",A1)    如果A1的内容为"EXCEL PX",则=FIND("e",A1)返回的值是4,因为FIND()区分大小写;而=SEARCH("e"A1)返回的值是1,因为它不区分大小写.   下面的公式将返回字符"a"在A1的字符串中第一次出现的位置的前一个位置.   =FIND("a",A1)-1   =SEARCH("?a",A1)

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

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

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

浏览过的版块