Excel VBA 「カー計簿」集計処理
Excel VBA 「カー計簿」集計処理
予め分類をキーに昇順に並び替えをおこないます。その後、分類をキーに集計処理をおこないます。
Excel VBA 「カー計簿」集計処理のイメージ
抽出した期間から分類別に集計を行います。この表からどうした分類に出費が多いかの参考になります。
Excel VBA 「カー計簿」ソースリスト
以下が集計処理のソースリストになります。コントロールブレイクともいいます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
'''''''集計＆プリント ''' Temp2: A:No B:N-No C:入力 D:Date E:Bunrui F:Koumoku G:Biko H:Kingaku ''' SyuKei:A:Bunrui B:Date C:Koumoku D:No E:yyyy/mm F:Kingaku G:Kei '''' For j = 2 To wk_Line '''J:送り側Row I:受け側Row '''集計処理 E1:分類名,D1:日付,H1:金額 ==>Syukei Wk_SoGoKei = Wk_SoGoKei + Sh_Temp2.Cells(j, "H").Value '金額集計(SoGoKei) If j = 2 Then Wk_TsuKi_Kei = Wk_TsuKi_Kei + Sh_Temp2.Cells(j, "H").Value '金額集計(TsukiKei) Wk_Bunrui_Kei = Wk_Bunrui_Kei + Sh_Temp2.Cells(j, "H").Value '金額集計(BunruiKei) Wk_BunruiLine = 3 Wk_BuLine = 3 SH_Syukei.Cells(i, "A") = Sh_Temp2.Cells(j, "E").Value '''分類セット Wk_Tsuki = TsuKiEDT(Sh_Temp2.Cells(j, "D").Value) SH_Syukei.Cells(i, "B") = Wk_Tsuki '''年月セット(YYYY/m) Wk_Bunrui = Sh_Temp2.Cells(j, "E").Value Wk_No = Sh_Temp2.Cells(j, "A").Value Else If Wk_Bunrui = Sh_Temp2.Cells(j, "E").Value Then Wk_Bunrui_Kei = Wk_Bunrui_Kei + Sh_Temp2.Cells(j, "H").Value '分類集計 If Wk_Tsuki = TsuKiEDT(Sh_Temp2.Cells(j, "D").Value) Then Wk_TsuKi_Kei = Wk_TsuKi_Kei + Sh_Temp2.Cells(j, "H").Value '月集計 Wk_No = Wk_No & "," & Sh_Temp2.Cells(j, "A").Value Else i = i + 1 '月ブレイク処理 wk_Tsuki_Cnt = wk_Tsuki_Cnt + 1 Set W_Border = SH_Syukei.Range("E" & CStr(i) & ":H" & CStr(i)).Borders(xlEdgeTop) W_Border.LineStyle = xlContinuous W_Border.LineStyle = xlDash SH_Syukei.Cells(Wk_BuLine, "D") = "No" & Wk_No SH_Syukei.Cells(Wk_BuLine, "B") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "E") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "F") = Wk_TsuKi_Kei SH_Syukei.Cells(Wk_BuLine, "G") = Sh_Temp2.Cells(j, "I").Value Wk_TsuKi_Kei = Sh_Temp2.Cells(j, "H").Value Wk_Tsuki = TsuKiEDT(Sh_Temp2.Cells(j, "D").Value) Wk_BuLine = i Wk_No = Sh_Temp2.Cells(j, "A").Value End If Else i = i + 1 '分類ブレイク処理 SH_Syukei.Cells(Wk_BuLine, "D") = "No" & Wk_No SH_Syukei.Cells(Wk_BuLine, "B") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "E") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "F") = Wk_TsuKi_Kei SH_Syukei.Cells(Wk_BuLine, "G") = Wk_Bunrui_Kei SH_Syukei.Cells(Wk_BuLine, "H") = (Wk_Bunrui_Kei / W_SouGouKei) * 100 Wk_Tsuki = TsuKiEDT(Sh_Temp2.Cells(j, "D").Value) Wk_TsuKi_Kei = Sh_Temp2.Cells(j, "H").Value wk_Tsuki_Cnt = wk_Tsuki_Cnt + 1 Wk_BuLine = i Wen_i = Wen_i + 1 SH_Syukei.Cells(Wen_i, "J") = Wk_Bunrui SH_Syukei.Cells(Wen_i, "K") = Wk_Bunrui_Kei With SH_Syukei.Range("A" & CStr(i) & ":H" & CStr(i)) '''罫線設定 .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeTop).Weight = xlThin End With Wk_Bunrui = Sh_Temp2.Cells(j, "E").Value SH_Syukei.Cells(i, "A") = Sh_Temp2.Cells(j, "E").Value Wk_No = Sh_Temp2.Cells(j, "A").Value Wk_Bunrui_Kei = Sh_Temp2.Cells(j, "H").Value wk_Tsuki_Cnt = 0 Wk_BunruiLine = i End If End If wk_i = i Next j i = i + 1 '分類最終出力処理 SH_Syukei.Cells(Wk_BuLine, "D") = "No" & Wk_No SH_Syukei.Cells(Wk_BuLine, "B") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "E") = Wk_Tsuki SH_Syukei.Cells(Wk_BuLine, "F") = Wk_TsuKi_Kei SH_Syukei.Cells(Wk_BuLine, "G") = Wk_Bunrui_Kei SH_Syukei.Cells(Wk_BuLine, "H") = (Wk_Bunrui_Kei / W_SouGouKei) * 100 Wen_i = Wen_i + 1 SH_Syukei.Cells(Wen_i, "J") = Wk_Bunrui SH_Syukei.Cells(Wen_i, "K") = Wk_Bunrui_Kei Wk_Tsuki = TsuKiEDT(Sh_Temp2.Cells(j, "D").Value) Wk_TsuKi_Kei = Sh_Temp2.Cells(j, "H").Value wk_Tsuki_Cnt = wk_Tsuki_Cnt + 1 Wk_BuLine = i With SH_Syukei.Range("A" & CStr(i) & ":H" & CStr(i)) .Borders(xlEdgeTop).LineStyle = xlContinuous '''.Borders(xlEdgeTop).Weight = xlMedium .Borders(xlEdgeTop).Weight = xlThin End With Wk_Bunrui = Sh_Temp2.Cells(j, "E").Value SH_Syukei.Cells(i, "A") = Sh_Temp2.Cells(j, "E").Value Wk_Bunrui_Kei = Sh_Temp2.Cells(j, "H").Value '分類ブレイク後の最後のセット wk_Tsuki_Cnt = 0 Wk_BunruiLine = i SH_Syukei.Cells(Wk_BunruiLine, "F") = Wk_SoGoKei |
ちょっと長いソースリストですが、要点は分類をキーに集計し出力するものです。
