用了十几年Excel,这个函数我居然才知道,不要太好用!【Excel教程】

编按:哈喽,大家好!要说在excel中最特别的文本函数,那必定非TEXT函数莫属,外界给它的称号不计其数“文本之王”“整容大师”“千面鬼才”等等,由此可看出对它的喜爱。下面小花就和大家汇总了TEXT函数5种最实用的用法,赶紧来看看吧!学习更多技巧,请收藏部落窝教育excel图文教程


TEXT函数是众多文本函数中颇受花瓣们喜爱的一个,小花将它称之为千机变。它和自定义数字格式相似度极高,多数时候二者都可以任意择用。要说二者的区别,大致有如下几点:

1.在自定义数字格式中,星号(*)表示重复某个字符直到填满单元格;而在TEXT中无法实现这一功能,这一功能需要REPT函数才能实现。

2.在自定义数字格式中,它为我们提供了八种不同的颜色可供使用,而在TEXT函数中同样无法使用这些颜色,但我们可以使用条件格式来辅助TEXT函数完成该功能。

3.自定义数字格式仅改变数字的显示格式,不改变数字的数值本质,这些被“易容”了的数字一样可以进行加减乘除求和平均等汇总计算;而TEXT函数则更“高明”一些,不仅“易容”,而且易质,经过TEXT函数处理后的数字,有些不能再参与运算,它们经过质变,已“表里如一”。

4.自定义数字格式可以在原数据所在单元格生效,而TEXT函数必须在新的单元格内设置,前者更为简洁,后者更具灵活性!

看完TEXT函数和自定义数字格式的区别后,让我们一起来通过几个例子看下千面鬼才——TEXT函数的绝代风华。

下面开始分享excel文本函数TEXT实战公式技巧。

基本用法

基本语句=TEXT(数值,文本格式)

用法说明:TEXT函数可通过格式代码对数字应用格式,进而更改数字的显示方式。其中,参数①是我们想要改变格式的数值,它可以是文本,也可以是数字;而参数②就是我们想要参数①最终变成的格式,它的作用原理和自定义数字格式一致,基本用法共分为四个分段,用英文分号(;)区分隔开:

[正数的格式];[负数的格式];[0的格式];[文本的格式]

具体用法,我们会在下方案例中逐一说明。

实战案例

案例1:固定数字位数

以发票号码为例,发票号码均为8位数,但有时系统导出来的发票号码将其前面的0省略了,遇到这种情况时,如何将0批量自动补齐呢?只需输入=TEXT(A4,"00000000")

用法说明:参数②为“00000000”,省略了负数、零值和文本的格式,这表示该格式对所有数值适用,但对文本不适用。此处的0是占位符,表示该数位如有有效数值,则取有效数值,否则以0填充该数位。以A4单元格为例,个位到十万位都有有效数值,所以这部分数值保持不变;前两位数没有有效数值,则用0填充,于是550277就变成了00550277,这就是TEXT函数的作用。

案例2:提取出生日期

如何从身份证号码中提取出生日期并按日期格式填写?这个问题的解决方案有很多,TEXT+MID函数的组合公式就是其中一种。

用法说明:MID(A2,7,8)用于提取18位身份证号码中出生日期的8位字符串,而TEXT函数负责将8位数的出生日期字符串按0000-00-00的格式排列填写。这里需要着重说明的是,由于MID函数提取出来的日期是一个字符串,而非真正的日期,所以,不能使用"yyyy-mm-dd"来设置格式。只有当我们需要转变的是真正的日期的显示格式时,才可以使用y(年)、m(月)、d(天)这三个字符的组合来实现。学习更多技巧,请收藏部落窝教育excel图文教程

案例3:简单的条件判断

在某种程度上,TEXT函数可以作为三种条件结果判断的加强版IF函数来使用,将满足条件的数值转化为指定的格式。以判断区域公司盈亏情况为例,利润为正则显示为盈,负数则为亏,0则显示为平,我们只需要使用如下公式即可:=TEXT(B2,"[>0]盈;[<0]亏;平")

公式说明:数字格式支持两次条件判断,即[条件1]格式1;[条件2]格式2;格式3,参数②"[>0]盈;[<0]亏;平"就是这种用法的一个实例。它对B列数值先进行条件1判断,如果大于0,则显示“盈”,如不大于0,则进行条件2判断,如果小于0,则显示“亏”;如前两个条件都不满足,则显示为“平”。

案例4:计算时间间隔

如何计算上班时长或者加班时长,一直是HR小哥哥小姐姐头痛的问题,不过还好,我们有TEXT函数,什么时间间隔,什么去除尾数,统统搞定!

用法说明:参数②"[h]"表示将数值转化为以1/24为一个单位的小时数,且只取整数位。这里的h即为hour小时,同理也可以写成分钟m或者秒钟s,分别计算相隔的分钟和秒钟。

想知道更多计算时长的方法,还可以查看往期教程《996和955到底差了多少小时,你会算吗?》

案例5:阿拉伯数字与中文数字互换

