考勤数据整理
利用Excel快速处理汇总刷卡考勤数据(之一)马上又要月底了,又要面临着把从刷卡机里导出的12月的考勤数据进行统计汇总。我们总不能还是花几天时间来手工处理这些考勤数据吧?前天晚上给HR们上课,正好介绍到考勤数据统计分析的一个例子,现在拿出来跟大家分享。实际数据如下图所示,从打卡机导出的数据是一个流水,要求:
1、找出每个人最早打卡时间和最晚打卡时间;
2、判断每个人每天的迟到、早退情况、加班情况。说明:出勤时间标准是8:00-17:30,18点以后为加班时间。这个表格数据的特点是:每个人的刷卡数据有好几个,分成了几行保存,而且刷卡日期和时间是保存在一个单元格,因此我们首先要把刷卡日期和时间分开,这个工作可使用“分列”工具栏完成,处理结果如下:剩下的问题,就是要解决本文前面提出的问题了。这些问题的解决,有很多方法,其中一个最容易掌握、也最简单的方法,是利用简单的几个函数IF、COUNTIFS、INDEX函数进行处理。下面进行具体说明。在右侧插入一个辅助列,标题为“第几次刷卡”,在单元格F2输入公式=COUNTIFS($B$2:B2,B2,$D$2:D2,D2),往下复制,就可以统计E列的某个刷卡时间是某人某天的第几次刷卡了。再插入两个辅助列,标题分别为“最早刷卡”和“最晚刷卡”。在单元格G2输入公式=IF(F2=1,E2,),往下复制,即可得到某个人在某天的最早刷卡时间。在单元格H2输入公式=IF(G,INDEX(E2:E875,COUNTIFS(B:B,B2,D:D,D2)),),往下复制,即可
得到某个人在某天的最晚刷卡时间。这三个公式的思路、逻辑和原理,这里就不再细说了。这样,就得到了如下的处理结果。将F列至H列的公式,采用选择性粘贴的方法转换为数值,然后筛选出所有空值单元格,予以删除,并删除E列和F列,就得到下面的每个人的刷卡考勤数据,以此数据来做考勤计算:在此表格的右侧进行迟到、早退、加班的计算,数字1表示迟到或早退,具体的时间表示加班时间,如下表:其中,各单元格的公式分别如下:单元格G2:=IF(E8/24,1,)单元格H2:=IF(F217.5/24,1,)单元格I2:=IF(F18/24,F2-18/2
考勤数据整理 来自淘豆网m.daumloan.com转载请标明出处.
