会計データから部門別の経費消費状況を、会計データとは別シート上にクロス集計するサンプルコードです。
会社の基幹システムの会計データを使い、各部門が経費をどのくらい消費しているかを表にまとめてチェックすることがよくあります。
その表を毎回ピボットテーブルを作成して作るのであれば、VBAコードで一度コードを組んでしまう方が楽なこともあります。
今回は、架空の会社の会計データを使いながら、VBAコードで表に集計させるサンプルをご紹介します。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画（音声無し）をご確認ください。
目次
VBAコードで各部門の経費を費目別にまとめるためのサンプルファイル
今回のサンプルファイルになります。サンプルシートは、会計レコードがある1枚目と、集計先の表がある2枚構成です。
会計データは、伝票ID、会計年、会計月、部門、費目、金額、予実区分の7項目から構成されています。
表は、行見出しに部門、列見出しに費目を配置しています。また、合計欄には「Sum関数」が入力されており、各部門、各費目の合計と総合計（右下セル）が算出されるようになっています。
なお、今回のサンプルからExcelシートの列表示をA1参照形式からR1C1参照形式に変更しています。
Tips
Excelシートの列をA1参照形式からR1C1参照形式に変える方法
VBAで各部門の経費を費目別に集計するためのサンプルコード
では次に、今回のVBAサンプルコードをご紹介しますね。ぜひまねて書き写してみてください。また、時間の無い方はサンプルファイルに標準モジュールを追加してからVBAコードをコピペするのもOKです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | '部門別に経費費目ごとに分けて経費を集計する Sub 部門別経費費目別サンプルコード() Application.ScreenUpdating = False '画面自動更新させない Application.Calculation = xlCalculationManual 'Excelファイルを手動計算モードにする Dim wstSelf As Worksheet '表のあるシート用 Dim wstData As Worksheet '会計データがあるシート用 Dim lngWRow As Long '表の書込み行番号格納用 Dim lngWCol As Long '表の書込み列番号格納用 Dim r As Integer Set wstSelf = Worksheets("部門別費目別クロス集計") 'クロス集計側シート設定 '結果表示範囲の初期化 With wstSelf .Range(.Cells(3, 2), .Cells(7, 4)).ClearContents End With Set wstData = Worksheets("集計データ") '会計データ側シート設定 'すべての会計データを繰り返し処理 With wstData For r = 2 To .Range("A" & .Rows.Count).End(xlUp).Row If .Cells(r, 7) = "実績" Then '予実区分が「実績」だけを抽出 '書込み行取得 Select Case .Cells(r, 4) Case "人事部": lngWRow = 3 Case "開発部": lngWRow = 4 Case "生産部": lngWRow = 5 Case "品質部": lngWRow = 6 Case "営業部": lngWRow = 7 End Select '書込み列取得 Select Case .Cells(r, 5) Case "人件費": lngWCol = 2 Case "部門費": lngWCol = 3 Case "設備費": lngWCol = 4 End Select '転記 wstSelf.Cells(lngWRow, lngWCol) = wstSelf.Cells(lngWRow, lngWCol) + .Cells(r, 6) End If Next End With 'Excelファイルを自動計算モードにする Application.Calculation = xlCalculationAutomatic End Sub |
VBAコードで各部門の経費を費目別に集計させるアルゴリズム
3行目： Application.ScreenUpdating = False
Excelシートの画面更新をOffにするコードです。会計データを順次処理すると、表には数値が加算され、セルの集計値が刻々と変わる様子を見る事ができるのですが、画面を更新する手間分だけ、計算が終了するまでに時間が掛かります。
そこで、このVBAコードを使い画面更新（セルの値が都度変わる様子）をさせないようにします。
4行目： Application.Calculation = xlCalculationManual
51行目： Application.Calculation = xlCalculationAutomatic
ワークシート上にある関数の計算方法を「手動計算」にする場合（4行目）や、「自動計算」にする場合（51行目）に指定するVBAコードです。これは、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コードの可読性向上にもなりますよね。