EXCEL技巧五十六:text函数应用分享

第一部分:TEXT函数基础介绍

  Text函数,有人称它是万能函数。其实,text的宗旨就是将自定义格式体现在最终结果里。Text函数主要是将数字转换为文本。当然,也可以对文本进行一定的处理。

  TEXT函数的语法:TEXT(value,format_text)
  Value:为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。
  Format_text:为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。

  text返回的一律都是文本形式的数据。如果需要计算,可以先将文本转换为数值,然后再计算。文本型数值遇到四则运算会自动转为数值。但文本会不参与sum之类的函数运算。

第二部分:text函数的应用

  第一,基本的数字处理方式。

  text函数的基本写法:text(数值,格式)

  =TEXT(12.34,"0"),含义是将数字12.34四舍五入到个位,然后以文本方式输出结果。这个公式也可以简写成:text(12.34,0),当只有一个0的时候,引号可以不加。
  text(数值,"0.0"),可以看到效果是保留一位小数。写几个0,代表想要数据形成几位数。

  “0”:数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。例如:=text(12.34,"00000"),就显示为:00012。

  “#”:数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量,则按“#”的位数四舍五入。例如:“###.##”,12.1显示为12.10;12.1263显示为:12.13。

  “G/通用格式”:以常规的数字显示,相当于“分类”列表中的“常规”选项。
  例:代码:“G/通用格式”。10显示为10;10.1显示为10.1。

  另外,前导0的效果,想显示几位就写几个0,可以配合rept函数来写,rept(字符,重复次数)。下图就是一个为数据前面加0的效果。

  第二,text在日期时间处理方面的应用。

  先说说日期这种特殊的数据类型,日期“2010-3-12”,其实是数字40249,如下图所示。

  所以,=text(40249,"yyyy-mm-dd")可以显示2010-03-12。
  text是把日期所代表的真正的数字,来转成所需要的日期格式的文本。
EXCEL技巧五十六:text函数应用分享
  而20110506,要显示2011-05-06的话,要用上面介绍的0的方法:=text(20110506,"0-00-00")。

  上图所示公式中:yyyy可以用e来代替。mm,表示显示两位月份,m显示一位。中间的连接号,还可以换成其余文本。
  text的结果是文本,如果text返回"2010-10-10"的话,再去设置格式就改变不了了。
  mmm、mmmm,ddd、dddd等等都有各自的含义。ddd代表英文星期。中文的星期用aaa和aaaa。
  时间里面有个m,和月份相同,所以,单独使用m的时候,系统默认是月份。m必须和h或s同用,才能表示分。[M]带中括号的时候,也表示分。因为加中块号是时间的特殊表示方式。

  第三,text表示四种数据类型。

  =text(数据,"正;负;零;文本")
  
text里面可以表示四种数据类型。正数、负数、零与文本,用分号隔开。根据数据的类型,返回对应位置里的格式。
  没有分号,代表一种格式。
  2个分号:表示单元格为两种格式:分号前面为正数和0;分号后面为负数。
  3个分号:表示单元格为三种格式:第1部分用于正数,第2部分用于负数,第3部分用于0值。比如"0;-0;",将只显示正数和负数,但不显示0;最后一个分号不能省略,如果写成"0;-0"表示的是不一样的含义。

  =text(数据,"1;2;3;@")
  
@是文本的通配符,相当于数值中的0。
  =text(数据,"1;2;3;@"),相当于:if(数据>0,1,if(数据<0,2,3))
  当数据大于0,返回1,小于0,返回2,等于0,返回3,是文本的话,返回其本身。根据分号内的格式自动分配。
  =text(数据,"1;;;")这种,分号内没有要显示的格式了,结果就显示空。也就是,当数据大于0时,显示1,其余显示为空。3分号,四类型。

  第四,强制符号方面的应用。

强制符号!有了它就可以强制显示0了。大于0,显示本身,其他显示0。

  第五,条件判断方面的应用。

  text很经典的用法就是在条件判断方面。因为可以省字符,数组公式中常用。条件需要用中括号括起来。这时,分号的作用就不是隔开正数、负数、零了。条件判断的顺序,是先左后右,如同if函数一样。

  第六,中文数字中的应用。

  其中,用0、和不用、用d的效果不一样。

  如下图根据小写转换为大写人民币:

  第一种数字转人民币大写形式的公式是:
=IF(B1<0,"金额为负无效",(IF(OR(B1=0,B1=""),"(人民币)零元",IF(B1<1,"(人民币)",TEXT(INT(B1),"[dbnum2](人民币)G/通用格式")&"元"))))&IF((INT(B1*10)-INT(B1)*10)=0,IF(INT(B1*100)-INT(B1*10)*10=0,"","零"),(TEXT(INT(B1*10)-INT(B1)*10,"[dbnum2]")&"角"))&IF((INT(B1*100)-INT(B1*10)*10)=0,"整",TEXT((INT(B1*100)-INT(B1*10)*10),"[dbnum2]")&"分")

  第二种数字转人民币大写形式的公式是:
="大写金额:"&IF(TRIM(B1)="","",IF(B1=0,"","人民币"&IF(B1<0,"负",)&IF(INT(B1),TEXT(INT(ABS(B1)),"[dbnum2]")&"元",)&IF(INT(ABS(B1)*10)-INT(ABS(B1))*10,TEXT(INT(ABS(B1)*10)-INT(ABS(B1))*10,"[dbnum2]")&"角",IF(INT(ABS(B1))=ABS(B1),,IF(ABS(B1)<0.1,,"零")))&IF(ROUND(ABS(B1)*100-INT(ABS(B1)*10)*10,),TEXT(ROUND(ABS(B1)*100-INT(ABS(B1)*10)*10,),"[dbnum2]")&"分","整")))

  

爱华网本文地址 » http://www.413yy.cn/a/25101010/27076.html

更多阅读

育儿随笔三十六 :“狼爸虎妈”棍棒教育模式背后的思考

育儿随笔(三十六):关于“狼爸”和“虎妈”教育方式的思考文:杨柳近几天,“中国狼爸”的教育方式成为各大媒体的热议的话题。被热议的“中国狼爸”是香港商人萧百佑,其严格苛刻的教育方式可以和前一阵热炒的“美国虎妈”相“媲美”。狼

看台湾电影之五十六:《初恋风暴》

新年伊始,便接连遭遇好电影。韩国电影《情侣们》带来的惊喜还没有过去,台湾小清新再度袭来,心中的温暖和感动一时难以言说。女主角仍然是最近老夫心中的最爱陈妍希,只是不知道这部电影的拍摄时间是在《那些年,我们一起追的女孩》之前还

声明:《EXCEL技巧五十六:text函数应用分享》为网友义无反顾分享!如侵犯到您的合法权益请联系我们删除