Text函数,对函数新手来讲,是一个陌生又让人生畏的函数。让人望而生畏那是因为对自定义格式不熟。如果熟悉自定义格式,应用这个函数就很简单。Text函数是函数中的自定义格式,可以将数字乔装易容成其他文本。它不但可以改头换面,还可以颠倒黑白、指鹿为马。 格式代码的规则基本上与自定义格式的相同。只是不能使用颜色。为正数时的格式 ; 为负数时的格式 ; 为零值时的格式 ; 为文本时显示的内容[条件1]满足条件1时显示的格式 ; [条件2]满足条件2时显示的格式 ; 不满足条件1和条件2时显示的格式 ; 为文本时显示的内容[条件1]满足条件1时显示的格式 ; [条件2]满足条件2时显示的格式 ; 不满足条件1和条件2时显示的格式[条件1]满足条件1时显示的格式 ; 不满足条件1时显示的格式为了对Text的语法有一个感性认识。我们来看几个示例。
示例1:常规应用
=TEXT(B2,'正#;负#;零;请输入数字') 示例2:指鹿为马,颠倒黑白
解释: =TEXT(B6,'数字;数字;数字;马') 不管是正数负数还是0,都显示为“数字”,如果是文本,不管是什么文本,都显示为“马” =TEXT(B8,'数字;数字;数字;白') 不管是正数负数还是0,都显示为“数字”,如果是文本,不管是什么文本,都显示为“白” 在了解了TEXT函数的基础知识后,来了解一下Text函数在日期时间中的一些常见应用: 解释:
代码
| 作用
|
---|
dddd
| 英文星期几
| aaaa
| 中文星期几
| aaa
| 中文星期几的简写,如:星期六显示“六”
| [$-zh-cn]aaa | 返回“周几”
| [dbnum1] | 返回中文小写
| [dbnum2] | 返回中文大写
| [dbnum3] | 返回全角阿拉伯数字
|
下面我们只使用Text函数来对日期时间进行分段。
1、划分上午还是下午
原数字 (A9单元格) | 显示效果 | 公式 | 9:30 | 上午 | =TEXT(A10,'上午/下午') | 15:00 | 下午 | =TEXT(A11,'上午/下午') | 9:30 | 上午9:30:00 | =TEXT(A12,'上午/下午h:mm:ss') | 15:23 | 下午3:23:00 | =TEXT(A13,'上午/下午h:mm:ss') |
在自定义格式中可以自动划分上午下午,以12:00为界。
也可用Am和Pm来划分。 2、划分白天还是晚上 如果要划分白天还是晚上,就要稍复杂一点
原数字 | 显示效果 | 公式 | 9:30 | 白天 | =TEXT(TEXT(A14,'h'),'[<20]白天;晚上') | 21:00 | 晚上 | =TEXT(TEXT(A15,'h'),'[<20]白天;晚上') |
解释:
[条件1]满足条件1时显示的格式 ; 不满足条件1时显示的格式 3、判断是上半年还是下半年 原数字 | 显示效果 | 公式 | 2020-2-1 | 上半年 | =TEXT(TEXT(A16,'m'),'[<7]上半年;下半年') | 2020-6-13 | 上半年 | =TEXT(TEXT(A17,'m'),'[<7]上半年;下半年') | 2020-7-25 | 下半年 | =TEXT(TEXT(A18,'m'),'[<7]上半年;下半年') | 2020-12-31 | 下半年 | =TEXT(TEXT(A19,'m'),'[<7]上半年;下半年') |
解释:
TEXT(A17,'m'),返回日期的月份数。 4、划分上旬、中旬、下旬
原数字 | 显示效果 | 公式 | 2020-2-1 | 上旬 | =TEXT(TEXT(A20,'d'),'[<10]上旬;[<20]中旬;下旬') | 2020-6-13 | 中旬 | =TEXT(TEXT(A21,'d'),'[<10]上旬;[<20]中旬;下旬') | 2020-7-20 | 下旬 | =TEXT(TEXT(A22,'d'),'[<10]上旬;[<20]中旬;下旬') | 2020-12-31 | 下旬 | =TEXT(TEXT(A23,'d'),'[<10]上旬;[<20]中旬;下旬') | TEXT(A21,'d')返回是日期是该月的哪一天。'[<10]上旬;[<20]中旬;下旬'是TEXT函数格式代码的“变体2.1”:[条件1]满足条件1时显示的格式 ; [条件2]满足条件2时显示的格式 ; 不满足条件1和条件2时显示的格式 5、划分季度
原数字 | 显示效果 | 公式 | 2020-2-1 | 一季度 | =TEXT(TEXT(TEXT(A24,'m'),'[<4]一季度;[<7]二季度;0'),'[<10]三季度;四季度') | 2020-6-13 | 二季度 | =TEXT(TEXT(TEXT(A25,'m'),'[<4]一季度;[<7]二季度;0'),'[<10]三季度;四季度') | 2020-7-20 | 三季度 | =TEXT(TEXT(TEXT(A26,'m'),'[<4]一季度;[<7]二季度;0'),'[<10]三季度;四季度') | 2020-12-31 | 四季度 | =TEXT(TEXT(TEXT(A27,'m'),'[<4]一季度;[<7]二季度;0'),'[<10]三季度;四季度') |
解释:
TEXT(A24,'m')返回日期的月份数。 TEXT函数最多只能判断三种情况,而要判断季度有四种情况,用一个TEXT是没法完成的,所以这里嵌套两层来实现四个种情况的判断。
'[<4]一季度;[<7]二季度;0'意思是月份小于4是返回一季度,月份小于7时返回二季度,前二个情况都不满足,则返回原月份数,留给外层的TEXT函数去处理。 最外层TEXT函数接到里层处理不了的月份数,继续按指定的规则'[<10]三季度;四季度'进行判断:如果小于10,则返回三季度,否则就返回四季度。
图文制作:龙逸凡 原载公众号:Excel偷懒的技术
|