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

この記事ではSUBTOTAL関数を用いて、非表示セルを除外して集計する方法を紹介します。

SUM関数やAVERAGE関数では非表示設定がされている行や列も集計してしまいます。これらはSUBTOTAL関数で集計対象より除外することが可能です。

集計のテーマ

以下のようなサンプル表をご覧ください。

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

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

※行を非表示とするには、行番号を右クリックして「非表示」をします。

非表示セルを集計対象から除外するにはSUBTOTAL関数を使います。

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

SUBTOTAL関数の書式は

=SUBTOTAL(集計方法,参照)

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

最初に集計方法を指定します。非表示セルを除外する集計をする場合には3桁の「100以上」の集計方法を指定します。SUMなら「109」としましょう。

「9」もSUMですが、1桁と2桁の番号は非表示セルを集計対象としてしまいます。

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

完成しました。

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

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

B9の式

=SUBTOTAL(109,B2:B7)

B10の式

=SUBTOTAL(101,B2:B7)

1桁、2桁番号では非表示セルも集計対象になります。

以下の3桁番号は非表示セルを集計対象から除外します。