【Excel】SUBTOTAL関数を使ってフィルターで絞り込んだデータのみ集計する方法

Excelでは、SUM関数やAVERAGE関数で集計を行うことができますが、参照するデータをフィルターで絞り込んでしまうと正しい計算ができなくなります。そのような場合、SUBTOTAL関数を使うことで絞り込んだデータのみを対象として計算することができます。

SUBTOTAL関数とは

SUBTOTAL関数とは、指定した範囲で表示されているセルだけを対象に集計する関数です。集計方法は数字で指定することができます。

関数	役割
SUBTOTAL(集計方法,参照1,[参照2]…)	参照した範囲の表示されているセルのみを指定した集計方法で計算する。

集計方法は以下の通り指定することができます。

集計方法	計算
「1」または「101」	平均を計算
「2」または「102」	数値のセルを数える
「3」または「103」	データのあるセルを数える
「4」または「104」	最大値を計算する
「5」または「105」	最小値を計算する
「9」または「109」	合計を計算する

SUBTOTAL関数を使ってフィルターで絞り込んだデータのみ集計する方法

以下のような営業成績表で、フィルターで絞り込んだデータのみを合計するようにしたいと思います。

合計を表示するセルG3に以下のように数式を入力します。

=SUBTOTAL(9,D4:D23)

数式	役割
SUBTOTAL(9	参照した範囲の表示されているセルの合計（指定方法「9」）を計算する。
D4:D23	参照する範囲はD4～D23の「営業成績」の列。

そうすると以下のように営業成績の合計「170」が表示されます。現時点ではフィルターで絞り込んでいないので、すべてのデータが集計されています。

次にこのデータをフィルターで「営業一課」だけに絞り込んでみます。

そうすると以下のように「営業一課」だけの合計「52」になります。

SUBTOTAL関数を使わないと

仮にSUBTOTAL関数を使わないで、SUM関数で合計すると以下のようにフィルターで絞り込んでも合計値は変更されません。

以下はSUM関数を用いて合計した結果で、「170」となります。

フィルターで「営業一課」に絞り込んでも合計値は「170」のままです。

これはフィルターで絞り込んだデータは表示はされていないものの、隠れたまま存在しているからです。