|
Sub test()
Dim arr, br, i, d As Object, str, lr, m
Set d = CreateObject("scripting.dictionary")
arr = Sheets("账目").Range("a1").CurrentRegion
ReDim br(1 To UBound(arr), 1 To 4)
For i = 2 To UBound(arr)
str = Format(arr(i, 1), "yyyy-mm")
lr = arr(i, 2) - arr(i, 3)
If Not d.exists(str) Then
m = m + 1
br(m, 1) = str: br(m, 2) = lr: br(m, 4) = 1
d(str) = m
Else
br(d(str), 2) = br(d(str), 2) + lr
br(d(str), 4) = br(d(str), 4) + 1
br(d(str), 3) = br(d(str), 2) / br(d(str), 4)
End If
Next
With Sheets("汇总")
.UsedRange.Offset(1, 0).ClearContents
.Range("a2").Resize(m, 3) = br
End With
End Sub
|
|