Option Explicit
Sub test()
Dim arr, i, j, k, p, m, sum, total
arr = Sheets("原表").[a1].CurrentRegion.Offset(1).Resize(, 5)
ReDim brr(1 To UBound(arr, 1) * 2, 1 To UBound(arr, 2))
ReDim sum(UBound(arr, 2)), total(UBound(arr, 2)), crr(UBound(arr, 2))
Call bsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
For i = 1 To UBound(arr, 1) - 1
If arr(i, 1) <> arr(i + 1, 1) Then
Call bsort(arr, p + 1, i, 1, UBound(arr, 2), 2)
For j = p + 1 To i
For k = 3 To UBound(arr, 2)
sum(k) = sum(k) + arr(j, k)
total(k) = total(k) + arr(j, k)
Next
If j = i Or arr(j, 2) <> arr(j + 1, 2) Then
m = m + 1
brr(m, 1) = arr(j, 1): brr(m, 2) = arr(j, 2)
For k = 3 To UBound(arr, 2)
brr(m, k) = sum(k): sum(k) = 0
Next
If j = i Then
m = m + 1
brr(m, 1) = arr(j, 1): brr(m, 2) = "小计"
For k = 3 To UBound(arr, 2)
brr(m, k) = total(k)
crr(k) = crr(k) + total(k): total(k) = 0
Next
End If
End If
Next
p = i
End If
Next
m = m + 1: brr(m, 2) = "总计"
For i = 3 To UBound(arr, 2)
brr(m, i) = crr(i)
Next
With Sheets("汇总").[g4]
.Resize(Rows.Count - 3, UBound(brr, 2)).ClearContents
.Resize(m, UBound(brr, 2)) = brr
End With
Sheets("原表").[a1].Resize(, UBound(arr, 2)).Copy Sheets("汇总").[g3]
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If StrComp(arr(j, key), arr(j + 1, key), vbTextCompare) = 1 Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next
Next
End Function |