SubTotal【ｻﾌﾞﾄｰﾀﾙ】メソッド
表をグループ化して集計を実行するには、Range【ﾚﾝｼﾞ】オブジェクトのSubTotal【ｻﾌﾞﾄｰﾀﾙ】メソッドを使用します。
SubTotal【ｻﾌﾞﾄｰﾀﾙ】メソッドの書式と設定値(引数)の説明
[]内は省略可能です。
オブジェクト.SubTotal( GuoupBy, Function, TotalList
[, Replace, PageBreaks, SummaryBelowData ])
- オブジェクト（必須）
集計するセル範囲をRange【ﾚﾝｼﾞ】オブジェクトで指定します。単一セルを指定した場合は、そのセルを含むアクティブセル領域が対象になります。 - GuoupBy【ｸﾞﾙｰﾌﾟﾊﾞｲ】（必須）
グループ化の基準となる列を表の左から数えて1から始まる整数値で指定します。 - Function【ﾌｧﾝｸｼｮﾝ】（必須）
集計方法を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列挙型の定数で指定します。
| 定数 | 値 | 内容 |
|---|---|---|
| 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
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
A1セルをWith【ｳｨｽﾞ】ステートメントで指定します。
3行目【.RemoveSubtotal】
RemoveSubtotal【ﾘﾑｰﾌﾞｻﾌﾞﾄｰﾀﾙ】メソッドで集計を解除します。
4行目【.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlYes】
Sort【ｿｰﾄ】メソッドを使用して担当者順になっている表を元のA列の売上日順位戻します。
実行結果
以上で、表をグループ化して集計する SubTotal【ｻﾌﾞﾄｰﾀﾙ】メソッドについての解説を終了します。ありがとうございました。