超好用的文字功能——TEXT

TEXT函数是一个超级好用的格式化文本函数，今天就给大家详细介绍一下。

函数基础

功能：

文本功能可以通过格式代码将格式应用于数字，从而改变数字的显示方式。

如果您想要以更易读的格式显示数字，或者想要将数字与文本或符号组合在一起，这非常有用。

语法

文本(数值、格式代码)

Value:数值、计算为数值的公式或对包含数值的单元格的引用。

Format_text:文本形式的数字格式。

文本总是以文本的形式返回数据。如果需要计算，可以先把文字转换成数值，然后再计算。

遇到四种操作时，文本数值会自动转换为数值。

但是，文本不参与sum等函数。

分类应用

TEXT函数主要通过格式代码应用格式。

今天，让我们使用文本函数格式的日期和时间：

1.格式化日期。

先看下图，TEXT函数通过不同的格式代码转换日期格式的结果：

(格式代码，即TEXT公式的第二个参数，下面公式中" "内的部分)。

公式中的代码及其含义：

代码含义

m月显示为不带前导零的数字。

Mm根据需要将月份显示为带前导零的数字。

mmm以缩写形式显示月份(1月至12月)。

Mmmm将月份显示为全名(1月至12月)。

将d显示为不带前导零的数字。

Dd根据需要将日期显示为带前导零的数字。

Ddd以缩写形式显示日期(从Sun到Sat)。

Dddd以全名显示日期(星期日到星期六)。

Yy显示为两位数。

Yyyy以四位数显示年份。

其他：将阿拉伯数字转换为中文数字时：

参数的格式为“[dbnum1]”:普通大写，如“789”；

参数格式为“[dbnum2]”:金融专用资金，如“七百八十九”；

参数的格式为“[dbnum3]”:阿拉伯数字之间加单位，如“7，089”；但是用“[dbnum3]”转换的数字是全角，如果转换成普通的半角，除了TEXT函数外，还要应用ASC函数。

2.格式化时间。

公式中的代码及其含义：

代码含义

h显示为不带前导零的数字。

[h]以小时为单位显示经过的时间。如果使用的公式返回小时数超过24小时的时间，请使用类似[h]:mm 3360s的数字格式。

Hh根据需要将小时显示为带前导零的数字。如果格式包含上午或下午，则显示基于12小时制的小时数。否则，小时是以24小时为基础显示的。

m分钟显示为不带前导零的数字。

注释m或mm代码必须紧接在h或hh代码之后，或者紧接在ss代码之前；否则，Excel会显示月份而不是分钟。

[m]以分钟为单位显示经过的时间。如果使用的公式返回的分钟数超过60，请使用类似[mm]:s的数字格式。

Mm根据需要将分钟显示为带前导零的数字。

注释m或mm代码必须紧接在h或hh代码之后，或者紧接在ss代码之前；否则，Excel会显示月份而不是分钟。

将s显示为不带前导零的数字。

[s]以秒为单位显示经过的时间。如果使用的公式返回超过60秒，请使用类似于[ss]的数字格式。

Ss根据需要将秒显示为带前导零的数字。要显示一秒的小数部分，请使用类似于h :mm 3360s . 00的数字格式

上午/下午、上午/下午、上午/下午、上午/下午显示时间基于12小时制。当时间在午夜和中午之间时，Excel会使用am、AM、A或A来表示时间。Excel使用pm、PM、p或p表示中午到午夜之间的时间。

3.千位分隔符。

以千位分隔符或1，000的倍数显示逗号。

