🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel SUBTOTAL関数 — フィルターを尊重する集計（と9 vs 109の落とし穴）

|

Excel SUBTOTAL関数 — フィルターを尊重する集計（と9 vs 109の落とし穴）

TL;DR — SUBTOTAL はフィルターを知っている集計です。素の SUM は常に すべての行を足しますが、SUBTOTAL はフィルターで非表示になった行をスキップするので、 フィルター済みリストの末尾の数字がリアルタイムで更新されます。構文は =SUBTOTAL(function_num, range)。function_num は演算の種類と非表示の扱い方の 両方を選びます。9 = フィルター済み行を無視するSUM、109 = フィルター済み行と 手動で隠した行の両方を無視するSUM。2つめの強みは、SUBTOTAL が範囲内の他の SUBTOTAL を無視すること。だから総合計がグループ小計を二重計上することがありません。 ただしエラー値はスキップできません — それは AGGREGATE の仕事です。Excel 2003以降で動作します。

=SUBTOTAL(9, Sales)        ' フィルターで表示されている行のSUM
=SUBTOTAL(109, Sales)      ' フィルターと手動非表示の両方を経て表示されている行のSUM

SUM の仕事はひとつ、そしてそれを無骨にこなします。フィルターがあろうがなかろうが、すべてを足す。 フィルター済みのリストを扱っていて、見えているものの合計が欲しいときには、その無頓着さこそが まさに問題になります。SUBTOTAL がその答えです。ただし最初の引数に有名な落とし穴を隠していて、 さらにほとんど誰も教わらない2つめの機能が、これを層状レポートに最適なツールにしています。両方を見ていきましょう。

この記事で学べること

  • メンタルモデル: フィルターに合わせて再計算する集計
  • function_num の一覧表 — 11の演算、2つのファミリー
  • 9 vs 109の落とし穴: なぜ手動で行を隠しても 9 の集計は変わらないのか
  • なぜ SUBTOTAL は他の SUBTOTAL を無視するのか — 二重計上しない超能力
  • 唯一できないこと: エラー値のスキップ
  • 判断の分かれ目: SUBTOTAL vs SUM vs SUMIFS

メンタルモデル: フィルターとともに動く集計

オートフィルターのかかった売上リストを思い浮かべてください。「西部」で絞り込むと10行が表示されます。 末尾の =SUM(Sales) は、表示・非表示にかかわらずすべての行を足し続けます。フィルターが存在することなど 知る由もないのです。=SUBTOTAL(9, Sales) はフィルターが表示のまま残した行だけを足すので、 フィルターを「東部」に変えた瞬間、合計は東部の数字に再計算されます。これがこの関数の概念のすべて。 SUBTOTAL はフィルターを認識する集計なのです。Excelのテーブルの集計行も、 データ → 小計機能も、SUM ではなく SUBTOTAL の数式を生成するのは、これが理由です。

function_numの一覧表: 11の演算、2つのファミリー

最初の引数は単に「9は合計」というだけのものではありません。これは演算と、非表示行をどう扱うかの 両方を選ぶコードです。演算は11種類あり、それぞれが2つのファミリーで使えます。

' 1〜11  : 演算。フィルターで非表示の行を無視する（手動非表示はカウント）
' 101〜111: 同じ演算。手動で非表示にした行も無視する
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

つまり =SUBTOTAL(1, …) はフィルターを認識するAVERAGE、=SUBTOTAL(104, …) は手動で隠した行も無視する MAXです。この表を丸暗記する必要はめったにありません。ただし2つのファミリーの違いは理解しておく必要があります。 落とし穴はそこに潜んでいるからです。

9 vs 109の落とし穴

人々をフォーラムに走らせるバグがこれです。=SUBTOTAL(9, Sales) があります。数行を右クリックして手動で 非表示にし（フィルターではなく）、合計が下がるだろうと期待します。ところがびくともしません。なぜでしょう？

1〜11 ファミリーはフィルターで隠された行しか無視しないからです。手動で隠した行はまだカウントされて います。手動の非表示も除外するには、101〜111 ファミリーが必要です。

=SUBTOTAL(9,   Sales)      ' フィルター済み行を無視。手動で隠した行はカウントする
=SUBTOTAL(109, Sales)      ' フィルター済み行 と 手動で隠した行 の両方を無視する

実践的なルールはこうです。**フィルターしか使わないなら、9 と 109 は同じ答えを返します。 手動で行を隠した瞬間に、100+ のコードを使いましょう。**迷ったら、合計はデフォルトで 109、 件数は 103 にしておくと、多くの人が「見えているものを集計」に期待する通りの挙動になります。

誰も教えない超能力: SUBTOTALは他のSUBTOTALを無視する

これこそ SUBTOTAL を「フィルター付きのSUM」以上のものにしている機能で、この関数が存在する理由そのものです。 SUBTOTAL は、その範囲内にある他の SUBTOTAL セルをすべてスキップします。 各地域のブロックの後に SUBTOTAL を置き、さらに列全体にまたがる1つの総 SUBTOTAL を置いたレポートを思い浮かべてください。

