考勤数据计算-Excel

背景

正常情况下，一般公司的考勤打卡机器会自动出考勤报表。但天有不测风雨，某些故障导致报表不能正常出的时候，就只能导出每一天的打卡记录，然后人工去做考勤了。一条条去check肯定是不可能的，所以研究了一下用Excel去做。

源数据

从考勤设备导出的原表如下结构，右侧时间是从左侧复制的，方便数据处理：

分列

对右侧考勤时间以空格进行分列，目的是得到日期的值

排序

以C列为首要依据，A列为次要依据，对数据进行排序

构造F列

C列&D列，以此公式构造F列

思路

至此，每个人的打卡时间，按照每天的从早到晚排列了。我们的问题变成了，如何去掉每个人每天中间的打卡记录。也就是，只保留每天第一次和最后一次的打卡记录。

构造辅助列

• G列计算一下每天共有几次打卡，统计F列即可，=COUNTIF(F:F,F2)
• H列返回这一行的行号，=ROW(C2)
• I列查找该姓名&日期组合第一次出现的行数，=MATCH(F2,F:F,0)
• H列减去I列再+1可以得知这是第几次出现，J列=H2-I2+1
• 值是1则表示是第一次打卡，那么最后一次打卡我们用K列来计算=IF(J2=G2,“last”,“not”)。逻辑也很简单，如果出现的次数=本日总次数，那么就是last
• 最后L列记录结果，方便筛选。=OR(IF(J2=1,1,0),IF(K2=“last”,1,0))。我们要保留的是J列是1的或者K列是last的，满足这俩条件其中一个就是true

最终结果如下：

最终只需要保留筛选L列中为True的数据即可。

总结

本次需求的实现，其实只是运用了一些简单的公式，做了一些组合。虽然其实可以高大上的把这些公式嵌套一下，在一列就搞出来，但我还是拆分成每一步，方便大家理解。

希望今后大家遇到繁琐的Excel数据处理需求，都可以找到方便的批量解决方法。