各部署ごとにシート分けされた会計レコードを、直接クロス集計するVBAサンプルコードをご紹介します。各部署ごとのレコードを、一度全社レコードとして1つのシートにまとめる必要がない場合は、コード量が少ないこちらのVBAコードがお勧めです。
またExcelシートで集めたアンケート回答シートを集計するような場合にも応用できます。
まずは、本記事の概要がつかめる動画（音声無し）を用意しましたので、宜しければこちらから先にご覧ください。
目次
1つのクロス集計表と各部署シートつきサンプルファイル
サンプルファイルは、集計先のクロス集計表があるワークシート1枚と、5部署の会計レコードが入力されているワークシート5枚の計6枚から構成されています。
クロス集計表シート
クロス集計表には、行見出しに5つの部署（営業部、開発部、人事部、生産部、品質部）、列見出しに3つの費目（人件費、設備費、部門費）があります。さらに列見出しは、予算、実績、差異の内訳があります。
また図の青色セルには、行列方向のTotalが集計されます。
部署別会計レコードシート
部署別の会計レコードが入力されているシートです。項目は、「伝票ID」、「会計年」、「会計月」、「費目」、「金額」、「予実区分」になります。
データの中身は、ランダムに作られたものですが、レコード数はすべての部署を合わせて10000レコード程になります。
ダウンロード用サンプルファイル
前述のワークシートを含むサンプルファイルをダウンロードできます。あとで解説するVBAサンプルコードを、標準モジュールに書き写してVBAを実行すれば、実際に実行結果を確認することができます。
VBAサンプルコード
今回のVBAサンプルコードは、メインプログラムを含む4つのサブプロシージャから構成されています。また、10000レコードありますので配列を導入して集計します。
VBAサンプルコードの実行結果
VBAサンプルコードを実行した結果です。図の赤い網掛け部は、VBAで数式が入力される範囲です。
余談ですが、レコードはランダムで作成したものですが、集計結果は差異がマイナスにならないよう微調整しています。
VBAサンプルコードのアルゴリズム
これよりVBAサンプルコードのアルゴリズムについて詳しく解説していきます。
ワークシートオブジェクト
まずはVBAサンプルコード内で扱うワークシートのオブジェクト名を確認します。今回は、クロス集計表があるワークシートのオブジェクト名を「CROSS」に変更した以外は、デフォルトのままにしてあります。
部署シートをデフォルトのままにしたのは、メインプログラムのFor Each ～ Next文の中でワークシート変数「wstSelf」にセットして処理しているからです。
モジュール変数
3行目：Dim varCros As Variant
4行目：Dim rngCros As Range
クロス集計シート全体（下図赤枠範囲）を格納するための変数です。
ここで数値が集計される範囲外の行見出しや列見出しまで含める理由ですが、配列の要素番号とExcelシートの行列番号を一致させるためです。これにより、処理中の配列がExcelシートのどのセルに該当するのかを直感的に把握しやすくなりますので、VBAコードのメンテナンス性や開発効率が向上します。
「複数シート上のレコードを直接クロス集計する」サブプロシージャ（メインプログラム）
サブプロシージャ「複数シート上のレコードを直接クロス集計する」は、メインプログラムですので、まずはVBAサンプルコード全体の大まかな処理の流れを把握します。
そこでメインプログラムを、下図4つのプロセスに分けて考えます。
処理順に箇条書きにすると以下のようになります。
- クロス集計表の結果表示範囲クリア
- 各部署ごとのシートからすべてのレコードをクロス集計表（配列）に集計
- クロス集計表（配列）の結果を、実際にExcelシートへ表示
- クロス集計表の差異欄と合計欄に数式を入力
VBAコード10行目と21行目は、それぞれ別のサブプロシージャの項で解説しておりますので、ここでは他のVBAコードについて詳しく見ていきます。
まず、VBAコード13～17行目のFor Each ～ Nextでサンプルファイルのすべてのシートを繰り返し処理しますが、続くVBAコード14行目のIf文により処理する対象のシートを部署シートに限定しています。
14行目：If Right(wstSelf.Name, 1) = "部" Then
集計対象を部署シートに限定するためのIf文の条件式です。ワークシート名の右1文字が「部」である特徴を捉え、Right関数を使って条件式を組み立てています。
15行目：Call 各部署レコードをクロス集計表へ転記(wstSelf)
処理中のワークシート（ここでは部署シート「wstSelf」）を引数に渡して、実際に部署シート上のすべてのレコードを、クロス集計表に集計しているサブプロシージャ「各部署レコードをクロス集計表へ転記」を実行します。
19行目：rngCros = varCros
配列「varCros」に格納されたクロス集計表の集計結果を、Excelシート上に表示する処理です。実際には、配列のデータをExcelシート上に張り付けてます。
「クロス集計表初期化」サブプロシージャ
27行目：.Range(.Cells(3, 2), .Cells(8, 13)).ClearContents
クロス集計シートの集計結果表示範囲をクリアしています。数値が表示される範囲を初期化のためクリアしています。
29行目：Set rngCros = .Range("A1").CurrentRegion
30行目：varCros = rngCros.Value
クロス集計表のセル情報を配列へ格納するための一連の処理です。まずVBAコード29行目で、クロス集計表の見出し含むすべての範囲（下図赤枠）を取得します。
つづくVBAコード30行目で、取得した範囲を配列「varCros」に格納します。
「各部署レコードをクロス集計表へ転記」サブプロシージャ
35行目：Private Sub 各部署レコードをクロス集計表へ転記(ByVal vSht As Worksheet)
サブプロシージャ宣言部です。引数は、各部署のワークシートになります。
36行目：Dim intWRow As Integer
37行目：Dim intWCol As Integer
クロス集計データを格納する配列変数「varCros」の1次元の要素番号と、2次元の要素番号を保持する変数です。今回これらはクロス集計表のExcelシートの行と列番号と一致するようにしています。
41行目：Dim varDept As Variant
42行目：Dim rngDept As Range
各部署のレコード範囲をセットするオブジェクト変数「rngDept」と、取得した範囲のデータを配列に格納するための配列変数「varDept」です。
VBAコード44～47行目は、部署シートのデータ範囲（見出し行を除く）を配列変数「varDept」に格納する処理です。まずVBAコード44行目で、部署シートのデータ範囲（見出し行を含む）を指定します。
その上で、続くVBAコード45行目で、集計に必要なデータ範囲（見出し行を除く）を、オブジェクト変数「rngDept」にセットします。
RangeオブジェクトのResizeプロパティを使って、配列に必要なデータを取得する方法の詳細については、下記記事でより詳しく解説しております。
46行目：varDept = rngDept.Value
部署シートのレコード範囲だけがセットされた変数「rngDept」の値を配列変数「varDept」に格納します。
VBAコード50～56行目は、引数で渡された部署別ワークシートのシート名により、クロス集計表への書込み行番号を判定する処理です（下図）。
ワークシート名を条件に指定したSelect文を用いて、部署名に応じたクロス集計表への書込み行番号を決定しています。
VBAコード59～74行目は、部署ごとのすべてのレコードに対する繰り返し処理です。VBAコード46行目の処理により、配列変数「varDept」には部署のすべてのレコードが格納されましたので、ここでFor ～ Next文を使い1つ1つのレコードの金額データを、クロス集計表へ加算集計します。
総レコード数は配列変数「varDept」の1次元の最大要素番号になりますので、For文の繰り返し最終値をUBound関数を使い求めています。
Tips
配列を使った集計に必須のUBound/LBound関数
VBAコード61～65行目は、部署別会計レコードの「費目」(赤枠、4列目）データを使いクロス集計表への書込み列番号lngWCol（青字）を判定しています（下図）。
ここでは、まず各費目別の予算に相当する列番号（上図青枠）を判定しています。
つづくVBAコード68～70行目では、会計レコードの「予実区分」(下図赤枠、6列目）が「実績」の場合、さらに書込み列番号を+1することにより、クロス集計表の実績列への書込み列番号をセットしています。
具体的には、VBAコード68行目のIf文で会計レコードの予実区分が「実績」の場合に、クロス集計表への書込み列番号intWColにさらに1を足します（VBAコード69行目）。
ここまでで、クロス集計表への書込み行番号「intWRow」と書込み列番号「intWCol」が決定しましたので、あとは会計レコードの金額（下図赤枠、5列目）を加算集計するだけです（VBAコード73行目）。
「合計欄に数式入力」サブプロシージャ
メインプログラムのVBAコード19行目で、クロス集計の結果をExcelに表示したあと、予実の差異欄やTotal欄に数式を入力する処理をするサブプロシージャです。下図は、VBAコード19行目の処理後のクロス集計表の状態を示したものです。
本サブプロシージャ内のVBAコードは、上図空欄（数式入力エリア）を3つの領域に分けて数式を入力するようにしています。
まず、VBAコード84～86行目で下図赤枠の差異欄へ数式を入力します。差異欄は、予算から実績を引いた差額です。VBAで数式を入力するためにRangeオブジェクトの.FormulaR1C1プロパティを使います。
FormulaR1C1プロパティは、セルに入力したい数式を上図のように文字列として指定することによりセル内に数式を入力することができます。
よって、上図赤枠で囲った1つの差異欄を1つのRangeオブジェクトとして扱い、そのRangeオブジェクトのFormulaR1C1プロパティで指定した数式を、For文で3回処理してクロス集計上のすべての差異欄に数式を入力しています。
VBAコード89～91行目は、下図赤枠の合計欄に数式を入力します。ちなみに下図は、直前の差異欄への数式入力処理後の状態です。
ここでは、合計欄の1つのセルにFormulaR1C1プロパティを使い数式を指定した式（VBAコード90行目）を、VBAコード89行目のFor文で2列目から10列目まで繰り返し処理することにより、結果的に9つのセルに数式を入力しています。
VBAコード94行目は、行方向の合計欄（下図赤枠）に数式を入力する処理です。ここでも数式を入力したい対象の範囲を、RangeオブジェクトのFormulaR1C1プロパティを使い数式を入力します。
まとめ
各部署の会計レコードを直接クロス集計表へ集計するVBAサンプルコードをご紹介しました。今回のように各部署のレコードを一度全社レコードに集約する必要がない場合は、コード量も少なく済みます。
とくにExcelシートで作成したアンケート回答シートなども、直接クロス集計表やグラフ用集計表に集計することが多いと思いますので、応用しやすいと思います。
また今回は予実の差異欄や行列方向にそれぞれ設けた合計欄に、RangeオブジェクトのFormulaR1C1プロパティを使い、セルに数式を入力しました。これについては、実務上クロス集計した後に、数字を手で修正する頻度が高い場合など、常に合計欄が反映されて便利です。
反対にクロス集計後の数値を、手入力による変更はしない場合については、必ずしも今回のように、セルに数式を入力する処理は必要ないと思います。その場合は、VBAコード内で合計処理などの計算結果をセルに表示させればよいですよね。
いづれの場合でも一度VBAコードを組んでしまえば、上記の修正は非常に簡単ですので、練習のために今回ご紹介したVBAサンプルコードを、数式入力ではなく合計値を出力するコードにアレンジしてみるのもよいかもしれません。
