- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("统计表")
- rq = .Range("d1").Value
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- .Range("c3:i" & r).ClearContents
- brr = .Range("a3:i" & r)
- For i = 1 To UBound(brr)
- d(brr(i, 2)) = i
- Next
- End With
- With Worksheets("基础表")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a1").Resize(r, c)
- End With
- For j = 2 To UBound(arr, 2)
- If d.exists(arr(1, j)) Then
- m = d(arr(1, j))
- For i = 2 To UBound(arr)
- If DateDiff("m", arr(i, 1), rq) = 0 Then
- brr(m, 3) = brr(m, 3) + arr(i, j)
- ElseIf DateDiff("m", arr(i, 1), rq) = 1 Then
- brr(m, 4) = brr(m, 4) + arr(i, j)
- End If
- If DateDiff("q", arr(i, 1), rq) = 0 Then
- brr(m, 6) = brr(m, 6) + arr(i, j)
- ElseIf DateDiff("q", arr(i, 1), rq) = 1 Then
- brr(m, 7) = brr(m, 7) + arr(i, j)
- End If
- If Year(arr(i, 1)) = Year(rq) And month(arr(i, 1)) <= month(rq) Then
- brr(m, 8) = brr(m, 8) + arr(i, j)
- End If
- Next
- End If
- Next
- With Worksheets("统计表")
- .Range("a3").Resize(UBound(brr), UBound(brr, 2)) = brr
- End With
- End Sub
复制代码 |