サイト内検索

別シート 転記する

VBAで年間予算実績トラッキンググラフと四半期レポートを作成

年間の予算消費状況を月次でトラッキングできるExcelグラフと、プレゼン用として四半期ごとにまとめた各部門の予実差異レポートを同時に作成するVBAサンプルコードを詳しくご紹介します。

今回は集計対象データから、2種類以上のグラフや表を一度に作成する場合のヒントになるVBAサンプルコードをご紹介します。

サンプルコードを応用すれば、2種類以上のグラフや表でも効率的に作成できますので、実務でも1つの集計対象レコードから、複数の定期レポートを月次や週次で作成する場合にも応用できます。

本記事でご紹介するVBAサンプルコード何ができるかを、下記1分動画(音声無し)でも確認できます。

予算実績トラッキンググラフと四半期レポートのExcelサンプルファイル

本記事で使用するサンプルファイルの中にあるExcelシートをご紹介します。なお、サンプルファイルのダウンロードはシートの紹介の後にあります。

集計データシート

集計対象になる生データを収めたシートです。レコード数は10000レコードあります。項目は「伝票ID」、「会計年」、「会計月」、「部門」、「費目」、「金額」、「予実区分」です。

集計対象データ(10000データ分)
集計対象データ(10000データ分)

会計年は2018年です。また予算は1~12月までありますが、実績は意図的に7月までにしています。部門は、人事部、開発部、生産部、品質部、営業部の5部です。また費目の種類は人件費、部門費、設備費です。

グラフ用シート(Chartシート)

年間の累積予算と累積実績をグラフ上で視覚的に確認できるシートです。実績は棒グラフで表示され、内訳を費目別に表示します。また予算は折れ線グラフで1月から12月まで表示されます。

グラフ表示用シート
グラフ表示用シート

グラフ表示用集計シート

グラフの元になるデータを集計するためのシートです。Excelシート2~4行目に費目ごとの実績値を集計します。また、Excelシート5行目には予算値を集計します。

グラフ表示用集計シート
グラフ表示用集計シート

プレゼン用集計表シート

プレゼン用に見栄えを良くした集計表です。集計対象データを部門別、四半期ごとの予算・実績に分けて集計した上、差異やTotal(合計値)を算出表示します。

プレゼン用集計シート
プレゼン用集計シート

ダウンロード用サンプルファイル

本記事で使用するサンプルファイルになります。

予算実績トラッキンググラフと四半期レポートを作成するVBAサンプルコード

VBAサンプルコードです。今回はSub、Functionを使って機能別にVBAコードを整理してみました。コードのアルゴリズムの解説は、後ほどお話ししますが、ダウンロードしたサンプルファイルに、このサンプルコードをコピペして実際に実行結果を確認することもできます。

 

VBAサンプルコード実行結果

サンプルファイルとサンプルコードによる実行結果です。

トラッキンググラフ

1つ目のトラッキンググラフです。下図のような実行結果になります。予算(青ライン)、実績(積上げ棒グラフ)とも累積値を示していますので、各月ごとの予実比較が一目で確認できます。また年間の予算に対する消費状況も把握しやすいですよね。

トラッキンググラフ実行結果
トラッキンググラフ実行結果

グラフ用集計シート

グラフ作成の元データとなるグラフ表示用集計シートの結果です。この後にご紹介するプレゼン用ではないため、グラフ作成に都合のよい表形式にしています。

グラフ表示用集計シート実行結果
グラフ表示用集計シート実行結果

プレゼン用集計表

プレゼン用に体裁を整えた集計表になります(下図)。Q1~Q4までの四半期ごとの予実と差異を部門ごとに集計しています。また年間Total欄や社全体のTotal欄も設けています。

プレゼン用集計表実行結果
プレゼン用集計表実行結果

グラフ用とプレゼン用2つの表を集計させるVBAのアルゴリズム

この項では、サンプルコードについて詳しく解説しております。また既出のアルゴリズムについては、ご案内している過去記事を参照ください。

サンプルコード内でのワークシートの取り扱いについて

今回のサンプルコードでは、3枚のワークシート名をオブジェクト名で扱います(下図)。

ワークシートのオブジェクト名
ワークシートのオブジェクト名

このワークシートのオブジェクト名の取り扱い方法は、「VBAコードにオブジェクト名を使う複数ワークシート管理法」の記事の中で詳しく解説しています。

モジュール変数

3行目: Dim varGTbl() As Variant: Dim rngGrph As Range
4行目: Dim varPTbl() As Variant: Dim rngPrsn As Range

