|
- Sub tt()
- arr = Sheet1.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- ReDim brr(1 To UBound(arr), 1 To 4)
- For i = 2 To UBound(arr)
- xm = arr(i, 3) '姓名
- If Not d.exists(xm) Then
- n = n + 1
- d(xm) = n
- brr(n, 1) = xm
- End If
- p = d(xm) '得到各名字在显示数组中对应行
-
- If Len(arr(i, 10)) = 0 Then arr(i, 10) = arr(i - 1, 10)
- t1 = arr(i, 10) '时间段1
- If Len(arr(i, 11)) = 0 Then arr(i, 11) = arr(i - 1, 11)
- t2 = arr(i, 11) '时间段2
- t11 = TimeValue(Split(t1, "-")(0)): t12 = TimeValue(Split(t1, "-")(1)) '时间段1左,时间段1右
- t21 = TimeValue(Split(t2, "-")(0)): t22 = TimeValue(Split(t2, "-")(1)) '时间段2左,时间段2右
- k1 = arr(i, 16): k2 = arr(i, 17) '打卡时间1,打卡时间2
-
- If arr(i, 9) <> "星期六" And arr(i, 9) <> "星期日" Then
- If Len(k1) = 0 And Len(k2) = 0 Then
- brr(p, 2) = brr(p, 2) + 1 '打卡1 打卡2均为空,则旷工+1
- Else
- If InStr(k1, "-") > 0 Then
- xrr = Split(k1, "-"): k11 = Trim(xrr(0)): k12 = Trim(xrr(1))
- If Len(k11) > 0 Then '打卡1左>时间段1左,或打卡1左为空,则迟到+1
- If TimeValue(k11) > t11 Then brr(p, 3) = brr(p, 3) + 1
- Else
- brr(p, 3) = brr(p, 3) + 1
- End If
- If Len(k12) > 0 Then '打卡1右<时间段1右,或打卡1右为空,则早退+1
- If TimeValue(k12) < t12 Then brr(p, 4) = brr(p, 4) + 1
- Else
- brr(p, 4) = brr(p, 4) + 1
- End If
- End If
-
- If InStr(k2, "-") > 0 Then
- xrr = Split(k2, "-"): k21 = Trim(xrr(0)): k22 = Trim(xrr(1))
- If Len(k21) > 0 Then '打卡2左>时间段2左,或打卡2左为空,则迟到+1
- If TimeValue(k21) > t21 Then brr(p, 3) = brr(p, 3) + 1
- Else
- brr(p, 3) = brr(p, 3) + 1
- End If
- If Len(k22) > 0 Then '打卡1右<时间段2右,或打卡2右为空,则早退+1
- If TimeValue(k22) < t22 Then brr(p, 4) = brr(p, 4) + 1
- Else
- brr(p, 4) = brr(p, 4) + 1
- End If
- End If
- End If
- End If
- Next
- With Sheet2
- .Activate
- .UsedRange.ClearContents
- .[a1].Resize(1, 4) = Array("姓名", "旷工次数", "迟到次数", "早退次数")
- .[a2].Resize(n, 4) = brr
- End With
- End Sub
复制代码 |
|