文本处理技术.xls

文文本本处处理理技技术术 文本是Excel的主要数据类型之一，也是用户在工作中经常接触到的内容。 一一、接接触触文文本本数数据据 1 1、认认识识文文本本 单元格的数据类型主要包括文本、数值和日期,但在日常工作中,用户接触最多的数据主 要是文本和数值数据。 而在文本数据中，经常会接触到例如员工姓名、部门名称、公司名称或某些带字母的商 品编号等数据，类似这样的数据都被视为文本数据，也是常说的文本字符串。 用户在单元格中输入任何数据，如数值、文本或错误值等，Excel一般都能自动识别，并 赋予相应的格式来显示。默认情况下，数值和日期使用右对齐方式进行显示，而错误值 和逻辑值居中显示，文本型数据则是左对齐显示的。 数数据据 Excel #NUM! 2013/1/8 TRUE 123 2 2、区区分分文文本本型型数数字字与与数数值值 虽然通过数据的默认对齐方式能够直观地识别文本格式的数据，但在有些时候，例如设 置了居中对齐，用户并不能很明确地区分文本型数字和真正的数值。例如在VLOOKUP函数 中利用文本型数值编号进行数据查询时，往往由于格式不匹配，会造成一些不必要的麻 烦。 Excel提供了以下几个方法帮助用户进行明确区分。 (1)智能标签功能。当数值以文本格式存储时，在单元格的左上角会出现一个绿色标记， 以提示当前数值以文本格式存储。 数数据据 123 258 (2)可以使用ISTEXT函数进行判断。 数数据据 123=ISTEXT(A24) 258 =ISTEXT(A25) (3)利用状态栏的辅助“求和”工具进行判断。 数数据据 123 258 3 3、文文本本型型数数字字与与数数值值的的转转换换 一个文本单元格转换为数值：(1)设为常规，并激活.(2)智能标签|转换为数字 多个文本单元格转换为数值：*1，+0（选择性粘贴） 一个数值单元格转换为文本：设为文本，并激活 多个数值单元格转换为文本：数据|分列（一次只能转换一列数据） 二二、文文本本的的比比较较 标标识识首首次次出出现现的的记记录录 某商店水果进货价格表,要求在标注列标出首次出现的水果名称,其他的单元格显示为空 日日期期水水果果名名称称价价格格标标注注 2007/5/9桔子3.10桔子=IF(C40=C41,C41) 2007/5/10桔子3.20 2007/5/11桔子3.00 2007/5/1苹果2.50苹果 2007/5/4苹果2.80 2007/5/5苹果2.60 2007/5/3葡萄2.70葡萄 2007/5/11葡萄3.30 2007/5/12葡萄2.90 2007/5/7香蕉2.90香蕉 2007/5/8香蕉3.00 2007/5/2雪梨2.60雪梨 2007/5/8雪梨3.00 2007/5/3杮子3.00杮子 2007/5/11杮子2.90 2007/5/12杮子3.10 在本应用中,用户应该特别注意单元格末尾的空格字符,如果有的数据中包含空格,公式的比较结 果为FALSE,这也是用户最容易忽略的。 三三、文文本本的的合合并并 如果希望将多个文本连接生成新的文本字符串，可以利用CONCATENATE函数来进行处理。为 了更快地合并字符串，Excel还提供了使用“&”符号实现文本合并的方法，而且更加简单易 用。 数数据据 ExcelExcelHome=CONCATENATE(A63,A64) HomeExcelHome=A63&A64 去去除除引引用用空空单单元元格格返返回回的的0 0:在数据查找应用中，当返回查找结果是空单元格时，可以使用 “&”符号结合空字符“”来实现转换，使其不显示为0。 员员工工号号部部门门姓姓名名职职称称职职称称考考试试成成绩绩 A1001技术部张三工程师82 A1002技术部王五经济师 A1003技术部李四76 A1004开发部赵六高级工程师81 A1005技术部孙七高级工程师0 A1006技术部钱八67 A1007开发部陈九工程师53 A1008开发部田七72 员员工工号号职职称称 A10060 员员工工号号职职称称 A1006 四四、有有目目的的地地提提取取字字符符串串 用户常常需要在文本中提取部分字符来做进一步处理，如从身份证号中提取出生年月、 从产品编号中提取字符来判断产品的类别、从学员编号中提取年级号等。Excel提供了3 个常用的提取字符函数和1个计算字符串长度函数。 函数名称说明 left返回左侧字符从字符串的最左端位置提取指定数量的字符 right返回右侧字符从字符串的最右端位置提取指定数量的字符 mid返回中间字符从字符串中间的任意位置提取指定数量的字符 len求长度计算字符串的长度 LEFT(text,num_chars)LEFT(text,num_chars)：从从字字符符串串的的最最左左端端位位置置提提取取指指定定数数量量的的字字符符 Text 是包含要提取字符的文本字符串。 Num_chars 指定要由 LEFT 所提取的字符数。 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 LEFT 返回所有文本。 如果省略 num_chars，则假定其为 1。 数数据据 求求字字符符串串 长长度度 从从左左边边取取 4 4位位 从从左左边边取取8 8 位位 第第2 2参参数数 为为0 0 省省略略第第2 2 参参数数 从从左左边边取取 1212位位 Sale Price10 SaleSale PriSSale Price Sweden 人民大会堂 1234567 从从左左边边提提取取1717位位身身份份证证号号 姓姓名名身身份份证证号号码码结结果果 欧阳芬110221650815224110221650815224=LEFT(B107,17) 李可35058319781012007 朱菊511801520925520511801520925520 周桂香110221290815224110221290815224 西门11010119700626551 张美女620123790513150620123790513150 张35022119490815224 利利用用日日期期函函数数计计算算公公民民年年龄龄 序号姓名性别出生年月 虚岁年龄 000005张三男1968061545=(YEAR(NOW()-LEFT(D117,4) 000006李四男1954071459 000007王二女19790513 000008周六女19741201 000009田七男19781229 000010赵八女19520710 000011雷九男19740620 根根据据性性别别分分别别称称呼呼女女士士或或先先生生 员工编号姓名性别称呼 005赵玉晓女赵女士=LEFT(B127)&IF(C127=男,先生,女士) 006徐生裕女徐女士 007勒智智男勒先生 008魏荣智男魏先生 009臧弟惠女臧女士 010王洪成男王先生 011刘梅瑜女刘女士 012吴娟红女吴女士 RIGHT(text,num_chars)RIGHT(text,num_chars)：从从字字符符串串的的最最右右端端位位置置提提取取指指定定数数量量的的字字符符 Text 是包含要提取字符的文本字符串。 Num_chars 指定希望 RIGHT 提取的字符数。 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。 如果忽略 num_chars，则假定其为 1。 数数据据 求求字字符符串串 长长度度 从从右右边边取取 4 4位位 从从右右边边取取8 8位位 Sale Price10 ricele Price Sweden6 edenSweden 人民大会堂5 民大会堂 人民大会堂 12345677 45671234567 数数据据 第第2 2参参数数 为为0 0 省省略略第第2 2 参参数数 从从右右边边取取1212位位 Sale PriceeSale Price SwedennSweden 人民大会堂堂人民大会堂 123456771234567 从从左左边边提提取取1717位位身身份份证证号号放放在在结结果果1 1列列中中, , 再再将将结结果果1 1列列中中的的号号码码从从右右边边提提取取1 1位位放放在在结结果果2 2中中 姓姓名名身身份份证证号号码码结结果果1 1结结果果2 2 提取第15 位或17位 欧阳芬11022165081522411022165081522444 李可3505831978101200777 朱菊51180152092552051180152092552000 周桂香11022129081522411022129081522444 西门1101011970062655111 张美女62012379051315062012379051315000 张3502211949081522444 编编号号要要4 4位位，不不足足四四位位前前再再加加0 0补补充充 编编号号修修正正后后 880088=RIGHT(0000&A167,4) 230023 1230123 40004 23452345 89008900 760076 890089 10001 从从身身份份证证号号码码中中提提取取性性别别信信息息 姓姓名名身身份份证证号号码码 提取第15 位或17位 对2取余性别性性别别 欧阳芬11022165081522440 女女 李可71 男男 朱菊51180152092552000 女女 周桂香11022129081522440 女女 西门11 男男 张美女62012379051315000 女女 张40 女女 MID(text,start_num,num_chars)MID(text,start_num,num_chars)：从从字字符符串串中中间间的的任任意意位位置置提提取取指指定定数数量量的的字字符符 Text 是包含要提取字符的文本字符串。 Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。 Num_chars 指定希望 MID 从文本中返回字符的个数。 如果 start_num 大于文本长度，则 MID 返回空文本 ()。 如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的 长度，则 MID 只返回至多直到文本末尾的 字符。 如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。 如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。 数数据据 求求字字符符串串 长长度度 从从第第1 1位位 开开始始，取取 3 3位位 从从第第2 2位位开开 始始，取取1010 位位 Sale Price10 Salale Price Sweden 人民大会堂 1234567 数数据据 从从-2-2位位开开始始，取取3 3位位 从从第第2 2位位开开始始，取取 -3-3位位 从从第第1111位位开开始始，取取3 3 位位 Sale Price#VALUE!#VALUE! Sweden 人民大会堂 1234567 从从文文本本日日期期中中提提取取年年、月月、日日 日日期期年年月月日日 2007051020070510 2007051320070513 2007051620070516 2007051920070519 2007052220070522 2007052520070525 2007052820070528 2007053120070531 利利用用DATEDIRDATEDIR函函数数计计算算员员工工周周岁岁 序序号号姓姓名名性性别别出出生生年年月月 虚虚岁岁年年龄龄 周周岁岁年年龄龄 000005张三男196806154544 000006李四男195407145958 000007王二女197905133433 000008周六女197412013938 000009田七男197812293534 000010赵八女195207106160 000011雷九男197406203938 =(YEAR(NOW()-LEFT(D222,4) =DATEDIF(DATE(LEFT(D222,4),MID(D222,5,2),RIGHT(D222,2),TODAY(),y) 利利用用日日期期函函数数计计算算员员工工退退休休日日期期 员员工工号号姓姓名名性性别别出出生生年年月月部部门门退退休休日日期期 A0000张三男19450620 综合部2005/6/21 A00431李四男19540714 开发部2014/7/15 A07520王二女19790513 设计部 A01402周六女19741201 开发部 A02700田七男19781229 推广部 A01234赵八女19520710 系统部 A09943雷九男19740620 系统部 =DATE(LEFT(D234,4)+IF(C234=男,60,55),MID(D234,5,2),RIGHT(D234,2)+1) LEN(text)LEN(text)：返返回回字字符符串串中中的的字字符符个个数数，即即计计算算字字符符串串的的长长度度。 语语法法LENLEN(texttext) Text 是要查找其长度的文本。空格将作为字符进行计数 数据求字符串的长度 abcde, AZ9 0 One8 人民大会堂5 五五、格格式式化化文文本本 1 1、单单元元格格格格式式与与格格式式化化文文本本的的区区别别 在单元格格式对话框中，可以自行定义数值格式，例如将数字15显示为“第15期”，将 108显示为“共108人”等。 同时，Excel提供了一个TEXT函数，也可以达到类似的目的。 TEXTTEXT(valuevalue,format_textformat_text) 主要用于将数值转换为格式化的文本。 参数Value可以是数值、计算结果为数字值的公式，或对包含数字值的单元格的引用，可 以是数字型数值，也可以是文本型数值。 参数format_text可以使用单元格格式的自定义格式代码中的大部分，但不是全部。不支 持的有（1）星号*（2）颜色代码。 但但是是两两者者的的最最大大区区别别在在于于： 单击菜单“格式”-“单元格”，在“单元格格式”对话框中“数字”选项卡下设置单 元格的格式，只是在显示（视觉）上进行改变，而不会影响其中的数值。 使用TEXT函数可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算. 单元格格式格式化文本 123第123期123第123期 345第345期345第345期 2 2、格格式式化化设设备备编编号号 通过文本文件导入的设备编号数据，由于单元格为常规格式，导致编号前不足8位的0全 部丢失，除了利用单元格自定义格式来恢复以外，使用TEXT函数也能够实现编号格式化 序序号号设设备备编编号号前前置置0 0转转换换 11009100010091=TEXT(B272,00000000) 2120400001204 31370000013700为数字占位符,当数字比代码的数量少时, 4482900004829显示无意义的零值 51059200010592 6999000009990 7867400008674 3 3、转转换换日日期期数数据据 日日期期文文本本格格式式转转为为日日期期格格式式 (1)日期字符串为连续数字 姓姓名名文文本本串串规规范范日日期期 欧阳芬196508151965/8/15 =-TEXT(B285,0-00-00) 李可法197810121978/10/12 =TEXT(B285,0-00-00)+0 朱菊19520925=TEXT(B285,0-00-00)*1 周桂香19290815 西门雄19700626 张美女19790513 张娟19490815 令孤强19600606 张生19710801 (2)日期字符串为半角句点间隔 文本日期规范日期 2008.8.8 2008/8/8 =-SUBSTITUTE($A308,.,-) 85.1 1985/1/1 2013.1.23 2013/1/23 将将日日期期格格式式转转换换为为日日期期文文本本 日日期期数数据据转转换换日日期期文文本本注：e为year简写，4位年份 2006/10/2520061025=TEXT($A303,emmdd) 2007/5/1320070513 2008/1/2020080120=TEXT($A305,yyyymmdd) 2008/5/2520080525 2008/8/82008.8.8=TEXT($A307,yyyy.m.d) 2008/8/92008.8.9=TEXT($A308,e.m.d) 选中连续文本单元格有智能标签|转换为数字 选中不连续文本单元格无智能标签 43254432541 43254432540 4325443254 文本单元格单引号开头4325443254 90904325443254 数值单元格数据|分列（一次只能转换一列数据） 999987987987123 345 98798798734 54 98798798765 67 9879879871345 654 987987987897 8766 987987987 987987987 连接注释文字和计算结果 设置一个考勤表的动态标题,可以自动显示出当前月份 鹤煤技师学院2月考勤表 =鹤煤技师学院&MONTH(NOW()&月考勤表 赵女士赵女士 徐女士徐女士 勒先生勒先生 魏先生魏先生 臧女士臧女士 王先生王先生 刘女士刘女士 吴女士吴女士 000088 000023 0000123 00004 00002345 年月日合成日期 19680615196806151968/6/15 全角空格与半角空格 操操作作练练习习 现现在在张张奇奇的的通通讯讯录录，试试统统计计张张奇奇的的同同学学所所在在区区的的区区号号。 姓名电话所在区号 李玉喜010-65659988010 王张瑞赵德刘贾晓提提取取籍籍贯贯地地区区信信息息：身身份份证证编编码码前前6 6位位为为省省份份地地区区信信息息。 姓名身份证号码性别出生日期 虚岁 年龄 周岁 年龄 身份证号 码的长度 提取身份证编 码前6位 欧阳芬110221650815224女1965/8/15484715 110221 李可1978/10/12353418 350583 朱菊511801520925520 周桂香110221290815224 西门张美女620123790513150 张令孤张卓诸葛按按字字符符数数量量排排序序 在制作一份歌曲清单时，人们习惯按照歌曲名字的字数来把它们分门别类。 歌手歌名排序 阿牛桃花朵朵开5 蔡幸娟问你 蔡幸娟两颗心四行泪 刘德华男人哭吧哭吧不是罪 陈慧琳今生你作伴 陈坤月半弯 范玮琪我们的纪念日 蓝心湄不怕付出 梁雁翎像雾像雨又像风 林俊杰曹操 裘正海爱你十分泪七分 薛凯琪寻找独角兽 杨坤无所谓 张含韵想唱就唱 张韶涵潘朵拉 将将文文本本日日期期格格式式转转为为日日期期格格式式 文文本本日日期期转转换换为为日日期期 200808082008/8/8 200612012006/12/1 将将日日期期格格式式转转换换为为日日期期文文本本 日日期期格格式式转转换换日日期期文文本本 2006/10/2520061025 2007/5/1320070513 2008/1/2020080120 2008/5/2520080525 2008/8/82008.8.8 2008/8/92008.8.9 1 1、计计算算身身份份证证长长度度3 3、计计算算年年龄龄 2 2、利利用用MIDMID函函数数提提取取身身份份证证号号码码中中的的8 8位位生生日日数数字字 身身份份证证号号码码均均为为1818位位 姓名身份证号码 性 别 身份 证长 度 文本串出 生日期 日期格式 （文本） 真正的出生 日期 虚岁 年龄 周岁年 龄 张三41060319680615008x 女18 19680615 1968-06-151968/6/154544 李男18 19930520 1993-05-201993/5/202019 王周钱彭九11010119700626551x 刘身身份份证证号号码码为为1515位位 姓名身份证号码 性 别 身份 证长 度 文本串出 生日期 日日期期格格式式 （文文本本） 真真正正的的出出生生 日日期期 张三410603680615008女15 19680615 1968-06-151968/6/15 李四410603930520001男15 19930520 1993-05-201993/5/20 王五410603670819003 周六510221740201021 钱七350583781012007 彭九110101700626551 刘二350221490815224 身身份份证证号号