下記のサンプルプログラムは、テーブル機能とVBAを組み合わせたクロス集計方法になります。事前に、データをテーブルとして設定する必要がありますので、下図を参照に設定してください。なお、下表の通りに設定しないと、プログラムは正常に動作しません。
【下表のデータを作成してテーブル登録します。データ件数は、少なくても構いませんが、項目「店舗名」・「管理番号」・・等及び列番号についは、下表の通りに入力します。】
【合計に計算式とシート集計表を作成します】
【以上で事前作業が終了】
Sub デーブルクロス集計()
Dim ws01, ws02 As Worksheet
Dim lRow, mCol As Long
Set ws01 = Worksheets("売上データ")
Set ws02 = Worksheets("集計表")
lRow = ws02.Range("C4").CurrentRegion.Rows.Count - 1 '所属名の個数（B列の縦）
mCol = ws02.Range("C4").CurrentRegion.Columns.Count - 1 '職名の個数（3行名の横）
ws02.Range("C4").Resize(lRow, mCol).FormulaR1C1 = "=sumifs(売上テーブル[合計],売上テーブル[店舗名],RC2,売上テーブル[商品],R3C)"
'テーブル機能とSumIfs関数を使って、クロス集計表を作成します。
End Sub
下記のサンプルプログラムは、上記のサンプルプログラム①の応用編です。サンプルプログラム①は、事前に別シートに集計結果を作成する必要がありますが、このサンプルプログラムは、シート「集計表」に店舗名・商品名のクロス集計表を自動作成するプログラムです。集計結果により表の項目数が自動調整できるので、結果表として見やすくなります。
※（注意）下記のプログラムを実行する場合は、必ずサンプルプログラム①で説明しました「テーブルデータ」を登録してください。
Sub デーブルクロス集計2() '集計表作成
Dim ws01, ws02 As Worksheet
Dim lRow, mRow, I As Long
Dim Dic01, Dic02 As Collection
Set ws01 = Worksheets("売上データ")
Set ws02 = Worksheets("集計表")
Set Dic01 = New Collection 'コレクション設定（店舗名）
Set Dic02 = New Collection 'コレクション設定（商品）
ws02.Cells.Clear 'シート「集計表」をクリアーします。
lRow = ws01.Cells(Rows.Count, "B").End(xlUp).Row 'シート「売上データ」B列の最終行を取得
On Error Resume Next '店舗名・商品名の重複データが発生するとエラーとなるので、エラーが発生しても継続
For I = 2 To lRow
Dic01.Add ws01.Cells(I, "B"), ws01.Cells(I, "B") '店舗名のリストを作成（重複なし）
Dic02.Add ws01.Cells(I, "D"), ws01.Cells(I, "D") '商品名のリストを作成（重複なし）
Next I
On Error GoTo 0
For I = 1 To Dic01.Count
ws02.Cells(3 + I, "B") = Dic01(I) '店舗名のリストをB列の4行名から順番に転記する
Next I
For I = 1 To Dic02.Count
ws02.Cells(3, 2 + I) = Dic02(I) '商品名のリストを３行のC列名から順番に転記する。
Next I
ws02.Range("C4").Resize(Dic01.Count, Dic02.Count).FormulaR1C1 = "=sumifs(売上テーブル[合計],売上テーブル[店舗名],RC2,売上テーブル[商品],R3C)"
'テーブル機能とSumIfs関数を使って、クロス集計表を作成します。
ws02.Range("B3") = "店舗名"
ws02.Range("B3:B" & 3 + Dic01.Count).Interior.ColorIndex = 37 'シート「集計表」B3～店舗名最終行まで背景色に色を付ける。
ws02.Range(Cells(3, "C"), Cells(3, 2 + Dic02.Count)).Interior.ColorIndex = 34 'シート「集計表」B3～商品名の最終列まで背景色に色を付ける。
ws02.Range(Cells(3, "B"), Cells(3 + Dic01.Count, 2 + Dic02.Count)).Borders.LineStyle = xlContinuous
'シート「集計表」B3～の店舗名の最終行と商品名の最終列まで間、格子罫線を引く
ws02.Range(Cells(4, "C"), Cells(3 + Dic01.Count, 2 + Dic02.Count)).NumberFormatLocal = "#,##0;[赤]-#,##0"
'シート「集計表」C4～データの最終行,最終行まで桁区切り表示する。
End Sub
