VBAで会計データから全社と部門別の月次予実グラフを自動作成

会社の会計データから、全社グラフと各部門別グラフに分けて、月ごとに予算と実績を比較できる棒グラフをVBAで自動作成します。各棒グラフの内訳には費目を使います。

過去記事の「VBAで月次予算実績推移の棒グラフを部門別内訳にして作成する」や「VBAで予算内訳を部門、実績内訳を費目にしたグラフを自動作成」では、1つのグラフで部門別、費目別の予実グラフを作成しました。

ただ1つのグラフでは、どうしても情報を詰め込み過ぎてしまうため、情報を見誤ったり、見落としたりしてしまいます。

本記事では、グラフの枚数を増やすことで、全社および各部門別に合計6枚のグラフを自動作成します。作成されるグラフは、1部署の費目内訳で表示されますので、上記2つのグラフより見やすくなります。

さらに、今回はVBAコードに二次元配列を導入したサンプルコードですので、処理速度もより速くなっていることを実感頂けるのではと思います。

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

会計データとグラフ用集計表、および全社、各部グラフ付きサンプルファイル

サンプルファイルのご紹介です。下図は、1枚目の集計対象レコードが入力されている「集計対象データ」シートと、グラフ表示のもとになる「グラフ用集計表」シートです。

集計対象レコードの項目には、「伝票ID」、「会計年」、「会計月」、「部門」、「費目」、「金額」、「予実区分」があります。

「グラフ用集計表」シートは、各グラフ表示用に全社および各部門それぞれの集計範囲があります。また棒グラフの内訳は「人件費」、「部門費」、「設備費」です。さらに各月には、予算と実績の集計列があります。

下図は、6枚のグラフ用シート（チャート）です。赤下線で示したように、それぞれ全社または部門別グラフには表題があります。

下からサンプルファイルをダウンロードできます。

97-2003形式は↓からどうぞ。

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

今回ご紹介するサンプルコードになります。ぜひ真似して書いてみてくださいね。また今回は二次元配列を導入したVBAコードです。

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

では、VBAコードの準備が出来ましたら実行してみてください。下図の通り、全社と各部グラフの棒グラフが表示されましたでしょうか。またグラフ用集計表もきちんと集計されているか、念のため確認しておいてくださいね。

VBAで集計対象レコードをグラフ用集計表に集計させるアルゴリズム

ここからVBAサンプルコードの解説をします。

VBA変数宣言

「集計対象データ」シートに関するVBA変数宣言

6行目： Dim wstData As Worksheet, lngERow As Long, r As Long
7行目： Dim rngData As Range, aryData As Variant
「集計対象データ」シートに関する変数を宣言しています。とくにVBAコード7行目の変数「rngData」は、1行目の 項目名を除くすべての集計対象レコード範囲を取得します。

また、配列用変数「aryData」は、変数「rngData」にセットされたデータ群を配列として保持します。

「グラフ用集計表」シートに関するVBA変数宣言

10行目： Dim wstGrph As Worksheet, lngWBsR As Long, lngWRow As Long, lngWCol As Long
11行目： Dim rngGrph As Range, aryGrph As Variant
「グラフ用集計表」シートに関する変数を宣言します。VBAコード10行目の変数「lngWBsR」は、全社用の書込み行番号を保持する変数です。また次の変数「lngWRow」は、各部用の書込み行番号を保持する変数です。

11行目は、集計先シート範囲をセットするためのRangeオブジェクト変数「rngGrph」と、その「rngGrph」のデータを配列として保持するための変数「aryGrph」を宣言しています。

ワークシートオブジェクト設定

16行目： Set wstData = Worksheets("集計対象データ")
17行目： Set wstGrph = Worksheets("グラフ用集計表")
今回扱う2枚のワークシートをワークシートオブジェクト変数にセットしています。

グラフ用集計表初期化

集計対象レコードの集計先をクリア

21～23行目： For i = 3 To 33 Step 6 ～ Next
今回の集計先範囲をクリアすることを考えるとき、各部署の集計範囲は一続きではありませんから、少し工夫する必要があります。そこで、サンプルコードでは下図のように、For文による繰り返し処理をすることで、飛び飛びになっている集計範囲（赤網掛け範囲）をクリアしています。

まずVBAコード21行目のFor文の開始位置を全社グラフ用集計範囲の人件費の行（3行目）に設定します。そして、繰り返しの最後は営業部の人件費の行（33行目）に設定します。

この For文 には「Step 6」が設定されていますが、これは各部署の集計先範囲が6行置きに規則的に配置されているためです。

VBAコード22行目では、各部署のクリアする範囲を.Range(.Cells(i, 2), .Cells(i + 2, 49))としています。まずこのクリア対象の範囲の開始行 i と終了行 i +2 ですが、各部署の集計エリアの行数が3行だからです。

またこのクリア範囲の開始列は2, 終了列は49ですが、これについては図中矢印で示しましたのでご確認ください。

「グラフ用集計表」用配列に格納する範囲をセット

25行目： Set rngGrph = .Range(.Cells(1, 1), .Cells(35, 49))
「グラフ用集計表」用の配列変数「aryGrph」に格納する範囲を、Rangeオブジェクト変数「rngGrph」にセットします。

ここでポイントなのは、配列の要素番号と、Excelシートの行列番号を一致させるために、集計先範囲の項目名、部門名、月表示など集計とは直接関係のないセルを含めて範囲指定している点です。下の図でいうと赤色網掛け部の範囲を指定していることになります。

