TEXT函数是一个超级好用的格式化文本函数,今天详细给大家讲来。

函数基础

功能:

TEXT 函数可通过格式代码对数字应用格式,从而更改数字的显示方式。

如果要按更可读的格式显示数字,或者将数字与文本或符号组合,它将非常有用。

语法

TEXT(数值,格式代码)

  text返回的一律都是文本形式的数据。如果需要计算,可以先将文本转换为数值,然后再计算。

文本型数值遇到四则运算会自动转为数值。

但文本会不参与sum之类的函数运算。

分类应用

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

今天,韩老师来讲TEXT函数格式日期与时间的用法:

1、格式日期

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

(格式代码,即是TEXT公式的第二个参数,下图公式中“”内的部分)

text是什么意思(函数text的使用方法图解)(1)

公式中的代码及其含义:

代码 含义
m 将月显示为不带前导零的数字。
mm 根据需要将月显示为带前导零的数字。
mmm 将月显示为缩写形式(Jan 到 Dec)。
mmmm 将月显示为完整名称(January 到 December)。
d 将日显示为不带前导零的数字。
dd 根据需要将日显示为带前导零的数字。
ddd 将日显示为缩写形式(Sun 到 Sat)。
dddd 将日显示为完整名称(Sunday 到 Saturday)。
yy 将年显示为两位数字。
yyyy 将年显示为四位数字。

另:阿拉伯数字与中文数字转换时:

2、格式时间

text是什么意思(函数text的使用方法图解)(2)

公式中的代码及其含义:

代码 含义
h 将小时显示为不带前导零的数字。
[h] 以小时为单位显示经过的时间。如果使用了公式,该公式返回小时数超过 24 的时间,请使用类似于 [h]:mm:ss 的数字格式。
hh 根据需要将小时显示为带前导零的数字。如果格式含有 AM 或 PM,则基于 12 小时制显示小时;否则,基于 24 小时制显示小时。
m 将分钟显示为不带前导零的数字。
注释 m 或 mm 代码必须紧跟在 h 或 hh 代码之后或紧跟在 ss 代码之前;否则,Excel 会显示月份而不是分钟。
[m] 以分钟为单位显示经过的时间。如果所用的公式返回的分钟数超过 60,请使用类似于 [mm]:ss 的数字格式。
mm 根据需要将分钟显示为带前导零的数字。
注释 m 或 mm 代码必须紧跟在 h 或 hh 代码之后或紧跟在 ss 代码之前;否则,Excel 会显示月份而不是分钟。
s 将秒显示为不带前导零的数字。
[s] 以秒为单位显示经过的时间。如果所用的公式返回的秒数超过 60,请使用类似于 [ss] 的数字格式。
ss 根据需要将秒显示为带前导零的数字。如果要显示秒的小数部分,请使用类似于 h:mm:ss.00 的数字格式。
AM/PM、am/pm、A/P、a/p 基于 12 小时制显示小时。时间介于午夜和中午之间时,Excel 会使用 AM、am、A 或 a 表示时间;时间介于中午和午夜之间时,Excel 会使用 PM、pm、P 或 p 表示时间。

3、千分位分隔符

要将逗号显示为千位分隔符或按倍数1,000 缩放数字。

