各位小伙伴大家好,我是Excel归故乡。TEXT函数可谓是一个很神奇的函数,说简单也非常简单,但是也有大神将其用的出神入化。本期就先给大家介绍一下TEXT函数比较简单实用的7个经典用法。
1、将日期变为星期几的形式。
我们在实际工作中,经常会遇到计算周末加班时长、周末加班补贴等情况,但是我们如何根据日期快速地判断出是星期几呢?这里就可以使用到TEXT函数来将日期格式进行转换。这里用到的格式代码是”aaaa”,对应的公式就是=TEXT(A2,”aaaa”)。下面看一下实际效果:
并且这里我们使用TEXT转换后内容是真实存在的,并不是像自定义格式那样仅仅改变了外显格式,所以我们在使用条件计数,条件求和等公式的时候,可以将星期几直接作为条件。(彩蛋:将aaaa改成aaa看看是什么效果~)
2、字符链接日期变成数字的处理办法
不知道大家在做报表的时候有没有需要在标题嵌入日期的情况,比如说“Excel归故乡2021年10月20日企划日报”,这个日期我们不想每天修改,所以就采用today函数来自动根据当天日期生成,当我们直接用连字符来讲文字和日期相连的时候却发现,日期被打回原形,变成了数字(看过我视频的小伙伴应该都知道日期的真值是数字),这该如何是好呢?此时我们可以用TEXT函数来将日期格式进行固定,然后再链接就可以了。这里用到的日期代码是”yyyy年m月d日”,对应公式为=” Excel归故乡”&TEXT(A2, ”yyyy年m月d日”)&”企划日报”,前后连接的文字可以根据自己的实际情况来设置。我们来看一下不用TEXT函数直接连接和使用TEXT函数连接的效果差异:
直接连接:
使用TEXT函数进行格式固定后连接:
(彩蛋:试试将年月日的日期格式改动一下,看看有什么意外发现)
3、从身份证提取出生日期(最短公式法)
从身份证提取出生日期,应该是每位HR逃脱不掉的问题了,网上的方法也是五花八门,这里给大家介绍一个最短的公式,一步到位。
=--TEXT(MID(A2,7,8),'0000-00-00')
首先是mid(A2,7,8)函数将身份证中代表出生年月日的8位数字给提取出来,例如20200704,但是只是一串数字,并不是我们需要的日期格式,说到格式那就可以用TEXT函数来进行格式的设置,简单来说就是在年月日之间加上代表日期的短横线,我们用0作为数字占位符,在对应的地方用短横线来隔开,进而得到这个公式=TEXT(MID(A2,7,8),'0000-00-00')。经过这一步之后我们可以得到日期格式的日期,但是这时候的日期是文本格式,不能正常参与计算(感兴趣的小伙伴,可以进入筛选页面,如果是正常日期格式的话筛选栏可以看到年月日的自动划分),如下图所示:
如果需要实现真正的日期格式,其实很简单,我们只需要在TEXT函数前面加上两个减号,或者乘以1,原理和将文本格式的数字转为数值是一样的。最后需要注意的是将单元格设置为日期格式,最终公式=--TEXT(MID(A2,7,8),'0000-00-00')
(彩蛋:试试两个减号改成DATEVALUE,效果一样)
4、批量统一工号位数(用0在前面将不足的位数补齐)
我们在编写工号的时候的,通常为了满足系统需求,或者是为了美观,要求工号的位数必须是一样的,比如要求都是6位,并且不足6位的用0在前面补齐,比如000188。我们很多小伙伴采用的是先将单元格设置为文本格式,然后挨个输入工号,这样非常耽误时间,实际上我们也可以用TEXT函数来批量实现这个功能。这里用到的格式代码是占位符0,对应的公式就是=TEXT(A2,”000000”),这里根据需求,统一为多少位工号,就填写多少个0。下面是实际效果:
5、手机号分段显示
手机号根据我们的阅读习惯,通常会被分成三段,如:188-8888-8888,那我们如何批量将未分段的手机号进行分段?其实我们在提取出生日期的案例已经讲过了,可以直接利用TEXT函数来分段,公式为=TEXT(A2,”000-0000-0000”),分隔符的短横线可以换成空格等,这个用法非常简单,就不做过多阐述。
6、取两个时间之间整的小时数
经常有一些HR会问到一个问题,如何计算上班小时数,从上班打卡到下班打卡到底上了多少个小时的班?18:00算下班,后面的时间算加班,那又加了多少个小时的班?这些都是常见问题,有的小伙伴可能看到后束手无策,但其实这个问题TEXT函数就可以解决~首先我们知道,两个时间,用较大的日期减去较小的时间,最后得到的还是一个时间,这个时间就是时差,但是我们只要小时部分,我们就可以用到格式代码”h”,算相差小时数的公式就可以写为=TEXT(B2-A2,”h”),这时候就可以得到两个时间之间相差的小时整数了。效果图:
(彩蛋:如果把h换成代表分钟的m,代表秒钟的s会是什么情况呢?)
7、简单的条件判断(类似基础的if判断)
我们在自定义格式当中讲解过,自定义格式可以用来做基础的if条件判断,其实TEXT函数也可以实现这个功能,但是TEXT函数局限于三段判断,具体的格式代码我们在自定义格式中有详细的讲解。这里的公式可以写为=TEXT(A2,'[>90]优秀↑;[>60]及格;不及格↓'),效果如下:
以上就是TEXT函数的7个简单但是又实用的经典用法。有时候一个函数用到极致的时候,你就是高手,一起学起来吧~
联系客服