|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With Worksheets("登记表")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:l" & r)
- End With
- n = 0
- For i = 1 To UBound(arr)
- If Not d1.exists(arr(i, 8)) Then
- n = n + 1
- d1(arr(i, 8)) = n
- End If
- Next
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 1)).exists(arr(i, 2)) Then
- Set d(arr(i, 1))(arr(i, 2)) = CreateObject("scripting.dictionary")
- d(arr(i, 1))(arr(i, 2))(1) = arr(i, 4)
- Set d(arr(i, 1))(arr(i, 2))(2) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 1))(arr(i, 2))(2).exists(arr(i, 5)) Then
- ReDim brr(1 To d1.Count)
- Else
- brr = d(arr(i, 1))(arr(i, 2))(2)(arr(i, 5))
- End If
- n = d1(arr(i, 8))
- brr(n) = brr(n) + arr(i, 12)
- d(arr(i, 1))(arr(i, 2))(2)(arr(i, 5)) = brr
- Next
- With Worksheets("汇总表")
- .UsedRange.Offset(1, 0).Clear
- m = 2
- For Each aa In d.keys
- m0 = m
- For Each bb In d(aa).keys
- m1 = m
- For Each cc In d(aa)(bb)(2).keys
- brr = d(aa)(bb)(2)(cc)
- .Cells(m, 4) = cc
- .Cells(m, 5).Resize(1, UBound(brr)) = brr
- m = m + 1
- Next
- With .Cells(m1, 2)
- .Value = bb
- .Resize(m - m1, 1).Merge
- End With
- With .Cells(m1, 3)
- .Value = d(aa)(bb)(1)
- .Resize(m - m1, 1).Merge
- End With
- Next
- With .Cells(m0, 1)
- .Value = aa
- .Resize(m - m0, 1).Merge
- End With
- Next
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- .Range("a1:g" & m - 1).Borders.LineStyle = xlContinuous
- End With
- End Sub
复制代码 |
|