EXCEL VBA テーブル機能とSUMIF関数を利用した条件集計・選択集計（テクニック）

●はじめに

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

テーブル機能とVBAを組み合わせた集計方法（単一条件）

●プログラム説明（サンプルプログラム①）

下記のサンプルプログラムは、テーブル機能とVBAを組み合わせた集計方法になります。事前に、データをテーブルとして設定する必要がありますので、下図を参照に設定してください。なお、下表の通りに設定しないと、プログラムは正常に動作しません。

【下表のデータを作成してテーブル登録します。データ件数は、少なくても構いませんが、項目「社員番号」・・等及び列番号についは、下表の通りに入力します。】

【作成したテーブルの範囲を指定します】
【テーブル名を設定する】
【別シートに役職別集計を作成します】

Sub デーブル集計()

    Dim ws01, ws02 As Worksheet
    Dim lRow  As Long
    
    Set ws01 = Worksheets("給与データ")
    Set ws02 = Worksheets("役職別集計")
    
    lRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row - 1 'シート「役職別集計」B列の最終行
    
    ws02.Range("C2").Resize(lRow, 1).FormulaR1C1 = "=sumif(給与テーブル[役職名],RC[-1],給与テーブル[支給合計])"
   
 '　↑　シート「役職別集計」のセル「C2」から役職名の数「lRow」までsumifの計算式を複写する。

End Sub

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

【上記のテーブルデータを元に職名ごとに集計します】

テーブル機能とVBAを組み合わせた集計方法（単一条件）＋　別シートに集計結果表を作成

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

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

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

Sub デーブル集計2()  '集計表作成

    Dim ws01, ws02 As Worksheet
    Dim lRow, mRow As Long
    
    Set ws01 = Worksheets("給与データ")
    Set ws02 = Worksheets("役職別集計")
    
    mRow = ws01.Cells(Rows.Count, "D").End(xlUp).Row 'シート「給与データ」B列「役職名」の最終行
    
    ws02.Cells.Clear 'シート「役職別集計」を全てクリア
    
    ws01.Range("D1:D" & mRow).Copy 'シート「給与データ」のD列「役職名」をD列の最終行までコピー
    ws02.Range("B1").PasteSpecial xlPasteValues  'シート「役所別集計」のB列にB1より値を貼り付ける。
    
    
    ws02.Range("B1:B" & mRow).RemoveDuplicates Columns:=1, Header:=xlYes  ' シート「役所別集計」B列の重複業を削除
    
    lRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row - 1 'シート「役職別集計」B列「役職名」最終行のマイナス１（役職名の数）
    
    
    ws02.Range("C2").Resize(lRow, 1).FormulaR1C1 = "=sumif(給与テーブル[役職名],RC[-1],給与テーブル[支給合計])"
     '　↑　シート「役職別集計」のセル「C2」から役職名の数「lRow」までsumifの計算式を複写する。

    ws02.Range("C1") = "支給合計"
    ws02.Range("B1:C1").Interior.ColorIndex = 37 'シート「役職別集計」B1～C1の先頭行の背景色に色を付ける。
    ws02.Range("B1:C" & lRow + 1).Borders.LineStyle = xlContinuous 'シート「役職別集計」B1～Cの最終行まで格子罫線を引き
    ws02.Range("C2:C" & lRow + 1).NumberFormatLocal = "#,##0;[赤]-#,##0" 'シート「役職別集計」C2～Cの最終行まで桁区切り表示

End Sub

●実行前～実行後　※プログラム実行後、自動に集計表がデータの項目に応じて作成しました。

テーブル機能とVBAを組み合わせた集計方法（選択条件）＋　別シートに集計結果表を作成

●プログラム説明（サンプルプログラム③）

