VBAコードで集計データを別シートに帳票形式で集計させる

集計対象レコードがあるシートとは別シートに、任意形式の帳票タイプで集計表を作ります。集計表を帳票形式にすることにより、ある切り口でデータ分析をしやすくなるだけではなく、プレゼン用として、そのままパワーポイントなどの資料に張り付けて使えます。

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

帳票形式表のExcelサンプルシート

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

今回は、別ショットで集計先シート「Slip」をご紹介します。

今回集計先になる「Slip」シートですが、7名の担当分別に同じ形式で縦に並んでいます。左側の赤枠の範囲に、加算集計される「在庫」、「原価」、「棚卸総資産」の合計があります。さらに「1個当たりの総資産」をVBAコードで求めます。

右側の青枠の範囲は、集計元データから「仕入担当」別の商品情報をVBAで転記する場所になります。

このような集計表にすることで、担当者別の情報がより分かりやすくなりますよね。

帳票形式表に集計させるVBAサンプルコード

では、今回ご紹介するVBAサンプルコードになります。ぜひ、まねして書き写してみてくださいね。

では書き写しましたらVBAを実行してみてください。下記のように各仕入担当の欄に集計結果が表示されましたでしょうか。

では続きまして、VBAコードの説明に入りたいと思います。

帳票形式の集計表へ集計させるVBAサンプルコードのロジック

7行目： Dim lngWRw0 As Long
「Slip」シート側への最終的な書込み行を格納する変数は「lngWRow」です。ここでは、その「lngWRow」を決めるための基準になる行番号を変数「lngWRw0」で宣言しています。詳しくは後ほどお話しします。

22行目： .Range(.Cells(r, 10), .Cells(r + 4, 10)).NumberFormatLocal = "yyyy/mm/dd"
「Slip」シートのリスト転記範囲にある「価格更新日」の表示形式を設定するVBAコードです。

Tips
VBAでセルの表示形式を変更するNumberFormatLocalプロパティ

帳票形式表のリストアップ部と加算集計部に集計するロジック

31～68行目： For r = 2 To lngERow ～ Next
この間の繰り返し処理で、「Slip」シートの「1個当たりの総資産」を除くすべての集計とリストアップをVBAで行います。

処理手順としては、まず「Data」シートのレコードから「仕入担当」データを読み取り、その値に対応した「Slip」シート上の基準行を判定します。その後、商品情報をリストアップする書込み先の行の書込み可否状態をチェックします。

書込み可能な行に商品情報をリストアップした後は、左側の「在庫」、「原価」、「棚卸資産」を加算集計して次のレコードの処理にうつります。

帳票形式表の書込み基準行番号を決定するロジック

33～41行目： Select Case .Cells(r, 6) ～ End Select
繰り返し処理の中で実行されるVBAコードです。集計対象データ6列目の「仕入担当」の値（.Cells(r, 6))を読み取り、「Slip」シートの書込み先基準行を判定します。（下図）

シートは「Slip」データの一部です。そのシート上で行番号を含めて赤枠で囲った行が書込みの基準になる行番号としています。

左上に書いてあるVBAコードの方でも確認しますね。レコードの「仕入担当」の読み取り値（.Cells(r, 6))に応じて、書込み基準の行番号を格納する変数「lngWRw0」の値が決められていますよね。

例えば「仕入担当」の値「.Cells(r, 6)」が"井田"の場合は、書込み基準行を格納する変数「lngWRw0」に4が入ります。これは、図右側のシートの「井田」を集計するデータエリアの赤枠で囲った行番号「4」と一致していますよね。

同様に、各仕入担当についても確認してみてください。ポイントは、このような同じ形をした表に対し、同じ位置（サンプルの場合は、リストアップ書込み開始行）に基準行を指定することです。

そうすることで、このサンプルの場合は各仕入担当の基準行が4, 11, 18, ... のように7つ飛びになり、VBAコードで処理するのに都合がよくなります。

VBAでリストアップ部への上書き防止を判定させるアルゴリズム

44行目： lngWRow = lngWRw0
書込み基準行が決まった次の処理になります。最終的に書込みをする行番号を保持する変数「lngWRow」に、書込み基準行「lngWRw0」を初期値として代入しています。

48～54行目：Do ～ Loop
リストアップエリアに実際に書き込めるかどうかを、チェックする処理範囲になります。44行目のVBAコードでは、書込み行の初期値として書込み基準行と同値を設定しています。

それに続くこのDo ～ Loopの中で、実際に「Slip」シート上の集計先が書き込めるかどうかを判定しています。

判定する必要についてですが、担当者によっては複数の商品を担当するケースがありますよね。その場合、リストアップされる度に一行ずつリストアップエリアの行が埋められていきます。

