文本函数中的战斗机万金油 Excel Text函数基本与组合用法

Excel中文本函数参数较少，易学易用，比如Left、Right、Mid函数等，但是有一个非常强大的文本函数，就是因为参数少加上看起来很简单被我们低估了。

它就是函数中的战斗机、函数中的“万金油”Text函数。

1.Text函数的基本用法

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

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

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

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

在B2单元格中输入公式=--TEXT(A2,"#-00-00")

将B列单元格格式更改为日期格式，即可完成操作。

这里需要注意的是，直接修改A列的单元格格式为日期格式或者直接转化为数字均是无法成为规范的日期格式的。

在这种情况下，Text的作用就凸显出现了。

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

在上周分享的文章《10个小技巧，数据输入效率提升10倍》中，我们知道身份证号码是文本格式的，要想从文本格式中提取数字信息需要利用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(数据,"正;负;零;文本")

不管是15位身份证号码还是18位身份证号码，出生日期都是从第7位开始的，区别是15位少了“19”，例如B4的出生日期是“690626”，即1969年06月26日。

公式=--TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00")

公式解析：(LEN(B2)=15)*19，如果身份证号码为15位，则结果为1*19=19，如果身份证号码不是15位，则结果为0*19=0。

MID(B2,7,6+(LEN(B2)=18)*2)从身份证号码的第7位开始取值，如果为18位，那么截取的位数就=6+1*2=8，如果为15位，那么截取的位数就是6+0*2=6。刚好实现了18位截取8位、15位截取6位的目的。

15位最后的结果：19与截取的6位合并，例如A4单元格，19690626。

18位最后的结果：0与截取的8位合并，例如A2单元格，019870623。

TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00")将字符串19690626转变为1969-06-26这样的格式，再通过--负负得正的运算将文本字符转换为日期字符。

最后再通过设置单元格格式，将数字格式设置为日期，最后年月日就计算出来了。

3.格式化员工工号

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

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

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

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

4.阿拉伯数字转换为中文大小写数字

在实际工作中，我们可能会遇到需要把数字表示的年、月、日转换为中文的年、月、日，例如2017年转换为“二〇一七年”或“贰零壹柒年”。

公式解析：[DBNum1]是中文小写格式，[DBNum2]是中文大写格式。

[DBNum1]后面加0表示让数字逐位显示。

加0与不加0区别如下：

还有一种是大小写金额的转换，在工作中也非常常见。

公式=TEXT(A11*100,"[DBNum2]0亿0仟0佰0拾0万0仟0佰0拾0元0角0分")

5.累计加班时间

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

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

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

6.自动判断盈亏、评定等级

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

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

可以用=text(数据,"正;负;零;文本")判断盈亏，公式=TEXT(B2,"增加￥0元;减少￥0元;持平;缺岗")

7.与SUM函数组合使用

现有公司3月份和4月份每天的销售额，需要计算3月和4月份的销售总额。

在E3单元格输入公式=SUM((TEXT($A$2:$A$21,"YYYYMM")=TEXT(D3,"YYYYMM"))*$B$2:$B$21)

同时按下ctrl+shift+enter键，得到3月份的销售总额。

公式解析：TEXT($A$2:$A$21,"YYYYMM")是将日期单元格转化为“201703”和“201704”的形式。TEXT(D3,"YYYYMM")也是将2017年3月转换为“201703”。

如果TEXT($A$2:$A$21,"YYYYMM")和TEXT(D3,"YYYYMM")相等的话（同时为201703），则返回值为1，否则返回值为0。

SUM((TEXT($A$2:$A$21,"YYYYMM")=TEXT(D3,"YYYYMM"))*$B$2:$B$21)是对所有返回值为1所对应的B列销售额进行求和。

今天的教程就到这里了

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。