EXCEL VBA テーブル機能とSUMIFS関数を利用したクロス集計（テクニック）

2020年1月17日

EXCEL VBA テーブル機能とSUMIFS関数を利用したクロス集計（テクニック）

●はじめに　【クロス集計】

EXCELの機能でデータベースを管理する場合、とても便利な機能でテーブル機能があります。このテーブル機能は、データベースを管理する場合にとても便利な機能です。VBAと組み合わせて利用する事により、条件による集計やクロス集計なども容易に作成する事ができます。今回は、テーブル機能とSUMIFS関数およびVBAを利用したクロス集計処理の作成方法を説明いたします。

テーブル機能とVBAを組み合わせたクロス集計方法（Sumifs）

●プログラム説明 (サンプルプログラム①)

下記のサンプルプログラムは、テーブル機能と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 デーブルクロス集計()

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

●実行前と実行後　※プログラム実行後、テーブル機能とSUMIFSを組み合わせて、クロス集計を実行しました。事前に上記の通りに事前準備が必要ですが、プログラム的には、１行で集計処理できるので、とても簡単だとおもいます。

テーブル機能とVBAを組み合わせたクロス集計方法　＋　別シートに集計結果表を自動作成

●プログラム説明　(サンプルプログラム②）

下記のサンプルプログラムは、上記のサンプルプログラム①の応用編です。サンプルプログラム①は、事前に別シートに集計結果を作成する必要がありますが、このサンプルプログラムは、シート「集計表」に店舗名・商品名のクロス集計表を自動作成するプログラムです。集計結果により表の項目数が自動調整できるので、結果表として見やすくなります。

※（注意）下記のプログラムを実行する場合は、必ずサンプルプログラム①で説明しました「テーブルデータ」を登録してください。

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