|
本帖最后由 lujianwkx 于 2015-3-26 14:35 编辑
各位大虾:
我们公司员工进出都需要打卡,考勤数据一天会有很多次,每次需人工逐笔分析是否迟到早退。现希望帮我用宏或公式实现
1.只显示每位员工每天的第一次和最后一次的考勤记录
2.想实现自动统计每位员工当月第一次打卡在上午9:30分之后的次数和最后一次打卡在19:30分的数据以便考核分析
可以在另个表页显示即可。数据请看附件。
比较急请帮忙,谢谢
- Sub 考勤()
- Dim Arr, Dic, Rq, Tm, Str$, Tm1, Tm2, Dicrt()
- Set Dic = CreateObject("Scripting.Dictionary")
- Arr = [a1].CurrentRegion
- ReDim Dicrt(1 To UBound(Arr), 1 To 5) '每天第一次和最末次打卡情况
- Columns("f:q").ClearContents
- For i = 2 To UBound(Arr)
- Rq = Format(Arr(i, 3), "yyyy-mm-dd")
- Tm = Format(Arr(i, 3), "hh:mm:ss")
- Str = Arr(i, 1) & "*" & Arr(i, 2) & "*" & Rq
- c = IIf(Tm < #1:00:00 PM#, 4, 5)
- If Not Dic.exists(Str) Then
- n = n + 1
- Dic(Str) = n
- Dicrt(n, 1) = Arr(i, 1)
- Dicrt(n, 2) = Arr(i, 2)
- Dicrt(n, 3) = Rq
- Dicrt(n, c) = Tm
- Else
- If c = 5 Then Dicrt(Dic(Str), c) = Tm
- End If
- Next
- [f1].Resize(1, 5) = Array("部门", "姓名", "打卡时间", "第一次打卡", "最未次打卡")
- [f2].Resize(n, 5) = Dicrt
-
- ReDim brr(1 To UBound(Dicrt), 1 To 6) '迟到、早退、未打卡情况
- For i = 1 To n
- Tm1 = Dicrt(i, 4): Tm2 = Dicrt(i, 5)
- Str = Dicrt(i, 1) & "*" & Dicrt(i, 2)
- If Not Dic.exists(Str) Then
- m = m + 1
- Dic(Str) = m
- brr(m, 1) = Dicrt(i, 1)
- brr(m, 2) = Dicrt(i, 2)
- End If
- p = Dic(Str)
- If Len(Tm1) = 0 Then brr(p, 5) = brr(p, 5) + 1 Else If Tm1 < #9:30:00 AM# Then brr(p, 3) = brr(p, 3) + 1
- If Len(Tm2) = 0 Then brr(p, 6) = brr(p, 6) + 1 Else If Tm2 > #7:30:00 PM# Then brr(p, 4) = brr(p, 4) + 1
- Next
- [L1].Resize(1, 6) = Array("部门", "姓名", "迟到", "早退", "上班未打卡", "下班未打卡")
- [L2].Resize(m, 6) = brr
- End Sub
复制代码
|
|