【需求】
正常情况下,从考勤机中导出的初始数据中,每人每天肯定会多次打卡。
相应的,考勤数据统计工作中,很重要的一件事情就是从中剔除“无效”的打卡数据。比如说:
- 从上午多次考勤数据中,提取第一次打卡数据;
- 从下午多次考勤数据中,提取最后一次打卡数据。
例如,某人2014年9月18日下午有2条打卡数据,具体如下图:
图 1 初始考勤数据示例
处理后,我们要提取“22:41”的考勤数据,并用于下一步的统计,如下图:
图 2 提取结果示例
【方法】
Step1:初始数据的初步处理:
1.1 将日期、时间分割开来,并分两列存储:
图 3 日期、时间分列效果示例
【说明】
- 这个数据数据截取功能;
- 截取日期的公式为:=MID(D3,1,FIND(" ",D3)-1)
-截取时间的公式为:=MID(D3,FIND(" ",D3)+1,LEN(D3)-FIND(" ",D3))
1.2 判断每条数据属于上午打卡还是下午打卡:
这个很简单,直接用分离出来的打卡时间于12:00去比较即可:
=IF(TIMEVALUE(I4)>=TIMEVALUE("12:00:00"),"下午","上午")
1.3 判断每条数据属于上午/下午第几次打卡:
这个也不难,属于表中数据的多条件计数:
=COUNTIFS(A:A,A3,H:H,H3,J:J,J3)
Step1的处理结果如图3所示。
Step2:从Step1处理后的数据中剔除“无效数据”并存储到另一张表中:
这一步处理起来比较繁琐。如果借助VBA操作,那么效率会比较好。具体的逻辑也比较简单:
※ 按照行依次判断;
※ 如果重复次数为“1”,则直接取该行数据,放到目标工作表;
※ 如果重复次数大于“1”,并且是上午打卡,那么取当天上午所有打卡数据的第一行数据存储;
※ 如果重复次数大于“1”,并且是下午打卡,那么取当天上午所有打卡数据的最后一行数据存储;
※ 依据每次取数结果,判断下次从哪一行开始读数。
图 4 上午、下午选取了两种不同的处理方式
实现Step2的功能,具体的代码如下:
---------------------------------------------------------------------------------------------------------------
Sub 去除重复数据_待改进()
Dim i As Integer '考勤数据源行数循环变量
Dim j As Integer '确定在考勤数据源中已经找到了第几行
Dim k As Integer '确定02-去除无效打卡中已经到了第几行
Dim l As Integer '某人、某天上午/下午共打了多少次卡判断
Dim m As Integer
Dim rng_staffcode As Range
Dim rng_date As Range
Dim rng_time As Range
Set rng_staffcode = Worksheets("01-考勤数据源").Range("A3:" & "A" & Range("A65536").End(xlUp).Row)
Set rng_date = Worksheets("01-考勤数据源").Range("H3:" & "H" & Range("A65536").End(xlUp).Row)
Set rng_time = Worksheets("01-考勤数据源").Range("J3:" & "J" & Range("A65536").End(xlUp).Row)
j = 3
k = 2
For i = 3 To Worksheets("01-考勤数据源").Range("A65536").End(xlUp).Row
l = Worksheets("01-考勤数据源").Range("K" & i)
If l > 1 And Worksheets("01-考勤数据源").Range("J" & i) = "下午" Then
Worksheets("02-去除无效打卡").Range("A" & k) = Worksheets("01-考勤数据源").Range("A" & i + l - 1) '提取员工编号
Worksheets("02-去除无效打卡").Range("B" & k) = Worksheets("01-考勤数据源").Range("B" & i + l - 1) '提取姓名
Worksheets("02-去除无效打卡").Range("C" & k) = Worksheets("01-考勤数据源").Range("C" & i + l - 1) '提取部门
Worksheets("02-去除无效打卡").Range("D" & k) = Worksheets("01-考勤数据源").Range("H" & i + l - 1) '提取日期
Worksheets("02-去除无效打卡").Range("E" & k) = Worksheets("01-考勤数据源").Range("I" & i + l - 1) '提取时间
Worksheets("02-去除无效打卡").Range("F" & k) = Worksheets("01-考勤数据源").Range("J" & i + l - 1) '提取上/下午
k = k + l - 1
i = i + l
ElseIf l > 1 And Worksheets("01-考勤数据源").Range("J" & i) = "上午" Then
Worksheets("02-去除无效打卡").Range("A" & k) = Worksheets("01-考勤数据源").Range("A" & i) '提取员工编号
Worksheets("02-去除无效打卡").Range("B" & k) = Worksheets("01-考勤数据源").Range("B" & i) '提取姓名
Worksheets("02-去除无效打卡").Range("C" & k) = Worksheets("01-考勤数据源").Range("C" & i) '提取部门
Worksheets("02-去除无效打卡").Range("D" & k) = Worksheets("01-考勤数据源").Range("H" & i) '提取日期
Worksheets("02-去除无效打卡").Range("E" & k) = Worksheets("01-考勤数据源").Range("I" & i) '提取时间
Worksheets("02-去除无效打卡").Range("F" & k) = Worksheets("01-考勤数据源").Range("J" & i) '提取上/下午
k = k + l - 1
i = i + l
Else
Worksheets("02-去除无效打卡").Range("A" & k) = Worksheets("01-考勤数据源").Range("A" & i) '提取员工编号
Worksheets("02-去除无效打卡").Range("B" & k) = Worksheets("01-考勤数据源").Range("B" & i) '提取姓名
Worksheets("02-去除无效打卡").Range("C" & k) = Worksheets("01-考勤数据源").Range("C" & i) '提取部门
Worksheets("02-去除无效打卡").Range("D" & k) = Worksheets("01-考勤数据源").Range("H" & i) '提取日期
Worksheets("02-去除无效打卡").Range("E" & k) = Worksheets("01-考勤数据源").Range("I" & i) '提取时间
Worksheets("02-去除无效打卡").Range("F" & k) = Worksheets("01-考勤数据源").Range("J" & i) '提取上/下午
k = k + 1
End If
Next i
Worksheets("02-去除无效打卡").UsedRange.Font.Name = "微软雅黑"
Worksheets("02-去除无效打卡").UsedRange.Borders.LineStyle = xlContinuous
Worksheets("02-去除无效打卡").UsedRange.Columns.AutoFit
Worksheets("02-去除无效打卡").UsedRange.HorizontalAlignment = xlCenter
Worksheets("02-去除无效打卡").UsedRange.VerticalAlignment = xlCenter
End Sub
---------------------------------------------------------------------------------------------------------------
【说明】
- HR写的代码,大家将就着用!
- 如果嫌麻烦,可以直接找我要带着代码的Excel表。