文本函数1课件.ppt

1、page1文本函数文本函数leftleft、rightright、midmid1substitutesubstitute、replace replace 、reptrept2lenlen、findfind3charchar、codecode4texttext5page2leftleft、rightright、midmid1 用用法讲解及示例法讲解及示例2 作作业业返回返回LEFTLEFT和和LEFTBLEFTB函数函数RIGHTRIGHT和和RIGHTBRIGHTB函数函数MIDMID和和MIDBMIDB函数函数page3使用使用LEFTLEFT和和LEFTBLEFTB函数返回左边字符函数返回

2、左边字符 这两个函数都有两个参数。Text表示包含要提取字符的文本字符串，num_chars指定函数LEFT所要提出的字符数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例1。 LEFT和LEFTB函数作用是基于所指定的字符数返回文本字符串中的第一个或前几个字符。所不同的是LEFT以字符为单位，而LEFTB以字节为单位。这两个函数表达式如下：LEFT(text,num_chars) LEFTB（text，num_bytes)page4示例1page5例题例1：现要求从人名中提取出姓氏进行称

3、呼的输入，要求输入为“某某市某先生/女士”。具体步骤如下： Step1:制作如图所示表格。 Step2：要想得到第一个人的称呼，即可在单元格D2中输入公式： 回车后则可得到第一个人的称呼。（concatenate函数实现合并字符串） step3:拖拽公式自动填充。page6使用使用RIGHTRIGHT和和RIGHTBRIGHTB函数返回函数返回右右边字符边字符 RIGHT和RIGHTB函数作用是基于所指定的字符数返回文本字符串中的最后一个或多个字符。所不同的是RIGHT以字符为单位，而RIGHTB以字节为单位。这两个函数表达式如下：RIGHT(text,num_chars)RIGHTB（tex

4、t，num_bytes) 这两个函数都有两个参数。Text表示包含要提取字符的文本字符串，num_chars指定函数所要提出的字符数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例。问题1：LEFT、LEFTB函数和RIGHT、RIGHTB函数有什么异同点？page7 MID和MIDB函数作用是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。所不同的是MID以字符为单位，而MIDB以字节为单位。这两个函数表达式如下：MID(text,stsrt_num,num_chars

5、)MIDB（text，stsrt_num,num_bytes)使用使用MIDMID和和MIDBMIDB函数查找特定的字符函数查找特定的字符 这两个函数都有3个参数。Text表示包含要提取字符的文本字符串，start_num表示文本中要提取的第一个字符的位置，文本中第一个字符的位置为1，以此类推。num_chars指定函数从文本中返回的字符个数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例。page8示例2水果英文名售量（箱）香蕉Banana50苹果Apple67公式结果说明=MID(A2

6、,1,2)香蕉从第一个字符开始，返回两个字符=MID(B2,3,3)nan从第三个字符开始，返回三个字符=MID(B2,3,6)nana要返回的字符大于总长度，则返回直到最后的字符=MID(C2,3,2)start_num超过字符长度，返回空值=MID(B3,0,2)#VALUE! start_num小于1，返回错误值=MID(B3,1,-1)#VALUE! num_chars小于0，返回错误值=MIDB($A2,1,2)香从第一个字节开始，返回两个字符=MIDB($B2,3,3)nan从第三个字节开始，返回三个字符=MIDB($B2,3,6)nana要返回的字节大于总长度，则返回直到最后的字

7、节=MIDB($C2,3,2)start_num超过字节长度，返回空值=MIDB($B2,0,2) #VALUE! start_num小于1，返回错误值=MIDB($B2,1,-1)#VALUE! num_chars小于0，返回错误值page9例题2 在网上注册一些表格时经常要填写身份证号码，填写完毕系统会自动生成你的出生日期。现以某公司员工为例，根据身份证号码提取员工出生年月日，具体的操作步骤如下： Step1:制作如图所示表格。 Step2:根据身份证号码提取员工出生年月日，在单元格C2中输入公式 “=MID(B2,7,8)”，回车即可。 Step3:拖拽公式自动填充。page10subs

8、titute、replace 、rept返回返回substitutereplacereptpage11 使用SUBSTITUTE函数替换文本 SUBSTITUTE函数的作用是在文本字符串中用new_text代old_text,其表达式如下： substitute（text,old_text,new_text,instance-num) 从表达式中可以看出该函数有4个参数。text为需要替换其中字符的文本，或对含有文本的单元格的引用； old_text为需要替换的旧文本； new_text为用于替换old_text的文本；instance-num为一个数值，用来指定以替换第几次出现的。该函数的具