，(逗号)显示数字中的千位分隔符。如果格式包含由数字符号(#)或零包围的逗号，Excel将千位分隔开。占位符后的逗号以1000为单位度量数字。例如，如果format_text参数为“#，# # .0”，Excel会将数字12，200，000显示为12，200.0。

公式中的代码及其含义：

代码含义

“#，# # #”只保留整数。

“#，# # # .00”保留两位小数。

“#”显示为1，000的整数倍。

“#，###.0”显示为1，000的整数倍，并保留一个小数位。

“0.0”显示为1，000，000的整数倍，并保留一个小数位。

其中：#只显示有意义的数字，不显示无意义的零。

4.格式化数字和货币。

公式中的代码及其含义：

代码含义

“0.00”只保留整数。

“#，# # 0”千位分隔符，仅保留整数。

“#，# # 0.00”第千位分隔符，保留整数的两位小数。

“$ #，# # 0”只保留整数。

“$ #，# # 0.00”保留两位小数。

"$#,##0.00_);($#，##0.00)"两位小数，负数。

“$ * #，# # 0”只保留整数，在$和数字之间有一个空字符。

“$ * #，# # 0.00”是两位小数，在$和数字之间有一个空字符。

5.添加0前缀以补充数字。

6.百分比。

7.特殊格式。

8、有条件的分段判决。

8.1四个条件部分：

默认情况下，TEXT函数的格式代码分为四个条件部分，每个部分用分号分隔。

默认情况下，这四个部分定义为：

[0];[0];[=0];[文本]

【例1】根据截面条件判断，然后返回相应的结果：

公式：=text (a2，" 0.00；-0;0;文本”)是指：

A2单元格的值，根据四种情况返回结果：

0，保留两位小数；

0，只保留整数；

=0，返回0值；

文字，返回“文字”二字。

【例2】根据截面条件，强制返回相应的结果：

公式：=text (A8，“1！0!0;5!0;0;文本”)是指：

A8单元格值，根据四种情况返回结果：

0，返回100；

0，返回50；

=0，返回0值；

文字，返回“文字”二字。

公式中使用的感叹号(英文半角)是转义字符，强制其后的第一个字符没有代码意义，只有数字。比如：1！0!0，将两个0强制转换为数字0，而不是数字格式代码0。

实际上，可以使用一些条件部分。

8.2三个条件部分：

这三个部分是：

[0];[0];[=0]

[示例3]

公式：=TEXT(A15，“利润”；损失；余额”)是指：

单元格A15的值根据三个条件返回结果：

0，返回“利润”；

0，返回“损失”；

=0，返回“平衡”；

8.3两个条件部分：

这两个部分是：

[0];[0]

[示例4]

公式：=TEXT(A22，“利润”；损失”)是指：

单元格A22的值在两种情况下返回结果：

0，返回“利润”；

0，返回“损失”；

一节，不是，昨天和前天的两篇文章，都是一节。

9.用户定义的条件部分。

TEXT函数不仅可以使用默认部分，还可以自定义条件部分。

9.1四个自定义条件部分：

这四个部分的定义如下：

[条件1]；[条件2]；【其他不符合条件的部分】；[文本]

[示例5]

公式：=text (a38，“[=85]优秀；[=60]合格；不合格；“没有成就”的意思是：

A38单元格的值根据四个用户定义的条件返回结果：

=85，回归“优秀”；

=60，返回“合格”；

如果值不满足上述条件，则返回“不合格”；

如果不是数值，则返回单词“text”。

9.2三个用户定义的条件部分：

三个部分定义为：

[条件1]；[条件2]；【其他不符合条件的部分】

[示例6]

公式：=text (a46，"[=85]优秀；[=60]合格；不合格”)是指：

A46单元格的值，根据自定义返回四种情况下的结果：

=85，回归“优秀”；

=60，返回“合格”；

不符合上述条件的，退回“不合格”；

9.3两个自定义条件部分：

两个部分的定义如下：

[条件]；【其他不符合条件的部分】

[示例7]

公式：=text (a54，"[=60]合格；不合格”)是指：

单元格A54的值根据四个用户定义的条件返回结果：

=60，返回“合格”；

不符合上述条件的，退回“不合格”；

10.熟练使用TEXT嵌套和自定义多条件节。

从上面的例子可以看出，成绩只能判断为“优秀、合格、不合格”。如果有更多的等级，一个TEXT是不能解决的。TEXT函数也可以嵌套来解决这个问题：

[示例8]

要求：

90分及以上，回归“优秀”；

70分及以上，回归“好”；

60分及以上，恢复为“合格”；

如果分数低于60分，将退回“不合格”。

结果如下：

公式：text (text (a62-60，“[=30]优秀；不合格；0”)，"[=10]为好；合格”)，分解解释：

正文(a62-60，“[=30]优秀；不合格；0)

按分段计算A62-60:

如果=30，则返回“优秀”；

如果为0，则返回“不合格”；

如果不满足上述条件，将返回分数的整数。

如果分数中有小数，最后一节可以写成0.0，或者0.00。

通过这个公式，等级分为三段=90、60、60~89。

Text (text (a62-60，"[=30]优秀；不合格；0”)，"[=10]为好；通行证”)

在这部分，对于60~89的分数，减去60，然后计算：

如果=10，返回“好”；

否则，返回“合格”；