Excel VBA 表をグループ化して集計する SubTotalメソッド

スポンサーリンク

SubTotal【サブトータル】メソッド

表をグループ化して集計を実行するには、Range【レンジ】オブジェクトのSubTotal【サブトータル】メソッドを使用します。

SubTotal【サブトータル】メソッドの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト.SubTotal( GuoupBy, Function, TotalList
[, Replace, PageBreaks, SummaryBelowData ])

  • オブジェクト(必須)
    集計するセル範囲をRange【レンジ】オブジェクトで指定します。単一セルを指定した場合は、そのセルを含むアクティブセル領域が対象になります。
  • GuoupBy【グループバイ】(必須)
    グループ化の基準となる列を表の左から数えて1から始まる整数値で指定します。
  • Function【ファンクション】(必須)
    集計方法をxlConsolidationFunction列挙型の定数で指定します。
xlConsolidationFunction列挙型の定数
定数 内容
xlSum -4157 合計
xlAverage -4106 平均
xlCount -4112 個数
xlCountNums -4113 数値の個数
xlMax -4136 最大値
xlMin -4139 最小値
xlProduct -4149 積(掛け算)
xlStDev -4155 標本に基づく標準偏差
xlStDevP -4156 母集団全体に基づく標準偏差
xlVar -4164 標本に基づく変動
xlVarP -4165 母集団全体に基づく変動
xlDistinctCount 111 Distinct Count 分析を使ったカウント
xlUnknown 1000 小計に使用する関数は指定されません。

  • TotalList【トータルリスト】(必須)
    集計を表示する列を配列で指定します。例えば、2列目~4列目であれば、Array関数を使用して「TotalList:=Array(2,3,4)」と記述します。
  • Replace【リプレイス】(省略可)
    既存の集計表と置き換える場合はTrue(既定値)を指定します。
  • PageBreaks【ページブレイクス】(省略可)
    グループごとに改ページする場合はTrueを指定します。省略した場合はFalseが指定され改ページされません。
  • SummaryBelowData【サマリーベロウデータ】(省略可)
    集計結果を表示する位置をXlSummaryRow列挙型の定数で指定します。
XlSummaryRow列挙型の定数
定数 内容
xlSummaryAbove 0 集計行を詳細行の上位置に表示します。
xlSummaryBelow 1 集計行を詳細行の下位置に表示します。(既定値)

【実行前】日ごと、担当者ごとの売上表

担当者をグループにして売上を集計するコードと解説

Sub 集計()
With Range("A1")
.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
.Subtotal groupby:=2, Function:=xlSum, totallist:=Array(3)
End With
ActiveSheet.Outline.ShowLevels (2)
End Sub
2行目【With Range(“A1”)】
A1セルをWith【ウィズ】ステートメントで指定します。


3行目【.Sort Key1:=Range(“B1”), Order1:=xlAscending, Header:=xlYes】
Sort【ソート】メソッドで売上日順になっている表をグループの基準になる担当者で昇順の並べ替えします。


4行目【.Subtotal groupby:=2, Function:=xlSum, totallist:=Array(3)】
Subtotal【サブトータル】メソッドを使用して担当者を基準に3列目の売上を合計で集計します。


6行目【ActiveSheet.Outline.ShowLevels (2)】
ShowLevels【ショウレベルズ】メソッドを使用してアウトラインのレベルを2に指定します。


実行結果


RemoveSubTotal【リムーブサブトータル】メソッド

SubTotal【サブトータル】メソッドによって作成された集計行やアウトラインを解除するには、Range【レンジ】オブジェクトの
RemoveSubTotal【リムーブサブトータル】メソッドを使用します。

RemoveSubTotal【リムーブサブトータル】メソッドの書式と設定値の説明

オブジェクト.RemoveSubTotal

  • オブジェクト(必須)
    解除するセル範囲をRange【レンジ】オブジェクトで指定します。単一セルを指定した場合は、そのセルを含むアクティブセル領域が対象になります。

集計された表を元に戻すコードと解説

Sub 集計解除()
With Range("A1")
.RemoveSubtotal
.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End With
End Sub
2行目【With Range(“A1”)】
A1セルをWith【ウィズ】ステートメントで指定します。


3行目【.RemoveSubtotal】
RemoveSubtotal【リムーブサブトータル】メソッドで集計を解除します。


4行目【.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlYes】
Sort【ソート】メソッドを使用して担当者順になっている表を元のA列の売上日順位戻します。


実行結果


以上で、表をグループ化して集計する SubTotal【サブトータル】メソッドについての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告