当前所在位置: 首页 > 儿童教育 > 正文

excel图表超级好用的文本函数——TEXT

2023-01-26 餐饮美食网 【 字体:

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,返回“良好”;否则,返回“合格”;
阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag