集計対象レコードのあるシートとは別シートに、クロス集計表を作ります。対象レコードのフィールド（見出し項目）数が多い場合、同一シート上に表を作成するスペースがありませんよね。そういう時は、対象レコードとは別のシートに結果用の表を作成します。
また、結果を別シートにすることで、そのまま印刷してレポートにする場合にも都合が良いです。羅列されている対象レコードを見る必要がない、あるいは見る時間が無い上司などに、結果だけを報告する場合にも当てはまりますよね。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画（音声無し）をご確認ください。
目次
別シートに集計先の表を持つサンプルファイル
それでは、今回のサンプルシートをご紹介しますね。
サンプルファイルは、対象レコードがある「Data」シートと、表がある「Result」シートで構成されています。
別シート上にある表に集計させるVBAサンプルコード
つづきまして、今回のVBAコードになりますので、ぜひまねして書き写してみてくださいね。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | Sub 結果を別シートに表示させる() Dim wstData As Worksheet '対象レコード用シート「Data」用 Dim wstRslt As Worksheet '結果表示用シート「Result」用 Dim lngWRow As Long '表への書込み行 Dim lngERow As Long '対象レコード最終行番号 Dim r As Long '2つのシートをオブジェクト変数にセット Set wstData = Worksheets("Data") Set wstRslt = Worksheets("Result") With wstRslt '表の集計エリア初期化 .Range(.Cells(2, 2), .Cells(9, 4)).ClearContents .Range(.Cells(2, 4), .Cells(9, 4)).NumberFormatLocal = "\#,##0" End With With wstData '対象レコード最終行番号取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row '対象レコード繰り返し処理 For r = 2 To lngERow '表への書込み行判定 Select Case .Cells(r, 6) '「仕入担当」の値で判定 Case "井田": lngWRow = 2 '「井田」の場合は8行目 Case "横山": lngWRow = 3 '「横山」の場合は9行目 Case "川村": lngWRow = 4 '「川村」の場合は10行目 Case "太田": lngWRow = 5 '「太田」の場合は11行目 Case "田中": lngWRow = 6 '「田中」の場合は12行目 Case "木村": lngWRow = 7 '「木村」の場合は13行目 Case "鈴木": lngWRow = 8 '「鈴木」の場合は14行目 End Select With wstRslt '決定した書込み行の11列目に、「棚卸資産」の値を加算と '同時に「棚卸資産」の合計欄にも加算 .Cells(lngWRow, 2) = .Cells(lngWRow, 2) + wstData.Cells(r, 5) .Cells(9, 2) = .Cells(9, 2) + wstData.Cells(r, 5) '決定した書込み行の12列目に、「在庫」の値を加算と '同時に「在庫」の合計欄にも加算 .Cells(lngWRow, 3) = .Cells(lngWRow, 3) + wstData.Cells(r, 3) .Cells(9, 3) = .Cells(9, 3) + wstData.Cells(r, 3) End With Next End With With wstRslt '1在庫当たりの棚卸資産額を求め、結果を小数第2位に揃える For r = 2 To 9 .Cells(r, 4) = .Cells(r, 2) / .Cells(r, 3) Next End With End Sub |
では、書き写しましたらVBAを実行してみてくださいね。VBA実行後は下図のように「Result」シートに集計結果が表示されるはずです。
別シート上にある表に集計させるVBAコードのアルゴリズム
本記事のVBAサンプルコードのロジックについてお話ししますね。
2行目：Dim wstData As Worksheet
3行目：Dim wstRslt As Worksheet
今回は扱うサンプルシートは複数になりますので、個々のワークシートをVBA上でしっかりと管理するために、それぞれのシートにオブジェクト変数を使います。
11行目： Set wstData = Worksheets("Data")
12行目： Set wstRslt = Worksheets("Result")
VBAコード2, 3行目で宣言したオブジェクト変数に、各サンプルシートを割り当てるVBAコードになります。
VBAコード14～18行目については、下の図でお話ししますね。
14～18行目までの間でオブジェクト変数「wstRslt」をWithで扱っています。これにより、16, 17行目については「wstRslt」の記述をせずにワークシートオブジェクト配下のRangeオブジェクトを「.」に続けて指定することができます。
Tips
Withを使うメリット
16行目のVBAコードは、集計結果の表示範囲（青枠）を初期化するために、セルをクリアしています。17行目のVBAコードは、「1個当たりの総資産」（緑色の枠）の表示形式を金額表示に指定しています。
VBAコード 20～52行目によるアルゴリズムについては、下の記事と同じになりますので、ここでは複数シートに対する「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か所省略できた上、すっきりとしたコードになりました。
まとめ
集計対象データと別シートに表を作成するVBAコードのサンプルを紹介しました。今回のようにVBAコードの中で複数のシートを扱う場合は、必ずオブジェクト変数を使い、コードの中で個別のワークシートをしっかり管理する必要があります。
また複数のシートを「With」で扱う場合、「.」の対象になるワークシートオブジェクトが、どのシートを指しているのかを意識してコーディングしないと、正しい集計結果が得られませんので注意してくださいね。
その上で、「With」の使用は、VBAコードの可読性を上げる以外に、コーディングの効率向上にも大変便利ですので、ぜひ使い方をマスターしてくださいね。