考勤数据处理
1、初步处理
考勤机导出来的数据不一定能够直接使用,可能是文本模式,可能是锁定模式,建议将所有数据复制粘贴在新的EXCEL表格中(图1)。选中C列,数据-分列,出现以下对话框,如果有两列数据或者多列数据可以使用“固定宽度”,分隔数据。得出结果(图2).
图1
图2
2、使用数据透视表
图二中同一天可能会存在多个考勤数据,使用数据透视表能解决诸多问题。选择“插入-数据透视表-选择数据源”,选定透视表的行与列,一般将“姓名”、“考勤号”及“日期”作为行,将“签到时间”作为列,同时区分出“签到”和“签退”。
图3
“签到”和“签退”使用“最小值”和“最大值”,并将时间格式设定为“时间”。得出以下表格(图6)
图4
图5
图6
选中透视表,工具栏出现“分析”及“设计”两栏,选择“设计”,自行调整表格,个人觉得如果功能不熟悉,可以逐个尝试,直至达到自己想要的效果
图7
图8
3、数据再处理
我们发现当使用透视表的“最大值”和“最小值”功能时,如果一天只有一次考勤时间,则这个时间为最大或者最小,我们需要将这个数据处理。将透视表的数据复制粘贴,可以选择“选择性粘贴-保留值和数字格式”,并对数据进行二次处理,
公式为“=IF(O2<12/24,O2,“”)”,
表示的是如果O2单元格的时间小于12点,则签到时间为O2,反之则没有数据,当然每个公司对于出勤的时间处理都有规定,比如什么时间开始算作迟到,什么时间算作旷工,这个根据实际情况来判断。这些处理结束后,复制Q列和R列的数值,依旧只保留值和数字格式。现在的考勤数据已经能进行迟到早退等的处理。
图9
“迟到”和“早退”使用IF条件,这里的迟到时间=7:30,早退时间=17:00。
关于24小时的表达法,如果是7点15分,保留整数位,分钟除以60作为小数位,即7.25/24。
迟到=IF(O2>7.5/24,1,“”)
早退=IF(AND(P2>0,P2<17/24),1,“”),如果仅使用“P2<17/24”这个条件,无考勤数据也符合这种条件,因此多加一条。
出勤次数=COUNTIFS(O2:P2,”<>”)
图10
4、数据横置
如果是少数的数据,在粘贴中有一项“转置”工具,但是我们处理的考勤数据往往有几十个甚至上百个,使用手动转置过于麻烦,我求助了万能的QQ群,得到一个公式:
=SUMIFS($D$2:$D$15,$B$2:$B$15,$B20,$C$2:$C$15,E$19),我的理解是:我们需要李四(B20)在1月1号(E19)的签到时间(D2:D15),加”$”绝对引用,这种引用可以保证下拉或者右拉的时,引用区域保持不变。
其中E2无数据,引用这个公式会出现“00:00”,处理数据时可以使用“查找-替换“,将数据清空。
图11
图12
有时我们只需统计迟到、早退等次数,使用公式:
=COUNTIFS($B$2:$B$15,$B34,$F2:$F15,"<>")
"<>”代表非空单元格,如果需要更详细的,可以输入你所需要查找的内容。
图13
图14
5、数据美化
最后一步,将你所需的数据复制粘贴,记得保留“值和数字格式”。
3楼 鹏城之旅
楼主估计多少天没有上三茅了把
SunflowerZ
@鹏城之旅:确实很久了
SunflowerZ
@鹏城之旅:没看三茅信息的自觉
鹏城之旅
@被花摧残的伪园丁:楼主啊,我就想知道那个表格要怎么弄而已,麻烦您有时间上一下三茅
2楼 鹏城之旅
楼主在不在啊
1楼 星888哇
将“签到时间”作为列,同时区分出“签到”和“签退”,请问如何自动区分出签到和签退?
鹏城之旅
@星888哇:你会使用吗