B10  =SUBTOTAL(9, B2:B9)      ' 西部の小計
B20  =SUBTOTAL(9, B11:B19)    ' 東部の小計
B21  =SUBTOTAL(9, B2:B20)     ' 総合計 — B10とB20を二重計上しない

素の =SUM(B2:B20) なら、2つのグループ小計を明細行に上乗せして、総合計が倍になってしまいます。 SUBTOTAL はネストした小計を静かに除外するので、範囲をそれらの周りで切り分けなくても総合計が正しくなります。 これはまさにデータ → 小計が、きちんと積み上がる多階層サマリーを作る仕組みそのもの。これを知れば、 明細行と途中の小計が混在する列を扱うときはいつでも SUBTOTAL に手が伸びるようになります。

唯一できないこと: エラーのスキップ

SUBTOTAL はフィルターを認識しますが、エラーは認識しません。範囲のどこかに #N/A や #DIV/0! が 1つでもあると、SUBTOTAL 全体がエラーを返します。

=SUBTOTAL(9, Sales)      ' Salesに #N/A が1つ → SUBTOTAL 自体が #N/A

これこそ AGGREGATE が埋めるために作られた、まさにそのギャップです。 AGGREGATE は SUBTOTAL のすることをすべて行ったうえで、エラー値を無視するオプション（と、 より広い関数のセット）を加えます。フィルター対象の列がエラーを含みうるなら——ヒットしない VLOOKUP、 ゼロ除算など——それが AGGREGATE へ格上げする合図です。

判断の分かれ目: SUBTOTAL vs SUM vs SUMIFS

これらは異なる問いに答えており、取り違えがほとんどの「合計が違う」混乱の根っこです。

  • SUM — フィルターを無視してすべてを合計。誰かがシートをフィルターしても動いてほしくない、 固定の総合計に向いています。
  • SUBTOTAL — フィルター（と任意で手動非表示）の後に見えているものを合計。対話的なリストの 末尾や、テーブルの集計行のエンジンとして最適です。
  • SUMIFS — 画面上で何がフィルターされていようと、数式に 入力した条件にマッチする行を合計。シートがそこに絞り込まれていようがいまいが、常に「西部 Q1」を 意味しなければならないレポートセルに向いています。

すっきり覚えるコツ。SUBTOTAL はフィルターに従い、SUMIFS はあなたが書いた条件に従います。 数字をフィルターのドロップダウンに反応させたいなら SUBTOTAL。定義に固定したいなら SUMIFS です。

ExcelMasterの活用法

SUBTOTAL のミスが構文の問題であることはまれです。問題は 9 と 109 のどちらを選ぶか、あるいは 総合計がネストした小計を二重計上していることに気づかないこと。ExcelMaster は、あなたが作っている ものを読み取り——このフィルター済みリストの末尾の合計で、私が隠した行も無視するものとか、 小計と、それがちゃんと積み上がる総合計のある地域レポートとか——正しい function_num、正しい範囲を書き、 エラーが絡んだ瞬間に AGGREGATE へ切り替えます。あなたはレポートを説明するだけ。コードはExcelMasterが選びます。

よくある質問

SUBTOTAL 9 と 109 の違いは？

どちらもフィルター後の表示行をSUMします。違いは手動で非表示にした行です。9 は右クリック → 非表示で 隠した行をまだカウントしますが、109 はそれも無視します。フィルターしか使わないなら一致します。 手で行を隠すなら 109 を使ってください。

行を隠してもSUBTOTALが変わらないのはなぜ？

おそらく 1〜11 のコード（9 など）を使っています。これはフィルターで隠された行しか無視せず、 手動で隠した行は無視しません。101〜111 ファミリー（例: 109）に切り替えると、手動の非表示も 合計に反映されます。

SUBTOTALは他のSUBTOTALセルを無視しますか？

はい — それは意図的な機能です。SUBTOTAL は範囲内の他の SUBTOTAL の結果をすべて除外するので、 すでにグループ小計を含む列にかけた総合計が、それらを二重計上することはありません。素の SUM なら してしまいます。

フィルター済み／表示中のセルだけを合計するには？

=SUBTOTAL(9, range)（手動で隠した行もスキップするなら 109）を使います。テーブルなら集計行を オンにしてください。SUBTOTAL を挿入してくれ、ドロップダウンから関数を選べるようになります。

SUBTOTALにエラー値をスキップさせるには？

できません。代わりに AGGREGATE を使ってください。 =AGGREGATE(9, 6, range) は表示行を合計しながらエラーを無視します。その追加の options 引数こそ、 AGGREGATE が存在する理由のすべてです。

検証環境

検証環境: Excel 365 (Windows 11) — 最終確認 2026-06-30。

関連ガイド: Excel AGGREGATE · Excel SUMIFS · Excel SUMPRODUCT · Excel FILTER · Excel COUNTIFS