超级好用的文本函数——TEXT
发布时间:2022-07-10 14:54:02
TEXT函数是一个超级好用的格式化文本函数,今天详细给大家来。
函数基础
功能:
TEXT 函数可通过格式代码对数字应用格式,从而更改数字的显示方式。
如果要按更可读的格式显示数字,或者将数字与文本或符号组合,它将非常有用。
语法
TEXT(数值,格式代码)
Value:数值,或是计算结果为数字值的公式,也或对包含数字值的单元格的引用。
Format_text:文本形式的数字格式。
text返回的一律都是文本形式的数据。如果需要计算,可以先将文本转换为数值,然后再计算。
文本型数值遇到四则运算会自动转为数值。
但文本会不参与sum之类的函数运算。
分类应用
TEXT 函数主要是通过格式代码来应用格式的。
今天,来 TEXT函数格式日期与时间的用法:
1、格式日期
先看下图,TEXT函数通过不同的格式代码,转换日期格式的结果:
(格式代码,即是TEXT公式的第二个参数,下图公式中“”内的部分)
公式中的代码及其含义:
代码含义
m将月显示为不带前导零的数字。
mm根据需要将月显示为带前导零的数字。
mmm将月显示为缩写形式(Jan 到 Dec)。
mmmm将月显示为完整名称(January 到 December)。
d将日显示为不带前导零的数字。
dd根据需要将日显示为带前导零的数字。
ddd将日显示为缩写形式(Sun 到 Sat)。
dddd将日显示为完整名称(Sunday 到 Saturday)。
yy将年显示为两位数字。
yyyy将年显示为四位数字。
另:阿拉伯数字与中文数字转换时:
格式参数为”[dbnum1]”:普通的大写,如“七百八十九”;
格式参数为”[dbnum2]”:财务专用大写,如“柒佰捌拾玖”;
格式参数为”[dbnum3]”:阿拉伯数字之间加单位,如“7百8十9”;但用”[dbnum3]”转成的数字是全角,所与如果转换成普通的半角,TEXT函数之外要套用ASC函数。
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。
公式中的代码及其含义:
代码含义
“#,###”只保留整数
“#,###.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函数的格式代码默认分为4个条件区段,各区段之间用半角分号间隔。
默认情况下,这四个区段的定义为:
[>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,返回“合格”;
不满足以上条件的数值,返回“不合格”;
非数值,返回“文本”二字。
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]良好;合格”),分解来解释:
TEXT(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,返回“良好”;
否则,返回“合格”;
猜你喜欢
- 办公软件Word是我们这些办公族每日必备的工具,平常只是用它来办公处理文档用。在办公之余我们可能会在办公室偷偷的上下QQ,或者躲着玩玩小游戏
- 在Word文档打开的时候,显示Word文档损坏,不能正常打开,但文档非常重要。由于Word的版本不同或者数据在复制过程中出错,所以导致文档受
- 如果要将某些常用的URL域名与其对应的IP地址相关联,则必须修改主机文件。 毕竟,当用户在浏览器中输入URL时,系统将首先从主机文件中查找相
- 这篇文章主要介绍了excel如何调换行列位置?excel快速换行列位置方法的相关资料,需要的朋友可以参考下本文详细内容。excel怎么调换行
- 有时候在excel中我们想输入007这些数字,显示的却只是7,那么0应该怎么输入呢?下面小编就为大家介绍在Excel中输入日期时怎么让0显示
- PDF文档的体积比较大时,传送和存档都非常不方便,特别是当文档中有很多图片的情况下,那么怎样才能减小PDF文档的大小呢?在这里教大家一种十分
- 在我们平时使用Excel表格时,经常会用到一些特别的功能。而今天我们带来的Excel考勤表批量填充的方法,可以说对很多人都很实用。那么它是如
- 常常在给word文档添加页眉页脚的时候就会突然多出一条多余的横线,这调横线根本不是自己想要的效果,那么在Word文档中怎么去除页眉里的横线?
- 缺省,又称“默认”。即系统默认状态,这里以word2007为例,设置缺省字号的方法如下:1、在电脑上打开word软件,点击字体旁边的箭头符号
- Office 2016怎么用?Office 2016 迎来了第二个预览版本,新增了手写输入公式的功能。对于教育、科研业者来说,这可真是个非常
- pdf文件怎么重新编排?pdf文件想要重新整理页面顺序,该怎么操作呢?我们需要使用第三方软件,下面就来看看重组PDF文档页面的教程,需要的朋
- 有时候在执行语句时,出现错误,就是因为有千分号的问题,那么我们要怎么去除千分号呢,一起来了解一下吧在日常工作中经常会使用excel,在表格数
- 欢迎观看 Pixelmator Pro 教程,小编带大家学习 Pixelmator Pro 的基本工具和使用技巧,了解如何在 Pixelma
- 我们在日常工作中,经常需要会收到一些PDF文档,而如果要对PDF文档进行编辑,就需要使用到相关的PDF编辑软件,或者将PDF文档转成可以编辑
- 在下图中我们可以看到输入的数值位数是没有规律的,如果我们要设定数值的位数该怎么办呢?首先选中需要更改的单元格,在鼠标右击菜单中选择“设置单元
- 方法一:1、单击插入----符号----其它符号;2、弹出符号对话框,在字体处选择“Wingdings”,选择如图所示的符号即可。方法二:1
- 在Word2003中选中某个单词其中的一部分字母时,Word会自动选定整个单词和其后的空格。用户可以根据实际需要将该功能取消,操作步骤如下所
- 在我们升级了win10操作系统后,一些小型合作伙伴发现,当我们升级和安装win101909版本时,系统的进展率总是达到88%。所以对于这个问
- 打印A4纸张很容易,一般的打印机都是默认的,只需要按几个按钮就行了。但是,日常工作之中,时常需要打印A3大小的纸张,而此操作并不是所有的人都
- 新建文档是word2003中最为基础的知识。下面将向大家介绍几种新建文档的方法。 方法一:如图1所示,通过“开始”菜单—“程序”—“micr