菜鸟记238-如何处理不规则的时间记录？谈一份学时统计表的变形记 / 四六文摘

关键词：EXCEL2016；替换；MID函数；IF函数；分列；操作难度***

某天，小菜在统计某项学生赛事学时统计时候，很开森郁闷地发现汇总上来的数据是这样的：

图1：源数据示意图

仔细分析，时间记录方式多种多样，日期表示不规范，有空格，有的分行是Alt+Enter人工分行形成的，时间跨度也存在中英文分号分隔符混用的情况……

现在难题来了，咱们应该怎么操作才能快速地完成学时的统计呢？

步骤1：替换

首先咱们利用替换，分别将中文分号替换为英文分号、替换不规范的时间连接符、去除空格、去除人工换行符；

图2：替换中文分号

图3：替换连接符

需要特别提示的咱们替换人工换行符，定位查找内容处，按住Alt键，依次点击小键盘的数字键1和0，即可以实现人工换行符的录入；

请看会动的图：

最后咱们得到的处理结果是这样的：

图4：替换结果

比较崩溃的是记录时间还不是完全一个标准，如果直接分列或用MID函数提取会出现多于24小时计数的情况；

这时候需要咱们利用已经学过的函数来提取相应的字符进行变形：

在C2单元格输入公式：=RIGHT(B2,11)

在D2单元格输入公式：=IF(MID(C2,1,2)*1>=16,"0"&MID(C2,2,1)&MID(C2,3,11),C2)

分别填充后得到：

图5：提取时间

小菜划重点：为什么要判断大于等于16呢？这是经过分析开始时间规律是早上8:30前面没有标记0，如果直接自右向左提取11位字符，会出现18:30开始错误数据。

对于得到的标准统一的时间表示，咱们可以用分列或MID函数来提取出开始时间和结束时间：

图6：提取开始和结束时间

以MID函数为例为您提供一个会动的图：

接下来咱们直接用结束时间减去开始时间得到：

图7：统计分钟时长

最后咱们用分钟时长除以45就可以得到核算的学时：

图8：核算学时

小菜划重点：眼尖的朋友肯定看到了有的学时出现了小数，这个就需要具体问题具体分析了，需要您人工判断一下这种不在标准值范围的学时应该如何认定，比如70分钟是1学时还是1.5学时，这个还是需要人工判断一下为好。

当然如果有确定的规则，我们可以让软件帮咱们判断，减少重复性劳动。

比如领导说规则是1.1-1.3算1学时，1.4-1.7算1.5学时，1.8-1.9算2学时，咱们可以输入公式：

=IF((INT(H2*10)-INT(H2)*10)=0,INT(H2),IF((INT(H2*10)-INT(H2)*10)<4,INT(H2),IF((INT(H2*10)-INT(H2)*10)<8,INT(H2)+0.5,INT(H2)+1)))

图9：公式提取结果

看上去是不是晕了，原理很简单，就是提取第一位小数判断，公式写得有点长，小菜还需要再琢磨怎么用简便的方法来比对……

其实，小菜零零散散说了那么多，就是想再次强调一个观点：机器不是万能，人学习才有进步的可能！

今天的内容就是这些，祝您工作生活都有一个好心情，希望小菜的分享能帮到您或有所帮助。

休息一下，休息一下