【Excel】SUBTOTAL関数の「9」とは？集計方法の番号一覧とフィルター後の集計

Excel
2025.02.07
この記事は約6分で読めます。

=SUBTOTAL(9, D5:D100) のような数式を見て、「この9って何だろう」と思った方に向けた記事です。

SUBTOTAL関数は先頭の引数に番号を書く珍しい関数で、この番号が「合計なのか、平均なのか、件数なのか」を決めています。番号の意味さえ分かってしまえば、あとは使う場面を選ぶだけの素直な関数です。

この記事で分かること

  • SUBTOTAL関数の「9」が何を意味しているか
  • 集計方法の番号一覧（1〜11 と 101〜111）
  • 1ケタの番号と100番台の番号の違い（間違えやすいところ）
  • フィルターで絞り込んだ行だけを集計する手順

先に結論：9は「SUM（合計）」

SUBTOTAL の第1引数に指定する 9 は SUM、つまり合計を意味します。

=SUBTOTAL(9, D5:D100)

この数式は「D5:D100 のうち、フィルターで表示されている行だけを合計する」という意味になります。

同じ範囲を =SUM(D5:D100) で合計すると、フィルターで隠れている行まで含めて計算されてしまいます。この違いがSUBTOTAL関数を使う理由です。

どんな場面で使うか

「表を絞り込んで、その状態の合計をすぐ知りたい」という場面に向いています。

  • 売上一覧を担当者で絞り込んで、その担当者ぶんの売上合計を見る
  • 在庫表を倉庫別に絞り込んで、その倉庫の在庫数を数える
  • 経費明細を科目で絞り込んで、科目ごとの金額を確認する
  • 一覧の一番下に「表示中の件数」を出しておく

フィルターを切り替えるたびに、集計結果もその場で計算し直されます。

基本説明

書式

=SUBTOTAL(集計方法, 範囲1[, 範囲2, ...])

第1引数の「集計方法」に番号を書き、第2引数以降に集計したい範囲を指定します。

集計方法の番号一覧

番号と関数の対応は次のとおりです。1〜11 と 101〜111 は、下2ケタが同じものが同じ計算をします。

番号 100番台 対応する関数 内容
1 101 AVERAGE 平均
2 102 COUNT 数値の個数
3 103 COUNTA 空欄以外の個数
4 104 MAX 最大値
5 105 MIN 最小値
6 106 PRODUCT 積
7 107 STDEV 標準偏差（標本）
8 108 STDEVP 標準偏差（母集団）
9 109 SUM 合計
10 110 VAR 分散（標本）
11 111 VARP 分散（母集団）

実務で使うのはほとんどが 9（合計）・1（平均）・3（件数） の3つです。

1〜11 と 101〜111 の違い

この2種類の違いは、「行を右クリックして手動で非表示にした行」を計算に含めるかどうかだけです。

フィルターで隠れた行 手動で非表示にした行
1〜11（例: 9） 集計に含めない 集計に含める
101〜111（例: 109） 集計に含めない 集計に含めない

ポイントは2つです。

  • フィルターで隠れた行は、どちらの番号でも必ず除外される。 ここは共通なので、フィルターしか使わないのであれば9でも109でも結果は同じになります
  • 違いが出るのは、行を手動で非表示にしたときだけ。 手動で隠した行も「無いもの」として扱いたいなら 109 を使います

「手動で隠した行を除きたいときは100番台」と覚えておけば迷いません。

手順：フィルター後の合計を出す

Step 1: データ範囲を用意する

売上データや在庫データなど、見出し行＋複数行のリストを用意します。

Step 2: フィルターを設定する

見出し行を選択し、「データ」タブ →「フィルター」をクリックします。各見出しに表示された▼から、絞り込みたい条件を指定します。

Step 3: 集計用のセルを用意する

集計セルはデータ範囲の外側に置きます。 表の最下部（データの最終行より下）か、表の右側の空いている列が安全です。

Step 4: 数式を入力する

D列に売上金額が入っているとして、次のように入力します。

=SUBTOTAL(9, D5:D100)

これで、フィルターで表示されている行だけの合計が表示されます。フィルターを操作するたびに再計算されます。

SUBTOTAL関数でフィルター後の合計を求めた例

Step 5: テーブルなら「集計行」で自動化できる

表をテーブル（Ctrl + T）に変換している場合は、数式を書かずに済みます。テーブル内のセルを選択して「テーブルデザイン」タブを開き、「集計行」にチェックを入れます。

テーブルデザインタブの集計行にチェックを入れる

テーブルに集計行が追加された状態

追加された集計行はドロップダウンで集計方法を選べますが、中身は SUBTOTAL関数です。仕組みは同じなので、フィルターにもそのまま対応します。

他の集計方法の例

番号を変えるだけで、合計以外の集計もフィルターに対応させられます。

=SUBTOTAL(1, D5:D100)    平均
=SUBTOTAL(2, D5:D100)    件数（数値のみ）
=SUBTOTAL(3, B5:B100)    件数（空欄以外）
=SUBTOTAL(4, D5:D100)    最大値
=SUBTOTAL(5, D5:D100)    最小値

「表示されている件数」を出したいときは、氏名や商品名など必ず値が入っている列を指定して SUBTOTAL(3, ...) を使うと確実です。数値列に SUBTOTAL(2, ...) を使うと、空欄やテキストが数に入りません。

よくあるエラー

合計がフィルターに反応しない

SUM関数のままになっている可能性があります。=SUM(...) はフィルターで隠れた行も計算するため、絞り込んでも数字が変わりません。数式バーで関数名を確認してください。

集計セルまで一緒に消える／数字がおかしくなる

集計セルをデータ範囲の中（フィルターの対象行）に置いていると、絞り込みの条件によっては集計行そのものが隠れてしまいます。集計セルはデータ範囲の外に置いてください。

なお、集計範囲の中に別のSUBTOTAL関数が入っている場合、SUBTOTALはそれを無視して計算します。小計と総計を同じ列に並べても、二重に足されることはありません。

手動で非表示にした行が計算に入ってしまう

1ケタの番号（9など）は、手動で非表示にした行を含めて計算します。手動で隠した行を除きたい場合は100番台（109など）に変えてください。

範囲が増えたときに更新し忘れる

データが増えるたびに D5:D100 の範囲を直すのは手間ですし、直し忘れると集計漏れになります。表をテーブルに変換しておくと範囲が自動で広がるので、行の追加が多い表ほど効果があります。

まとめ

  • SUBTOTAL関数の9はSUM（合計）。=SUBTOTAL(9, 範囲) は表示されている行だけを合計する
  • 番号は1〜11と101〜111があり、下2ケタが同じなら同じ計算をする
  • フィルターで隠れた行は、どちらの番号でも常に除外される
  • 違いが出るのは手動で非表示にした行だけ。これも除きたいときは100番台を使う
  • 集計セルはデータ範囲の外に置く。テーブルの「集計行」を使えば数式を書かずに同じことができる

フィルターの状態に左右されず、条件を指定して集計したい場合は SUMIFS・COUNTIFS を使う方法もあります。表示している行を集計したいのか、条件で集計したいのかで使い分けてください。

関連記事

タイトルとURLをコピーしました