サイト内検索

別シート 転記する

VBAコードで部門別に各経費費目に分けて集計する

会計データから部門別の経費消費状況を、会計データとは別シート上にクロス集計するサンプルコードです。

会社の基幹システムの会計データを使い、各部門が経費をどのくらい消費しているかを表にまとめてチェックすることがよくあります。

その表を毎回ピボットテーブルを作成して作るのであれば、VBAコードで一度コードを組んでしまう方が楽なこともあります。

今回は、架空の会社の会計データを使いながら、VBAコードで表に集計させるサンプルをご紹介します。

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

VBAコードで各部門の経費を費目別にまとめるためのサンプルファイル

今回のサンプルファイルになります。サンプルシートは、会計レコードがある1枚目と、集計先の表がある2枚構成です。

サンプルファイルの構成
サンプルファイルの構成

会計データは、伝票ID、会計年、会計月、部門、費目、金額、予実区分の7項目から構成されています。

表は、行見出しに部門、列見出しに費目を配置しています。また、合計欄には「Sum関数」が入力されており、各部門、各費目の合計と総合計(右下セル)が算出されるようになっています。

なお、今回のサンプルからExcelシートの列表示をA1参照形式からR1C1参照形式に変更しています。

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

VBAで各部門の経費を費目別に集計するためのサンプルコード

では次に、今回のVBAサンプルコードをご紹介しますね。ぜひまねて書き写してみてください。また、時間の無い方はサンプルファイルに標準モジュールを追加してからVBAコードをコピペするのもOKです。

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

 

 

VBAコードで各部門の経費を費目別に集計させるアルゴリズム

3行目: Application.ScreenUpdating = False
Excelシートの画面更新をOffにするコードです。会計データを順次処理すると、表には数値が加算され、セルの集計値が刻々と変わる様子を見る事ができるのですが、画面を更新する手間分だけ、計算が終了するまでに時間が掛かります。

そこで、このVBAコードを使い画面更新(セルの値が都度変わる様子)をさせないようにします。

4行目: Application.Calculation = xlCalculationManual
51行目: Application.Calculation = xlCalculationAutomatic

ワークシート上にある関数の計算方法を「手動計算」にする場合(4行目)や、「自動計算」にする場合(51行目)に指定するVBAコードです。これは、Excelファイルメニューから、「Excelのオプション」の「数式」をクリックした画面から計算方法の選択を設定するのと同じことになります。(下図参照)

Excelオプション ⇒ 数式をクリック後画面
Excelオプション ⇒ 数式をクリック後画面

今回の表の合計欄には、Sum関数が入力されています。Sum関数に限らず、セル上に入力された関数は、Excelファイルが自動計算モードの場合、VBAコードが実行中でも常に自動計算されます。

そうなると、関数の計算時間の分、VBAコードの実行時間が長くなりますので、必然的に関数が多く使われているほど、VBAの実行時間が長くなります。

そこで、VBAコードの実行中は必要のない関数の計算を止めるために、VBAコードの初めに「手動計算」を指定し、VBAコードが終わる前に「自動計算」に戻すようにしています。

ただし、上記のように設定する場合、1点だけ気を付けることがあります。それは、 実行中のVBAコードが、関数が入力されたセル値を参照する場合です。

この場合は、そのVBAコードから参照される直前に自動計算モードに戻して(参照先セルの関数を自動計算させて)からセル参照させないと、意図した計算結果が得られませんので注意が必要です。

17~19行目: With wstSelf ~ End With
表の結果表示範囲(下図赤枠)を初期化しています。

表の結果表示範囲を初期化
表の結果表示範囲を初期化

26行目: If .Cells(r, 7) = "実績" Then
会計データのうち、「予実区分」が「実績」のみを抽出するための条件式になります。左辺「.Cells(r, 7)」は、レコードの「予実区分」(会計データ7列目)ですよね。その値と右辺の「実績」が等しい場合は、Then以降で表に集計させます。

予実区分の「実績」のみを抽出
予実区分の「実績」のみを抽出

表の書込み行を決めるVBAコードのアルゴリズム

VBAコード29行目から35行目は、表への書込み行番号を決めています。まず、VBAコード29行目のSelect文の条件に「.Cells(r, 4)」、すなわち会計データ4列目「部門」を指定しています。

クロス集計表への書込み行を決めるアルゴリズム
クロス集計表への書込み行を決めるアルゴリズム

その「部門」の値に応じて、VBAコード30~34行目で書込み行を保持する変数「lngWRow」を決めています。すなわち、ここで表側サンプルシート上の行番号を割り当てているのです。

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

VBAコード38~42行目で、表への書込み列を決めています。まず、会計データの「.Cells(r, 5)」、すなわち「費目」をSelect文の条件にしています。そして、その費目に応じて、書込み列番号を格納する変数「lngWCol」に、表側サンプルシートの列番号を割り当てています。

表側サンプルシートへの書込み列番号を決めるアルゴリズム
表側サンプルシートへの書込み列番号を決めるアルゴリズム

 

45行目: wstSelf.Cells(lngWRow, lngWCol) = wstSelf.Cells(lngWRow, lngWCol) + .Cells(r, 6)
このVBAコードの前で、書込み行「lngWRow」と書込み列「lngWCol」が決まるので、続くこのコードにより対応するクロス集計表のセルへ加算集計します。

表へ加算集計させるアルゴリズム
表へ加算集計させるアルゴリズム

会計データから「金額」(.Cells(r, 6))を読み取り、既に集計されている.Cells(lngWRow, lngWCol)を加算して、左辺の.Cells(lngWRow, lngWCol)へ再度代入しています。

まとめ

今回は見出し行に「部門」、見出し列に「費目」を配置した表を自動集計するVBAコードのサンプルをご紹介しました。このサンプルを流用すれば、任意の2つの項目の関係を分析するための表を、VBAコードで効率的にまとめることができます。

ポイントは、集計先の書込み行と書込み列を別々に分けて考えることです。こうすることで、行見出し、もしくは列見出しだけが変わった場合でも、それぞれ分けてコードの改修ができますし、VBAコードの可読性向上にもなりますよね。

[スポンサーリンク]



サイト内検索

-別シート, 転記する
-

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