用活Text函数 Excel技巧:Text函数数据变身更高效

工作中经常需要对数据进行排序、筛选、求和等操作 , 但有些数据显示不规范 , 不符合要求 , 会影响上述操作的顺利进行 。活学活用Text函数 , 可以按照实际需要快速地对数据进行格式变换 , 获得符合自己需要的显示形式 。
1 。日期格式变身
Excel中默认使用类似2020-07-01、2020/07/01的日期格式 , 但这种格式在进行筛选操作时却可能会失效 。比如下表是某商品7月销售的统计 , 现在需要通过日期筛选找出当月所有周六、周日销售的明细(图1) 。
由于默认的日期格式并不会显示星期字样 , 我们无法直接按照日期进行周六、周日的筛选 , 这时就可以借助于Text函数进行日期格式的转换 。在A列后插入一个新列 , 接着在B2中输入公式“=TEXT(A3 , ”aaaa”)”(表示将A3中的文本转换为“aaaa”格式 , 即Excel默认的中文星期格式) , 下拉填充即可显示星期 。如果要转换为英文星期形式 , 则可以输入“=TEXT(A3 , ”dddd”)” 。现在选中B列点击“数据→筛选” , 在打开的筛选窗口中勾选“星期六”、“星期天”选项 , 然后在C35中输入公式“=SUBTOTAL(109 , C3:C34)”(109表示只对筛选后的数据求和 , 忽略隐藏数据) , 这样即可求出周末销售之和了(图2) 。
使用Text函数还可以将非标准日期数据转换为标准格式 。比如很多朋友会使用类似“20200701”、“20200702”的格式输入日期 , 这种不规范的日期在Excel中会被识别为数字 , 无法参与正常的运算 。现在只要在原来的数据列后插入一列 , 在B2中输入公式“=TEXT(A3 , ”0年00月00日”)” , 下拉即可变为类似“2020年07月01日”的标准形式 , 之后同样可以使用上述函数显示星期等信息(图3) 。
公式解释:
0是占位符 , 使用年月日的形式将8位数字分成三段 。日期格式是按照从右到左依次划分 , 最右边2位为日 , 中间2位为月 , 最左边4位为年 。
【用活Text函数 Excel技巧:Text函数数据变身更高效】如果要将标准日期如“2020年07月01日”转换为“20200701”的形式(比如很多公司的数据库系统的日期使用的就是这样的8位数字格式 , 这样要将Excel生成的数据导入数据库就得更改格式) 。同上 , 在B2中输入公式“=TEXT(A2 , ”yyyymmdd”)”(表示将A2的日期按照年yyyy、月mm、日dd的形式进行组合) , 公式下拉后就可以变为8位数字的格式(图4) 。
小提示:如果要将转换后的格式变为单元格实际显示的数字如20200701(而不是公式) , 可以选中转换后的数字进行复制 , 然后选择“粘贴→选择性粘贴→数值”即可 。
2 。时间格式变身
在考勤统计中 , 常常需要对时间进行运算 , 比如要计算员工加班时间 , 需要按小时进行统计 。但Excel默认的时间统计超过24小时会自动进位为天 , 这样统计的数据是不准确的 , 因为加班费都是按总计小时乘以单价来计算的(图5) 。
借助于Text函数可以将天转为小时 。定位到C2并输入公式“=TEXT(SUM(B2:B13) , ”[h]:mm:ss”)”(表示对求和的数值按照时分秒的格式显示) , 然后下拉 , 这样就不会按天进位 , 直接显示为总的加班时间了(图6) 。
3 。数字格式变身
财务人员经常要对数字进行大小写的转换 , 如在金额总计里要填写大写金额 。如果要将普通数字更改为大写 , 同样可以借助Text函数进行转换 。比如上图中需要计算总的加班费(假设每小时加班费为4 。33元) , 并且使用大写数字进行表示 , 只需定位到B16并输入公式“=B15*4 。33*24”(因为B15是时间格式 , 它和24相乘后会显示为实际小时数字) , 这样得到加班费为225 。16元 。继续在B17输入公式“=TEXT(B16*100 , ”[DBnum2]0佰0拾0圆0角0分”)”(因为财务上大写数字没有小数点 , 这里B16*100后就变为整数 , [DBnum2]表示使用百圆角分的形式定义数据) , 这样就可以按照财务圆角分的要求显示加班费了(图7) 。
4 。运算结果转换为文本显示
在一些统计汇报中 , 文字说明可以更直观地表示最终结果 , 比如下表中直接显示盈利XX元、亏损XX元或保本 。这类文本的显示同样可以使用Text函数进行转换 , 在C2中输入公式“=TEXT(B2-A2 , ”盈利0 。00元;亏损-0 。00元;保本”)”(这里将B2-A2的运算结果以盈利、亏损、保本的形式显示) , 下拉公式 , 即可显示直观的结果(图8) 。

    推荐阅读