每天清晨六点,准时与您相约 TEXT函数是一个超级好用的格式化文本函数,今天韩老师详细给大家讲来。 功能: TEXT 函数可通过格式代码对数字应用格式,从而更改数字的显示方式。 如果要按更可读的格式显示数字,或者将数字与文本或符号组合,它将非常有用。 语法 TEXT(数值,格式代码)
text返回的一律都是文本形式的数据。如果需要计算,可以先将文本转换为数值,然后再计算。 文本型数值遇到四则运算会自动转为数值。 但文本会不参与sum之类的函数运算。 TEXT 函数主要是通过格式代码来应用格式的。 今天,韩老师来讲TEXT函数格式日期与时间的用法: 先看下图,TEXT函数通过不同的格式代码,转换日期格式的结果: (格式代码,即是TEXT公式的第二个参数,下图公式中“”内的部分) 公式中的代码及其含义:
另:阿拉伯数字与中文数字转换时:
公式中的代码及其含义:
要将逗号显示为千位分隔符或按倍数1,000 缩放数字。 ,(逗号)在数字中显示千位分隔符。如果格式中含有被数字符号(#) 或零包围起来的逗号,Excel 会分隔千位。位占位符后的逗号会以1,000 为单位计量数字。例如,如果format_text 参数为 '#,###.0,',Excel会将数字 12,200,000显示为 12,200.0。 公式中的代码及其含义:
其中:# 只显示有意义的数字而不显示无意义的零。 公式中的代码及其含义:
8.1四个条件区段: TEXT函数的格式代码默认分为4个条件区段,各区段之间用半角分号间隔。 默认情况下,这四个区段的定义为: [>0];[<0];[=0];[文本] 【举例1】按区段条件判断,然后返回相应结果: 公式:=TEXT(A2,'0.00;-0;0;文本')的含义是: A2单元格的值,按照四种情况返回结果:
【举例2】按区段条件,强制返回相应结果: 公式:=TEXT(A8,'1!0!0;5!0;0;文本')的含义是: A8单元格的值,按照四种情况返回结果:
公式中使用的感叹号(英文半角)是转义字符,强制其后的第一个字符不具备代码的含义,而仅仅是数字。比如:1!0!0,将两个0强制成数字0,而不是数字格式代码0。 在实际应用中,可以使用部分条件区段。 8.2三个条件区段: 三个区段为: [>0];[<0];[=0] 【举例3】 公式:=TEXT(A15,'盈利;亏损;平衡')的含义是: A15单元格的值,按照三种情况返回结果:
8.3两个条件区段: 两个区段的为: [>0];[<0] 【举例4】 公式:=TEXT(A22,'盈利;亏损')的含义是: A22单元格的值,按照两种情况返回结果:
一个区段的,就不讲了,昨天前天的两篇文章,都算是一个区段的。 TEXT函数除了可以使用默认区段以外,还可以自定义条件区段。 9.1四个自定义条件区段: 四个区段的定义为: [条件1];[条件2];[不满足条件的其他部分];[文本] 【举例5】 公式:=TEXT(A38,'[>=85]优秀;[>=60]合格;不合格;无成绩')的含义是: A38单元格的值,按照自定义的四种情况返回结果:
9.2三个自定义条件区段: 三个区段的定义为: [条件1];[条件2];[不满足条件的其他部分] 【举例6】 公式:=TEXT(A46,'[>=85]优秀;[>=60]合格;不合格')的含义是: A46单元格的值,按照自定义的四种情况返回结果:
9.3两个自定义条件区段: 两个区段的定义为: [条件];[不满足条件的其他部分] 【举例7】 公式:=TEXT(A54,'[>=60]合格;不合格')的含义是: A54单元格的值,按照自定义的四种情况返回结果:
以上举例中,我们可以看到,成绩只能判断到“优秀、合格、不合格”级别,如果再多级别,一个TEXT就解决不了了。TEXT函数也可以嵌套解决这个问题: 【举例8】 要求: 90分及以上,返回“优秀”; 70分及以上,返回“良好”; 60分及以上,返回“合格”; 60分以下,返回“不合格”。 结果如下: 公式: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,然后计算:
|
|