VBAで集計対象データをカテゴリ別複数シートに分けて集計する

VBAで集計対象レコードの、ある１つの項目内訳別に集計シートを作成し、その各シートに集計結果を展開します。

過去記事「VBAコードで複数シートからなる帳票形式データを集約する」では、複数の別シートに分けずに、1つの別シート上にすべての内訳を帳票形式で表示しました。

本記事では、サンプルデータは同じですが、内訳別に用意した複数の別シート上に集計元データを集計させます。

まずは本記事の内容が90秒で分かる動画を用意しましたので、宜しければこちらを先にご覧ください。

VBAでカテゴリ別複数シートに集計元データを集計させるサンプルファイル

サンプルファイルは、集計元レコードが入力されている「Data」ワークシートが1枚と、その集計元データを複数の別シートに集計させる7枚のシートがあります。

集計元データが入力されている「Data」ワークシート

集計元データの項目は、「商品ID」、「商品名」、「在庫」、「原価」、「棚卸資産」、「仕入担当」、「仕入先」、「価格更新日」の8項目です。

これらの項目のうち、今回は仕入担当の内訳となる担当者ごとに別シートを作り、それらに集計元レコードの内容を再集計させます。

担当者別ワークシート(7名分)

「Data」ワークシートにある集計元レコードを、7名の仕入れ担当者ごとに用意した別シートに再集計させます。7枚の仕入れ担当者別シートは、下図の仕入担当者入力エリアの仕入れ担当者名以外は同じです。

仕入担当者別シートは、3つのエリアからなる帳票形式です。リストアップエリア（緑枠）は、集計元レコードから該当するデータを、そのままリストアップ表示するエリアになります。

加算集計エリア（青枠）は、リストアップされた個々のデータの「在庫」、「原価」、「棚卸資産」の各項目の合計を、それぞれ「在庫合計」、「原価合計」、「総資産」別に集計するエリアになります。

加算集計エリアの最後の項目「1個当たりの総資産」は、商品1個あたりの資産を平均して算出しています。

ダウンロード用サンプルファイル

実際に動かして確認できるように、上記ワークシートが格納されたサンプルファイルを用意しておりますので、宜しければダウンロードしてご利用ください。

VBAでカテゴリ別集計シートに集計元データを集計させるサンプルコード

下記はVBAサンプルコードになります。

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

VBAサンプルコードの実行結果を簡単に確認します。ここでは出力先別シートのリストアップエリアと加算集計エリアの2つに分けて順番に確認します。

リストアップエリア集計結果の確認

7名の担当者がいますが、確認方法はすべての担当者で同じですので、ここでは仕入担当者名が「井田」、「川村」、「鈴木」のリストアップエリアの集計結果だけを確認します。

担当者「井田」のリストアップエリア確認

下図右下の集計元データの仕入れ担当者が井田に該当するデータは2つです。ちゃんとイチゴとさくらんぼのデータをリストアップしているのが確認できます。

担当者「川村」のリストアップエリア確認

下図は仕入担当者「川村」のリストアップエリアの集計結果を確認したものです。集計元レコードには、該当するデータが3つありますが、正しくリストアップされているのが分かります。

担当者「鈴木」のリストアップエリア確認

最後に仕入担当者「鈴木」のリストアップエリアの集計結果を確認してみましょう。下図の通り、3つの該当レコードがありますが、やはり正しく集計されてますよね。

加算集計エリア集計結果の確認

つぎに各担当者別シートの加算集計エリアの確認をします。まず準備として、「Data」ワークシートエリアから、下図のようなピボットテーブルを作り、これと各仕入担当者別シートの加算集計エリアの集計結果と比較します。

また、加算集計エリアの確認方法もすべての担当者で同じですので、今度は「横山」、「田中」、「木村」を取り上げて加算集計結果を確認してみます。

担当者「横山」の加算集計エリア確認

仕入担当者「横山」の加算集計エリアの集計結果を比較確認しますと、ピボットテーブルの「在庫合計」、「原価合計」、「棚卸総資産」の合計、「1個当たりの総資産」すべてが同じ値ですよね。

担当者「田中」の加算集計エリア確認

