用活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位为年。

如果要将标准日期如“2020年07月01日”转换为“20200701”的形式（比如很多公司的数据库系统的日期使用的就是这样的8位数字格式，这样要将Excel生成的数据导入数据库就得更改格式）。同上，在B2中输入公式“=TEXT（A2，"yyyymmdd"）”（表示將A2的日期按照年yyyy、月mm、日dd的形式进行组合），公式下拉后就可以变为8位数字的格式（图4）。

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.运算结果转换为文本显示

在一些统计汇报中，文字说明可以更直观地表示最终结果，比如下表中直接显示盈利××元、亏损××元或保本。这类文本的显示同样可以使用Text函数进行转换，在C2中输入公式“=TEXT（B2-A2，”盈利0.00元：亏损-0.00元：保本”）”（这里将B2-A2的运算结果以盈利、亏损、保本的形式显示），下拉公式，即可显示直观的结果（图8）。