VBAで予算内訳を部門、実績内訳を費目にしたグラフを自動作成

会社の経費データから毎月の予算・実績を比較した棒グラフをVBAで自動作成するサンプルコードです。予算と実績の棒グラフ内訳は、予算は部門別に、実績は費目別にしています。

例えば、予算内訳は年間を通じて各部門の申請値をガイドラインとして予算キープしながら、実績は会社全体として何に消費したのかを毎月確認するような場合に使えます。

まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画をご確認ください。

予実内訳が異なるVBA集計サンプルファイル

本記事のサンプルファイルの内容を紹介します。

97-2003形式は↓からダウンロードできます。

サンプルファイルには3枚のシートがあります。それぞれのシート名は、「集計対象データ」、「グラフ用集計表」、「グラフ」（チャート）です。

このうち「グラフ用集計表」と「グラフ」シートには、既に実績用の内訳「人件費」、「設備費」、「部門費」が表示されています。

予算を部門別、実績を費目別に分けて表示させるVBAサンプルコード

「グラフ用集計表」に集計対象レコードのデータを集計させるVBAサンプルコードをご紹介します。ぜひ一度真似て書き写してみてくださいね。

VBAで予算と実績を異なる内訳に分けて集計するアルゴリズム

9行目： Dim wstData As Worksheet, lngERow As Long, r As Long
「集計対象データ」シートをワークシートオブジェクト変数「wstData」で扱うための宣言をしています。また、wstData上で取り扱うレコード最終行番号を格納するための変数「lngERow」と繰り返し処理用変数「r」も同じ行で宣言しています。

12行目： Dim wstGrph As Worksheet, lngWRow As Long, lngWCol As Long
「グラフ用集計表」シートを ワークシートオブジェクト変数 「wstGrph」で扱うための宣言です。また、「グラフ用集計表」への書込み行用の変数「lngWRow」と書込み列用の変数「lngECol」も同じ行で宣言しています。

15行目： Set wstData = Worksheets("集計対象データ")
16行目： Set wstGrph = Worksheets("グラフ用集計表")
宣言済みの2つの ワークシートオブジェクト変数 に、「集計対象データ」シートと「グラフ用集計表」を設定しています。

VBAコード18～20行目は、「グラフ用集計表」の集計範囲（ 下図赤枠 ）をクリアしています。なおクリアする範囲をデータが集計される範囲より若干広く取っていますが、特別な意味はありません。

23行目： lngERow = .Range("A" & .Rows.Count).End(xlUp).Row
「集計対象データ」シートの最終レコードの行番号を取得しています。

集計対象レコードの繰り返し処理アルゴリズム

26～57行目： For r = 2 To lngERow ～ Next
すべての集計対象レコードに対して、このFor文内で1レコードずつ繰り返し処理しています。内容については、これ以降図を織り交ぜながら詳しくお話ししますね。

繰り返し処理の内容は、大きく分けて2つあります。

1つ目は、VBAコード27～53行目のIf ～ End If文内で各レコードの「予実区分」であるCells(r, 4)を判定し、「グラフ用集計表」への書込み行番号と書込み列番号を決めています。

VBAコード27行目の If文でレコードの予実区分が「予算」であった場合は図の赤枠の処理を行います。一方、レコードの予実区分が「実績」であった場合は、必然的にVBAコード41行目の判定結果がTrueになりますので、VBAコード43行目以降の青枠内の処理が実行されます。

ここで、VBAコード41行目は「実績」の場合でしかないはずですので、代わりに「Else」を利用しても問題ありません。今回は敢えて明示的に「ElseIf ～ Then」を使いました。

2つ目は、決まった書込み行番号と書込み列番号に相当する「グラフ用集計表」上のセルに加算集計します（図の緑枠）

では次以降、さらに詳しく見ていきますね。

VBAコード27行目が真（「予実区分」が「予算」のとき）の場合

レコードの「予実区分」の値が「予算」であった場合の処理について解説します。

「予実区分」が「予算」のときの書込み行番号

まずVBAコード29行目で集計対象レコードの「部門」データ（Cells(r, 4))を判定します。その次以降のCase文の分岐条件により（赤字）、書込み行番号lngWRowが決まります（青字）。

「予実区分」が「予算」のときの書込み列番号

VBAコード39行目で、「予実区分」が「予算」の場合の書込み列を決めています。図中赤字で示した「3」は、会計月が1月のときの書込み列番号です。これを初項として、会計月が2月、3月、4月、・・・になると書込み列番号は、それぞれ7, 11, 15, ・・・と4づつ増えます（「グラフ用集計表」シート上の緑枠と青枠の関係）。

これは、初項「3」の等差数列の式 3 + 4(n-1)そのものになります。

Tips
書込み列番号が等差数列になる仕組みを詳しく解説した記事

VBAコード27行目が偽（「予実区分」が「実績」のとき）の場合

つづいて、レコードの「予実区分」が「実績」であった場合の処理です。

「予実区分」が「実績」のときの書込み行番号

VBAコード44行目で、集計対象レコードの「費目」データを判定（赤字）しています。その値に応じて、「グラフ用集計表」への書込み行番号を決定（青字）します。

「予実区分」が「実績」のときの書込み列番号

VBAコード51行目で書込み列番号を決めています。図の赤文字「4」は、1月実績の列番号であり、2月以降は4の倍数で列番号が増えていきます。予算列と実績列は隣合わせですので、先ほどのVBAコード39行目の等差数列式の初項を+1ずらすことで、「実績」列を算出する式ができます。

「グラフ用集計表」シートへの書込み

56行目：  wstGrph.Cells(lngWRow, lngWCol) = wstGrph.Cells(lngWRow, lngWCol) + .Cells(r, 6)
繰り返し処理の最後に、決定した書込み行番号と書込み列番号に応じた グラフ用集計表上のセルへ「金額」を加算集計しています。

まとめ

本記事では、会社の会計データから、各部の予算申請値と会社全体の経費別実績値を月ごとに比較できる月次レポートをVBAで自動作成しました。ご紹介したサンプルコードを応用することで、予算と実績内訳が異なる棒グラフを作成できます。

例えば、筆者の実務経験ですが、予算は各部申請値の内訳、実績はオーダ毎に内訳を作成したことがありました。とはいえあまりニーズのないケースかもしれませんが、実務では会社の事情により色々な形式のグラフを作成することがありますよね。

そのような状況のなかで、今回ご紹介したグラフ用集計表への集計スキルは、アイディア次第では色々なグラフを作成する際のヒントになると思いますので、ぜひ参考にしてみてくださいね。