そうなると、いつも同じ書込み行を指定すると、もし既存のデータがある場合、後から処理されるデータに上書きされてしまいます。そのような理由で、書き込む前に空いている行を、VBAで確認しているのです。

具体例と図を使ってもう少しお話ししたいと思います。次の図は、仕入担当者が「井田」の場合です。

この場合、まずVBAコード33行目（図の右上に記載あり）のSelect文で書込み基準行を保持する変数「lngWRw0」には「4」が入ります。すると44行目では、実際に書込みする行を保持する変数「lngWRow」にも初期値として「4」が入ります。

その状態で、Do ～ Loop内で最終的な書き込み可能な行をチェックします。

まずIf文の条件式の左辺は、この場合「Slip」シート上のCells(4, 5)になります。それと右辺の「""」が等しくなければThen以降の処理をすることになります。すなわち、Cells(4, 5)が空欄じゃない場合は、Then以降のVBAコードが実行されます。

ところが、図のシート上Cells(4, 5)は、空欄ですからIf文の条件式は偽になり、処理はVBAコード51行目のElse以下の処理が実行されます。

Else以下の処理は、52行目のExit Do、すなわち「Do ～ Loopを抜けなさい」ですので、処理が55行目以降に飛びます。

念のため、「井田」の2つ目の商品の場合（「井田」の2回目の処理）についても次の図で補足しますね。

今度は2つ目の商品ですから、既に図のように「イチゴ」に関する情報が転記されてますよね。2つ目の商品の処理でもVBAコード44行目によって、書込み行を保持する変数「lngWRow」は「4」に初期化されます。

よって、Do ～ Loop処理は1つ目の商品の時と同様、必ず基準行「4」からチェックされます。

今回は、VBAコード49行目のIf文の条件式は「真」、すなわち「空欄じゃない」（「イチゴ」がインプットされている）ですので、そのままThen以降のVBAコードが実行されます。

そのThen以降のVBAコード50行目では書込み行に+1しますので、この時変数「lngWRow」は、「5」になります。その後、50行目のLoopにより再び49行目のIf文の条件式で評価されます。

その時はCells(5, 5)は空欄になりますので、ここで最終的な書込み行が「5」に決定します。あとは前半のお話しと同様の処理の流れになります。

VBAで集計対象レコードの各データを、帳票形式表へ転記させるアルゴリズム

VBAコード56～66行目は、「Data」シート上のレコード個々のデータを、「Slip」シート上に転記しています。（下図）

図の赤枠・赤字は帳票右側のリストアップエリアに関する処理の説明です。また青枠・青字は、帳票左側の加算集計エリアに関する処理の説明です。

左上のVBAコードを見ると、9つの代入式からなる処理になってますよね。赤色のリストアップエリアでは、集計対象レコードの各データ（右辺）を、「Slip」シート上のリストアップエリアの6項目にそれぞれ代入しています。

青色の加算集計エリアでは、集計対象レコードの各データと「Slip」シート上の集計先セルの値をVBAコード内で合算したものを、再び集計先セルに代入しています。

図に示すように、これら加算集計エリアの各項目の集計先行番号は、各仕入担当別に決めた書込み基準行「lngWRw0」から、同じオフセット量で指定することができます。

このように書込み基準行の考え方は、いろいろな表への書込みを行う際に、大変便利な考え方ですので、ぜひマスターしてくださいね。

補足として、緑色の文字「With wstSlip」により、主に左辺などで「wstSlip」の記述を省略しています。

Tips
VBAコードの可読性を上げるWith

帳票形式表の計算処理部へ計算結果を表示するロジック

72～74行目： For r = 8 To 50 Step 7 ～ Next
「Slip」シート上の「1個当たりの総資産」を計算させるVBAコードです。この処理に入る前に、すべてのレコードのデータが「Slip」シートに転記されましたので、この繰り返し処理により「1個当たりの総資産」をVBAコードで計算させます。

「Slip」シート上の「1個当たりの総資産」は、「井田」のr=8から始まり、7行置きに各担当の「1個当たりの総資産」の入力セルがあります。これをFor文で繰り返し処理をしています。

73行目： .Cells(r, 3) = .Cells(r - 1, 3) / .Cells(r - 3, 3)
繰り返し処理の中で実行されるVBAコードです。「1個当たりの総資産」は、「Slip」シート上の「総資産」を「在庫合計」で除して求めることができます。

まとめ

帳票形式の表は、ある特定の切り口で集計データを分析するのに便利ですよね。本記事では「仕入担当」別の帳票形式に分けた表をサンプルとして紹介しました。

本記事で紹介した基本的な手法をマスターすることで、意図したグラフを作成するための元になる表の作成も容易になります。また帳票形式は、プレゼン用の表として、コピペしてパワポに貼って使うことができます。

ぜひここでご紹介したサンプルコードをマスターして、いろいろな形の集計表作成にチャレンジしてみてくださいね。