|
发表于 2015-12-15 20:02
|
显示全部楼层
本楼为最佳答案
- Sub TJ()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = Range("A2").CurrentRegion
- For i = 2 To UBound(arr)
- x = arr(i, 1)
- If arr(i, 5) * arr(i, 6) > 0 Then
- d(x) = d(x) + 1
- d1(x) = d1(x) + arr(i, 5)
- End If
- Next
- With Sheet2
- .Cells.Clear
- .[a2].Resize(1, 4) = Array("序号", "公司", "授信次数", "授信额度")
- .[b3].Resize(d.Count, 3) = Application.Transpose(Array(d.keys, d.items, d1.items))
- .[b3].Resize(d.Count, 3).Sort key1:=.[b3] '按公司名称排序
- For i = 1 To d.Count: .Cells(i + 2, 1) = i: Next
- .Cells(i + 2, 2) = "汇总"
- .Cells(i + 2, 3).Resize(, 2).Formula = "=sum(r3c:r[-1]c)"
- .Activate
- End With
- MsgBox "统计完成!"
- End Sub
复制代码 |
|