部門別に各費目内訳があり、さらに各費目ごとに予算実績内訳がある3段行見出しのクロス集計です。会計データから月別の経費実績を、別シートに集計させます。
実務ではSAPなどのシステムから抽出する会計データを、Excelで加工した上で、表やグラフにまとめて月次や週次のレポートにすることがあります。
とくに会計データの中にある「予算」と「実績」を利用し、期首計画（年度予算）に対する毎月の消費実績をトラッキングする表やグラフをよく見ますよね。
本記事では、そのトラッキング資料をVBAで自動作成するために役立つスキルを、基本的なVBAサンプルコードを通してご紹介します。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画をご確認ください。
目次
VBAで部門別に費目内訳を作り、さらに予実別に集計するサンプルファイル
今回ご紹介する表は、行見出しが「部門別」、「費目別」、「予実区分」の3段になっています。
2枚目のサンプルシートには、「部門」、「経費費目」、「予実区分」の3つの行見出しがあります。また「予算」と「実績」の差異を計算させるため、式を入力した行を追加しております。
実務であれば、さらに各部門の合計についても「予算」、「実績」、「差異」欄を作るべきですが、まずは簡単なサンプルとしてお話ししたいため、今回はあえて設けておりません。
VBAで部門別の費目内訳別に「予算」と「実績」を集計させる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 47 48 49 50 51 52 53 54 55 56 57 58 59 | '各部門費目別に予実を分けてさらに差異を集計するサンプルコード Sub 部門別費目別予実別の月ごとのクロス集計サンプルコード() Application.ScreenUpdating = False '画面更新オフ 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 For r = 2 To 44 Step 3 .Range(.Cells(r, 4), .Cells(r + 1, 15)).ClearContents Next End With Set wstData = Worksheets("集計データ") With wstData 'すべての集計対象データを繰り返し処理 For r = 2 To .Range("A" & .Rows.Count).End(xlUp).Row '「部門」による書込み基準行取得 Select Case .Cells(r, 4) Case "人事部": lngWRow = 2 Case "開発部": lngWRow = 11 Case "生産部": lngWRow = 20 Case "品質部": lngWRow = 29 Case "営業部": lngWRow = 38 End Select '費目別による書込み基準行のオフセット Select Case .Cells(r, 5) Case "人件費": lngWRow = lngWRow + 0 Case "部門費": lngWRow = lngWRow + 3 Case "設備費": lngWRow = lngWRow + 6 End Select '実績の場合は書込み基準行に1追加 If .Cells(r, 7) = "実績" Then lngWRow = lngWRow + 1 End If '書込み列取得 lngWCol = .Cells(r, 3) + 3 '転記 wstSelf.Cells(lngWRow, lngWCol) = wstSelf.Cells(lngWRow, lngWCol) + .Cells(r, 6) Next End With Application.Calculation = xlCalculationAutomatic End Sub |
では書き写しましたらVBAコードを実行してみてください。VBA実行結果は以下のようになります。
VBAで各部門の費目内訳別に予算と実績を集計させるサンプルコードのアルゴリズム
17～21行目： With wstSelf ～ End With
2枚目サンプルシートのセル範囲（下図赤枠）を初期設定のためクリアします。今回クリアする範囲は、一続きの範囲ではなく、「差異」の行を挟んでいるため飛び飛びになっていますよね。
そこで、VBAコード18～20行目では、飛び飛びになっている範囲ごとにクリアするため、For文で繰り返しながらクリア処理をしています。
具体的には、まず繰り返し処理する開始行は「2」です。そして、Stepを使って3つ飛び（青字）の行ごと（緑枠）に、「.Range(.Cells(r, 4), .Cells(r+1, 15))」（赤枠）の範囲を「.ClearContents」メソッドを使いクリアしていきます。
VBAで表の部門別の費目内訳ごとに予実を分けるアルゴリズム
VBAコード29～47行目は、表への書込み行を決めています。その書込み行を決めるプロセスは、大きく分けて各行見出しの数の分だけ3つあります。
図はその3つをサンプルシート上に色分けして示したものです。
最初の「部門」による基準行を決めるVBAコードが赤枠。次に「経費費目」の値によるオフセット量を決めているVBAコードが青枠。最後に「予実区分」が「実績」値であった場合にオフセット+1を実行しているVBAコードが緑枠です。
つぎから、さらに1つずつ見ていきましょう。
「部門」により書込み行基準行を決めるVBAアルゴリズム
最初に集計対象レコードの「部門」の値（.Cells(r, 4))を使い、集計先の書込み基準となる行を決めています。下の例では、はじめの3部について、色分けで説明しています。
ここで、Select文の条件「.Cells(r,4)」は、集計対象レコードの「部門」の値です。その値が「人事部」であった場合、サンプルシート上の書込み基準行は「2」（赤枠）になります。また「開発部」では「11」（青枠）、「生産部」では「20」（緑枠）になります。
既にお気づきかもしれませんが、これらの基準行の間隔は「9」ずつになります。この行番号が等間隔になることがポイントです。ほかの2部門についても、この間隔をパターンとして考えた値になりますので、サンプルシート上で確認してみてくださいね。
「経費費目」により書込み基準行からのオフセット量を決めるVBAアルゴリズム
「部門」ごとに書込み基準行を決めた次は、「経費費目」により基準行からのオフセット行数を決めます。
上図に「経費費目」によりオフセット量が決まる様子を表してみました。まず、部門によって決まる書込み基準行は、サンプルシート上でいう赤枠の行番号になることは前項で説明しました。この赤枠は、同時に各「人件費」の表示範囲の先頭行に位置しますよね。
つまり、経費費目が「人件費」の場合は、基準行と同じ行になりますので、図のVBAコード39行目にある通り、オフセット行数は「0」になります。
次の「部門費」の各先頭行については、書込み基準行=「人件費」の行番号（赤枠）に対し、3行足した行番号になりますので、VBAコード40行目にある通り、オフセット行数を「3」にしています。
最後の「設備費」の各先頭行については、 書込み基準行=「人件費」の行番号（赤枠）に対し、 6行足した行番号になります。よって、VBAコード41行目で、オフセット行数を「6」としています。
予実区分が「実績」の場合は、さらに1オフセッツさせるVBAアルゴリズム
ここまで「部門」、「経費費目」の各値によって、サンプルシート上の書込み行を決めてきました。下図でいうと、ここまでは赤枠で囲ったいずれかの行番号になります。
サンプルシートをよく見ると、すべて「予算」が集計される行になってますよね（赤枠）。そこで、最後に、VBAコード45行目により、集計対象データの「予実区分」の値を読み取ります。もし「予実区分」の値が「実績」であれば 、それまでの書込み行に対してさらに1を加えます（VBAコード46行目）。
そうすると、実績値がきちんと「実績」の行に集計されるようになります。
以上、ここまでが書込み行を判定するまでのVBAコードのアルゴリズムの解説になります。ポイントは、3段の行見出し「部門」、「経費費目」、「予実区分」ごとに分けて、書込み行を判定することです。
VBAで会計月に応じて集計させるアルゴリズム
51行目： lngWCol = .Cells(r, 3) + 3
何月に集計させるかを決めるための書込み列を決めるVBAコードになります。
サンプルシートの列番号（赤枠）は、すぐ下の会計月（青枠）の数値に3を加えた関係にありますよね。よって、右辺では集計対象レコードの会計月「.Cells(r, 3)」に3を加えて、左辺の書込み列の変数「lngWCol」に代入しています。
54行目： wstSelf.Cells(lngWRow, lngWCol) = wstSelf.Cells(lngWRow, lngWCol) + .Cells(r, 6)
最後は、決定した書込み行「lngWRow」と書込み列「lngWCol」に応じたクロス集計上のセル「.Cells(lngWRow, lngWCol)」に、加算集計していくだけです。
まとめ
今回ご紹介した3段からなる見出し行と月別に集計できる表は、これまでご紹介してきた表と同様、まず「書込み行」と「書込み列」に分けて考えます。
さらに「書込み行」では行見出し分の数に分けて考えることで、順番に書込み行を判定しながら、最終的な書込み行を決めました。こうすることにより、一見複雑そうな表でも意外と簡単なVBAコードでまとめることができます。
またこの考え方は、ほとんどの表で応用できますので、ぜひマスターしてくださいね。