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

●実行前と実行後　※プログラム実行後、テーブル機能と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

●実行前～実行後　※プログラム実行後、空白のシート「集計表」にクロス集計表が作成されました。今回は、Sumifsとテーブル機能を組み合わせでクロス集計を作成しました。このように、VBA＋EXCEL関数を組み合わせる事で、複雑なクロス集計も簡単に作成する事ができます。

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

AKIRA