Subtotal関数を利用した集計

このページでは Subtotal 関数について説明します。

Subtotal 関数は第1引数に集計機能に対応した数値を指定することで、さまざまな集計処理が行える関数です。例えば、合計処理を行いたい場合、第1引数に”9″を指定して次のように使用します。

sum = WorksheetFunction.Subtotal( 9, Range("B4:B13") )

～目次～

集計機能
Subtotal 関数
1. 使用例
【補足】Subtotal 関数を使用せずに集計する

集計機能

合計以外にも平均値の計算やセルのカウント、最大値・最小値の取得などが可能です。以下に第1引数とその集計機能の対応表を記載します。

Subtotal 関数の第1引数に指定可能な数値と
その集計機能との対応表
集計方法	集計機能	対応する関数
1	平均計算	AVERAGE
2	数値セルのカウント	COUNT
3	入力済セルのカウント	COUNTA
4	最大値取得	MAX
5	最初値取得	MIN
6	積計算	PRODUCT
7	標本による標準偏差計算	STDEV
8	母集団による標準偏差計算	STDEVP
9	合計計算	SUM
10	標本による分散計算	VAR
11	母集団による分散計算	VARP

しかし、ただ合計するだけなら Sum 関数を使った方がコードを見たときにわかりやすいです。では、Subtotal 関数を使用する理由は何でしょうか？

それは、オートフィルタによって非表示となった行を自動的に集計対象外としてくれる機能があるためです。この機能は上記表のすべての集計機能に適用されます。

また、Subtotal 関数の第1引数には、前述した1～11の数値以外にも、これらの数値に100を加えた101～111の数値を指定することが可能です。この100を加えた数値を指定すると、手動で非表示（オートフィルタは使用しない）に設定した行を集計対象外とすることもできます。

これらの使用方法は後述の使用例で取り上げていますので、その違いを理解していただけると思います。

Subtotal 関数の第1引数に指定可能な数値と
その集計機能との対応表（非表示行集計対象外）
集計方法	集計機能	対応する関数
101	平均計算	AVERAGE
102	数値セルのカウント	COUNT
103	入力済セルのカウント	COUNTA
104	最大値取得	MAX
105	最初値取得	MIN
106	積計算	PRODUCT
107	標本による標準偏差計算	STDEV
108	母集団による標準偏差計算	STDEVP
109	合計計算	SUM
110	標本による分散計算	VAR
111	母集団による分散計算	VARP

Subtotal 関数

構文

WorksheetFunction.Subtotal(集計方法, 範囲1 [, 範囲2…])

【戻り値】 倍精度浮動小数点型 (Double)

引数名	省略	説明
集計方法	×	前述の集計方法を指定します。集計方法にかかわらず、オートフィルタにより非表示となった行は集計対象外となります。
範囲	※	集計対象のセル範囲（レンジオブジェクト）を指定します。

※…1つ目は省略不可。2つ目以降は任意。

使用例

以下のマクロはワークシート上にある売上表において、1月売上の合計値を3種類の方法で計算してその計算結果を比較するものです。

合計方法[1]・・・Sum 関数を使用
合計方法[2]・・・Subtotal 関数を使用（第1引数 = 9）
合計方法[3]・・・Subtotal 関数を使用（第1引数 = 109）

Sub sample_wf015_01()
    Dim sumRng As Range
    Dim s As Double

    '集計対象範囲の設定
    Set sumRng = Range("B4:B13")

    '[1]Sum関数
    s = WorksheetFunction.Sum(sumRng)
    Debug.Print "[1]合計 " & s

    '[2]Subtotal 集計方法=9
    s = WorksheetFunction.Subtotal(9, sumRng)
    Debug.Print "[2]合計 " & s

    '[3]Subtotal 集計方法=109
    s = WorksheetFunction.Subtotal(109, sumRng)
    Debug.Print "[3]合計 " & s
End Sub

Setステートメント

ケース1：フィルターなし

特にオートフィルタや、非表示行がない通常のケースです。
当然、3種類の合計値はすべて同じになります。

※赤色点線範囲が合計対象範囲

マクロ実行結果（イミディエイトウィンドウ）
```
[1]合計 1589
[2]合計 1589
[3]合計 1589
```

ケース2：フィルターあり

店舗名でオートフィルタをかけたケースです。
Subtotal 関数を使用した方法は、表示されている行だけ合計されていることがわかります。

マクロ実行結果（イミディエイトウィンドウ）
```
[1]合計 1589
[2]合計 527
[3]合計 527
```

ケース3：非表示行あり

手動で行を非表示にしたケースです。
Subtotal 関数を使用し、第1引数に109を指定した方法のみ、表示されている行だけ合計されていることがわかります。

マクロ実行結果（イミディエイトウィンドウ）
```
[1]合計 1589
[2]合計 1589
[3]合計 1062
```

【補足】Subtotal 関数を使用せずに集計する

Subtotal 関数は集計の条件が指定できないため、実際に Subtotal 関数を使える場面はかなり限られてくると思います。そんなときは Hiddenプロパティにより行が表示されているか否かを判別し、ループ処理で集計します。