|
本帖最后由 阿西木 于 2016-6-3 07:49 编辑
各位高手
统计员工的加班时间,想用VBA或者函数实现,请参考添付文件。
规则:
参考文件【集计用资料】,按照员工加班的“日期”和“类别”,将文件中的“加班时间”,转记到【集计结果】对应员工的日期和类别交集的单元格中。
求助高手予以解答。
- Sub Macro1()
- Dim arr, brr, crr, d, i&, j%
- Set d = CreateObject("scripting.dictionary")
- brr = Range("a2").CurrentRegion
- With Workbooks.Open(ThisWorkbook.Path & "\集计用资料.xls")
- arr = .Sheets(1).Range("a1").CurrentRegion
- .Close 0
- End With
- For i = 2 To UBound(arr)
- If arr(i, 1) = "" Then
- arr(i, 1) = arr(i - 1, 1)
- arr(i, 2) = arr(i - 1, 2)
- End If
- zf = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 4) & "," & arr(i, 3)
- d(zf) = d(zf) + arr(i, 5)
- Next
- ReDim crr(1 To UBound(brr) - 2, 1 To UBound(brr, 2) - 4)
- For i = 3 To UBound(brr)
- If brr(i, 1) = "" Then
- brr(i, 1) = brr(i - 1, 1)
- brr(i, 2) = brr(i - 1, 2)
- End If
- z = brr(i, 1) & "," & brr(i, 2) & "," & brr(i, 3)
- For j = 5 To UBound(brr, 2)
- zf = z & "," & brr(2, j)
- If d.exists(zf) Then crr(i - 2, j - 4) = d(zf)
- Next
- Next
- Range("e3").Resize(UBound(crr), UBound(crr, 2)) = crr
- End Sub
复制代码
|
|