|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 逐级汇总()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- 't = yfs
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With Worksheets("代码科目")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:g" & r)
- End With
- ReDim brr(1 To UBound(arr), 1 To 13)
- For i = 1 To UBound(arr)
- For j = 1 To 3
- brr(i, j) = arr(i, j)
- Next
- brr(i, 4) = arr(i, 6)
- brr(i, 5) = arr(i, 7)
- d(brr(i, 1)) = i
- Next
- k = 6
- For Each ws In Worksheets(Array("明细", "分1"))
- With ws
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:l" & r)
- For i = 1 To UBound(arr)
- If d.Exists(arr(i, 2)) Then
- m = d(arr(i, 2))
- brr(m, k) = brr(m, k) + arr(i, 10)
- brr(m, k + 1) = brr(m, k + 1) + arr(i, 12)
- End If
- Next
- k = k + 2
- End With
- Next
- fla = False
- For i = UBound(brr) - 1 To 1 Step -1
- bm = brr(i, 1)
- ReDim crr(4 To 13)
- j = i + 1
- Do While brr(j, 1) Like bm & "*"
- If brr(j, 1) Like bm & "###" Then
- For k = LBound(crr) To UBound(crr)
- If k = 10 Or k = 13 Then
- crr(k) = crr(k) + brr(j, k)
- Else
- If brr(j, 3) = "加" Then
- crr(k) = crr(k) + brr(j, k)
- Else
- crr(k) = crr(k) - brr(j, k)
- End If
- End If
- Next
- fla = True
- End If
- j = j + 1
- If j > UBound(brr) Then
- Exit Do
- End If
- Loop
- If fla Then
- For k = LBound(crr) To UBound(crr)
- brr(i, k) = crr(k)
- Next
- fla = False
- d1(i) = Len(brr(i, 1))
- End If
- Next
- With Worksheets("汇总")
- .UsedRange.Offset(1, 0).Clear
- .Columns(1).NumberFormatLocal = "@"
- .Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
- r = 1 + UBound(brr)
- With .Range("a1:m1")
- .HorizontalAlignment = xlCenter
- End With
- With .UsedRange
- .VerticalAlignment = xlCenter
- With .Font
- .Size = 10
- End With
- End With
- With .Range("a2:a" & r)
- .HorizontalAlignment = xlLeft
- End With
- .Range("a1:m" & r).Borders.LineStyle = xlContinuous
- For Each aa In d1.keys
- Select Case d1(aa)
- Case 1
- ys = 22
- Case 4
- ys = 40
- Case 7
- ys = 37
- Case Else
- ys = 15
- End Select
- .Cells(aa + 1, 1).Resize(1, 13).Interior.ColorIndex = ys
- Next
- End With
- End Sub
复制代码 |
|