グラフ用集計表データ範囲を二次元配列に格納

28行目： aryGrph = rngGrph.Value
VBAコード25行目でRangeオブジェクト変数「rngGrph」で指定した範囲を、配列「aryGrph」へ格納しています。

すべての集計対象レコードを配列格納

VBAコード31～33行目（下図）は、集計対象レコードを配列に入れる前に、格納すべき対象となるレコード範囲をセットしています。このVBAコードのアルゴリズムを理解するために、下図を使いながら3つに分けて解説したいと思います。

まず、wstData.Range("A1").CurrentRegionで赤枠範囲が指定されます。そして、.Resize(.Rows.Count-1）により赤枠範囲の行数（Rows.Count）より1行少ないサイズに変更(Resize)されます（青枠）。

最後に、その1行減らされた範囲（青枠）に対し.Offset(1)で範囲全体が行方向へ+1移動させられ、最終的にRangeオブジェクト変数「rngData」にセットされる範囲は、緑枠の範囲と等しくなり,結果的に純粋なデータ範囲が取得できたことになります。

35行目： aryData = rngData.Value&nbsp
前出のVBAコード28行目と同じアルゴリズムで、ここではRangeオブジェクト変数「rngData」にセットされた集計対象レコードのデータを、配列「aryData」に格納します。

繰り返し処理

37～65行目： For r = 2 To UBound(aryData) ～ Next
配列「aryData」に格納されたすべてのレコードに対し、このFor～Next文の中で集計処理をしています。

UBound関数は、配列要素の最大番号を取得する関数です。ちなみにUBound（aryData, 2）のように第二引数を指定することで、aryDataの2次元要素の最大値を取得することができます。

具体的な使い方では、サンプルコードのように( )内の第二引数を省略すると1次元要素を指定したことになり、この場合UBound(aryData)は7950になります。また、第二引数が指定されたUBound(aryData, 2)は7になります。

また参考までに、配列の最小要素番号を取得する関数はLBoundです。引数の指定の仕方はUBound関数と同じです。

書込み行判定

グラフ用集計表への書込み行番号を決める処理を解説します。今回は、書込み行番号は2つあります。

1つめは全社グラフ用集計表（「グラフ用集計表」最上部）に書き込むための行番号を保持する変数「lngWBsR」です。2つめは各部のグラフ用集計表に書込むための行番号を保持する変数「lngWRow」です。

全社グラフ集計先の書込み行判定

VBAコード40～44行目は、全社グラフの集計先範囲の書込み行番号「lngWBsR」を決めるためのコードです。

前項で2つの配列「aryData」と「aryGrph」の要素番号と、Excelシート上の行列番号が一致するように配列を定義してきましたので、下図のようにExcelシート上で配列へのデータの受け渡しを考えることができます。

よって、これ以降Excelシートを使ってアルゴリズムを解説していきますが、その際のExcelシート行列番号はそのまま配列要素の番号になると考えてくださいね。

各部集計先の書込み行番号判定

VBAコード47～53行目は、集計対象レコードの部門データの読み取り値（赤枠4列目)に応じて、部門別集計先シートへの書込み行番号を判定しています。

下図青字は、全社データの集計表から人事部と営業部それぞれの集計表がどれだけオフセットしているかを図示したものです。Excelシート行番号を見ると、各部署の集計表は6行置きに配置されていますよね。

このことは、図の青網掛け部にある「lngWBsR +」の後にある数字が6の倍数になっていることからも確認ができます。

書込み列判定

VBAコード56～60行目では、集計対象レコード7列目「予実区分」の読み取り値が「予算」か「実績」かで分岐処理して、集計先の書込み列を決めています。

「予実区分」が予算の場合は、下図の通り初項3、公差4の等差数列式により決まります。また「予実区分」が実績の場合は、初項4, 公差4の等差数列式により決まります。

書込み列が等差数列により決まるアルゴリズムの詳細については、下記記事で詳しくお伝えしておりますので、宜しければご参考ください。

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

グラフ用集計表配列に加算集計

繰り返し処理の最後になります。ここまでで、全社グラフの集計表と、いづれかの部門用グラフの集計表への書込み行番号の2つ（lngWBsR, lngWRow）と列番号が決まりましたので、それらに対応する配列要素に金額（6列目データ）を加算集計します。

「グラフ用集計表」シートに集計結果を表示

67行目： rngGrph = aryGrph
繰り返し処理が終わり、すべてのレコードの金額が配列「aryGrph」に集計されました。あとはこのVBAコードにより、配列の中にある集計結果を「グラフ用集計表」シートに表示させます。

まとめ

SAPなどの基幹システムから取り出した会計データなどを、視覚化するために作成する月次の棒グラフを自動作成するサンプルコードをご紹介しました。

棒グラフの内容は、月ごとに予算と実績を比較できるもので、さらに棒グラフの内訳を費目別にしました。また会社全体と5部門の各組織に対して別々に計6枚のグラフを作成しました。

今回のサンプルコードでは、レコード数が約8000、グラフ枚数が5部でしたが、配列を使わない場合、「グラフ用集計表」へのアクセス数が増え、実行速度が遅くなることを考慮し配列を導入してみました。

下の記事でも紹介しましたが、1000レコード程度でも配列の有無により、VBAの実行速度の違いが分かりますので、Excelシートの集計業務には基本的に配列を使う事に慣れておいた方が良いと思います。

Tips：配列導入効果の分かる記事
VBA集計業務で二次元配列を使い処理を高速化させるサンプル