VBAコードで処理対象レコードと同じシート上に結果表を作る

レコードの中の複数項目を分析するのによく使われるクロス集計表を、処理対象レコードと同一シート上に作成します。処理対象データの傾向を分析したいときによく使われるのが、ピボットテーブルですよね。

ただ毎回同じパターンの表をピボットテーブルで作ったり更新するのであれば、あらかじめVBAコードを組んでおいてもよいですよね。

そうすれば、VBAコードをショートカットで実行させることで、より早くしょ結果を求めることができます。

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

処理対象データと結果表が同一シート上にあるExcelサンプルシート

本記事では、処理対象データと同一シートに結果を表示する表を作るVBAコードをご紹介します。では今回のサンプルファイルをご紹介しますね。

左側は処理対象のデータです。右側はVBA実行前の結果表の見出しだけが記載されています。過去記事で単純集計表をご紹介した際のサンプルシートに対して、「在庫」、「在庫1個当たりの資産額」の列を追加しています。

また、結果表の最後の行には「合計」欄も追加しています。

同一シート上に集計させるVBAサンプルコード

それでは、今回のVBAサンプルコードをご紹介しますね。ぜひ、まねして書いてみてください。

では書き終わりましたらVBAを実行してみてください。VBAコードが上手く実行されれば下記のような結果になります。

今回のコードの概要ですが、まず単純集計表と同様の手法で担当者別に「棚卸資産」と「在庫」を集計しています。さらに、それぞれの合計欄を集計することで最終的に担当者全員の合計を集計しています。ここまでを前半のFor ～ Next文で行っています。

つづく後半のFor ～ Next文では「在庫1個当たりの資産額」を算出しています。

では次にVBAコードの詳細の話にうつりますね。

同一シート上に集計させるVBAコードのアルゴリズム

8行目： Range(Cells(8, 11), Cells(15, 13)).ClearContents
結果表の集計範囲を初期化するためにセルの内容（下図赤枠）をクリアしています。

29行目： Cells(lngWRow, 11) = Cells(lngWRow, 11) + Cells(r, 5)
30行目： Cells(15, 11) = Cells(15, 11) + Cells(r, 5)
結果表の「棚卸資産」列へ集計させるVBAコードです。下図はr=2の先頭レコード実行時の様子をあらわしています。

Cells(lngWRow, 11)は、各レコードの「仕入担当」の値によって決まる結果表上のセルになります。またCells(15, 11)は、結果表の「棚卸資産」列(K列, 11列目）の 最下行（15行目）である「合計」を集計するセルです。

繰り返し処理の中で、「仕入担当」別に集計先が決まる29行目のVBAコード（青字）に対し、30行目のVBAコード（赤字）の書込み行は常に15行目です。

つまり、繰り返しの度に、すべてのレコードの「棚卸資産」がこの15行目の合計値に加算されていきます。これが合計値を集計するアルゴリズムになります。

またVBAコードを改めて確認すると、これら2行のコードの違いは、下線部の行指定の値だけですよね。

35行目： Cells(lngWRow, 12) = Cells(lngWRow, 12) + Cells(r, 3)
36行目： Cells(15, 12) = Cells(15, 12) + Cells(r, 3)
これらも29, 30行目の関係と同じです。集計する値が「在庫」になっただけですね。（下図）

図はr=2の時の様子です。VBAコード29, 30行目と比べると、集計する対象の列番号が12に変わっているだけです。サンプルシート12列目(L列)は結果表の「在庫」欄になりますので、これらの式によって、仕入担当別の在庫と総在庫数が求まることになります。

40～43行目： For r = 8 To 15 ～ Next
結果表の右端の列「在庫1個当たりの総資産」を求めるVBAコードになります。

このVBAコードが実行される前に、すべてのレコードの「棚卸資産」と「在庫」の集計が終わることになります。(下図）

そのあと「在庫1個当たりの総資産」をこのFor文で繰り返し集計しています。

41行目： Cells(r, 13) = Cells(r, 11) / Cells(r, 12)
For文の中で繰り返し実行されるVBAコードです。「在庫1個あたりの総資産」は、「棚卸資産」÷「在庫」で計算できます。そこで、結果表の各行rの11列目(K列)「棚卸資産」を12列目(L列)「在庫」で割った値を、13列目(M列)「在庫1個当たりの総資産」に代入しています。

下図に計算式と、計算対象となるセルを色分けで対照表示しましたのでご参照ください。

データチェック作業の効率化に必須の「NumberFormatLocal」プロパティ

42行目： Cells(r, 13).NumberFormatLocal = "0.00"
これもFor文の中で繰り返し実行されるVBAコードです。ここではRangeオブジェクトであるセルの中の書式を指定するプロパティとして使います。表示形式として"0.00"を指定し、小数第二位まで表示させています。

NumberFormatLocalプロパティは、VBAコードでセルの表示形式を変更するためによく使われます。集計作業においては、今回のサンプルのように結果表の体裁を良くして、データの確認がしやすいようにするために使います。

参考のために、わたしが実務でよく使う例をあげておきますね。

ところで、この42行目のVBAコードですが、今回のサンプルのように繰り返し文で実行させる以外に、下図のようにFor文のあとで「1個当たりの総資産」の集計結果の範囲をRangeオブジェクトで指定して使うVBAコードもありです。

または、初期設定のVBAコードの類だと考えて、下図のように、前半の集計範囲をクリアするVBAコードとまとめて書けば、「初期設定のためのVBAコード」として分かりやすくまとめることもできます。

まとめ

集計対象データと同一シート上に結果表を作るVBAコードのサンプルをご紹介しました。毎回行う集計対象データの簡易的なデータチェックであれば、わざわざ別シートにピボットテーブルを作ったり、あるいは別シートにあるピボットテーブルを更新したりするよりは簡単にデータチェックが行えます。

また集計結果の表示形式を変更できる「NumberFormatLocal」プロパティは、集計後のデータ確認作業をする上で便利なプロパティですので、ぜひ使い方をマスターしてくださいね。