室内设计培训
平面设计培训
部落窝教育
网站首页 >> Excel教程 >> 文章内容

Excel中text函数的用法以及六大text函数的使用案例

[日期:2018-07-20]   来源:IT部落窝  作者:IT部落窝   阅读:158[字体: ]
内容提要:Excel中text函数的用法、text函数日期格式、text函数转换文本、text函数转换时间、补零等等在本篇Excel教程中都有讲解.

  在Excel的函数里有一个神奇的函数,这个函数就是TEXT函数。函数的结构非常简单,只需要两个参数:TEXT(数据,格式代码)。今天为大家分享excel中text函数的用法以及这个函数的六个妙用。

一、将日期变为星期

  有这样的一个销售明细表,现在需要将周六周日的数据筛选出来做分析:

Excel中text函数的用法

  可能有些童鞋想到通过自定义格式将日期变成星期然后筛选,这样是很简单,对这一列设置自定义格式,代码为aaaa:

text函数日期格式

  确定后日期就显示为为星期了,可是当你筛选的时候发现并不能达到想要的效果:

text函数转换文本

 

  因为设置自定义格式只是改变了数据的显示效果,实际内容还是日期,这时候就需要TEXT函数出马了。

  在日期后面插入一列,标题为星期,然后输入公式=TEXT(A3,"aaaa"),就可以按星期筛选了:

text函数转换时间

 
这个公式里的代码”aaaa”就是星期的效果,大家不妨自己再试试代码”aaa”、 ”ddd”和”dddd”的效果是什么,是不是很有意思呢?

  再来看Text函数第二个妙用:

二、格式化员工工号

  在公司的员工名单中,原来的工号都是按部门排列的,每个部门的工号都是从1开始,现在要求工号前面带上部门名称,同时工号统一为三位数,不足三位的前面补0,此时我们就可以使用TEXT函数来得到新的工号,B2单元格公式为:
=C2&TEXT(A2,"000")

excel text函数 补零

  这个地方用到了代码”000”,0在TEXT中是数字占位符,一个0就代表一个数位。这个方法可以用在很多需要前置加0的地方,这也是TEXT最常用的代码之一。

  接下来要看的这个妙用就更加奇妙了,使用TEXT来完成IF函数的工作,是什么问题呢?一起来看看:

三、设置盈亏平衡判断

  根据收入和支出数据设置盈亏平衡判断。收入大于支出设置为盈利,收入小于支出设置为亏本,收入等于支出设置为平衡。通常这类问题我们会用IF函数来处理,其实TEXT也有这个功能,公式为:=TEXT(A2-B2,"盈利0.00万;亏损0.00万;平衡;")

text函数判断

  这里的格式代码就与之前的例子不同了,利用的是分段设置的方法,TEXT函数可以将数据分为正数、负数、零和文本四种类型来分别指定显示方式,类型之间使用分号隔开,标准格式为"正;负;零;文本",在本例中A2-B2得到的数字会出现正数、负数和零,不会有文本的类型,按照对应的类型进行设置就是"盈利0.00万;亏损0.00万;平衡;",文本的位置留空即可。

四、日期的特殊处理

  有时候我们需要根据表格的数据来编辑一些信息,例如:

Excel中text函数的用法

  可能有些朋友会说,直接用&连起来啊,如果直接连起来的话,结果是这样的:

六大text函数的使用案例

  日期变成了数字,因此要想按照实际需求显示的话,还得TEXT出马,公式修改为:=TEXT(A2,"yyyy年m月d日")&B2&"销售额为:"&C2

<span class=keyword><a href=http://www.ittribalwo.com/article/list_1.html target=_blank>excel教程<a></span>

  这里还是用了TEXT函数来强制显示日期,甚至可以用TEXT函数将日期也变成汉字的方式,公式修改为:
=TEXT(A2,"[DBNum1]yyyy年m月d日")&B2&"销售额为:"&C2

Excel中text函数

  在格式代码前增加了[DBNum1],就可以将阿拉伯数字变成中文数字,这里的1还可以用2、3、4来代替,自己试试都是什么效果吧。

五、对时间进行求和

  有时候会遇到对时间求和的问题,例如在计算加班时间合计的时候,直接用sum函数得到的结果显然是不对的:

text函数对时间进行求和

  因为时间在累计超过24小时的时候,会进位到天,并不是直接在小时数累加,这时候又该TEXT函数大显身手了,只需要在SUM的外面加个TEXT,公式修改为:=TEXT(SUM(C2:C20),"[h]:mm:ss")

Excel时间求和

 求和结果正确,因为代码[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函数

  首先用MID函数提取18位身份证号码中的第17位,MID(B2,17,1);

  再用MOD函数判断奇偶,简单来说一下MOD函数,这个函数有两个参数,格式为:MOD(被除数,除数),而结果是余数,本例中被除数是身份证号码的第17位数字,除数是2,当被除数是偶数时,余数为零,反之余数为1,利用TEXT的四段分类显示规则"正;负;零;文本",将正数定义为“男”,零定义为“女”,就实现了提取性别的目的。

  今天的教程就到这里咯,大家到qq群:488925627下载课件进行练习才会理解更深刻哟!

  需要学习更多的excel教程,请关注部落窝教育Excel微信公众号,每天和小编一起学原创excel教程

Excel微信公众号

  excel教程相关推荐阅读:Excel各年度同比增长率和环比增长怎么计算的方法和案例

IT部落窝PS,CDR,213班 分享到: QQ空间 新浪微博 腾讯微博 人人网
photoshop教程
Photoshop教程
平面设计教程
Photoshop教程