会計データから、部門別に月ごとの経費消費実績をクロス集計させます。作成するクロス集計表は、会計データとは別シートに作成されます。
基幹システムから得た会計データに対して、会計月ごとに部門別の実績を表にして分析することがあります。さらに、その表を元に毎月の消費推移をグラフで報告することもあります。
そのような定例業務の場合、一度VBAコードを組んでおけば、毎月のレポート作成作業にかかる時間が大幅に短縮できます。今回は、会計データから部別に月ごとの消費実績を一覧表にまとめるVBAコードをご紹介します。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画（音声無し）をご確認ください。
目次
VBAで会計データの消費実績を部門別、月ごとに集計させるサンプルファイル
まずは今回使用するサンプルファイルのシート構成をご紹介します。サンプルシートは、会計データがある1枚目のシートと、経費の消費実績を月別に表示するための集計表がある2枚目から構成されています。
表は、行見出しに「部門」を、列見出しに「各月」を配置しています。また、行と列の最後に合計欄と、右下に総合計のセルをそれぞれ「Sum関数」を入力して配置しています。
Tips
Excelシートの列をA1参照形式からR1C1参照形式に変える方法
VBAで会計データを部門別に月ごとに集計させるサンプルコード
では次に今回のVBAサンプルコードをご紹介します。ぜひまねして書いてみてください。また時間のない場合は、サンプルファイルに標準モジュールを挿入してから、下記サンプルコードを標準モジュールにコピペして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 | '部門別に月ごとの経費を集計する Sub 部門別月別サンプルコード() Application.ScreenUpdating = False '画面更新Off Application.Calculation = xlCalculationManual '手動計算モード Dim wstSelf As Worksheet '表側シート用 Dim wstData As Worksheet '会計データシート用 Dim lngWRow As Long '書込み行 Dim lngWCol As Long '書込み列 Dim r As Integer Set wstSelf = Worksheets("部門別月別クロス集計") '結果表示範囲初期化 With wstSelf .Range(.Cells(2, 2), .Cells(6, 13)).ClearContents End With Set wstData = Worksheets("集計データ") '会計レコード繰り返し処理 With wstData For r = 2 To .Range("A" & .Rows.Count).End(xlUp).Row If .Cells(r, 7) = "実績" Then '実績のみ抽出 '書込み行取得 Select Case .Cells(r, 4) Case "人事部": lngWRow = 2 Case "開発部": lngWRow = 3 Case "生産部": lngWRow = 4 Case "品質部": lngWRow = 5 Case "営業部": lngWRow = 6 End Select '書込み列取得 lngWCol = .Cells(r, 3) + 1 '転記 wstSelf.Cells(lngWRow, lngWCol) = wstSelf.Cells(lngWRow, lngWCol) + .Cells(r, 6) End If Next End With Application.Calculation = xlCalculationAutomatic '自動計算モード End Sub |
実行結果は以下の通りになります。
VBAで月別に各部門ごとの経費を集計させるサンプルコードのアルゴリズム
17～19行目： With wstSelf ～ End With
表のセル範囲をクリアするためのコードです（下図）。
VBAコード28～34行目は、結果を表示する集計表上の書込み行番号を決めるVBAコードになります。まず、VBAコード28行目のSelect文の条件に、会計データの4列目のデータ「部門」を指定しています。この.Cells(r, 4)の値に対応する書込み行「lngWRow」が、VBAコード29～33行目によって決まります。
表の赤枠内のサンプルシートの行番号と部門名の関係が、Select文の中の29～33行目の分岐条件に対応してますよね。
VBAコードで月別に集計させるためのアルゴリズム
37行目： lngWCol = .Cells(r, 3) + 1
表の書込み列番号は、月別に対応してます。このVBAコードにより、書込み列「lngWCol」は、「会計月」である.Cells(r,3)に1を加えた値になります（下図）。
赤字は会計データにある「会計月」の値です。青字は、表への書込み列番号です。VBAコード37行目は、右辺で「会計月」に1を足した値を書込み列の変数「lngWCol」に代入しています。
例えば、先頭レコードの「会計月」を見ると10ですよね。その10に1を加えた値が「lngWCol」になります。サンプルシート上の表で確認すると、lngWCol = 11は、確かに10月に対する書込み列番号になってますよね。
つまり、この表への書込み列番号は、会計月に1を足した関係にあります。表の列番号（青枠）と会計月（赤枠）の関係を見るとよくわかります。
VBAコード40行目は、決定した書込み行と書込み列に対応したセル「.Cells(lngWRow, lngWCol）」に会計データの「金額」を加算しています。こちらのアルゴリズムについては、以下で詳しく解説しております。
まとめ
会計データは、ふつう会計年や会計月の項目を持っていますが、今回はある会計年の月別の経費を部署別に集計するサンプルをご紹介しました。ご紹介したサンプルコードを応用すれば、毎月報告するレポートをVBAコードで素早く集計できます。