'凑了一个,看上去应该差不多,,,
Option Explicit
Sub test()
Dim arr, i, j, k, p, m, sum, total
arr = Sheets("原表").[a1].CurrentRegion.Offset(1).Resize(, 6)
ReDim brr(1 To UBound(arr, 1) * 3, 1 To UBound(arr, 2))
ReDim sum(UBound(arr, 2)), total(UBound(arr, 2)), crr(UBound(arr, 2)), drr(UBound(arr, 2))
For i = 1 To UBound(arr, 1) - 1
m = m + 1
For j = 1 To UBound(arr, 2)
brr(m, j) = arr(i, j)
If j > 3 Then
sum(j) = sum(j) + arr(i, j)
total(j) = total(j) + arr(i, j)
crr(j) = crr(j) + arr(i, j)
drr(j) = drr(j) + arr(i, j)
End If
Next
If arr(i, 1) <> arr(i + 1, 1) Or arr(i, 2) <> arr(i + 1, 2) Or arr(i, 3) <> arr(i + 1, 3) Then
m = m + 1: brr(m, 3) = arr(i, 3) & Space(1) & "汇总"
For j = 4 To UBound(arr, 2)
brr(m, j) = sum(j): sum(j) = 0
Next
If arr(i, 2) <> arr(i + 1, 2) Then
m = m + 1: brr(m, 2) = arr(i, 2) & Space(1) & "汇总"
For j = 4 To UBound(arr, 2)
brr(m, j) = total(j): total(j) = 0
Next
End If
If arr(i, 1) <> arr(i + 1, 1) Then
m = m + 1: brr(m, 1) = arr(i, 1) & Space(1) & "汇总"
For j = 4 To UBound(arr, 2)
brr(m, j) = crr(j): crr(j) = 0
Next
End If
End If
Next
m = m + 1: brr(m, 1) = "总计"
For i = 4 To UBound(arr, 2)
brr(m, i) = drr(i)
Next
With Sheets("汇总").[a4]
.Resize(Rows.Count - 3, UBound(brr, 2)).ClearContents
.Resize(m, UBound(brr, 2)) = brr
End With
End Sub |