【需求】

正常情况下,从考勤机中导出的初始数据中,每人每天肯定会多次打卡。

相应的,考勤数据统计工作中,很重要的一件事情就是从中剔除“无效”的打卡数据。比如说:

- 从上午多次考勤数据中,提取第一次打卡数据;

- 从下午多次考勤数据中,提取最后一次打卡数据。

例如,某人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表。

您可以返回【考勤系统】首页或进入【新闻资讯】阅读更多资讯