Categories: VBA基礎

EXCEL VBA表をグループ化による集計・小計・合計(データ集計・グループ化の設定/解除:SubTotalメソッド）

EXCEL VBA 表をグループ化による集計・小計・合計　(データ集計・グループ化の設定・解除：SubTotalメソッド）

●はじめに

今回説明するのは、表をグループ化による集計をEXCEL　VBAを利用して行います。普段業務等でデータを集計する事があると思いますが、このグループ化による集計を使う事で簡単および素早く集計する事ができます。一から集計するブログラムを作成するよりも簡単に作成する事ができます。それでは、サンプルプログラムを交えて順番に説明致します。

●【連想配列でクロス集計・項目別集計は、下記を参照して下さい】

●【SubTotalメゾットについては、下記を参照して下さい（Microsoft社　様）】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.subtotal

●書式の説明　（SubTotalメソッド）

●　SubTotalメソッドを利用するには、下記の通りに設定を行います。

●　オブジェクト.Subtotal (GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

●　SubTotalメゾットの引数について

引数	必須・省略	データ型	説明
GroupBy	必須	Long	グループ化の基準となるフィールドの番号を整数で指定します。
Function	必須	XlConsolidationFunction ※別表参考	小計関数を指定します。
TotalList	必須	Variant	集計をフィールドを指定します。集計するフィールドは、複数指定する事ができます。
Replace	省略可能	Variant	既存の集計表と置き換えるには、Trueを指定します。既定値は、Trueです。
PageBreaks	省略可能	Variant	グループごとに改ページ指定するか設定します。Trueは、改ページします。Falseは、改ページしません。既定値は、Falseです。
SummaryBelowData	省略可能	XlSummaryRow ※別表参照	集計データを小計を上が下に指定します。

●　集計方法の定数一覧（Function）

定数	値	説明
xlAverage	-4106	平均
xlCount	-4112	カウント
xlCountNums	-4113	カウント数値のみ。
xlDistinctCount	11	Distinct Count 分析を使ったカウント
xlMax	-4136	最大
xlMin	-4139	最小
xlProduct	-4149	積
xlStDev	-4155	標本に基づく標準偏差
xlStDevP	-4156	母集団全体に基づく標準偏差
xlSum	-4157	合計
xlUnknown	1000	小計に使用する関数は指定されません。
xlVar	-4164	標本に基づく変動
xlVarP	-4165	母集団全体に基づく変動

●　集計位置について（SummaryBelowData）

定数	値	説明
xlSummaryAbove	0	集計行は、上に設置します。
xlSummaryBelow	1	集計行は、下に設置します。

【使用例】（サンプルプログラム）

'
'
Sub グループ化サンプルSum()
 
  'セルA2を含めた表範囲を指定
  Range("A2").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=2
  'A列(1)を対象に集計・SUM合計で集計・B列(2)数値を対象に合計（集計）
  Range("A2").CurrentRegion.ClearOutline
  '集計グループ化を解除
End Sub
'

【注意点】

●　SubTotalメゾット（グループ化・集計）を実行する際は、集計対象（グループ化：GroupBy）の列は並び替えを実行前に（昇順又は、降順）整列する必要があります。

●　集計対象列を並び替えした場合としない場合

【並び替えを行った場合】・・・正常に集計します。

【並び替えを行わなった場合】・・・集計がバラバラになってしまう。（集計項目が重複する）

EXCEL VBA 表をグループ化（集計対象を１項目）による集計・小計・合計　(データ集計・グループ化の設定）

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

下記のサンプルプログラムは、表をグループ化して集計対象１項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。それでは、順番に説明いたします。

【プログラムの流れ】

①　集計結果が表示されるワークシート「集計」をクリアーします。
②　ワークシート「元データ」に登録されてる表の範囲全体をコピーします。
③　ワークシート「集計」のセル「A1」を指定して、先ほどコピーした内容を貼り付けます。（文字列のみ貼り付け）
④　ワークシート「集計」に貼り付けたデータのA列「勘定科目」ごとに集計します。（C列：税抜き・D列：消費税・E列：合計）
⑤　ワークシート「集計」のアウトラインをクリアー（削除）
⑥　ワークシート「集計」の表範囲に罫線を引く
⑦　ワークシート「集計」A列の最終行と１行目の最終列を取得します。
⑧　ワークシート「集計」の1行目に背景色33の色を塗りつぶします。
⑨　ワークシート「集計」のB列が空白なら集計行なので背景色34の色を塗りつぶします。
⑩　ワークシート「集計」の」C列～E列（最終列）までセルの表示形式を「”#,##0″」にします。
⑪　ワークシート「集計」の表の最終行に背景色3５の色を塗りつぶします。
⑫　ワークシート「集計」をアクティブにする（前面表示）

【プログラム実行条件】

●　ワークシート名：「元データ」を作成して集計元となるデータを登録します。
●　ワークシート名：「集計」を作成します。（ワークシート作成のみ）

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　Grouping①

'
'
Sub Subtotal01() '勘定科目ごと集計（税抜き・消費税・合計）

    Dim ws01, ws02 As Worksheet
    Dim lCol, I, lRow As Long
  
    Set ws01 = Worksheets("元データ")
    Set ws02 = Worksheets("集計")
 
    ws02.Cells.Clear 'シート「集計」をクリアー
    
    ws01.Range("A1").CurrentRegion.Copy  'シート「元データ」を表の範囲をコピー
    
    With ws02.Range("A1")  'シート「集計」のセル「A１」を指定
        .PasteSpecial xlPasteValues  'シート「集計」のセル「A1」に文字列のみ貼り付け
        .CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5)
         'A列を集計元にC・D・E列の数値をSUMで集計します。（集計・総計）
        .CurrentRegion.ClearOutline  ' シート「集計」の アウトラインをクリアー（削除）
        .CurrentRegion.Borders.LineStyle = True  '表の範囲に罫線を引く
        
    End With
    
    
    lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row 'シート「集計」A列の最終行を取得します。
    lCol = ws02.Cells(1, Columns.Count).End(xlToLeft).Column  'シート「集計」１行目の最終列を取得します。
    
    With ws02
        .Range(.Cells(1, 1), .Cells(1, lCol)).Interior.ColorIndex = 33  '1行目に背景色33を指定

        For I = 2 To lRow
            If .Cells(I, "B") = "" Then  'シート「集計」B列が空白なら集計行なので背景色34を指定
                    .Range(ws02.Cells(I, 1), ws02.Cells(I, lCol)).Interior.ColorIndex = 34
            End If
            .Range(ws02.Cells(I, 3), .Cells(I, lCol)).NumberFormatLocal = "#,##0"
            'シート「集計」C列～E列（最終列）までセルの表示形式を「"#,##0"」にします。
        Next I
            .Range(ws02.Cells(I - 1, 1), .Cells(I - 1, lCol)).Interior.ColorIndex = 35
            '最終行の背景色34を指定
            .Activate  'シート「集計」をアクティブにする（前面表示）
    End With

End Sub
'

●実行前～実行後　※プログラム実行後、ワークシート「元データ」を元にワークシート「集計」に勘定科目ごとに集計されました。
（画面クリックして拡大）

EXCEL VBA 表をグループ化（集計対象を２項目）による集計・小計・合計　(データ集計・グループ化の設定）

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

下記のサンプルプログラムは、表をグループ化して集計対象2項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。それでは、順番に説明いたします。

【SubTotalメゾット】の記入方法　(サンプル①とサンプル②の比較）
①　サンプル①の場合
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5)
【説明】GroupBy＝１は、A列をグループ化・TotalListでArrayを使う事で複数列（C,D,E）を集計対象にする事ができます。

②　サンプル②の場合
.Subtotal GroupBy:=Array(1, 2), Function:=xlSum, TotalList:=Array(3, 4, 5)
【説明】GroupBy＝Array(1, 2)は、A列・B列がグルー化・TotalListでArrayを使う事で複数列（C,D,E）を集計対象にする事ができます。
※Arrayを使う事で複数列をグループ・集計対象にする事が出来ます。

【プログラムの流れ】（※基本、プログラムの流れは、サンプル①とほぼ同じです。）

①　集計結果が表示されるワークシート「集計」をクリアーします。
②　ワークシート「元データ」に登録されてる表の範囲全体をコピーします。
③　ワークシート「集計」のセル「A1」を指定して、先ほどコピーした内容を貼り付けます。（文字列のみ貼り付け）
④　ワークシート「集計」に貼り付けたデータのA列「勘定科目」＋B列「内容」ごとに集計します。（C列：税抜き・D列：消費税・E列：合計）
⑤　ワークシート「集計」のアウトラインをクリアー（削除）
⑥　ワークシート「集計」の表範囲に罫線を引く
⑦　ワークシート「集計」B列の最終行と１行目の最終列を取得します。（集計列がB列のため）
⑧　ワークシート「集計」の1行目に背景色33の色を塗りつぶします。
⑨　ワークシート「集計」のA列が空白なら集計行なので背景色34の色を塗りつぶします。
⑩　ワークシート「集計」の」C列～E列（最終列）までセルの表示形式を「”#,##0″」にします。
⑪　ワークシート「集計」の表の最終行に背景色3５の色を塗りつぶします。
⑫　ワークシート「集計」をアクティブにする（前面表示）

【プログラム実行条件】

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　Grouping②

'
'
Sub Subtotal02() '勘定科目＋内容ごとに集計（税抜き・消費税・合計）

    Dim ws01, ws02 As Worksheet
    Dim lCol, I, lRow As Long
  
    Set ws01 = Worksheets("元データ")
    Set ws02 = Worksheets("集計")
 
    ws02.Cells.Clear 'シート「集計」をクリアー
    
    ws01.Range("A1").CurrentRegion.Copy  'シート「元データ」を表の範囲をコピー
    
    With ws02.Range("A1")  'シート「集計」のセル「A１」を指定
        .PasteSpecial xlPasteValues  'シート「集計」のセル「A1」に文字列のみ貼り付け
        .CurrentRegion.Subtotal GroupBy:=Array(1, 2), Function:=xlSum, TotalList:=Array(3, 4, 5)
         'A/B列を集計元にC・D・E列の数値をSUMで集計します。（集計・総計）
        .CurrentRegion.ClearOutline  ' シート「集計」の アウトラインをクリアー（削除）
        .CurrentRegion.Borders.LineStyle = True  '表の範囲に罫線を引く
        
    End With
    
    
    lRow = ws02.Cells(Rows.Count, "B").End(xlUp).Row 'シート「集計」B列の最終行を取得します。
    lCol = ws02.Cells(1, Columns.Count).End(xlToLeft).Column  'シート「集計」１行目の最終列を取得します。
    
    With ws02
        .Range(.Cells(1, 1), .Cells(1, lCol)).Interior.ColorIndex = 33  '1行目に背景色33を指定

        For I = 2 To lRow
            If .Cells(I, "A") = "" Then  'シート「集計」A列が空白なら集計行なので背景色34を指定
                    .Range(ws02.Cells(I, 1), ws02.Cells(I, lCol)).Interior.ColorIndex = 34
            End If
            .Range(ws02.Cells(I, 3), .Cells(I, lCol)).NumberFormatLocal = "#,##0"
            'シート「集計」C列～E列（最終列）までセルの表示形式を「"#,##0"」にします。
        Next I
            .Range(ws02.Cells(I - 1, 1), .Cells(I - 1, lCol)).Interior.ColorIndex = 35
            '最終行の背景色34を指定
            .Activate  'シート「集計」をアクティブにする（前面表示）
    End With

End Sub
'

●実行前～実行後　※プログラム実行後、ワークシート「元データ」を元にワークシート「集計」に勘定科目＋内容ごとに集計されました。
（画面クリックして拡大）

EXCEL VBA 表をグループ化　日付による集計・小計・合計　(データ集計・グループ化の設定）

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

下記のサンプルプログラムは、サンプルプログラム①の応用として表をグループ化して日付を利用した集計対象１項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。今回のプログラムでは、日付をグループ化する時に日付がシリアル値にならないように改良しています。それでは、順番に説明いたします。

①【日付データのまま集計を実行】

②【日付データを文字列に変更して集計を実行】

【改善点】

①　ワークシート「元データ」のA列の日付データを日付から文字列に変更する。
②　ワークシート「元データ」のA列の日付データが登録されているセルの書式設定を文字列に変更する。
③　①で日付データから文字列に変更したデータをA列に文字列データとして転記する。

【プログラム実行条件】

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　Grouping③

'
'
Sub Subtotal03() '日付ごと集計（税抜き・消費税・合計）

    Dim ws01, ws02 As Worksheet
    Dim lCol, I, lRow As Long
    Dim str As String
    Set ws01 = Worksheets("元データ")
    Set ws02 = Worksheets("集計")
 
    ws02.Cells.Clear 'シート「集計」をクリアー

    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row

    For I = 2 To lRow  'シート「元データ」Ａ列の最終行を取得します。
        str = CStr(Format(ws01.Cells(I, "A"), "yyyy/mm/dd")) 'Ａ列の日付データを文字列に変換します・
        ws01.Cells(I, "A").NumberFormat = "@"  'シート「元データ」Ａ列の書式設定を文字列に変更します。
        ws01.Cells(I, "A") = str  'シート「元データ」A列に日付データを文字列に変更したデータをA列のセルに登録します。
    Next I
    
    ws01.Range("A1").CurrentRegion.Copy  'シート「元データ」を表の範囲をコピー
    
    With ws02.Range("A1")  'シート「集計」のセル「A１」を指定
        .PasteSpecial xlPasteValues  'シート「集計」のセル「A1」に文字列のみ貼り付け
        .CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5)
         'A列を集計元にC・D・E列の数値をSUMで集計します。（集計・総計）
        .CurrentRegion.ClearOutline  ' シート「集計」の アウトラインをクリアー（削除）
        .CurrentRegion.Borders.LineStyle = True  '表の範囲に罫線を引く
        
    End With
    
    
    lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row 'シート「集計」A列の最終行を取得します。
    lCol = ws02.Cells(1, Columns.Count).End(xlToLeft).Column  'シート「集計」１行目の最終列を取得します。
    
    With ws02
        .Range(.Cells(1, 1), .Cells(1, lCol)).Interior.ColorIndex = 33  '1行目に背景色33を指定

        For I = 2 To lRow
            If .Cells(I, "B") = "" Then  'シート「集計」B列が空白なら集計行なので背景色34を指定
                    .Range(ws02.Cells(I, 1), ws02.Cells(I, lCol)).Interior.ColorIndex = 34
            End If
            .Range(ws02.Cells(I, 3), .Cells(I, lCol)).NumberFormatLocal = "#,##0"
            'シート「集計」C列～E列（最終列）までセルの表示形式を「"#,##0"」にします。
        Next I
            .Range(ws02.Cells(I - 1, 1), .Cells(I - 1, lCol)).Interior.ColorIndex = 35
            '最終行の背景色34を指定
            .Activate  'シート「集計」をアクティブにする（前面表示）
    End With

End Sub
'
'

●実行前～実行後　※プログラム実行後、ワークシート「元データ」を元に日付ごとにデータが集計されました。
（画面クリックして拡大）

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

AKIRA