- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 142
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2026-7-16
                
|
回復 1# sunnyso - Sub ex()
- Dim ap(11, 17), s#, i&, j%, k%, cnt#
- s = Timer
- Set dic = CreateObject("Scripting.Dictionary") '月
- ay = Array("1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月", "", "1季", "2季", "3季", "4季")
- ak = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
- With Sheets("原始資料")
- ar = .Range("A1").CurrentRegion.Value
- For i = 2 To UBound(ar, 1)
- dic(ar(i, 1) & Month(ar(i, 2)) & "月") = dic(ar(i, 1) & Month(ar(i, 2)) & "月") + ar(i, 3) '月
- dic(ar(i, 1)) = dic(ar(i, 1)) + ar(i, 3) '累計
- dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "季") = dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "季") + ar(i, 3)
- Next
- End With
- For j = 0 To 16
- cnt = 0
- For k = 0 To 9
- ap(k, j) = dic(ak(k) & ay(j))
- cnt = cnt + dic(ak(k) & ay(j))
- Next
- ap(10, j) = cnt
- Next
- Sheets("總表").[B4].Resize(11, 17) = ap
- MsgBox Timer - s
- End Sub
|
|
發表於 2013-6-1 23:22
| 