非表示セルをSUM・AVERAGEなどの集計対象から除外する|SUBTOTAL

「SUM・AVERAGE・MAX・MIN・COUNT・COUNTA・PRODUCT」といった関数を使うと範囲内を集計することができますね。
例えば下図のセルB9ではB2:B7をSUM関数で集計しています。

しかし通常では非表示設定をしたセル・行・列も集計対象となってしまいます。
偶数番号の店舗を非表示にしましたがセルB9:B10の集計結果には影響がありません。
「通常では非表示セルも集計対象となる」のです。

非表示セルを集計対象から除外するには「SUBTOTAL」関数を使います。
非表示部を再表示してから作り直してみます。
「=su…」と入力すれば「SUBTOTAL」が出現します。選択して{Tab}キーを使うと採用されます({Enter}キーではない)。

SUBTOTAL関数の書式は
=SUBTOTAL(集計方法,参照)
となります。[参照]とは集計対象とする範囲です。[集計方法]にはSUMするのかAVERAGEするのか、…ということを番号で指定します。「109→SUM」「101→AVERAGE」となります。

最初に集計方法を指定します。非表示セルを除外する集計をする場合には「100以上」の集計方法を指定します。SUMなら「109」としましょう。「9」もSUMですが、こちらは非表示セルを集計対象としてしまいます。

「109」のあとにはカンマ(,)を入れます。カンマのあとには集計対象とする範囲を指定しましょう。

完成しました。

集計方法に「101」を使えば「AVERAGE」になります。

店舗番号が偶数の行を非表示にしてみました。
集計対象から除外されています。

subtotal関数の完成ファイルサンプル

Follow me!