下記のサンプルプログラムは、上記のサンプルプログラム①②を更に応用したサンプルプログラムです。今回は、シート「集計表」に集計区分を設定し、２つの区分「役職名」と「所属名」のいづれかを選択して、選択した区分に応じて、別シートに集計するサンプルプログラムです。下記のサンプルプログラムを実行する前に、サンプルプログラム①で説明しました様に事前に設定・登録する必要な事がありますので、下図を参照して下さい。

※（注意）下記のプログラムを実行する場合は、下図の説明通りに設定をして下さい。

【下記のプログラムは、シート「集計表」に集計区分を設定して、集計区分に「所属名」・「役職名」いづれかを選択して「実行」を行い選択した集計表が作成されます。】

【集計区分の作成およびリストの作成方法】

【実行ボタンの作成については、下記のURLを参照して下さい】
https://akira55.com/button/

Sub デーブル集計3() '選択区分

    Dim ws01, ws02 As Worksheet
    Dim lRow, mRow, Retsu, I As Long
    Dim Kubun As String
    Dim sentaku As String
    
    Set ws01 = Worksheets("給与所属データ")
    Set ws02 = Worksheets("集計表")
    
    
    mRow = ws01.Cells(Rows.Count, "C").End(xlUp).Row 'シート「集計表」C列「最終行]の最終行
    ws02.Range("B6:I" & mRow + 1).Clear  '上記の最終行を取得して事前にシート集計表データをクリアーする。
    
    Kubun = ws02.Range("B2")  '選択した所属名か役職名を取得する。
    
    Select Case Kubun    '選択した区分に応じてシート集計表に貼り付け内容を指定します。
    
        Case Is = "所属名"
            sentaku = "D1:D"
            
        Case Is = "役職名"
            sentaku = "E1:E"
        
        Case Else
            MsgBox "所属名か役職名を選択して下さい。"
            Exit Sub  '所属名または、役職名のいづれかが選択されていないとプログラム終了
            
    End Select

    ws01.Range(sentaku & mRow).Copy ''シート集計表の「B2」の選択により、シート「給与所属データ」のD列または、E列の最終行までコピー
    ws02.Range("B5").PasteSpecial xlPasteValues  'シート「集計表」のB列にB5より値を貼り付ける。
    
    
    mRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row 'シート「集計表」B列「最終行]の最終行
    ws02.Range("B5:B" & mRow).RemoveDuplicates Columns:=1, Header:=xlYes  ' シート「役所別集計」B列の重複業を削除
    
    
    lRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row - 5 'シート「集計表」B列の最終行からマイナス5（項目の件数）

     

    With ws02.Range("C6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[基本給])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    With ws02.Range("D6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[役職手当])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    With ws02.Range("E6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[住宅手当])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    With ws02.Range("F6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[家族手当])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With

    With ws02.Range("G6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[残業手当])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    With ws02.Range("H6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[休出手当])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    With ws02.Range("I6")
        .Formula = "=sumif(給与所属テーブル[" & Kubun & "],$B6,給与所属テーブル[支給合計])"
        .AutoFill Destination:=.Resize(lRow, 1) 'AutoFillを使い横６列分複写する
    End With
    
    lRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row

    ws02.Range("B5:B" & lRow).Interior.ColorIndex = 37 'シート「集計表」B5～B列の最終行まで背景色に色を付ける。
    ws02.Range("B5:I" & lRow).Borders.LineStyle = xlContinuous 'シート「集計表」B5～Iの最終行まで格子罫線を引き
    ws02.Range("C6:I" & lRow).NumberFormatLocal = "#,##0;[赤]-#,##0" 'シート「集計表」C6～Iの最終行まで桁区切り表示
    
        
End Sub

●実行前～実行後　　※プログラム実行を集計区分に応じて集計する区分によりデータが集計されました。

今回、EXCEL関数とVBAを組み合わせたプログラムになりましたが、このようなお互いの機能の良いところを合わせる事で、複雑な作業も簡潔なプログラムで作成する事が出来ますので、今後もこのようなプログラムを紹介させて頂きます。

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

AKIRA