|
发表于 2011-12-30 11:57
|
显示全部楼层
本楼为最佳答案
本帖最后由 liuguansky 于 2011-12-30 12:03 编辑
- Public d As New Dictionary, Mr&
- Sub j()
- Dim Arr, i&, Ar, Rng As Range
- Dim j As Byte, s$, arrt()
- With Worksheets("数源1")
- Arr = .Range("d9:g" & .Range("d9").End(4).Row).Value
- End With
- Ar = Array("迟到", "早退", "请假", "外出", "上班漏刷", "下班漏刷")
- d.RemoveAll
- For i = 0 To UBound(Ar)
- d.Add Ar(i), IIf(i > 4, 4, i)
- Next
- For i = 1 To UBound(Arr)
- If d.Exists(Arr(i, 1)) Then
- Ar = d(Arr(i, 1))
- Ar(d(Arr(i, 4))) = Ar(d(Arr(i, 4))) + 1
- d(Arr(i, 1)) = Ar
- Else
- Ar = Array(0, 0, 0, 0, 0)
- Ar(d(Arr(i, 4))) = Ar(d(Arr(i, 4))) + 1
- d(Arr(i, 1)) = Ar
- End If
- Next i
- With Worksheets("数源2")
- Arr = .Range(.[a3], .[e3].End(4)).Value
- s = "外出"
- For i = 1 To UBound(Arr)
- If Arr(i, 5) Like "*私事*" Then
- If d.Exists(Arr(i, 1)) Then
- Ar = d(Arr(i, 1))
- Ar(d(s)) = Ar(d(s)) + 1
- d(Arr(i, 1)) = Ar
- Else
- Ar = Array(0, 0, 0, 0, 0)
- Ar(d(s)) = Ar(d(s)) + 1
- d(Arr(i, 1)) = Ar
- End If
- End If
- Next
- Set Rng = .Cells.Find("请假、 记事")
- If Not Rng Is Nothing Then
- Arr = .Range(.Cells(Rng.Row + 1, 1), .Cells(Rng.Row + 1, 5).End(4)).Value
- s = "请假"
- For i = 1 To UBound(Arr)
- If Arr(i, 5) Like "*私事*" Then
- If d.Exists(Arr(i, 1)) Then
- Ar = d(Arr(i, 1))
- Ar(d(s)) = Ar(d(s)) + 1
- d(Arr(i, 1)) = Ar
- Else
- Ar = Array(0, 0, 0, 0, 0)
- Ar(d(s)) = Ar(d(s)) + 1
- d(Arr(i, 1)) = Ar
- End If
- End If
- Next
- End If
- End With
- With Worksheets("统计")
- Mr = .Cells.Find("说明:", lookat:=xlPart).Row - 3
- Call Fh(.Range("a3"))
- Call Fh(.Range("i3"))
- Call Fh(.Range("q3"))
- Call Fh(.Range("y3"))
- Call Fh(.Range("ag3"))
- End With
- End Sub
- Sub Fh(Rng As Range)
- Dim arrt(), i&, j As Byte
- Arr = Worksheets("统计").Range(Rng, Rng.End(4)).Value
- ReDim arrt(1 To UBound(Arr), 1 To 6)
- For i = 1 To UBound(Arr)
- If d.Exists(Arr(i, 1)) Then
- Ar = d(Arr(i, 1))
- For j = 0 To 4
- arrt(i, j + 2) = Ar(j)
- Next j
- arrt(i, 1) = (arrt(i, 2) + arrt(i, 3) + arrt(i, 6)) * 0.5
- End If
- Next i
- Rng.Offset(0, 2).Resize(Mr, 6).ClearContents
- Rng.Offset(0, 2).Resize(UBound(arrt), 6) = arrt
- End Sub
复制代码
已修正
有冻结窗口。最后表的说明没注意看,不好意思
20111229.rar
(26.17 KB, 下载次数: 19)
|
|