今回はモジュール変数を4つ設定しました。VBAコード3行目は、グラフ用集計シートの集計用としての配列変数とRangeオブジェクトを、VBAコード4行目はプレゼン用集計シートの集計用としての配列変数とRangeオブジェクトをそれぞれ宣言しています。

今回は10000レコードありますので、集計処理は配列を使い処理を高速化します。

Sub Main

7行目: Call 初期処理
8行目: Call 集計処理
9行目: Call 結果表示

VBA実行をスタートさせるメインのサブルーチンです。たった3行しかありませんが、これは処理内容と処理の流れが把握しやすいようにするためです。

今回のような小規模な集計システムでは、ここまでする必要はないかもしれませんが、中規模以上のコード行数が数百から数千行にも及ぶものについては、Sub, Functionを使った処理の構造化を取り入れたプログラミングをお勧めします。

初期処理

このサブルーチンでは、集計前に実行する初期処理をまとめていますが、大きく2つのことを処理しています。1つ目は、集計データ範囲のセルをクリアしています(下図赤枠)。2つ目は、集計処理に使う配列の設定をしています(下図青枠)。

初期処理の2つの処理内容
初期処理の2つの処理内容
集計シート上の集計データ表示範囲をクリア

前半のセルのクリア処理です。今回のサンプルでは、グラフ集計用とプレゼン用の2つの表がありますので、それら2枚のシートの初期化を行います。

下図赤で示したのは、グラフ用集計表のデータ集計範囲に関する処理です。そして、青で示したのはプレゼン用集計表のデータ集計範囲に関する処理です。

2つの集計表のクリア処理
2つの集計表のクリア処理
配列設定

初期処理の前半部分で、各集計表シートのセルをクリアしましたので、次に集計結果を格納する配列の設定をします。

ポイントは、Excelシートの行列番号と配列の要素番号を合わせるために、配列に格納するセル範囲の起点をA1セルに指定することです。

この手法のバリエーションについては、「VBA集計業務で二次元配列を使い処理を高速化させるサンプル」でも詳しく解説しておりますので、ここでは簡単に触れておきます。

下図赤で示したのは、グラフ用集計表に関する処理で、赤枠で示した範囲(rngGrph)をまるごと配列varGTblに格納(VBAコード31行目)しています。

また、下図青で示したのは、プレゼン用集計表に関する処理で、青枠で示した範囲(rngPrsn)をまるごと配列varPTblに格納(VBAコード38行目)しています。

配列設定範囲と処理
配列設定範囲と処理

集計処理

集計対象レコードをFor文で繰り返し処理しながら、配列varGTblと配列varPTblに集計結果を格納する処理 (下図太字部) になります。Call文でサブルーチンを呼び出すときに、引数として処理対象行番号の変数rを渡しています。

これら2つのCall文は、For ~ Next文中で使われていますので、それぞれレコード数分の10000回実行されることになります。

そのくり返し処理の後は、varGTblに対してはグラフを累積表示させるための累計処理(下図青字)を、varPTblに対しては合計欄と差異欄の集計処理(下図緑字)をします。

集計処理サブルーチン
集計処理サブルーチン
グラフ用集計表

集計対象レコードから、グラフ用集計表へ集計するための処理になります。集計結果は、最終的にVBAコード77行目で一旦varGTblに格納されます。

初めから見ていきますね。

VBAコード58行目で、処理対象行rを引数として受け取ることで、処理中のレコードが特定されます。その後、書込み行の処理のところ(赤字部)では、はじめに予実区分を判定(VBAコード63行目)した後に、費目の値に応じた処理をしています。

続くVBA74行目では、対象レコードの月データ値(集計データシート3列目)に+1することで書込み列を算出しています。

グラフ用集計表用データ集計
グラフ用集計表用データ集計
プレゼン用集計表

こちらの引数も集計対象レコード行番号を格納した変数rです(VBAコード80行目)。順に書込み行番号(下図赤文字)、書込み列番号(下図青文字)を判定し、最後に結果を配列変数varPTblに格納(下図緑字)しています。

プレゼン用集計表データ集計
プレゼン用集計表データ集計

ここから配列変数varPTblへの代入について補足します。

104行目: varPTbl(lngWRow, lngWCol) = varPTbl(lngWRow, lngWCol) + Data.Cells(r, 6) / 1000
算出された書込み行番号(lngWRow)と書込み列番号(lngWCol)を要素番号にもつ配列変数varPTbl(lngWRow, lngWCol)に、集計対象レコードの6列目の金額を1000で割って代入しています。ここで1000で割っている理由は、プレゼン用集計表右上の単位が千円であるためです。

