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

2020年1月13日

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

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

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

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,給与所属テーブル[基本給])"