|
- Sub test()
- Dim r%, i%
- Dim arr, brr, zrr()
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("基础数据")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:h" & r)
- End With
- For i = 1 To UBound(arr)
- nd = Year(arr(i, 1))
- If Not d.exists(arr(i, 4)) Then
- Set d(arr(i, 4)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 4)).exists(nd) Then
- m = 1
- ReDim brr(1 To 4, 1 To m)
- Else
- brr = d(arr(i, 4))(nd)
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 4, 1 To m)
- End If
- brr(1, m) = arr(i, 7)
- brr(2, m) = arr(i, 3)
- brr(3, m) = arr(i, 1)
- brr(4, m) = arr(i, 8)
- d(arr(i, 4))(nd) = brr
- Next
- ReDim crr(1 To 10000, 1 To 11)
- m = 1
- x = 0
- For Each aa In d.keys
- hs = 0
- For Each bb In d(aa).keys
- n = IIf(bb = 2019, 1, 7)
- brr = d(aa)(bb)
- crr(m, 1) = aa
- crr(m, 7) = aa
- If hs < UBound(brr, 2) Then
- hs = UBound(brr, 2)
- End If
- For j = 1 To UBound(brr, 2)
- For i = 1 To UBound(brr)
- crr(m + j - 1, n + i) = brr(i, j)
- Next
- Next
- Next
- x = x + 1
- ReDim Preserve zrr(1 To x)
- zrr(x) = Array(m, hs)
- m = m + hs + 1
- Next
- With Worksheets("效果")
- .UsedRange.Offset(2, 0).Clear
- .Range("a3").Resize(m, UBound(crr, 2)) = crr
- For k = 1 To UBound(zrr)
- With .Cells(zrr(k)(0) + 2, 1).Resize(zrr(k)(1), 11)
- .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
- End With
- Next
- With .Range("f1:f" & 2 + m - 2)
- .Interior.ColorIndex = 6
- End With
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|