9、体应用见示例3。page12示例3水果香蕉4854654substitute公式结果说明=SUBSTITUTE(A1,香蕉,苹果)水果苹果用“苹果”替换“香蕉”=SUBSTITUTE(B1,4,9,2)4859654用“9”替换第二个“4”=SUBSTITUTE(B1,4,9)9859659用“9”替换的所有的“4”=SUBSTITUTE(C1,s,S,1)Substitute用“S”替换第一个“s”=SUBSTITUTE(C1,s,T)TubTtitute用“T”替换的所有的“s”=SUBSTITUTE(B1,4,9,0)#VALUE!instance_num小于1，返回错误值page13

10、使用replace函数替换文本replace函数的作用是使用new_text根据指定的字符数替换old_text中的部分文本,其表达式如下：replace（old_text,start_num,num_chars,new_text)从表达式中可以看出该函数有4个参数。old_text为需要替换其部分字符的旧文本；start_num表示要用new_text替换的old_text中字符的位置；num_chars表示函数replace使用new_text替换old_text中字符的个数；该函数的具体应用见示例4。 问题2：replace和substitute函数有什么异同点？page14示例4水果香

11、蕉4854654replace2004公式结果说明=REPLACE(A1,3,2,苹果) 水果苹果用“苹果”替换“香蕉”=REPLACE(B1,1,3,*)*4654用“*”替换前三位字符=REPLACE(B1,4,9,Excel)repExcel用“Excel”替换第四位以后的字符=REPLACE(A2,4,1,5)2005用“5”替换“4”=REPLACE(A2,-2,3,Excel) #VALUE!start_num小于0，返回错误值=REPLACEB(A1,3,2,苹果)水苹果香蕉以字节为单位，用“苹果”替换2个字节=REPLACEB(B1,1,3,*)*4654用“*”替换前三个字节

12、=REPLACEB(C1,4,9,Excel) repExcel用“Excel”替换第四位以后的字节=REPLACEB(A2,4,1,5)2005用“5”替换“4”=REPLACEB(A2,-2,3,*)#VALUE!start_num小于0，返回错误值page15 例题例题3 某市的电话号码要升位，在原来的电话号码前加一个“8”，现使用REPLACE函数完成某公司几位员工的电话号码的升位。具体步骤如下：1)制作如图所示的表格；2)计算升位的电话号码。输入公式“=REPLACE(B2,1,3,”0218”)”，回车后得到“李宁”的新的电话号码；3)拖拽公式自动填充。姓名性别原电话号码新电话号码

13、李宁男0216845231张明男0216562623陈晓女0216984520赵强男0216666452刘玉梅女0216000213邵刚男0216444852李燕女0216999988page16 使用rept函数复制文本 rept函数的作用是按照给定的次数复制显示文本，其表达式如下： rept（text,num_times) 其中text表示需要重复显示的文本，num_times为指定文本重复次数的正数。如果num_times等于0，返回空值；如小于0，返回错误；若为小数则截尾取整。rept函数的结果不能大于32767个字符，否则返回错误。具体应用见示例5。page17示例5公式结果说明=R

14、EPT(函,4) 函函函函将“函”复制了5次=REPT(,3) 将“”复制了3次=REPT(*,7)*将“*”复制了7次=REPT(*,0)num_times为0，返回空值=REPT(,5.6) num_times为小数，截尾取整=REPT(,-2)#VALUE!num_times小于0，返回错误值page18例题例题4现有部分销售人员某种化妆品如下图，在单元格中绘制出销售量除以50的条形图。具体的操作步骤如下：step1：制作表格；step2：选中单元格，单击【插入】中【符号】菜单项打开【符号】对话框，切换到【符号】选项卡中，在【字体】下拉列表中选择【普通文本】，在【子集】下拉列表中选择【方

