|
用一个字典,我是这样写的,不知一个字典还有没有别的算法
Sub aa()
Set d = CreateObject("Scripting.Dictionary")
rw = Range("a" & Rows.Count).End(3).Row
ar = Range("a2:e" & rw)
'ReDim br(1 To UBound(ar) + 2, 1 To 3 * UBound(ar) + 1),极端情况下这样,但数据大了会不会溢出???
ReDim br(1 To UBound(ar) + 2, 1 To 301) '还是给个限制,最多100个地区
ReDim cr(1 To 301)
m = 1: s = 2
For i = 1 To UBound(ar)
r = d(ar(i, 1))
n = d(ar(i, 2))
If r = "" Then
s = s + 1: d(ar(i, 1)) = s: r = s
br(r, 1) = ar(i, 1)
End If
If n = "" Then
m = m + 3: d(ar(i, 2)) = m: n = m
br(1, n - 1) = ar(i, 2)
br(2, n - 2) = "库存": br(2, n - 1) = "销量1": br(2, n) = "销量2"
End If
br(r, n - 2) = br(r, n - 2) + ar(i, 3): br(r, n - 1) = br(r, n - 1) + ar(i, 4): br(r, n) = br(r, n) + ar(i, 5)
cr(n - 2) = cr(n - 2) + ar(i, 3): cr(n - 1) = cr(n - 1) + ar(i, 4): cr(n) = cr(n) + ar(i, 5)
Next
br(1, 1) = "行标签"
Range("j17").Resize(s, n) = br
cr(1) = "合计"
Range("j" & 17 + s).Resize(1, m) = cr
End Sub |
|