サイト内検索

別シート 転記する

VBAコードで部門別に月ごとの経費を集計するサンプル

会計データから、部門別に月ごとの経費消費実績をクロス集計させます。作成するクロス集計表は、会計データとは別シートに作成されます。

基幹システムから得た会計データに対して、会計月ごとに部門別の実績を表にして分析することがあります。さらに、その表を元に毎月の消費推移をグラフで報告することもあります。

そのような定例業務の場合、一度VBAコードを組んでおけば、毎月のレポート作成作業にかかる時間が大幅に短縮できます。今回は、会計データから部別に月ごとの消費実績を一覧表にまとめるVBAコードをご紹介します。

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

VBAで会計データの消費実績を部門別、月ごとに集計させるサンプルファイル

まずは今回使用するサンプルファイルのシート構成をご紹介します。サンプルシートは、会計データがある1枚目のシートと、経費の消費実績を月別に表示するための集計表がある2枚目から構成されています。

会計データ(サンプルシート1枚目)
会計データ(サンプルシート1枚目)
結果を表示する表(サンプルシート2枚目)
結果を表示する表(サンプルシート2枚目)

表は、行見出しに「部門」を、列見出しに「各月」を配置しています。また、行と列の最後に合計欄と、右下に総合計のセルをそれぞれ「Sum関数」を入力して配置しています。

Tips
Excelシートの列をA1参照形式からR1C1参照形式に変える方法

VBAで会計データを部門別に月ごとに集計させるサンプルコード

では次に今回のVBAサンプルコードをご紹介します。ぜひまねして書いてみてください。また時間のない場合は、サンプルファイルに標準モジュールを挿入してから、下記サンプルコードを標準モジュールにコピペしてVBAを実行してください。

Tips
サンプルファイルに標準モジュールを追加する方法

 

実行結果は以下の通りになります。

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

VBAで月別に各部門ごとの経費を集計させるサンプルコードのアルゴリズム

17~19行目: With wstSelf ~ End With
表のセル範囲をクリアするためのコードです(下図)。

結果表示範囲をクリアするVBAコード
結果表示範囲をクリアするVBAコード

VBAコード28~34行目は、結果を表示する集計表上の書込み行番号を決めるVBAコードになります。まず、VBAコード28行目のSelect文の条件に、会計データの4列目のデータ「部門」を指定しています。この.Cells(r, 4)の値に対応する書込み行「lngWRow」が、VBAコード29~33行目によって決まります。

表への書込み行番号を決めるアルゴリズム
表への書込み行番号を決めるアルゴリズム

表の赤枠内のサンプルシートの行番号と部門名の関係が、Select文の中の29~33行目の分岐条件に対応してますよね。

VBAコードで月別に集計させるためのアルゴリズム

37行目: lngWCol = .Cells(r, 3) + 1
表の書込み列番号は、月別に対応してます。このVBAコードにより、書込み列「lngWCol」は、「会計月」である.Cells(r,3)に1を加えた値になります(下図)。

表の書込み列番号を決めるアルゴリズム
表の書込み列番号を決めるアルゴリズム

赤字は会計データにある「会計月」の値です。青字は、表への書込み列番号です。VBAコード37行目は、右辺で「会計月」に1を足した値を書込み列の変数「lngWCol」に代入しています。

例えば、先頭レコードの「会計月」を見ると10ですよね。その10に1を加えた値が「lngWCol」になります。サンプルシート上の表で確認すると、lngWCol = 11は、確かに10月に対する書込み列番号になってますよね。

つまり、この表への書込み列番号は、会計月に1を足した関係にあります。表の列番号(青枠)と会計月(赤枠)の関係を見るとよくわかります。

VBAコード40行目は、決定した書込み行と書込み列に対応したセル「.Cells(lngWRow, lngWCol)」に会計データの「金額」を加算しています。こちらのアルゴリズムについては、以下で詳しく解説しております。

Tips
クロス集計表への加算集計をするアルゴリズム

まとめ

会計データは、ふつう会計年や会計月の項目を持っていますが、今回はある会計年の月別の経費を部署別に集計するサンプルをご紹介しました。ご紹介したサンプルコードを応用すれば、毎月報告するレポートをVBAコードで素早く集計できます。

[スポンサーリンク]



サイト内検索

-別シート, 転記する
-

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