同様に、仕入担当者「田中」の場合についても、各項目とも集計結果が正しいことが分かります。

担当者「木村」の加算集計エリア確認

最後、念のため仕入担当者「木村」のデータも確認しておきます。ここでもやはり集計結果とピボットテーブルの各値が同じであることが確認できます。

VBAサンプルコードのアルゴリズム

変数宣言

6行目：Dim wstData As Worksheet 
7行目：Dim wstSlip As Worksheet
VBAコード6行目は、集計元レコードが入力されている「Data」ワークシート用のオブジェクト変数です。VBAコード7行目は、仕入担当者別シートを順次処理するために必要なオブジェクト変数です。

ちなみにサンプルファイルのワークシート数が「Data」ワークシートと、各仕入担当者別の合計で8シートあるのに、用意するオブジェクト変数が2つですむ理由は以下の通りです。

その理由は、後述のアルゴリズムの解説をご覧いただいても分かるのですが、VBAコード内で実際のデータをやり取りする際は、「Data」ワークシートと仕入担当者別シートのうちのいづれか1シートだけが対象となるからです。

9行目：Dim lngERow As Long
10行目：Dim lngWRow As Long
VBAコード9行目は、「Data」ワークシートに入力されている集計元データの最終行番号を取り扱う長整数型変数です。VBAコード10行目は、仕入担当者別シートのリストアップエリアに書き込むExcelシートの行番号を保持する長整数型変数です。

各仕入担当者別シートのデータ範囲の初期化

VBAコード17～28行目は、「Data」ワークシートを除くすべての仕入担当者別シートを1シートずつ繰り返し処理しています。処理内容は、各仕入担当者別シートのデータ集計エリアの初期化です。

初期化の内容は主に2つあります。1つは、データ範囲のクリア処理と、もう１つはセルの表示形式です。

まずVBAコード17行目のFor Each ～ Next文で、すべてのワークシートを処理対象にします。

その上で、VBAコード19行目で処理対象のワークシートを、仕入担当者別シートだけに絞っています（下図）。こうすることで、目的の「各仕入担当者別シートのデータ集計エリアを初期化する」対象シートだけが絞れることになります。

VBAコード20, 21行目は、初期化の1つ目の目的である仕入担当者別シートのデータ範囲をクリアしています（下図）。ClearContentsは、単一セルやセル範囲などのRangeオブジェクトに対するメソッドで、セルの中身をクリアします。

VBAコード23～25行目は、セル範囲の書式を設定します。赤字部分は、金額の書式に、青字部分は日付の書式に設定しています。

ところで今回はVBAコード20, 21行目で、データの内容をクリアするためにClearContentsを採用しているため、実はこれら3行のVBAコードは1度実行してしまえば、その後実行される必要はありません。

さらに言えば、仕入担当者別シートの作成段階で、Excelの書式設定機能により該当するセル範囲に金額と日付をあらかじめ設定しておけば、この3行分のVBAコードは書かなくてもよいです。

ただ、取り扱うシートの仕様により、セル範囲をクリアするためのメソッドをClear（セル範囲の書式を含めてすべて初期化）や、ClearFormat（セル範囲の書式を初期化）などが使われる場合には、これら3行の文のように都度書式を設定する必要があります。

集計元レコードを各担当者別シートへ集計するアルゴリズム

VBAコード30～62行目は、「Data」ワークシートに入力されているすべての集計元レコードを順次読み取り、各担当者別シートごとに分けて集計する処理になります。

31行目：lngERow = .Range("A" & .Rows.Count).End(xlUp).Row
「Data」ワークシートに入力されている集計元レコードの最終レコードが入力されている最終行番号を取得しています。

VBAコード34～61行目は、「Data」ワークシートに入力されているすべての集計レコードを1レコードずつ繰り返し処理する部分になります。

35行目：Set wstSlip = Worksheets(.Cells(r, 6).Value)
処理中の対象レコードが、どの仕入担当者別シートに転記されるのかを判定しています。.Cells(r, 6).Valueは、今回の場合wstData.Cells(r, 6).Valueですので、下図の通り集計元レコードの「仕入担当」項目のデータになります。

