【Googleスプレッドシート】SUBTOTAL関数の活用!フィルタを除外した集計

Googleスプレッドシートでフィルタをかけた後に合計や平均を計算すると、非表示行も含まれてしまい困った経験はありませんか。SUBTOTAL関数を使えば、フィルタで除外した行を自動的に無視して集計できます。この記事ではSUBTOTAL関数の基本から応用まで、フィルタ除外集計の具体的な手順を解説します。

フィルタ後のデータだけを正確に集計したい方や、集計範囲を動的に変更したい方に役立つ内容です。この記事を読めば、SUBTOTAL関数を使いこなして効率的にデータ分析できるようになります。

SUBTOTAL関数の仕組みとフィルタ除外のしくみ

SUBTOTAL関数は、指定した範囲に対して合計や平均などの集計を行い、さらにフィルタや手動の行非表示を考慮して計算できる関数です。通常のSUM関数ではフィルタで隠した行もすべて合計してしまいますが、SUBTOTAL関数を使うと表示されているデータだけを対象にできます。この動作は「関数番号」で制御します。関数番号は1から11までと101から111までの2種類があり、それぞれ非表示行の扱いが異なります。1〜11は手動で非表示にした行を無視しますが、フィルタで隠した行は無視しません。一方、101〜111はフィルタで隠した行だけでなく手動非表示行も無視します。フィルタ除外集計には101〜11の番号を使います。SUBTOTAL関数は合計、平均、カウント、最大値、最小値など11種類の集計を1つの関数で切り替えられます。これにより、フィルタのオン・オフに応じて集計値が自動的に更新される便利なシートが作れます。

SUBTOTAL関数の基本的な使い方とフィルタ除外集計の手順

ここでは、SUBTOTAL関数を使ってフィルタ除外集計を行う具体的な手順を説明します。売上データの表を例に、フィルタで特定の月だけ表示して合計額を求める方法を解説します。

手順1: 集計したいデータにフィルタを設定する

1. データ範囲を選択する
   見出し行を含むデータ範囲をドラッグで選択します。例えばA1からE100までの売上表です。
2. フィルタを追加する
   メニューから「データ」→「フィルタを作成」をクリックします。各列に見出しのプルダウンアイコンが表示されます。
3. フィルタ条件を設定する
   月の列で「4月」を選択し、4月のデータだけを表示します。

手順2: SUBTOTAL関数を入力する

1. 集計結果を表示するセルを選ぶ
   例えばセルE101など、表の外の空いているセルをクリックします。
2. 関数を入力する
   「=SUBTOTAL(」と入力すると、関数番号の候補が表示されます。フィルタ除外で合計を求めたいので、関数番号は「109」を選びます。「=SUBTOTAL(109,」と続けます。
3. 集計範囲を指定する
   合計したい金額の列(例えばE2:E100)をドラッグして範囲を指定し、「)」で閉じます。式は「=SUBTOTAL(109,E2:E100)」となります。
4. Enterキーで確定する
   すると、現在フィルタで表示されている行だけの合計が計算されます。フィルタを変更すると自動的に値が更新されます。

手順3: フィルタの切り替えで集計値が変わることを確認する

フィルタのプルダウンから別の月を選ぶと、SUBTOTAL関数の結果がその月の合計に変わります。すべてのデータを表示すると、全行の合計が表示されます。これでフィルタ除外集計が動的に動作することを確認できます。

SUBTOTAL関数使用時の注意点とよくある失敗例

SUBTOTAL関数は便利ですが、使い方を誤ると意図しない結果になります。代表的な注意点を説明します。

関数番号を間違えるとフィルタ除外が機能しない

関数番号9を使うと、フィルタで隠した行も合計に含まれます。フィルタ除外をするには必ず109を使います。同様に平均なら1ではなく101、カウントなら2ではなく102というように、先頭に「10」が付く番号を選びます。番号一覧は関数の引数ヒントに表示されますので、確認しながら入力してください。

SUBTOTAL関数の範囲内に他のSUBTOTALがあると二重集計になる

SUBTOTAL関数の集計範囲の中に別のSUBTOTAL関数が含まれていると、その部分は無視されます。しかし意図せず入れ子になると集計がずれる原因になります。範囲指定はできるだけ単純な列範囲にし、小計行には別の関数を使うなど工夫しましょう。

手動で行を非表示にした場合の挙動を理解する

関数番号109は、フィルタで隠した行だけでなく、手動で非表示にした行も無視します。もし手動非表示行は含めたい場合は、関数番号9を使います。状況に応じて使い分ける必要があります。

SUBTOTAL関数の関数番号と集計内容の比較表

上の表の通り、関数番号が小さいグループ(1〜11)は手動で非表示にした行を無視しますが、フィルタで隠れた行は無視しません。一方、大きいグループ(101〜111)は両方の非表示行を無視します。フィルタだけでなく手動非表示もまとめて除外したい場合は101〜111を使います。使い分けのポイントは、フィルタのみで行を隠しているのか、手動でも非表示にしているのかです。

まとめ

SUBTOTAL関数を使えば、フィルタで絞り込んだデータだけを簡単に集計できます。関数番号に109を指定することで、表示されている行のみの合計が得られます。平均やカウントなど他の集計も同様の関数番号で切り替えられます。また、関数番号の選択によって手動非表示行の扱いも変えられます。データの一部だけを集計したい場合や、動的なレポートを作成する際に非常に便利です。ぜひSUBTOTAL関数を活用して、フィルタ操作と連動したスマートな集計シートを作成してみてください。