返回目录:excel表格制作
Excel TEXT函数的用法
Excel提供了非常多的强大的函数,应用也非常的广泛,如果你了解这些函数五成的功能,我想也能解决很多的实际问题。
TEXT函数一些高级应用和技巧
1. 平常日期输入时,会有一些极不规范的输入,比如“20171010”和“2017.10.10”,Excel只会把它们当作文本来 处理,不会把它们识别成日期。那我们就需要把它们转换成日期才行。
假设A1内容为20171020,可以使用:
TEXT(A1,"#-00-00")=2017-10-20
假设A1内容为2017.10.20,可以使用:
SUBSTITUTE(A1,".","-") =2017-10-20。
SUBSTITUTE有人会认为它就是一个查找与替换,但它的功能远远不止这些,比如说13615767564,把其中5位数字用*来代替为136*****564,同样假设A1内容为13615767564。
SUBSTITUTE(A1,MID(A1,4,5),"*****")=136*****564。
MID的作用就是把A1从第四位开始取5个值,然后使用SUBSTITUTE来替换成"*",这不难理解。
关于SUBSTITUTE的其它应用,在以后的文章中将再来详述。
2. 在条件格式中使用变量。
它的应用格式为:TEXT(数据源,"条件代码"&变量&";条件代码"&变量&";"&变量),举个例子,假设A1单元格的值为12,TEXT(A1,"[<=12]"&A1&"月;非月份数据")=12月,假设A1单元格的值为18,TEXT(A1,"[<=12]"&A1&"月;非月份数据")=非月份数据
这种应用类似于IF函数,但是TEXT函数最多只能有四个判断条件。个人觉得并不实用。
3. TEXT在数组公式中的应用。
假设A1内容为1985-6-8,A2内容为1997-3-2,A3内容为2001-5-6,A4内容为1983-12-11。求最大的年份:{MAX(TEXT(A1:A4,"yyyy")+0)&"年"}= 2001年,其中{ }为数组公式。详细内容见下图
其中公式中的"+0"就是为了把它转换成数值。前一篇文章中有具体说过,可以翻看前一篇文章。
这一类函数应用有很强的实际意义。特别是人事部门在做员工信息资料的时候会用到,比如说年假计算之类的。后续打算在日期函数的时候再详述。
4. TEXT函数嵌套使用。格式如下:
=TEXT(TEXT(TEXT(TEXT(A1,"[>=90]优秀;[>=80]良好;0"),"[>=70]中等;[>=60]及格;0"),"[>=50]不及格;[>=40]差;0"),"[>=30]很差;[>=20]特别差;0") |
5. 中文大写格式。假设假设A1为”2017“,TEXT(A1,"[dbnum2]")=贰仟零壹拾柒
6.TEXT在财务金额上大写的用法。这个有些复杂,需要慢慢领会。主要就是应用了SUBSTITUTE多次嵌套和TEXT函数。
假设:A2=123456009.12
公式如下:
=IF(A2<0,"负",)&SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A2)),"[dbnum2]")&"元"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分"),"零角","零"),"零分","整"),"零整","整"),"零元零",),"零元",) |
结果为:
=壹亿贰仟叁佰肆拾伍万陆仟零玖元壹角贰分
TEXT函数还有很多应用的方法,鉴于并不常用,就不再缀述。