また、wstData.Cells(r, 6).Valueは、各仕入担当者別シートのシート名とも同じですので、このVBAコードにより集計先対象のシートを、wstSlipとしてオブジェクト変数で扱うことができます。

VBAコード40行目以降の処理内容は、下図で色分けして示したように、大きく分けて3つに分けることができます。

はじめの2つ（赤、青）は、各仕入担当者別シートのリストアップエリアに関する処理です。まずリストアップエリアの始めの処理（赤）で、リストアップエリアの何行目にデータを書き込むことができるかを判定しています。

そして2つ目の処理（青）で、直前で決定した書込み行に該当するリストアップエリアの行へ、集計元レコードのデータを転記しています。

3つ目の処理（緑）は、各仕入担当者別シートの加算集計エリアへの加算処理です。

以上、3つの処理をVBAコード34～61行目のFor～Next文で1レコードずつ読み取り、データをリストアップしたり加算集計処理をしています。

リストアップエリアに対する処理詳細

まずリストアップエリアに対する2つの処理を詳しく解説します。

リストアップエリアへの書込み行判定処理

まず1つ目の処理（VBAコード40～47行目）で、リストアップエリア（下図赤網掛エリア）のどの行に書き込むかを判定しています。VBAコード40行目で、書込み行番号を保持する変数lngWRowに、リストアップエリアの一番上の行、すなわち4行目の4を代入します。

つづくVBAコード41～47行目で、書込み行lngWRowが決定するまで、Do～Loop内で処理を繰り返します。

lngWRowを判定するアルゴリズムは、上図青枠で囲った商品名（.Cells(lngWRow, 5))が "" (ブランク)かどうかで決めています。

もし.Cells(lngWRow, 5)が ""(ブランク）ではない場合（ = VBAコード42行目のIf文内の条件を満たす場合）は、次の行lngWRow = lngWRow + 1 により書込み行の判定を次の行に移してから、再度If文の条件式で判定させています。

また.Cells(lngWRow, 5)が ""(ブランク）の場合(VBAコード44行目を満たす場合）、データを書込むことができますので、この時点でlngWRowが決定します。そして、つづくVBAコード45行目のExit DoによりDo～Loopを抜けて次の処理に移行します。

リストアップエリアへのデータ書込み（転記）処理

書込み行番号lngWRowが決まれば、あとは集計元レコードの処理対象行番号rにあるレコードの各データを、各担当者別シートのリストアップエリアへ転記するだけです。

上図は、赤字・赤枠は「Data」ワークシート上にある集計元レコードの読み取り位置を示し、青字・青枠は各担当者別シートの書込み先の位置を示したものです。

加算集計エリアへの加算処理詳細

3つ目の処理は、加算集計エリアへの集計処理です。下図の青枠の加算集計エリアへ、各レコードの「在庫」、「原価」、「棚卸資産」を加算しながら集計しています。

「1個当たりの総資産」を算出するVBAコードのアルゴリズム

VBAコード65～71行目では、すべての集計元レコードの処理が終わった後、最後にもう1度各担当者別シートを処理しています。処理内容は、各担当者別シートの「1個当たりの総資産」です。

VBAコード65～67行目については、既にご説明したVBAコード17～19行目と同様、「Data」ワークシートを除くすべての各担当者別シートを抽出して処理させるVBAコードです。

68行目：.Cells(8, 3) = .Cells(7, 3) / .Cells(5, 3)
各シートで処理する内容になります。ここでは「1個当たりの総資産」を、それまで加算処理して求めた「総資産」を、同様にして求めた「在庫合計」で除すことにより、在庫1個当たりの総資産額を求めています。

まとめ

本記事では集計元レコードを、あるカテゴリ別に準備した複数のExcelシート上に分けて集計させるVBAコードのサンプルをご紹介しました。ここでご紹介したVBAコードをマスターすれば、ある集計元レコードを任意のカテゴリ別に複数のシートに分けて集計できるようになります。

もちろん、集計先の体裁なども本記事でご紹介したVBAコードのアルゴリズムを応用すればいろいろなフォーマットで集計することが可能ですので、毎月あるいは毎週報告するレポート形式や、いろいろな帳票形式にアレンジできるようになります。