108行目: varPTbl(lngWRow, 15) = varPTbl(lngWRow, 15) + Data.Cells(r, 6) / 1000
110行目: varPTbl(lngWRow, 16) = varPTbl(lngWRow, 16) + Data.Cells(r, 6) / 1000

VBAコード104行目に続き、集計対象レコードの予実区分により、プレゼン用集計表のTotal欄の予算(プレゼン用集計シート15列目)、もしくは実績(プレゼン用集計シート16列目)にも金額/1000を加算しています。

こうすることで、1回の処理で該当する四半期の欄と合計欄の2か所に対して集計をしています。

累計処理

集計処理サブルーチンの繰り返し処理が終わり、グラフ用集計シートに表示するデータは配列変数varGTblに格納されました。あとは、実行結果でもご紹介しました通り、グラフ上の予算と実績を累積表示させるための処理をこのサブルーチンで行います。

累計処理は予算・実績とも必要ですので、下図の通り行方向(赤字)と列方向(青字)に順次繰り返して処理をします。累計値を求める考え方は、当月のデータと前月のデータを一旦足し合わせたものを、再び当月データに代入します。(VBAコード121行目)

ここでポイントとなるのは、累積予算は12月までを必要としますが、累積実績の方は計上されている月までにすることです。この点については、VBAコード120行目で、当月データが0の場合は処理を実施しないというIf文で実現しています。

グラフ表示用集計シートの累計処理
グラフ表示用集計シートの累計処理
合計差異

集計処理サブルーチンの最後のサブルーチンになります。こちらはプレゼン用集計表に対する処理になります。

まず、VBAコード131~136行目(下図赤字)で、各四半期と年Total欄(5列)の予算と実績の差異を集計しています。そして、VBAコード138~143行目で列方向のTotal値を集計しています。

合計欄と差異欄の集計
合計欄と差異欄の集計

以上、集計処理サブルーチンについて解説してきましたが、書込み行や書込み列算出の考え方や、差異、累積、Totalの求め方の詳細については、「VBAで会計データから全社と部門別の月次予実グラフを自動作成」「VBAで月次予算実績推移の棒グラフを部門別内訳にして作成する」などの過去記事で詳細に解説しておりますので、こちらもご参考にしてみてください。

結果表示

147行目:rngGrph.Value = varGTbl
148行目: rngPrsn.Value = varPTbl

ここまでですべての集計結果が配列変数varGTblとvarPTblに格納されましたので、あとはVBAコード147行目と148行目で、それぞれの集計表への表示をするだけになります。

追加の表やグラフを増やしたい場合は?

本記事の最後に、追加で表やグラフを増やしたい場合、どのようにコードを修正すればよいかについて、今回のサンプルコードを元にお話ししたいと思います。

まず第一に、グラフが増えても、表が増えてもコード側からすれば集計表が増えただけです。よって、グラフとかプレゼン用とか意識せずにコードの追加修正をすればよいことになります。

モジュール変数を追加

まずはモジュール変数として、新たに配列変数とRangeオブジェクトを追加します(下図)。

追加する集計表用にモジュール変数を追加
追加する集計表用にモジュール変数を追加

初期処理サブルーチンに追加修正

初期処理では、2つの処理を実行していましたよね。よって、ここにもそれぞれ追加の処理を記述します。

初期処理サブルーチンへの追加箇所
初期処理サブルーチンへの追加箇所

集計処理サブルーチンに追加修正

集計処理サブルーチンに対しては、まず集計対象レコードを繰り返し処理するFor ~ Next文の中に、追加した集計表に対する処理サブルーチンを引数rを指定して記述します。

そして、図中に示した通り必要に応じて追加の処理サブルーチンも追加します。この必要に応じて追加するサブルーチンですが、文字通り必ずしも必要ではないということになります。

今回のサンプルでは、グラフ用集計シートに対しては累計処理が追加で必要でしたし、プレゼン用集計表では差異や合計欄などの追加処理が必要でしたので、それぞれ「累計処理」や「合計差異」をサブルーチンとして追加しています。

集計処理サブルーチンへの追加箇所
集計処理サブルーチンへの追加箇所

結果表示サブルーチンに追加修正

追加した集計表への集計結果を表示させるための処理記述が必要になります。

結果表示サブルーチンへの追加箇所
結果表示サブルーチンへの追加箇所

まとめ

今回はサブルーチンを使い、ある意味でまとまった処理ごとに整理してみました。このようにすることで、後でグラフが追加されたり、集計表が追加された場合でも、どこを追加すれば分かりやすくなったのではと思います。

また、不要となった処理を削除(あるいはコメントアウト)したり、VBAコードの改修箇所も把握しやすくなったのではと思います。

[スポンサーリンク]



サイト内検索

-別シート, 転記する
-,

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.