中文数字和阿拉伯数字如何互换的问题,让很多花瓣头痛不已。解决方案就是将TEXT的第二参数设置为"[DBnum1]"即可,"[DBnum1]"可以将阿拉伯数字转化为中文小写数字,但仅限整数。

公式1:

=TEXT(A2,"[DBnum1]")

公式2:

{=MATCH(A2,TEXT(ROW($1:$9999),"[DBnum1]"),)}

公式说明:公式1极易理解,它通过"[DBnum1]"将阿拉伯数字转化为中文小写数字;公式2比较生涩,它是将1-9999的阿拉伯数字逐一转变成中文小写数字,形成一个用中文小写数字表示的从一到九千九百九十九的数组,最后使用MATCH来实现精确匹配,通过A列中文数字在该中文数组中的位置值来确定中文数字对应的阿拉伯数字。所以说,公式2的本质和公式1是一样的,实质上都是利用"[DBnum1]"来实现的,这一点着实巧妙!

提问:大家知道如何将阿拉伯数字转化成中文大写的数字吗?

不知道的可以看下这篇文章哦~《如果函数有职业,TEXT绝对是变装女皇!》

THE END

TEXT函数在联合其他函数使用时也有很出色的表现,生成诸如月度财务简报、流程数据披露、个人绩效介绍等有序固定文字模板,结合数据有效性,我们可以很轻易地在Excel中生成我们需要的文字+数字的复杂段落。这些用法通常需要小花瓣们自行根据实际工作情况设置公式,小花就不在此处详解了,上述五个例子极具代表性,相信一定有助于加深各位小花瓣们对TEXT函数的理解和掌握!学习更多技巧,请收藏部落窝教育excel图文教程

****部落窝教育-excel 文本函数TEXT 应用技巧****

原创:小花/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育

(0)

相关推荐

  • Excel常用函数之TEXT函数

    在说TEXT函数之前,请先回忆一下Excel自定义单元格公式的知识,如果没有印象,请先不要看这个函数,去复习一下自定义单元格格式的内容先.如果有印象,再继续往下看.今天要学习的TEXT函数的功能是将数 ...

  • Excel竟然可以这样“到期提醒”,太方便了

    如图所示,如何在Excel中设置到期提醒? 首先我们先来看看具体的公式,在单元格输入公式=IFERROR(IF(DATEDIF(TODAY(),C2,'D')<=10,TEXT(DATEDIF( ...

  • 用了十几年Excel,这3个函数我居然才知道,不要太好用

    私信回复关键词[工具],获取Excel高效小工具合集! 让你的Excel效率开挂~ 大家好,我是最近在研究函数的小E~ 有人问我: Excel 中有没有大部分人都不知道的隐藏功能呢? 我:隐藏功能?? ...

  • 用了十几年Excel,这个隐藏函数我居然才知道,不要太好用!

    哈喽~大家好!我是一只正在疯狂赶稿的可爱书~ 说起计算日期间隔,可是愁坏了不少职场人士. 比如,需要计算两个日期之间相差多少年,或多少月,或多少天. 听说我的好同事昨天对着这份数据算了一个晚上,到最后 ...

  • Excel小教程三十:IF和AND函数嵌套应用案例

    我们在实际工作中,往往都是多个Excel函数嵌套使用来解决实际问题.其中IF和AND这些逻辑函数也是经常结合使用,下面我们一起来看看if和and函数的使用方法和行业运用案例. 下面的截图,需要解决的问 ...

  • Excel查找函数如何精确匹配查找

    精确匹配查找是可以通过Excel函数中的vlookup函数进行运算,下面来实践演示一下具体的操作方法吧! 1.打开表格,要通过实习生编号查找到姓名. 2.在表格中输入vlookup函数. 3.在公式中 ...

  • excel查找函数失效视频:查找错误结果数据格式不匹配转换方法

    excel查找函数失效视频|excel查找错误结果视频|excel数据格式不匹配视频|excel格式转换方法视频 本视频教程由部落窝教育分享.

  • excel查找函数原理视频:vlookup运算原理查找区域定义方式

    excel查找函数原理视频|vlookup运算原理视频|vlookup查找方式视频|excel定义查找区域视频 本视频教程由部落窝教育分享.

  • 比Vlookup好用十倍,超级查找函数Clookup来了!

    Vlookup在兰色教程中总是被虐惨的那个函数,先后被Lookup.Xlookup.filer等函数秒杀,谁让它有这么多缺陷呢?今天介绍的是一个超级查找函数(非VBA编写),集所有查找函数优点于一身, ...

  • 95%的人都不知道的两个Excel冷门函数,真香!

    每天一点小技能 职场打怪不得怂 编按:Excel中有几百个函数,我们不可避免的会不知道很多函数,它们或许很实用却没有被我们知道的机会.下面,小E要讲的两个冷门函数,就是这样的宝藏!巧妙应用它们,可以不 ...

  • Excel日期函数20关

    今天我们来对日期相关函数做一个小结,大部分我们都已经写过文章,所以我们就采用快问快答的方式来快速过一下!看看你目前学会了几个,能闯几关,记得自己先思考一下,看是否能写出!最后一个有亿点点难度! 问题1 ...