15、块元素】；step3：输入格式“=REPT($I$12,INT(G13/50)”回车得到销量条图。姓名销量条形图李宁216张明356陈晓197赵强296刘玉梅456邵刚280李燕320page19lenlen、findfind、charchar、codecodecharchar、codecodelenlen、findfindtexttextpage20使用len和lenb函数计算字符串长度len和lenb函数作用是返回字符串长度。表达式如下：len(text)lenb(text)text表示需要查找其长度的文本，值得注意的是空格也作为字符计算在内。具体应用见示例6。page21示例6水果英文名

16、售量（箱）香蕉Banana50苹果Apple67公式结果说明=LEN(A2)2以字符为单位，返回“香蕉”的长度=LEN(B2)6以字符为单位，返回“Banana”的长度=LEN(C3)2以字符为单位，返回“67”的长度=LEN(D3)00空单元格的长度为0=LENB(A2)4以字节为单位，返回“香蕉”的长度=LENB(B2)6以字节为单位，返回“Banana”的长度=LENB(C3)2以字节为单位，返回“67”的长度=LENB(D3)00空单元格的长度为0page22使用find和findb函数计算字符串长度find和findb函数作用是查找一个字符串在另一个字符串出现的位置。表达式如下：fi

17、nd(find_text,within_text,start_num)findb(find_text,within_text,start_num)find_text表示需要查找的字符串，within_text是包含要查找字符串的字符串,start_num用以指定开始进行查找的字符参数中的首字符是编号为1的字符，如果忽略则假设其为1。具体应用见示例7。page23示例7水果英文名售量（箱）香蕉Banana50苹果Apple67桔子Orange45公式结果说明=FIND(苹,A3)1从第一个字符开始找，该字符在第一位=FIND(p,B3)2从第一个字符开始找，第一个“p”在第二位=FIND(o,B

18、4)#VALUE!区分大小写，找不到“o”，返回错误值=FIND(a,B2,3)4从第三个字符开始找，第一个“a”在该字符串的第四位=FIND(5,C4)2数字可以不用引号，返回“5”所在的位置=FIND(,C3)1find_text为空，则返回第一个字符所在的位置=FIND(c,B4)#VALUE!找不到该字符，返回错误值=FIND(5,C2,0)#VALUE!start_num参数小于1，返回错误值=FIND(5,C2,3)#VALUE!start_num参数大于该字符的总长度，返回错误值=FINDB(果,A3)3以字节为单位,一个汉字占两个字节，所以返回3=LEFTB(a,B3)#VAL

19、UE!区分大小写，找不到“a”，返回错误值=FINDB(a,B2,3)4一个英文字母占一个字节，从第三个字符查找，第一个“a”在第三位=FINDB(c,B4)#VALUE!找不到该字符，返回错误值=FINDB(5,C2,-1)#VALUE!start_num参数小于1，返回错误值=FINDB(5,C2,3)#VALUE!start_num参数大于该字符的总长度，返回错误值page24char、code char函数的作用是将数字代码转换成相应的字符，code函数的作用是将文本字符串中的第一个字符转换成数字代码，这两个函数表达式如下： char(number) code(text) number

20、表示用于转换的数字代码，介于1到255之间。 char函数和code函数的区别与联系是什么？ 答：二者可以相互转换，即CHAR函数中的参数可以作为CODE函数中的结果，不同的是CHAR函数中的参的取值在1到255之间。而CODE函数可以返回中文字符的代码，其数值会超过255。page25char函数char函数公式结果说明=CHAR(48)0返回字符表中第48个字符=CHAR(25)返回字符表中第25个字符=CHAR(68)D返回字符表中第68个字符=CHAR(255)返回字符表中第255个字符=CHAR(3.2)返回字符表中第3个字符=CHAR(52.6)4返回字符表中第52个字符=CHAR

21、(256)#VALUE!大于255，返回错误值=CHAR(-2)#VALUE!小于1，返回错误值page26code函数code函数公式结果说明=CODE(A)65返回字符A对应的数字代码=CODE(a)97返回字符a对应的数字代码=CODE(ABDF)65返回第一个字符A对应的数字代码=CODE(5)53返回5对应的数字代码=CODE( )32返回空格对应的数字代码=CODE(A)#NAME?输入不是字符类型，返回错误值=CODE(5)53返回5对应的数字代码=CODE(函)47791返回“函”对应的代码=CODE(函数)47791返回“函数”对应的代码=CODE(我)52946返回“我”对

22、应的代码=CODE(我)#NAME?输入不是字符类型，返回错误值page27使用text函数将数值转换为文本 TEXT函数的作用是将数值转换为按指定数字格式表示的文本，其表达式如下： TEXT（value,format_text） value表示数值、计算结果为数字值的公式，或对包含数字值的单元格的引用；format_text为【单元格格式】对话框【数字】选项卡中【分类】列表框中的文本形式的数字格式，可以通过单击【格式】中【单元格】菜单项打开【单元格格式】对话框。该参数不能包含星号。使用TEXT可以将数值转换为带格式的文本，而结果将不再作为数字参与计算。该函数的具体应用如下：page28text函数2564.58658430.32公式结果说明=TEXT(A1,0)2565按照个位的格式将其四舍五入=TEXT(A1,$0.00)$2564.58转换成货币格式=TEXT(B1,dd-mmm-yyyy) 07-Apr-2080转换成日期格式=TEXT(C1,0.00%)32.00%转换成百分比格式page29实实 验验 作作 业业1.仿照示例1，做关于RIGHT和RIGHTB函数的表格。2.使用len函数将工资按其位数分隔开。现某公司部分员工工资已知如图，试将工资按位数分开。课下练习课件中的例题。精品课件精品课件！精品课件精品课件！