,(逗号)在数字中显示千位分隔符。如果格式中含有被数字符号(#) 或零包围起来的逗号,Excel 会分隔千位。位占位符后的逗号会以1,000 为单位计量数字。例如,如果format_text 参数为 "#,###.0,",Excel会将数字 12,200,000显示为 12,200.0。

text是什么意思(函数text的使用方法图解)(3)

公式中的代码及其含义:

代码 含义
"#,###" 只保留整数
"#,###.00" 保留两位小数
"#," 显示为1,000的整倍数
"#,###.0," 显示为1,000的整倍数,且保留一位小数
"0.0,," 显示为1,000,000的整倍数,且保留一位小数

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

4、格式数字、货币

text是什么意思(函数text的使用方法图解)(4)

公式中的代码及其含义:

代码 含义
"0.00" 只保留整数
"#,##0" 千分位分隔符,只保留整数
"#,##0.00" 千分位分隔符,保留整数两位小数
"$#,##0" 只保留整数
"$#,##0.00" 保留两位小数
"$#,##0.00_);($#,##0.00)" 两位小数,负数
"$ * #,##0" 只保留整数,$与数字间一个空字符
"$ * #,##0.00" 两位小数,$与数字间一个空字符

5、加0前导符补充位数

text是什么意思(函数text的使用方法图解)(5)

6、百分比

text是什么意思(函数text的使用方法图解)(6)

7、特殊格式

text是什么意思(函数text的使用方法图解)(7)

8、条件区段判断

8.1四个条件区段:

TEXT函数的格式代码默认分为4个条件区段,各区段之间用半角分号间隔。

默认情况下,这四个区段的定义为:

[>0];[<0];[=0];[文本]

【举例1】按区段条件判断,然后返回相应结果:

text是什么意思(函数text的使用方法图解)(8)

公式:=TEXT(A2,"0.00;-0;0;文本")的含义是:

A2单元格的值,按照四种情况返回结果:

【举例2】按区段条件,强制返回相应结果:

text是什么意思(函数text的使用方法图解)(9)

公式:=TEXT(A8,"1!0!0;5!0;0;文本")的含义是:

A8单元格的值,按照四种情况返回结果:

公式中使用的感叹号(英文半角)是转义字符,强制其后的第一个字符不具备代码的含义,而仅仅是数字。比如:1!0!0,将两个0强制成数字0,而不是数字格式代码0。

在实际应用中,可以使用部分条件区段。

8.2三个条件区段:

三个区段为:

[>0];[<0];[=0]

【举例3】

text是什么意思(函数text的使用方法图解)(10)

公式:=TEXT(A15,"盈利;亏损;平衡")的含义是:

A15单元格的值,按照三种情况返回结果:

8.3两个条件区段:

两个区段的为:

[>0];[<0]

【举例4】

text是什么意思(函数text的使用方法图解)(11)

公式:=TEXT(A22,"盈利;亏损")的含义是:

A22单元格的值,按照两种情况返回结果:

一个区段的,就不讲了,昨天前天的两篇文章,都算是一个区段的。

9、自定义条件区段

TEXT函数除了可以使用默认区段以外,还可以自定义条件区段。

9.1四个自定义条件区段:

四个区段的定义为:

[条件1];[条件2];[不满足条件的其他部分];[文本]

【举例5】

text是什么意思(函数text的使用方法图解)(12)

公式:=TEXT(A38,"[>=85]优秀;[>=60]合格;不合格;无成绩")的含义是:

A38单元格的值,按照自定义的四种情况返回结果:

9.2三个自定义条件区段:

三个区段的定义为:

[条件1];[条件2];[不满足条件的其他部分]

【举例6】

text是什么意思(函数text的使用方法图解)(13)

公式:=TEXT(A46,"[>=85]优秀;[>=60]合格;不合格")的含义是:

A46单元格的值,按照自定义的四种情况返回结果:

9.3两个自定义条件区段:

两个区段的定义为:

[条件];[不满足条件的其他部分]

【举例7】

text是什么意思(函数text的使用方法图解)(14)

公式:=TEXT(A54,"[>=60]合格;不合格")的含义是:

A54单元格的值,按照自定义的四种情况返回结果:

10、巧用TEXT嵌套自定义多条件区段

以上举例中,我们可以看到,成绩只能判断到“优秀、合格、不合格”级别,如果再多级别,一个TEXT就解决不了了。TEXT函数也可以嵌套解决这个问题:

【举例8】

要求:

结果如下:

text是什么意思(函数text的使用方法图解)(15)

公式:TEXT(TEXT(A62-60,"[>=30]优秀;不合格;0"),"[>=10]良好;合格"),分解来解释:

TEXT(A62-60,"[>=30]优秀;不合格;0")

对A62-60进行分段计算:

通过这个公式,把成绩分段成了>=90,<60,60~89三个区段。

TEXT(TEXT(A62-60,"[>=30]优秀;不合格;0"),"[>=10]良好;合格")

这一部分,对60~89的成绩,减去60,然后计算: