您所在的位置:首页 > 学习教程 > office教程 > 正文

大神级函数Text,有太多你不知道的秘密

来源:新锦程教育 日期:2018/7/7 9:48:43 人气:1 标签:

Excel中文本函数参数较少,但是有一个非常强大的文本函数,就是因为参数少加上看起来很简单被我们低估了,其实text函数有太多不为人知的神奇之处。接下来宁波新锦程电脑培训学校老师给大家讲解text函数的用法。


(1)Text函数的基本用法

text函数的语法:text(value,format_text),其中:Value为数字值,Format_text 为设置单元格格式中自己所要选用的文本格式。

简单来说,Text函数最主要的功能就是将数值转换成文本。

因此需要注意,Text函数产生的结果一定要文本,是无法参与计算的。

举例来说:在工作中我们经常可以看到一些文本格式的日期,在Excel中这样的日期是不规范的,也不能直接作为日期参与计算。因此为了方便统计与分析,我们需要将文本日期转换为真正的日期。


(2)提取身份证号码中的性别和出生日期

在上周分享的文章(http://mp.weixin.qq.com/s/bB768aMEGHbOHK5fCZi8EA)中,我们知道身份证号码是文本格式的,要想从文本格式中提取数字信息需要利用Text函数进行转换。

在15位身份证号码中,最后一位表示性别,男性为奇数,女性为偶数。18位身份证号码中,倒数第二位表示性别,同样是男性为奇数,女性为偶数。

公式=TEXT(MOD(MID(B2,15,3),2),"男;;女")

公式解析:MID(B2,15,3)表示提取15身份证号码中的最后一位,提取18位身份证号码中的第15、16、17位;MOD(MID(B2,15,3),2)判断奇偶;TEXT(MOD(MID(B2,15,3),2),"男;;女")余数为正数显示“男”,余数为负数显示空,余数为0则显示“女”。

注意:这里Text表示四种数据类型,常用方法为=text(数据,"正;负;零;文本")


(3)格式化员工工号

由于公司重组合并,需要对员工工号进行升级,不足8位的需要在工号前补0升成8位,升级后的工号前还需要有其所属的部门。

公式=A2&TEXT(B2,"00000000")

公式解析:TEXT(B2,"00000000")将原始工号升级为8位数的工号。

A2&TEXT(B2,"00000000")将部门名称与8位数工号合并。


(4)累计加班时间

下图为某员工上半月每天的加班时长,现需要计算加班总时长。如果使用公式=SUM(B2:B16)得出来的结果为18:35(实际为42:35),显然是错误的。

这是因为单元格自动把超过24小时的时间进位到天了,而结果只显示小于24小时的时间。

所以,我们需要借助Text公式=TEXT(SUM(B2:B16),"[h]:mm"),阻止单元格自动把超过24小时的时间进位到天,只以时间累计。


(5)自动判断盈亏、评定等级

一提到盈亏、等级,可能很多首先想到的是IF函数,其次是LOOKUP函数,其实Text函数也可以判断盈亏、评定等级。

公式=TEXT(B2,"[>90]!优秀;[<80]!不及格;及格")