|
发表于 2015-12-30 14:25
|
显示全部楼层
本楼为最佳答案
- Sub 汇总()
- Set d = CreateObject("scripting.dictionary")
- For k = 1 To 2
- nf = Val(Sheets(k).Name) '年份
- arr = Sheets(k).[a1].CurrentRegion
- For i = 2 To UBound(arr)
- sf = arr(i, 14) '省份
- x = sf & nf & "总件数": d(x) = d(x) + arr(i, 6) '省份+年份+总件数
- x = sf & nf & "总金额": d(x) = d(x) + arr(i, 7) '省份+年份+总金额
- x = sf & nf & "不重复人数" '省份+年份+不重复人数
- y = sf & nf & arr(i, 2) '省份+年份+客户
- If Not d.exists(y) Then
- d(y) = ""
- d(x) = d(x) + 1
- End If
- Next
- Next
- With Sheets(3)
- .[b2:p1000].ClearContents
- r = .[a65536].End(3).Row
- arr = .Range("a1:q" & r)
- krr = Array(2, 4, 6, 7, 9, 11)
- For jj = 0 To UBound(krr)
- j = krr(jj)
- For i = 2 To r - 1
- x = arr(i, 1) & arr(1, j)
- arr(i, j) = d(x) '各年各省件数、金额、人数
- arr(r, j) = arr(r, j) + d(x) '各省各年总件数、总金额、总人数
- Next
- Next
- For i = 2 To r
- For j = 2 To UBound(arr, 2) - 1
- If j = 3 Or j = 5 Or j = 8 Or j = 10 Then
- If arr(i, j - 1) > 0 Then arr(i, j) = arr(i, j - 1) / arr(r, j - 1) '占比
- ElseIf j = 12 Or j = 14 Or j = 16 Then
- x = arr(i, j - 5) - arr(i, j - 10)
- If x <> 0 Then
- arr(i, j) = x '增减
- If arr(i, j - 10) > 0 Then arr(i, j + 1) = x / arr(i, j - 10) '增减率
- End If
- End If
- Next
- Next
- .Range("a1:p" & r) = arr
- End With
- End Sub
复制代码 |
|