popupwindow 动态修改text的值_Excel中超神奇的百变大咖—Text函数玩法

文 | 雷哥 编辑 | 小西瓜

Text函数是一个超神奇的文本函数，到底有哪些神奇地方呢？

TEXT基本语法为

=TEXT (value,format_text）

其中 Value 可以为数值、能够返回数值的公式，或者对数值单元格的引用
Format_text 为设置Value中格式。

看起来，感觉TEXT函数好像并不神奇。那么，到底TEXT是何方神圣？都有哪些玩法？

雷哥给大家总结了4大玩法。具体如下。

四舍五入

要求：对数据四舍五入到小数点后1位。

方法：=TEXT(A1,"0.0")

解释：该公式表示对A1单元格的数值，四舍五入到小数点后1位。
如果四舍五入到小数点后2位，公式如何表达？ =TEXT(A1,"0.00")
其他实现数据四舍五入到小数点后1位的方法：=ROUND(A1,1)，其中公式中的1表示A1的数据四舍五入到小数点后1位。

格式转换

A. 日期格式转换

① 修改日期格式为 yyyy-mm-dd

=TEXT(A2,"yyyy-mm-dd")

② 修改日期格式为yyyy年mm月dd日

=TEXT(A2,"yyyy年mm月dd日")

③ 提取月和日

方法：=TEXT(A2,"mm")

解释：提取单元格A2中的月份

方法：=TEXT(A2,"dd")

解释：提取单元格A2中的日

④ 转换为星期

=TEXT(A2,"AAAA")

⑤ 实现普通日期变为农历日期

如图所示，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")

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个！

盈亏判断

要求：如图所示，D列数据收入-支出>0, 结果返回“赚”；如果收入-支出=0, 结果返回“平”；收入-支出<0, 结果返回“亏”。

方法：=TEXT(D2,"赚;亏;平")

解释：语法为 TEXT(D2,"正数的显示值;负数的显示值;零的显示值") 。相当于：if(数据>0,"赚",if(数据<0,"亏","平"))。
Text用来条件判断确实是太好用了。

自定义多条件判断

① 要求：如图所示，成绩大于等于90分，等级为“优”；成绩在60~90分之间，等级为"及格"；小于60分，等级为"差"！ 如何利用函数进行判断？

方法：=TEXT(B2,"[>=90]优;[>=60]及格;差")

解释：函数 =TEXT(B2,"[>=90]优;[>=60]及格;差") 表示 如果 B2单元格中的数据，成绩>=90分，等级为“优”；>=60且<90，等级为及格；<60，等级为差。相当于if函数 = if(B2>=90,"优",if(B2<60,"差","及格")) 。

② 学到这里，有些同学可能会问：假如有4个条件，或者5个以上的条件，TEXT函数还适用吗？

我们可以测试下。

要求：如图所示，成绩大于等于90分，等级为“优”；成绩在>=70且 <90分，等级为“良”；成绩在>=60且 <70分，等级为及格；小于60分，等级为“差”！ 如何利用函数进行判断？

方法：根据之前讲解的思路=TEXT(B2,"[>=90]优;[>=70]良;[>=60]及格;差")。

结果如下，发现结果是 #VALUE。

解释：原来TEXT函数， 最多只能添加2个条件，如果3个以上的条件，需要考虑if函数或text嵌套函数！

③ 要求：如图所示，成绩大于等于90分，等级为“优”；成绩在60~90分之间，等级为“及格”；小于60分，等级为“差”！如果没参加考试，标记为“缺考”！ 如何利用函数进行判断？

方法：=TEXT(B2,"[>=90]优;[>=60]及格;差;缺考")

解释：=TEXT(B2,"[>=90]优;[>=60]及格;差;缺考") 公式表示>=90，返回“优”；B2分数>=60且<90，返回“及格”；<60，返回“差”；如果B2是文本，则返回文本“缺考”。

TEXT函数实现大小写转换语法：TEXT（要转换的数值，格式参数）
格式参数为"[dbnum1]"：普通大写，如“二百五十九”；
格式参数为"[dbnum2]"：财务大写，如“柒佰捌拾”；
格式参数为"[dbnum3]"：阿拉伯数字+单位，如“2百4十９”；

用法一：四舍五入

公式：=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十９”；

更多精彩内容欢迎大家关注微信公号【雷哥office】

——————————

修改日志：

2019/6/4 增加了用法五

2019/7/6 增加了text函数实现日期变为农历