【需求】
正常情況下，從考勤機中導出的初始數(shù)據(jù)中，每人每天肯定會多次打卡。
相應的，考勤數(shù)據(jù)統(tǒng)計工作中，很重要的一件事情就是從中剔除“無效”的打卡數(shù)據(jù)。比如說：
- 從上午多次考勤數(shù)據(jù)中，提取第一次打卡數(shù)據(jù)；
- 從下午多次考勤數(shù)據(jù)中，提取最后一次打卡數(shù)據(jù)。
例如，某人2014年9月18日下午有2條打卡數(shù)據(jù)，具體如下圖：
圖 1 初始考勤數(shù)據(jù)示例
處理后，我們要提取“22:41”的考勤數(shù)據(jù)，并用于下一步的統(tǒng)計，如下圖：
圖 2 提取結果示例
【方法】
Step1：初始數(shù)據(jù)的初步處理：
1.1 將日期、時間分割開來，并分兩列存儲：
圖 3 日期、時間分列效果示例
【說明】
- 這個數(shù)據(jù)數(shù)據(jù)截取功能；
- 截取日期的公式為：=MID(D3,1,FIND(" ",D3)-1)
-截取時間的公式為：=MID(D3,FIND(" ",D3)+1,LEN(D3)-FIND(" ",D3))
1.2 判斷每條數(shù)據(jù)屬于上午打卡還是下午打卡：
這個很簡單，直接用分離出來的打卡時間于12:00去比較即可：
=IF(TIMEVALUE(I4)>=TIMEVALUE("12:00:00"),"下午","上午")
1.3 判斷每條數(shù)據(jù)屬于上午/下午第幾次打卡：
這個也不難，屬于表中數(shù)據(jù)的多條件計數(shù)：
=COUNTIFS(A:A,A3,H:H,H3,J:J,J3)
Step1的處理結果如圖3所示。
Step2：從Step1處理后的數(shù)據(jù)中剔除“無效數(shù)據(jù)”并存儲到另一張表中：
這一步處理起來比較繁瑣。如果借助VBA操作，那么效率會比較好。具體的邏輯也比較簡單：
※ 按照行依次判斷；
※ 如果重復次數(shù)為“1”，則直接取該行數(shù)據(jù)，放到目標工作表；
※ 如果重復次數(shù)大于“1”，并且是上午打卡，那么取當天上午所有打卡數(shù)據(jù)的第一行數(shù)據(jù)存儲；
※ 如果重復次數(shù)大于“1”，并且是下午打卡，那么取當天上午所有打卡數(shù)據(jù)的最后一行數(shù)據(jù)存儲；
※ 依據(jù)每次取數(shù)結果，判斷下次從哪一行開始讀數(shù)。
圖 4 上午、下午選取了兩種不同的處理方式
實現(xiàn)Step2的功能，具體的代碼如下：
---------------------------------------------------------------------------------------------------------------
Sub 去除重復數(shù)據(jù)_待改進()
Dim i As Integer '考勤數(shù)據(jù)源行數(shù)循環(huán)變量
Dim j As Integer '確定在考勤數(shù)據(jù)源中已經(jīng)找到了第幾行
Dim k As Integer '確定02-去除無效打卡中已經(jīng)到了第幾行
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-考勤數(shù)據(jù)源").Range("A3:" & "A" & Range("A65536").End(xlUp).Row)
Set rng_date = Worksheets("01-考勤數(shù)據(jù)源").Range("H3:" & "H" & Range("A65536").End(xlUp).Row)
Set rng_time = Worksheets("01-考勤數(shù)據(jù)源").Range("J3:" & "J" & Range("A65536").End(xlUp).Row)
j = 3
k = 2
For i = 3 To Worksheets("01-考勤數(shù)據(jù)源").Range("A65536").End(xlUp).Row
l = Worksheets("01-考勤數(shù)據(jù)源").Range("K" & i)
If l > 1 And Worksheets("01-考勤數(shù)據(jù)源").Range("J" & i) = "下午" Then
Worksheets("02-去除無效打卡").Range("A" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("A" & i + l - 1) '提取員工編號
Worksheets("02-去除無效打卡").Range("B" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("B" & i + l - 1) '提取姓名
Worksheets("02-去除無效打卡").Range("C" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("C" & i + l - 1) '提取部門
Worksheets("02-去除無效打卡").Range("D" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("H" & i + l - 1) '提取日期
Worksheets("02-去除無效打卡").Range("E" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("I" & i + l - 1) '提取時間
Worksheets("02-去除無效打卡").Range("F" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("J" & i + l - 1) '提取上/下午
k = k + l - 1
i = i + l
ElseIf l > 1 And Worksheets("01-考勤數(shù)據(jù)源").Range("J" & i) = "上午" Then
Worksheets("02-去除無效打卡").Range("A" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("A" & i) '提取員工編號
Worksheets("02-去除無效打卡").Range("B" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("B" & i) '提取姓名
Worksheets("02-去除無效打卡").Range("C" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("C" & i) '提取部門
Worksheets("02-去除無效打卡").Range("D" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("H" & i) '提取日期
Worksheets("02-去除無效打卡").Range("E" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("I" & i) '提取時間
Worksheets("02-去除無效打卡").Range("F" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("J" & i) '提取上/下午
k = k + l - 1
i = i + l
Else
Worksheets("02-去除無效打卡").Range("A" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("A" & i) '提取員工編號
Worksheets("02-去除無效打卡").Range("B" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("B" & i) '提取姓名
Worksheets("02-去除無效打卡").Range("C" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("C" & i) '提取部門
Worksheets("02-去除無效打卡").Range("D" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("H" & i) '提取日期
Worksheets("02-去除無效打卡").Range("E" & k) = Worksheets("01-考勤數(shù)據(jù)源").Range("I" & i) '提取時間
Worksheets("02-去除無效打卡").Range("F" & k) = Worksheets("01-考勤數(shù)據(jù)源").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表。