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

2021年11月6日

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関数では集計値は変わりません。(非表示も含む)

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

Excelを効率的に習得したい方へ

当サイトの情報を電子書籍用に読み易く整理したコンテンツを

買い切り950円またはKindle Unlimited (読み放題) で提供中です。

Word-A4サイズ:1,400ページの情報量で

(実際のページ数はデバイスで変わります)

基本的な使い方、関数の解説(140種類)、

頻出テクニックと実用例(109種類)、

XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。

体系的に学びたい方は是非ご検討ください。

アップデートなどの更新事項があれば随時反映しています。

なお購入後に最新版をダウンロードするには

Amazonへの問い合わせが必要です。

関連記事

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