![b8634253792d13e3f16be153746941a2.png](https://i-blog.csdnimg.cn/blog_migrate/b24aa99a8d8758677a44ab39ad7bd903.jpeg)
![3955408da94adfba7c47006d92de4e32.png](https://i-blog.csdnimg.cn/blog_migrate/0b50f4310a6cca799ad6c75ad4bd3f05.jpeg)
文 | 雷哥 编辑 | 小西瓜
Text函数是一个超神奇的文本函数,到底有哪些神奇地方呢?
TEXT基本语法为
=TEXT (value,format_text)
其中 Value 可以为数值、能够返回数值的公式,或者对数值单元格的引用
Format_text 为设置Value中格式。
看起来,感觉TEXT函数好像并不神奇。那么,到底TEXT是何方神圣?都有哪些玩法?
雷哥给大家总结了4大玩法。具体如下。
![651fee650fc6ba1a2c1ca749c317b7f4.png](https://i-blog.csdnimg.cn/blog_migrate/e55c2a93797a0b0daa7c6dc8dcf860b9.png)
四舍五入
要求:对数据四舍五入到小数点后1位。
方法:=TEXT(A1,"0.0")
解释:该公式表示对A1单元格的数值,四舍五入到小数点后1位。
如果四舍五入到小数点后2位,公式如何表达? =TEXT(A1,"0.00")
其他实现数据四舍五入到小数点后1位的方法:=ROUND(A1,1),其中公式中的1表示A1的数据四舍五入到小数点后1位。
![82ff80a5ece3ffd50326c25c96cc0d2c.gif](https://i-blog.csdnimg.cn/blog_migrate/3c17d73bf97cadfa2a07fea554ec82fa.gif)
![8f8062211aad4bd2506b3828905f9dca.png](https://i-blog.csdnimg.cn/blog_migrate/9810f45c73d71c387af195e1758c728a.png)
格式转换
A. 日期格式转换
① 修改日期格式为 yyyy-mm-dd
=TEXT(A2,"yyyy-mm-dd")
![fa656eb5f53b5abb380ac79f3dd855dd.gif](https://i-blog.csdnimg.cn/blog_migrate/4ccfd6e21a5ed64de664797f44a7c70f.gif)
② 修改日期格式为yyyy年mm月dd日
=TEXT(A2,"yyyy年mm月dd日")
![6426eed2cafee6371118a3e47a800e83.gif](https://i-blog.csdnimg.cn/blog_migrate/111f28805a3003b603055cd3a2458d32.gif)
③ 提取月和日
方法:=TEXT(A2,"mm")
解释:提取单元格A2中的月份
方法:=TEXT(A2,"dd")
解释:提取单元格A2中的日
![a684764098042e7e6bc978adca93b716.gif](https://i-blog.csdnimg.cn/blog_migrate/d64bb06a29a0e037681d21882e1d9c0c.gif)
④ 转换为星期
=TEXT(A2,"AAAA")
![fb73be5ce9e3fd8c6c956dcb1cffe39c.gif](https://i-blog.csdnimg.cn/blog_migrate/af4f48b20f0622e4dea5eb5761b38a60.gif)
⑤ 实现普通日期变为农历日期
如图所示,A列是公历日期,我们在B2单元格输入公式:
=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")
可以发现,阳历日期变为阴历了。
公式解析
假设 MM=IF(LEN(TEXT(A1,"[$-130000]dd"))=1,"初",""),那么该公式就可以转化为
=TEXT(A1,"[$-130000]yyyy年m月"&MM,"")&"d")
![82ff80a5ece3ffd50326c25c96cc0d2c.gif](https://i-blog.csdnimg.cn/blog_migrate/3c17d73bf97cadfa2a07fea554ec82fa.gif)
a 首先解释MM公式
TEXT(A1,"[$-130000]dd"),A1公历日期转换为阴历,提取日。比如2017/1/25转为阴历后为2016年十二月二十八,那么TEXT(A1,"[$-130000]dd")处理后的结果是28
因此MM公式就表示,如果日(例如,28)字符数=1,那么就输出“初”,否则输出空内容
b =TEXT(A1,"[$-130000]yyyy年m月"&MM,"")&"d") 就表示把阳历日期转换为阴历日期,如果日是个位数,前面就加上“初”字。
B.占位符
强迫症福音——补齐位数。
要求:下图中的数据,位数需要统一为6位。
语法:=TEXT(A1,"000000")
解释:0表示占位符,如果需要统一为6位,直接输入公式 =TEXT(A1,"000000") 即可。其中0的个数为6个!
![0364d5b9c723a2635ec200eec28436c6.gif](https://i-blog.csdnimg.cn/blog_migrate/ce35ef7aebcc1c3803fdff0fdaf25b75.gif)
![947d17af4b036c621e2be5b20ec24081.png](https://i-blog.csdnimg.cn/blog_migrate/32e505463993fe2a11552b484cd65a81.png)
盈亏判断
要求:如图所示,D列数据收入-支出>0, 结果返回“赚”;如果收入-支出=0, 结果返回“平”;收入-支出<0, 结果返回“亏”。
方法:=TEXT(D2,"赚;亏;平")
解释:语法为 TEXT(D2,"正数的显示值;负数的显示值;零的显示值") 。相当于:if(数据>0,"赚",if(数据<0,"亏","平"))。
Text用来条件判断确实是太好用了。
![4b47e684eae6cbb35a469c4abd5ff491.gif](https://i-blog.csdnimg.cn/blog_migrate/9164ab04be757bb63c7b72a13278bb21.gif)
![8d92ff01b0ea4c4f026f54f7e1ccb32f.png](https://i-blog.csdnimg.cn/blog_migrate/0771690ce17bd09c877a4e67abd7e074.png)
自定义多条件判断
① 要求:如图所示,成绩大于等于90分,等级为“优”;成绩在60~90分之间,等级为"及格";小于60分,等级为"差"! 如何利用函数进行判断?
方法:=TEXT(B2,"[>=90]优;[>=60]及格;差")
解释:函数 =TEXT(B2,"[>=90]优;[>=60]及格;差") 表示 如果 B2单元格中的数据,成绩>=90分,等级为“优”;>=60且<90,等级为及格;<60,等级为差。相当于if函数 = if(B2>=90,"优",if(B2<60,"差","及格")) 。
![bf7eaa3ffbaee308ba3400bbd57a6135.gif](https://i-blog.csdnimg.cn/blog_migrate/74e586127f19cba2d08c357b12c41a36.gif)
② 学到这里,有些同学可能会问:假如有4个条件,或者5个以上的条件,TEXT函数还适用吗?
我们可以测试下。
要求:如图所示,成绩大于等于90分,等级为“优”;成绩在>=70且 <90分,等级为“良”;成绩在>=60且 <70分,等级为及格;小于60分,等级为“差”! 如何利用函数进行判断?
方法:根据之前讲解的思路=TEXT(B2,"[>=90]优;[>=70]良;[>=60]及格;差")。
结果如下,发现结果是 #VALUE。
解释:原来TEXT函数, 最多只能添加2个条件,如果3个以上的条件,需要考虑if函数或text嵌套函数!
![8af88459e0fe4b231a523f50454873d6.gif](https://i-blog.csdnimg.cn/blog_migrate/610cc54bc6a2c2329e51f2b2c493ba56.gif)
③ 要求:如图所示,成绩大于等于90分,等级为“优”;成绩在60~90分之间,等级为“及格”;小于60分,等级为“差”!如果没参加考试,标记为“缺考”! 如何利用函数进行判断?
方法:=TEXT(B2,"[>=90]优;[>=60]及格;差;缺考")
解释:=TEXT(B2,"[>=90]优;[>=60]及格;差;缺考") 公式表示>=90,返回“优”;B2分数>=60且<90,返回“及格”;<60,返回“差”;如果B2是文本,则返回文本“缺考”。
![8e50234a23022c28b7233a984dacd03a.gif](https://i-blog.csdnimg.cn/blog_migrate/698e7b8ed0ef0166f227a4eeb3d154ee.gif)
![204dc26ad2654cf35ff27825299027ee.png](https://i-blog.csdnimg.cn/blog_migrate/daae4c87cecd79320427185d712dc72d.png)
TEXT函数实现大小写转换语法:TEXT(要转换的数值,格式参数)
格式参数为"[dbnum1]":普通大写,如“二百五十九”;
格式参数为"[dbnum2]":财务大写,如“柒佰捌拾”;
格式参数为"[dbnum3]":阿拉伯数字+单位,如“2百4十9”;
![f57ef1dfc708a33c549256b632b020fc.png](https://i-blog.csdnimg.cn/blog_migrate/044f38d29ce39f339f3977ddf870e872.jpeg)
![9a7dd73aacccf0ec5e544142554e80a3.png](https://i-blog.csdnimg.cn/blog_migrate/ffd3208134d7c5899725a0d73f8f1862.png)
用法一:四舍五入
公式:=text(value,"0.0")
解释:需要保留几位小数,小数点后就加几个0。
用法二:格式修改
公式:=text(value,format_text)
解释:
format_text为所需要目标格式
用法三:Text函数盈亏判断
TEXT(value,"正数的显示值;负数的显示值;零的显示值")
解释:
如果value>0, 输出内容“正数的显示值”;
如果value<0, 输出内容“负数的显示值”;
如果value=0, 输出内容“零的显示值”
用法四:Text函数自定义多条件判断
公式: = text(value," [条件1]显示内容;[条件2]显示内容;否则显示内容;文本显示格式")
解释:
① text函数中,条件可以为1个,最多只能添加2个条件。如果条件>=3,需要使用if或者ifs或者嵌套的text函数。
②[文本] 部分可以省略不写
用法五:TEXT函数实现大小写转换
语法:TEXT(要转换的数值,格式参数)
解释:
格式参数为"[dbnum1]":普通大写,如“二百五十九”;
格式参数为"[dbnum2]":财务大写,如“柒佰捌拾”;
格式参数为"[dbnum3]":阿拉伯数字+单位,如“2百4十9”;
更多精彩内容欢迎大家关注微信公号【雷哥office】
——————————
修改日志:
2019/6/4 增加了用法五
2019/7/6 增加了text函数实现日期变为农历