VBAで月次予算実績推移の棒グラフを部門別内訳にして作成する

VBAで集計対象データをグラフ用集計表へ集計させ、月次予算実績報告グラフを作成します。月次予算実績報告グラフは、棒グラフで月ごとの予算と実績を比較でき、さらに予実の棒グラフはそれぞれ部門ごとに内訳表示させます。

実務によくある月次報告レポートであれば、今回ご紹介するサンプルコードを一度VBAで組んでしまえば、あとは毎月データを入れ替えるだけで一瞬にして月次報告用のレポートを作成することができます。

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

集計対象データ、グラフ用集計表、グラフ付きサンプルファイル

今回使用するサンプルファイルになります。

サンプルファイルには3枚のシートがあります。1枚目「集計対象データ」シートには、約8000レコードの集計対象データがあります。2枚目「グラフ用集計表」シートは、グラフ表示のためにレイアウトされた集計表であり、一般的にプレゼンなどで人に見せるような体裁のよい表ではありません。

3枚目は月次予算実績報告グラフが表示されるチャートになります。これから紹介するVBAサンプルコードを実行することで、グラフ用集計表に集計値が入ることでグラフが表示されます。

VBAで集計対象データをグラフ用集計表に集計させるサンプルコード

では今回のサンプルコードになります。ぜひ書き写してみてくださいね。

今回はグラフ表示させるためのグラフ用集計表に、集計対象レコードを集計させるのですが、集計のアルゴリズムについては別シートへの集計テクニックと同じになります。

VBA実行結果

ではサンプルコードを実行してみましょう。実行後のグラフ用集計表とチャートシートは下図のようになります。

グラフを確認しますと、月ごとに予算と実績の棒グラフが横並びで比較されています。そして予実それぞれの棒グラフの内訳は、5部門の内訳表示になっています。

今回の集計対象データは、実務のイメージに近づけたいため、あえて7月までの実績しか準備しませんでした。

もし毎月報告するグラフであれば、ご紹介したサンプルコードを1度組んでしまえば、あとは「集計対象データ」シート上にあるデータを入れ替えたり、追加したりした後、VBAを実行するだけで一瞬にしてグラフが出来上がります。

集計対象データからグラフ用集計表へ転記させるサンプルコードのアルゴリズム

それではVBAコードのアルゴリズムについてのお話しです。

9行目： Dim wstGrph As Worksheet, lngWRow As Long, lngWCol As Long
「グラフ用集計表」シートに関する変数の宣言です。「グラフ用集計表」に集計データを書き込みますので、この行にまとめて「lngWRow」と「lngWCol」を記述しました。

12行目： Set wstData = Worksheets("集計対象データ")
13行目： Set wstGrph = Worksheets("グラフ用集計表")
集計対象データと集計先の表は別シートになりますので、今回は2枚のシートをワークシート名を使って管理します。

23～44行目： For r = 2 To lngERow ～ Next
すべての集計対象レコードを繰り返し処理するVBAコードになります。

処理の中身は大きく分けて「【】」を付けた3つの手順になります。【書込み行】の決定後、【書込み列】を決めます。最後に、決定した書込み行列番号に相当するセルに【書込み】を行います。

それでは、これより【 書込み行 】⇒【 書込み列 】⇒【 書込み 】の順を追って解説を進めていきますね。

書込み行判定アルゴリズム

まずはグラフ用集計表への書込み行を決定するアルゴリズムです。下図は、別シート間でデータを集計させるVBAのアルゴリズムを説明したものです。

VBAコード25行目のSelect文の判定基準に「集計対象データ」シート上の4列目のデータを指定しています(緑枠）。

この4列目のデータは、各レコードの部門データが入っていますので、Select文の分岐条件となるCase以降の文字列データは、各部門名称になります。そして、分岐条件によって、グラフ用集計表への書込み行番号を格納する変数「lngWRow」の値が決まります。

図赤枠に示す通り「lngWRow」は、「グラフ用集計表」シートの各部門のExcelシート行番号に対応しています。

書込み列判定アルゴリズム

続いて書込み列のアルゴリズムです。書込み列を決めるのは、VBAコード36行目とVBAコード39行目です。手順としてはVBAコード36行目で、予算列を確定します。

そしてVBAコード39行目で、集計対象レコードが実績であれば列を+1ずらす処理をしています。では、これら2行の処理を前半と後半に分けて詳しく解説していきますね。

書込み列番号（予算列）を決めるアルゴリズム

前半はVBAコード36行目で、グラフ用集計表の予算欄の列番号を決めています。下図のように、集計対象データの3列目である会計月データを読み取り（緑枠）、それとグラフ用集計表の予算欄の列の並びの規則性を考慮し計算で求めています。

計算式は高校数学に出てくる等差数列の公式そのものです。まずは、赤文字の3ですが、期首である1月の予算列の行番号になります。これを初項として予算列は、+4置きに並んでいるのがわかります（青字）。

つまり、グラフ用集計表の予算列は、3を初項とする4の倍数を加算して得られることになります。

集計対象レコードの予実区分が「実績」だったら、さらに書込み列番号に+1する

39行目： If .Cells(r, 7) = "実績" Then lngWCol = lngWCol + 1
最終的な書込み列番号は、集計対象レコード7列目のデータである「予実区分」を確認することで決定します。

VBAコード39行目のIf文の条件式「.Cells(r, 7)="実績"」（緑文字）により、集計対象データの7列目「予算区分」データが「実績」であれば(True）Then以降を実行します。Then以降の実行内容は、VBAコード36行目で求めた「予算列」番号が格納されたlngWColに+1して、再びlngWColに再代入しています。

グラフ用集計表への書込みコード

43行目： wstGrph.Cells(lngWRow, lngWCol) = wstGrph.Cells(lngWRow, lngWCol) + .Cells(r, 6)
最後は、決定したlngWRowとlngWColに対応するセルCell(lngWRow, longWCol）に、各レコードの6列目のデータ「金額」を加算集計していきます。

まとめ

本記事では、ある会社の経費予算実績の月次報告グラフを、VBAで作成するサンプルコードをご紹介しました。グラフは毎月の予算と実績を月ごとで比較できる棒グラフにまとめました。

さらに棒グラフの内訳を部門ごとにしましたので、視覚的にどこの部門が予算超過しているかなど簡単に確認できます。

今回のレコード数は8000くらいでしたが、ご紹介した短いコードにより集計作業が一瞬にして完了しますので、とくに定例業務でグラフ付き報告書などを作る際は、ご紹介したサンプルコードを応用して利用されることをおすすめします。