evaluate函数使用无效_千面鬼才TEXT函数

‍

    要说在excel中最特别的文本函数，那必定非TEXT函数莫属，外界给它的称号不计其数“文本之王”“整容大师”“千面鬼才”等等，由此可看出对它的喜爱。

下面小花就和大家分享5种最实用的TEXT函数用法，赶紧来看看吧！

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

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

 

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

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

 

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

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

1

基本用法

基本语句 ：=TEXT(数值，文本格式)   用法说明 ：TEXT函数可通过格式代码对数字应用格式，进而更改数字的显示方式。其中，参数①是我们想要改变格式的数值，它可以是文本，也可以是数字；而参数②就是我们想要参数①最终变成的格式，它的作用原理和自定义数字格式一致，基本用法共分为四个分段，用英文分号(;)区分隔开：   [正数的格式];[负数的格式];[0的格式];[文本的格式]   具体用法，我们会在下方案例中逐一说明。

2

实战案例

案例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(天)这三个字符的组合来实现。     案例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]"来实现的，这一点着实巧妙！

   THE END    

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

《告别无效学习：Excel效率达人养成记》

作者：许万鸿

毕业于四川大学；公众号【小花学Excel】版主；

组织过多次Excel技能线上线下培训，

具有丰富的Excel实操实战经验；

长期从事财务数据分析汇报，报表设计等工作。

识别下方二维码可快速购买正版图书

《告别无效学习：Excel效率达人养成记》

识别下方二维码进Excel效率达人交流群

如果对你有帮助，点下“在看”呦