|
发表于 2016-4-21 10:52
|
显示全部楼层
本楼为最佳答案
- Sub 统计()
- x = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
- arr = Sheet1.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- ReDim brr(1 To UBound(arr), 1 To 26)
- For i = 3 To UBound(arr)
- xm = arr(i, 5) '项目
- yf = Month(arr(i, 2))
- c = InStr(x, xm) + 2 '项目在brr中对应的列
- brr(i - 2, 1) = arr(i, 2)
- brr(i - 2, 2) = arr(i, 3)
- brr(i - 2, c) = arr(i, 6)
- xkey = xm & yf & "月合计"
- d(xkey) = d(xkey) + arr(i, 6)
- Next
-
- With Sheet2
- .[a4:z1000] = ""
- .[a4].Resize(i - 2, 26) = brr
- End With
-
- With Sheet3
- .[b4:k18] = ""
- arr = .[a3:k18]
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- xkey = arr(i, 1) & arr(1, j)
- arr(i, j) = d(xkey)
- Next
- Next
- .[a3:k18] = arr
- End With
- End Sub
复制代码 |
|