Excel中text函数的用法以及六大text函数的使用案例
内容提要：Excel中text函数的用法、text函数日期格式、text函数转换文本、text函数转换时间、补零等等在本篇Excel教程中都有讲解.
在Excel的函数里有一个神奇的函数，这个函数就是TEXT函数。函数的结构非常简单，只需要两个参数：TEXT（数据，格式代码）。今天为大家分享excel中text函数的用法以及这个函数的六个妙用。
一、将日期变为星期
有这样的一个销售明细表，现在需要将周六周日的数据筛选出来做分析：
文章图片
可能有些童鞋想到通过自定义格式将日期变成星期然后筛选，这样是很简单，对这一列设置自定义格式，代码为aaaa：
文章图片
确定后日期就显示为为星期了，可是当你筛选的时候发现并不能达到想要的效果：
文章图片
因为设置自定义格式只是改变了数据的显示效果，实际内容还是日期，这时候就需要TEXT函数出马了。
在日期后面插入一列，标题为星期，然后输入公式=TEXT(A3,"aaaa")，就可以按星期筛选了：
文章图片
这个公式里的代码”aaaa”就是星期的效果，大家不妨自己再试试代码”aaa”、 ”ddd”和”dddd”的效果是什么，是不是很有意思呢？
再来看Text函数第二个妙用：
二、格式化员工工号
在公司的员工名单中，原来的工号都是按部门排列的，每个部门的工号都是从1开始，现在要求工号前面带上部门名称，同时工号统一为三位数，不足三位的前面补0，此时我们就可以使用TEXT函数来得到新的工号，B2单元格公式为：
=C2&TEXT(A2,"000")
文章图片
这个地方用到了代码”000”，0在TEXT中是数字占位符，一个0就代表一个数位。这个方法可以用在很多需要前置加0的地方，这也是TEXT最常用的代码之一。
接下来要看的这个妙用就更加奇妙了，使用TEXT来完成IF函数的工作，是什么问题呢？一起来看看：
三、设置盈亏平衡判断
根据收入和支出数据设置盈亏平衡判断。收入大于支出设置为盈利，收入小于支出设置为亏本，收入等于支出设置为平衡。通常这类问题我们会用IF函数来处理，其实TEXT也有这个功能，公式为：=TEXT(A2-B2,"盈利0.00万;
亏损0.00万;
平衡;
")
文章图片
这里的格式代码就与之前的例子不同了，利用的是分段设置的方法，TEXT函数可以将数据分为正数、负数、零和文本四种类型来分别指定显示方式，类型之间使用分号隔开，标准格式为"正;
负;
零;
文本"，在本例中A2-B2得到的数字会出现正数、负数和零，不会有文本的类型，按照对应的类型进行设置就是"盈利0.00万;
亏损0.00万;
平衡;
"，文本的位置留空即可。
四、日期的特殊处理
有时候我们需要根据表格的数据来编辑一些信息，例如：
文章图片
可能有些朋友会说，直接用&连起来啊，如果直接连起来的话，结果是这样的：
文章图片
日期变成了数字，因此要想按照实际需求显示的话，还得TEXT出马，公式修改为：=TEXT(A2,"yyyy年m月d日")&B2&"销售额为:"&C2
文章图片
这里还是用了TEXT函数来强制显示日期，甚至可以用TEXT函数将日期也变成汉字的方式，公式修改为：
=TEXT(A2,"[DBNum1]yyyy年m月d日")&B2&"销售额为:"&C2
文章图片
在格式代码前增加了[DBNum1]，就可以将阿拉伯数字变成中文数字，这里的1还可以用2、3、4来代替，自己试试都是什么效果吧。
五、对时间进行求和
有时候会遇到对时间求和的问题，例如在计算加班时间合计的时候，直接用sum函数得到的结果显然是不对的：
文章图片
因为时间在累计超过24小时的时候，会进位到天，并不是直接在小时数累加，这时候又该TEXT函数大显身手了，只需要在SUM的外面加个TEXT，公式修改为：=TEXT(SUM(C2:C20),"[h]:mm:ss")
文章图片
求和结果正确，因为代码[h]就是将数据锁定到小时这一级，不会向上进位了。
最后再来看看TEXT在遇到身份证号码的时候，又会发生什么：
六、提取身份证号码中的性别和出生日期
如何从身份证号码中提取出生日期，这是很多人都在问的一个问题，借助TEXT函数可以很容易的实现，C2单元格公式为：
=--TEXT(MID(B2,7,8),"0-00-00")
文章图片
首先使用MID函数从身份证号码中的第7位开始提取8个数字出来，这部分就是出生日期，再用TEXT将这个8位数字以"0-00-00"的格式显示，此时得到结果只是表面像日期，并不是真正的日期格式，还需要在TEXT函数前加上负负得正的运算，将文本字符转换为日期字符，最后设置单元格格式。
在身份证号码中，除了含有出生日期之外，还能判断性别，倒数第二位表示性别，男性为奇数，女性为偶数。
根据这个规则，公式可以这样写：=TEXT(MOD(MID(B2,17,1),2),"男;
;
女")
文章图片
首先用MID函数提取18位身份证号码中的第17位，MID(B2,17,1)；
再用MOD函数判断奇偶，简单来说一下MOD函数，这个函数有两个参数，格式为：MOD（被除数，除数），而结果是余数，本例中被除数是身份证号码的第17位数字，除数是2，当被除数是偶数时，余数为零，反之余数为1，利用TEXT的四段分类显示规则"正;
负;
零;
文本"，将正数定义为“男”，零定义为“女”，就实现了提取性别的目的。
今天的教程就到这里咯，大家到qq群：488925627下载课件进行练习才会理解更深刻哟！
需要学习更多的excel教程，请关注部落窝教育Excel微信公众号，每天和小编一起学原创excel教程。
文章图片
excel教程相关推荐阅读：Excel各年度同比增长率和环比增长怎么计算的方法和案例
日期怎么转成星期、数字怎么添加千分位、位数不同的编号怎么统一成相同的编号、数字怎么自动添加备注……
这些乱七八糟的问题都和Text函数有关，没有想到吧？
Text函数可以说是Excel函数中的百变王子、化妆大师，它可以轻轻松松地将一个数值转换成另外的样子。让我们一起来看一看Text的变化手段吧。
Text函数的基本语法
公式：Text(单元格,转换后的格式)作用：将指定单元格的内容转换成指定的格式。
日期和星期相关的
文章图片
看起来好像表格中的公式挺多的，公式的作用主要是可以提取一个日期中的年、月、日、星期。大致的规律可以归纳成下面两点：
年、月、日的英文单词是“year”、“month”、“day”，所以和年有关的用字母“yyyy”和“yy”；和月有关的用字母“m”，1-3个“m”，分别可以得到的月份的格式不同；和天数有关的用字母“d”。和星期有关的可以用“dd”、“ddd”、“aaa”和“aaaa”，字母短的将得到星期缩写，长的得到完整的星期名称。时间相关的
文章图片
和上面的日期类似。小时、分钟、秒的英文单词分别是“hour”、"minute”、“second”，所以可以用单词首字母来提取相应的数值。
至于是一个字母还是两个字母，就看你的需要了。例如“2018/1/4 6:2:00”，用“hh”就会得到“06”，用“h”就会得到“6”，也就是说两个字母式，Excel会根据需要自动在前面补零。
注意，不要用“Text(时间,"mm")”提取分钟，因为提取月份用的也是“mm”，这样在提取分钟时，就会出错。
千分位和小数位数相关的
文章图片
看晕了吧？又是“0”又是“#”的。
其实，很容易。首先，“0”和“#”的区别在于，一个会在需要的时候补“0”，一个不会，如前两行所示。
其次，加了一个逗号“,”可以将数字转换成英文形式，给数字每隔3位添加一个分隔符。
数值转换相关的
文章图片
和上一个格式有点类似，主要用到的也是0”和“#”，区别也是相同的。
想在数值前面添加货币符号，就可以用第一个公式。想转换成百分比，就直接在格式后添加“%”，保留几位小数，就看text函数中小数点后面有几位。
手机号、银行卡号分段显示时，用0”和“#”写出分段后的效果即可达到目的。
条件格式的
文章图片
格式1：=TEXT(A3,"A3为正数时显示的文字;
A3为负数时显示的文字;
A3为零时显示的文字")格式2：=TEXT(A7,"[条件1]A7满足条件1时显示的文字;
[条件2]A7满足条件2时显示的文字;
A7不满足条件1和2时显示的文字")
这个公式就类似于IF，但比IF要简洁一些。
补充
Text函数上面说的这些格式，是可以一起使用的。例如，可以用“=TEXT(B3,"yyyy-mm-dd AAAA 上午/下午")”将B3单元格的日期转换成“年-月-日 星期 上午/下午”的格式。
【Excel中text函数的用法以及六大text函数的使用案例】最后，再回到文章开头的问题“位数不同的编号怎么统一成相同的编号，例如你输入的是“1、33、445”怎么快速统一成“00001、00033、00445”？
推荐阅读
- 热闹中的孤独
- Shell-Bash变量与运算符
- JS中的各种宽高度定义及其应用
- 2021-02-17|2021-02-17 小儿按摩膻中穴-舒缓咳嗽
- 深入理解Go之generate
- 异地恋中，逐渐适应一个人到底意味着什么（）
- 我眼中的佛系经纪人
- 《魔法科高中的劣等生》第26卷（Invasion篇）发售
- “成长”读书社群招募
- 2020-04-07vue中Axios的封装和API接口的管理