SUBTOTAL関数は、「データ」機能の「小計」を実行した時と「テーブル」機能の集計行に自動入力される便利な集計関数です。
引数に集計方法を指定することができ、指定範囲のうち、表示されているセルだけを集計するか、すべての値を集計するかを指定できます。
SUBTOTAL関数について書かれているサイトを調べると、「フィルターを多用する方には便利です！」とよく書かれています。しかし、「フィルターしても、集計方法9と109が同じになるのはなぜ？」 など、不具合のような質問も数多くあります。
フィルターでは、表示でも非表示でも同じ集計結果になるのはExcelの仕様です。例えば、フィルター結果のみの合計値を得たい場合は、集計方法の9でも109でもよいことになります。
SUBTOTAL関数のヘルプにある解説をよくよく読んでみると次のようなことがかかれています。
SUBTOTAL関数の特徴
リンクをクリックすると、解説箇所にジャンプします。
- 行方向の集計のみできます。
- 引数の「範囲」内にSUBTOTAL関数で集計したセルが含まれている場合は、その値を無視します。
- 集計方法の「表示／非表示」とは、行を右クリックして「表示」「再表示」を選択した状態とアウトラインの機能で表示[+]／非表示[-]のボタンを押した状態です。
- 「範囲」がフィルターされた場合は、どの集計方法（1～111）でも表示されているセルのみが集計の対象となります。
基本構文＝SUBTOTAL（集計方法 , 範囲 ）
| 集計方法 | 関数 | |
| 非表示の値も含める | 非表示の値を無視する | |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
検証内容
サンプルデータ：subtotal_sample.xlsx
元データ
2.引数の「範囲」内にSUBTOTAL関数で集計したセルが含まれている場合は、その値を無視します。
セルB15の集計範囲は、B2～B14を指定しています。
3-1.集計方法の「表示／非表示」とは、行を右クリックして「表示」「再表示」を選択した状態
下図は、「区分」がAとB（2～6行目）を非表示にしています。
3-2.アウトラインの機能で表示[+]／非表示[-]のボタンを押した状態
下図は、「区分」AとBの行を選択し、「データ」タブ「アウトライン」の「グループ化」を選択し、非表示にした結果です。
4.指定した「範囲」がフィルターされた場合は、どの集計方法（1～111）でも表示されているセルのみが集計の対象となる
