员工考勤求助

梅子子子

如何根据考勤，自动统计出每个人的出勤天数，是否满勤，迟到次数，早退次数，未打卡等，求高手分享，非常感谢

小zhi

1. Sub 考勤统计()
   
2.     Dim sht, sht1 As Worksheet, i, n, m, x, y, z As Long, d As Object, arr As Variant
   
3.     Set sht = ThisWorkbook.Worksheets(1)
   
4.     Set sht1 = ThisWorkbook.Worksheets(2)
   
5.     arr = sht.Range("c2:c" & sht.Cells(Rows.Count, 3).End(xlUp).Row)
   
6.     Set d = CreateObject("scripting.dictionary")
   
7.     For i = 1 To sht.Cells(Rows.Count, 3).End(xlUp).Row - 1
   
8.         d(arr(i, 1)) = d(arr(i, 1)) + 1
   
9.     Next
   
10.     sht1.Cells(3, 1).Resize(d.Count, 1) = WorksheetFunction.Transpose(d.keys)
    
11.     sht1.Cells(3, 3).Resize(d.Count, 1) = WorksheetFunction.Transpose(d.items)
    
12.     For n = 3 To sht1.Cells(Rows.Count, 3).End(xlUp).Row
    
13.         sht1.Cells(n, 3).Value = sht1.Cells(n, 3).Value / 2
    
14.         sht1.Cells(n, 1).Value = Replace(sht1.Cells(n, 1).Value, " ", "")
    
15.     Next
    
16.     For m = 3 To sht1.Cells(Rows.Count, 1).End(xlUp).Row
    
17.         y = 0
    
18.         z = 0
    
19.         For x = 3 To sht.Cells(Rows.Count, 3).End(xlUp).Row
    
20.             sht.Cells(x, 3).Value = Replace(sht.Cells(x, 3).Value, " ", "")
    
21.             If sht1.Cells(m, 1).Value = sht.Cells(x, 3).Value Then
    
22.                 Select Case sht.Cells(x, 6)
    
23.                     Case #8:30:00 AM# To #12:00:00 PM#
    
24.                     y = y + 1
    
25.                     sht1.Cells(m, 5).Value = y
    
26.                     Case #1:00:00 PM# To #5:30:00 PM#
    
27.                     z = z + 1
    
28.                     sht1.Cells(m, 6).Value = z
    
29.                 End Select
    
30.             End If
    
31.         Next
    
32.     Next
    
33. End Sub
    

复制代码

小zhi

已经解决，你看看能用不