|
- Sub tt()
- arr = [a1].CurrentRegion
- Set dic = CreateObject("scripting.dictionary")
- ReDim brr(UBound(arr), 1 To 5)
- brr(0, 1) = "考勤号码": brr(0, 2) = "姓名"
- brr(0, 3) = "出勤次数": brr(0, 4) = "迟到次数": brr(0, 5) = "早退次数"
- For i = 2 To UBound(arr)
- If arr(i, 6) <> "重复记录" And arr(i, 6) <> "无效记录" Then '去掉重复、无效记录
- d = DateValue(arr(i, 3)) '日期
- t = TimeValue(arr(i, 3)) '时间
- xkey = arr(i, 2): ykey = arr(i, 2) & d
- If Not dic.exists(xkey) Then
- n = n + 1
- dic(xkey) = n
- brr(n, 1) = arr(i, 1)
- brr(n, 2) = arr(i, 2)
- End If
- p = dic(xkey)
- If Not dic.exists(ykey) Then '名字+日期去重即为出勤天数
- brr(p, 3) = brr(p, 3) + 1
- dic(ykey) = ""
- End If
- If InStr(arr(i, 5), "签到") > 0 Then '签到:上班时间7:30—11:30之间,14:30以后属迟到
- If (t > TimeValue("7:30") And t < TimeValue("11:30")) Or _
- t > TimeValue("14:30") Then brr(n, 4) = brr(n, 4) + 1
- ElseIf InStr(arr(i, 5), "签退") > 0 Then '签退:11:30前,14:30—20:30之间属早退
- If t < TimeValue("11:30") Or _
- (t > TimeValue("14:30") And t < TimeValue("20:30")) Then brr(n, 5) = brr(n, 5) + 1
- End If
- End If
- Next
- [j1].Resize(n + 1, 5) = brr
- End Sub
复制代码 |
|