サイト内検索

別シート 転記する

VBAコードで対象レコードとは別シートに集計させる

集計対象レコードのあるシートとは別シートに、クロス集計表を作ります。対象レコードのフィールド(見出し項目)数が多い場合、同一シート上に表を作成するスペースがありませんよね。そういう時は、対象レコードとは別のシートに結果用の表を作成します。

また、結果を別シートにすることで、そのまま印刷してレポートにする場合にも都合が良いです。羅列されている対象レコードを見る必要がない、あるいは見る時間が無い上司などに、結果だけを報告する場合にも当てはまりますよね。

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

別シートに集計先の表を持つサンプルファイル

それでは、今回のサンプルシートをご紹介しますね。

対象レコードとは別シートにある表
対象レコードとは別シートにある表

サンプルファイルは、対象レコードがある「Data」シートと、表がある「Result」シートで構成されています。

別シート上にある表に集計させるVBAサンプルコード

つづきまして、今回のVBAコードになりますので、ぜひまねして書き写してみてくださいね。

 

では、書き写しましたらVBAを実行してみてくださいね。VBA実行後は下図のように「Result」シートに集計結果が表示されるはずです。

別シートにクロス集計表の結果が表示されている
VBAサンプルコード実行後

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

本記事のVBAサンプルコードのロジックについてお話ししますね。

2行目:Dim wstData As Worksheet
3行目:Dim wstRslt As Worksheet

今回は扱うサンプルシートは複数になりますので、個々のワークシートをVBA上でしっかりと管理するために、それぞれのシートにオブジェクト変数を使います。

Tips
複数のワークシートを扱う場合の考え方

11行目: Set wstData = Worksheets("Data")
12行目: Set wstRslt = Worksheets("Result")

VBAコード2, 3行目で宣言したオブジェクト変数に、各サンプルシートを割り当てるVBAコードになります。

VBAコード14~18行目については、下の図でお話ししますね。

Resultシートを初期化する範囲を解説
「Result」シートの初期化

14~18行目までの間でオブジェクト変数「wstRslt」をWithで扱っています。これにより、16, 17行目については「wstRslt」の記述をせずにワークシートオブジェクト配下のRangeオブジェクトを「.」に続けて指定することができます。

Tips
Withを使うメリット

16行目のVBAコードは、集計結果の表示範囲(青枠)を初期化するために、セルをクリアしています。17行目のVBAコードは、「1個当たりの総資産」(緑色の枠)の表示形式を金額表示に指定しています。

VBAコード 20~52行目によるアルゴリズムについては、下の記事と同じになりますので、ここでは複数シートに対する「With」の使い方について、図の中で補足したいと思います。

Withの取り扱いに関する図解
Withの取り扱いについての補足

Withは、VBAコードの可読性を上げるために使われますが、もう1つのメリットは、開発効率の向上です。今回のサンプルコードの中でどのようにWithを使っているかについてお話しします。

まずVBAコード22~37行目では、「Data」シート上の各レコードから「仕入担当」の値を読み取り、「Result」シート上の書込み行を決定しています(青枠範囲)。

そして、39~50行目で「Result」シート上の表に、集計結果を表示しています(赤枠範囲)。

もう一度全体を見てみますと、「With」がネスティング(入れ子状態)してますよね。

外側の「With wstData」は、青枠範囲のVBAコードが「Data」シートの処理を中心にしているためです。これにより、青枠範囲内の「.Range~」、「.Rows~」、「.Cells~」の3か所で、「wstData」の繰り返し記述を省略できます。

中にある「With wstRslt」では、赤枠の範囲が「Result」シートの処理が中心であるためです。これにより、「.Cells~」の8か所で「wstRslt」の繰り返し記述が省略されます。

ただこの赤枠の範囲内で注意することが1つあります。この「With wstRslt」の範囲(赤枠)ですが、その範囲の処理の中で「Data」シートの読み取り値(「棚卸資産」と「在庫」)を加算しています。(赤枠範囲内の青枠で囲った「wstData.Cells(r, 5)」と「wstData.Cells(r, 3)」)

これらは、「Data」シートの値ですので、間違えて「.Cells(r, 3)」、「.Cells(r, 5)」のように記述しますと、「Result」シートのCells(r, 3)とCells(r, 5)を指定してしまうことになり、正しく集計されませんので注意して下さいね。

最後の54~59行目までは、「Result」シートの処理になりますので、「With wstRslt」を使いました。これにより、「.Cells~」のオブジェクトの繰り返し記述が3か所省略できた上、すっきりとしたコードになりました。

「1個当たりの総資産」を処理するResultシートの説明図
「Result」シートの「1個当たりの総資産」を計算させる処理

まとめ

集計対象データと別シートに表を作成するVBAコードのサンプルを紹介しました。今回のようにVBAコードの中で複数のシートを扱う場合は、必ずオブジェクト変数を使い、コードの中で個別のワークシートをしっかり管理する必要があります。

また複数のシートを「With」で扱う場合、「.」の対象になるワークシートオブジェクトが、どのシートを指しているのかを意識してコーディングしないと、正しい集計結果が得られませんので注意してくださいね。

その上で、「With」の使用は、VBAコードの可読性を上げる以外に、コーディングの効率向上にも大変便利ですので、ぜひ使い方をマスターしてくださいね。

[スポンサーリンク]



サイト内検索

-別シート, 転記する
-

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