Excel 様々な方法で表示されている値のみを集計(SUBTOTAL関数)

2020年5月8日

SUBTOTAL関数は非表示のセルの値除外して集計する関数です。

フィルターを用いる場合、SUM関数など通常の集計関数では、

非表示のセルも全て集計対象としますが、

SUBTOTAL関数は非表示のセルを集計対象から除外します。

仕様

=SUBTOTAL(集計方法,参照1 …省略… 参照254)
引数省略時の値説明
集計方法省略不可データの集計方法を示す数値を指定。詳細は後述。
参照1~254省略不可集計対象となるセル範囲を指定。
各引数の詳細

集計方法の値

フィルターで非表示となったセルは常に集計対象から除外されますが、

書式設定で非表示となっているセルを除外するか否かは設定値で変わります。

集計方法
(非表示を含める) 
集計方法
(非表示を除外する)
関数
1101平均:AVERAGE
2102個数:COUNT
3103空白以外の個数:COUNTA
4104最大:MAX
5105最小:MIN
6106積:PRODUCT
7107標本標準偏差:STDEV
8108標準偏差:STDEV.P
9109合計:SUM
10110不遍分散:VAR
11111標本分散:VARP
集計方法の一覧

使用例

フィルタを使う表全般に使用が適切です。

今回は日毎の数字(ランダム)を用意し、曜日でフィルタリングします。

B3セルはSUBTOTAL関数と同じ範囲のSUM関数です。

フィルタリング前(全件が集計)
フィルタリング前(全件が集計)

フィルタで土曜日と日曜日のみとしました。

SUBTOTAL関数の集計結果は表示セルのみとなりましたが、

SUM関数では集計値は変わりません。(非表示も含む)

フィルタリング後(表示されているものだけが集計)
フィルタリング後(表示されているものだけが集計)

関連記事

関数の仕様と使い方の記事一覧