サイト内検索

同じシート

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

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

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

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

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

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

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

同一シート上に結果表を作るサンプルシート
同一シート上に結果表を作るサンプルシート

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

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

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

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

同一シート上に結果表を作るVBAサンプルコード
同一シート上に結果表を作るVBAサンプルコード

では書き終わりましたら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の先頭レコード実行時の様子をあらわしています。

Excelシート上で棚卸資産合計を集計するしくみを解説
棚卸資産合計を集計するしくみ

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 ~ Next文実行前の状態

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

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

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

「在庫1個当たりの総資産」を図解
「在庫1個当たりの総資産」を求める

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

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

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

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

NumberFormatLocalプロパティの集計業務上でよく使う例
NumberFormatLocalプロパティ
(集計業務でよく使う例)

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

For文のあとで「1個当たりの総資産」範囲の表示形式を変更する

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

NumberFormatLocalの記載位置をVBAコードの前半にまとめて書く
NumberFormatLocalを初期設定のVBAコードとまとめる

まとめ

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

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

[スポンサーリンク]



サイト内検索

-同じシート
-

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