処理対象レコードがあるExcelシートと同じシート上に、特定の1項目を分析するための表を作ります。
もちろん、ピボットテーブルを使って作ることも可能です。ただ、いつも決まった表形式であれば、都度別シートのピボットテーブルを更新して確認するよりは、一度VBAコードを組んでしまえば、あとは割り当てたショートカットからVBAを実行する方が楽です。
そこで本記事では、処理対象れコーdの1つの項目について出した結果を、単純集計表の形式で同一シート上に表示させるVBAコードのサンプルをご紹介したいと思います。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画（音声無し）をご確認ください。
では今回のサンプルシートをご紹介します。
左側に処理対象のレコード、右側に単純集計表があります。集計するのは、各仕入担当別の「棚卸資産」の合計です。
続けて今回紹介するVBAコードのサンプルです。ぜひ真似て書いてみてくださいね。
サンプルコードの概要ですが、まず、すべての処理対象レコードを、繰り返し処理します。処理の内容は、「仕入担当」のデータを読み取り、その読み取った値により単純集計表へ書き込む行を決定しています。
書き込み行が決まれば、集計先の列はK列（11列目）ですので、1つのセルが必然的に決まります。そこに「棚卸資産」の値を加算していけば、目的の結果が得られます。
では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 | Sub 棚卸資産() Dim lngWRow As Long '書込み行用変数 Dim lngERow As Long '処理対象レコード最終行番号 Dim r As Long '結果表示範囲の初期化 Range(Cells(8, 11), Cells(14, 11)).ClearContents '処理対象レコード最終行番号取得 lngERow = Range("A" & Rows.Count).End(xlUp).Row 'すべての処理対象レコード繰り返し処理 For r = 2 To lngERow '書込み行判定 Select Case Cells(r, 6) '「仕入担当」の値で判定 Case "井田": lngWRow = 8 '「井田」の場合は8行目 Case "横山": lngWRow = 9 '「横山」の場合は9行目 Case "川村": lngWRow = 10 '「川村」の場合は10行目 Case "太田": lngWRow = 11 '「太田」の場合は11行目 Case "田中": lngWRow = 12 '「田中」の場合は12行目 Case "木村": lngWRow = 13 '「木村」の場合は13行目 Case "鈴木": lngWRow = 14 '「鈴木」の場合は14行目 End Select '決定した書込み行の11列目に、「棚卸資産」の値を加算 Cells(lngWRow, 11) = Cells(lngWRow, 11) + Cells(r, 5) Next End Sub |
2行目： Dim lngWRow As Long
単純集計表のどこの行へ集計するかを保持する変数です。後程お話ししますが、この変数「lngWRow」は、処理対象レコードの「仕入担当」の値により決定します。
8行目： Range(Cells(8, 11), Cells(14, 11)).ClearContents
結果を表示する範囲を初期化するために、該当するセル範囲をクリアしています。
17～25行目： Select Case Cells(r, 6) ～ End Select
VBAコード14～29行目の繰り返し処理の中で実行されるコードです。Select文の条件判定に「Cells(r, 6)」、すなわち「仕入担当」の値を指定しています。
Tips
Select文の使い方
それでは、Select Case ～ End Selectの中身を含めて、図を用意しましたのでこちらで詳しいお話しをしたいと思います。
今回のSelect Case文の目的は、単純集計表のどこの行に集計するかを決めています。図で説明しますと、処理対象のレコードが持つ「仕入担当」の文字列（赤枠）を読み取り、その読み取り値に応じて決まった書込み行番号を変数「lngWRow」に代入しています。
例えば、先頭レコードの仕入担当は「田中」ですので、lngWRow=12になります。これは、単純集計表の見出しに書かれている「田中」の行番号が12だからです。同様に、2番目のレコード「鈴木」はlngWRow=14であり、同様にサンプルシート14行目と一致するようにしています。
28行目： Cells(lngWRow, 11) = Cells(lngWRow, 11) + Cells(r, 5)
前のSelect文で書込み行「lngWRow」が決まりましたので、それに続くこのVBAコードでは、単純集計表の「棚卸資産」の欄に、各レコードの棚卸資産を加算しています。（下図）
赤字の「Cells(r, 5)」は、各レコードの棚卸資産の値です。そして、このCells(r, 5)の集計先のセルが「Cells(lngWRow, 11)」になります。lngWRowは、前で説明しました単純集計表への書込み行番号が格納されています。
そして、「11」はK列に相当しますので、「Cells(lngWRow, 11)」は単純集計表の棚卸資産のセル（上図の列見出しを除いた青枠範囲内）になります。
まとめ
処理対象レコードと同一シート上に、単純集計表を作成するサンプルをご紹介しました。同一シート上に展開した表は、最終結果を出す前の特定の項目に対する値のチェック用としても使えます。
VBAコード自体も基本的な使い方で済みますので、実務にもすぐに応用できます。ぜひ一度本記事でご紹介するサンプルコードで練習してくださいね。