|
发表于 2012-6-10 13:21
|
显示全部楼层
本楼为最佳答案
试下这个,分析表一二行是预先固定的- Sub 分类汇总()
- Dim ar, br, cr, dr
- Dim i As Integer, j As Integer
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Sheets("结果表")
- ar = .Range("a2:a" & .Range("a65536").End(xlUp).Row)
- End With
- With Sheets("分析")
- ReDim br(1 To 9)
- For i = 2 To 10
- If .Cells(1, i) = "" Then
- br(i - 1) = .Cells(1, i - 1) & .Cells(2, i)
- Else
- br(i - 1) = .Cells(1, i) & .Cells(2, i)
- End If
- Next
- cr = Sheets("数据源1").Range("a1").CurrentRegion
- For i = 2 To UBound(cr)
- If cr(i, 18) = "证券买入" Or cr(i, 18) = "证券卖出" Then
- s = cr(i, 1) & cr(i, 18) & cr(i, 5)
- Else
- s = cr(i, 1) & cr(i, 18)
- End If
- d(s) = d(s) + cr(i, 12)
- Next
- ReDim dr(1 To UBound(ar), 1 To UBound(br))
- For i = 1 To UBound(ar)
- For j = 1 To UBound(br)
- dr(i, j) = d(ar(i, 1) & br(j))
- Next
- Next
- .Range("a3:j65536").Clear
- .Range("a3").Resize(UBound(ar), 1) = ar
- .Range("b3").Resize(UBound(dr), UBound(dr, 2)) = dr
- End With
- End Sub
复制代码 |
|