Excel関数入門：SUBTOTAL関数の使い方【フィルター後の合計や平均を集計】

2025.04.04

フィルターをかけたデータの合計や平均などを、正しく計算したいときに便利なのが SUBTOTAL（サブトータル）関数 です。通常のSUM関数では無視されない非表示行も、SUBTOTALを使えば簡単に除外できます。

この記事では、SUBTOTAL関数の基本的な使い方、関数番号の種類をわかりやすく解説します。

PR

SUBTOTAL関数とは？

SUBTOTAL関数は、指定した集計方法（合計、平均、最大値など）で、表示されているデータだけを集計することができる関数です。

書式

=SUBTOTAL(集計方法の番号, 範囲)

引数の意味

  • 集計方法の番号：どの集計を行うか（SUM、AVERAGEなど）を番号で指定
  • 範囲：集計したいセル範囲

集計方法の番号一覧

番号関数名内容
1 / 101AVERAGE平均
2 / 102COUNT数値セルの個数
3 / 103COUNTA空白以外のセルの個数
4 / 104MAX最大値
5 / 105MIN最小値
6 / 106PRODUCT積（掛け算の結果）
7 / 107STDEV標本標準偏差
8 / 108STDEVP母標準偏差
9 / 109SUM合計
10 / 110VAR標本分散
11 / 111VARP母分散

SUBTOTAL関数では、指定する番号によって非表示行の扱いが異なります。

  • 1〜11：フィルターで非表示になった行は除外するが、手動で非表示にした行は集計に含まれる。
  • 101〜111：フィルターで非表示になった行に加え、手動で非表示にした行も集計から除外する。

なお、1～11 と 101～111 は計算に使う関数そのものは同じで、違うのは非表示行の扱いだけです。

SUBTOTAL関数の使い方

例： 合計を求める

=SUBTOTAL(9, D3:D10)

ここで使われている「9」は、集計方法を指定する番号で、「9」を指定すると
SUM（合計） が実行されます。

つまり、この数式は「D3:D10の中で、表示されている行の合計を求める」
という意味になります。

例：営業部のみの合計を求める

SUBTOTAL関数を使うと、フィルターでデータを絞り込んだときに、
表示されている行だけを自動で集計できます。

たとえば、部署別の売上一覧で「営業部」だけをフィルター表示すると、
関数が自動的に「営業部の売上合計」だけを計算してくれます。

通常のSUM関数では非表示の行も合計されてしまいますが、
SUBTOTAL関数は見えているデータだけを反映できるのが大きな特徴です。

例：平均を求める

=SUBTOTAL(1,C3:C10)

集計方法の番号「1」→ C3:C10 の平均を求める

SUBTOTAL関数の「9」と「109」の違い

SUBTOTAL 関数の第1引数には「どんな集計を行うか」を示す番号を指定します。
その中でもよく使われるのが「9」と「109」です。

どちらも「合計（SUM）」を求めるための番号ですが、
手動で非表示にしたデータを合計に含めるかどうか に違いがあります。

  • 1〜11 を指定した場合：
    　→ フィルターで非表示になった行だけを集計から除外します。
  • 101〜111 を指定した場合：
    　→ フィルターで非表示になった行に加え、手動で非表示にした行も集計から除外します。

SUBTOTAL関数では、指定する集計番号によって非表示の行の扱いが変わります。
「9」を指定した場合は、手動で非表示にした行は合計に含まれます。
一方、「109」を指定すると、手動で非表示にした行も集計から除外されます。

注意点

  • 範囲には連続したセルを指定するのが基本
  • フィルターでの集計には「1〜11」を使用（手動非表示も除外したい場合は「101〜111」）
  • 入れ子にすることはできません（他のSUBTOTAL内で使うと正しく動作しない）

SUBTOTALとSUMの違い

関数非表示行の扱いフィルター対応
SUBTOTAL表示行のみ集計される○
SUM非表示でも集計される×

関連関数

関数名主な用途
SUM関数範囲内の合計を求める
AVERAGE関数平均値を求める
AGGREGATE関数指定した計算方法で集計
COUNT関数数値の個数を数える

まとめ

SUBTOTAL関数は、「表示されているデータだけを正確に集計したい」場面で大活躍します。フィルター付きリストやレポート作成、ピボットの代替としても有効です。複数の集計方法を1つの関数で使える便利な関数。SUM関数よりも一歩上の使い方として覚えておきましょう！

関連記事：SUM関数の使い方まとめ