この記事で分かること

  • SUBTOTAL関数の関数番号1〜11と101〜111で、手動非表示行の扱いがどう変わるか
  • AGGREGATE関数が関数番号とオプション引数を分けている理由と、エラー値を無視できる仕組み
  • Excelテーブルの集計行がSUBTOTALを自動で使う理由と、フィルター連動レポートの作り方
  • 入れ子のSUBTOTALによる二重集計を避ける仕組みと、SUMIFS・SUMPRODUCTとの使い分け

結論：フィルターや非表示行を除いて集計したいならSUBTOTAL・AGGREGATE

案件一覧や実績データの一覧表で、フィルターを変えるたびに一番下の合計セルが自動で追随してほしい、あるいは非表示にした行やエラー値が混じった行を除いて集計したい、という場面ではSUM関数だけでは足りません。SUMは参照範囲に含まれるセルをすべて機械的に合算するため、フィルターで絞り込んでいるかどうか、行を手動で非表示にしているかどうかを一切区別しないからです。

この問題を解決するのがSUBTOTAL関数とAGGREGATE関数です。どちらも「今、画面に表示されている行だけを集計する」という発想を関数の引数として持っており、SUBTOTALは関数番号を1〜11か101〜111かで切り替えることで手動非表示行を含めるか除外するかを選べます。AGGREGATEはさらに一歩進み、関数番号（計算方法）とオプション（非表示行・エラー値・入れ子集計をどう扱うか）を別々の引数として持たせることで、エラー値が混じっていても計算を止めずに済むという拡張性を持っています。使い分けの軸は「セルの値による条件」で選ぶSUMIFS・SUMPRODUCTとは別物で、こちらは「画面上の表示状態」で選ぶ関数だと理解しておくと迷いません。

SUBTOTALの関数番号（1〜11／101〜111）と非表示行の扱い

SUBTOTAL関数はSUBTOTAL(関数番号, 参照1, [参照2], …)という形で使います。関数番号は集計方法を表す数値で、1〜11と101〜111の2系統が用意されています。同じ集計方法でも番号が100だけ異なるペアが対応しており、その差が手動で非表示にした行を含めるかどうかです。

SUBTOTAL(関数番号, 参照1, [参照2], …)
関数番号に1〜11を指定すると、行を右クリックして非表示にした行の値も集計に含めます。101〜111を指定すると、その手動非表示行を集計から除外します。一方で、オートフィルターやExcelテーブルのフィルターによって表示されていない行は、関数番号が1〜11でも101〜111でも常に集計から無視されます。この「フィルターで除外された行は無視する」という仕様は両系統に共通です。

関数番号（手動非表示を含む）関数番号（手動非表示を除外）集計内容
1101AVERAGE（平均）
2102COUNT（数値の個数）
3103COUNTA（空白でないセルの個数）
4104MAX（最大値）
5105MIN（最小値）
6106PRODUCT（積）
7107STDEV（標本標準偏差）
8108STDEVP（母標準偏差）
9109SUM（合計）
10110VAR（標本分散）
11111VARP（母分散）

実務でもっともよく使うのは合計を表す9と109の組み合わせです。財務モデルのレポートでは「フィルターで絞った範囲だけ合計したいが、行を手動で隠して見た目を整えることもある」という場面が多いため、まず9と109の違いを体で覚えておくと、あとの関数番号は表を見ながら選べば十分です。

間違えやすいのは、フィルターと手動非表示を同じものだと思い込んでしまうことです。オートフィルターやテーブルの絞り込みで表示されなくなった行は、SUBTOTALにとって最初から集計対象に入ってきません。これに対して手動非表示は「本来は集計対象だが、見た目のためだけに行を隠している」状態であり、関数番号を1〜11にするか101〜111にするかで、その行を含めるか除くかを選べます。この違いを意識せずにレポートを作ると、担当者が行を手動で隠しただけのつもりが合計セルの数字まで動いてしまい、原因が分からず数式を疑ってしまうという事故につながります。

AGGREGATE関数の拡張性：エラー値と非表示行を同時に除外する

SUBTOTALには弱点があります。参照範囲の中に#DIV/0!や#N/Aなどのエラー値を含むセルが1つでもあると、関数番号が1〜11でも101〜111でも、SUBTOTAL自体がエラー値を返してしまうのです。SUBTOTALの関数番号にはエラー値を無視するという選択肢がありません。財務モデルでは、予定工数が0の案件で進捗率を割り算した結果が#DIV/0!になる、期初のデータが空欄でCOUNTA系の計算が想定と食い違う、といった形でエラー値が実績データに紛れ込むことが珍しくありません。

AGGREGATE(関数番号, オプション, 参照1, [参照2], …)
AGGREGATEは関数番号（1〜19、AVERAGE・SUM・MAXに加えLARGE・SMALL・PERCENTILE.INC・QUARTILE.INCなどの順位・分位関数も含む）と、オプション（0〜7）を別々の引数として持ちます。オプションを変えるだけで、同じ関数番号のまま「何を無視して集計するか」を切り替えられる点がSUBTOTALとの最大の違いです。

オプション無視する対象
0（省略時）入れ子のSUBTOTAL・AGGREGATE関数
1非表示の行、入れ子のSUBTOTAL・AGGREGATE関数
2エラー値、入れ子のSUBTOTAL・AGGREGATE関数
3非表示の行、エラー値、入れ子のSUBTOTAL・AGGREGATE関数
4何も無視しない
5非表示の行
6エラー値
7非表示の行、エラー値

実務で使う頻度が高いのはオプション6と7です。エラー値だけを無視したいなら6、フィルターの表示状態に加えてエラー値も無視したい集計行を作りたいなら7を選びます。なお、Microsoftのヘルプは「入れ子のSUBTOTAL・AGGREGATE関数」をどのオプションでも無視すると説明しており、この点は次の見出しで扱う二重集計の回避と関係しています。

Excelテーブルの集計行との連携

Excelテーブルには、テーブルの直下に合計・平均などを表示する「集計行」を追加する機能があります。テーブルのデザインタブから集計行をオンにすると、各列にドロップダウンリストが現れ、SUMやAVERAGEなど代表的な関数を選べます。ここで重要なのは、Excelがこのドロップダウンから数式を作るとき、単純なSUM関数ではなく=SUBTOTAL(109,[列名])という構造化参照つきの数式を自動生成する点です。関数番号109（SUM・手動非表示を除外する系統）が既定で使われるため、テーブルの集計行はフィルターで絞り込んだ結果にも、手動で非表示にした行を除いた結果にも自動的に追随します。テーブルの集計行のオン・オフを切り替えても、Excelは選んだ関数を記憶しているため、列ごとに異なる集計方法（合計・平均・件数など）を持たせたまま運用できます。

集計行や関数の設計を型として練習する

SUBTOTAL・AGGREGATEの挙動を理解しても、実務のモデルでは条件分岐・エラー処理・検算セルとの組み合わせ方まで含めて設計する力が問われます。財務モデリング教材ではExcel関数を実務のレポート様式に落とし込む工程を扱っています。

→ Excel関数・モデリング教材を見る

設例：フィルターを変えても集計行が自動追随するレポートを作る

以下は説明用の仮設例です。あるチームがデューデリジェンス案件を12件抱えており、ステータス（進行中・完了・保留）ごとに金額と進捗率を管理しているとします。まずはオートフィルターを使わず、担当者が画面を見やすくするために6件（No.2・4・6・7・9・11）を右クリックで手動非表示にしている状態を考えます。加えてNo.3の進捗率は、予定工数が0のため#DIV/0!のエラーになっています。

No.案件名ステータス金額(百万円)進捗率表示状態
1A社DD進行中8045%表示
2B社DD完了120100%手動で非表示
3C社DD進行中55#DIV/0!表示
4D社DD保留400%手動で非表示
5E社DD進行中9560%表示
6F社DD進行中6570%手動で非表示
7G社DD完了110100%手動で非表示
8H社DD進行中12030%表示
9I社DD保留300%手動で非表示
10J社DD進行中7555%表示
11K社DD完了90100%手動で非表示
12L社DD進行中5040%表示

まず金額列を検算します。12件すべてを単純にSUMすると80+120+55+40+95+65+110+120+30+75+90+50=930（百万円）になります。ここではオートフィルターを一切使っていないため、SUBTOTAL(9,…)も手動非表示にした6件（No.2・4・6・7・9・11）をそのまま合計に含めてしまい、結果はSUMと同じ930（百万円）です。関数番号9は「フィルターで除外された行だけは常に無視するが、手動非表示の行は無視しない」という仕様であり、フィルターが存在しない今の状態では非表示行を除く手段になっていません。一方、SUBTOTAL(109,…)は手動非表示の6件（合計120+40+65+110+30+90=455）を除外するため、残り6件（No.1・3・5・8・10・12）の合計80+55+95+120+75+50=475（百万円）を返します。フィルターを使わない場面では、9と109の違いはここで初めて意味を持ちます。

案件一覧12件（フィルターなし・6件を手動非表示）の金額集計（百万円） 表示 手動で非表示 No.1 A社DD 80百万円・表示 No.2 B社DD 120百万円・手動非表示 No.3 C社DD 55百万円・表示 No.4 D社DD 40百万円・手動非表示 No.5 E社DD 95百万円・表示 No.6 F社DD 65百万円・手動非表示 No.7 G社DD 110百万円・手動非表示 No.8 H社DD 120百万円・表示 No.9 I社DD 30百万円・手動非表示 No.10 J社DD 75百万円・表示 No.11 K社DD 90百万円・手動非表示 No.12 L社DD 50百万円・表示 単純SUM(範囲) 結果 930百万円 表示・非表示を区別せず 全12件を合算 SUBTOTAL(9,範囲) 結果 930百万円 フィルターがないため9は 手動非表示も除外しない SUBTOTAL(109,範囲) 結果 475百万円 手動非表示の6件を除外 （テーブル既定の集計方法）
図：設例のため作成。フィルターは使わず12件のうち6件（No.2・4・6・7・9・11）を手動非表示にした状態でSUM・SUBTOTAL(9)・SUBTOTAL(109)を比較。9と109の違いはフィルターが存在しないこの状態でのみ現れる。

ここで同じ12件のデータのまま、手動非表示をやめてステータス列を「進行中」でオートフィルターをかけ、フィルターを通過した7件（No.1・3・5・6・8・10・12）のうちNo.6だけを追加で右クリックして手動非表示にしたとします（この設定は次の見出しのデューデリジェンス案件一覧と同じです）。直感的には、フィルターで除外された5件（No.2・4・7・9・11、合計390）を930から引いた540がSUBTOTAL(9,…)、そこからさらにNo.6の65を引いた475がSUBTOTAL(109,…)になりそうに思えます。ところが実際にExcelで組んでみると、SUBTOTAL(9,…)もSUBTOTAL(109,…)も、どちらも475を返します。同じ範囲に有効なオートフィルターが存在する状態では、関数番号を1〜11にしても、フィルターを通過した範囲内でさらに手動非表示にした行まで自動的に無視されてしまうためです。9と109の違いが実際に意味を持つのは、範囲にフィルターが一切かかっていないときだけであり、フィルターと手動非表示を併用した瞬間にその違いは消える、という点が実務でもっとも間違えやすい落とし穴です。

フィルター＋手動非表示を併用すると9と109の差は消える 直感的な想定（誤り） SUBTOTAL(9) 540 SUBTOTAL(109) 475 実機Excelの結果（正） SUBTOTAL(9) 475 SUBTOTAL(109) 475 オートフィルターが有効な範囲では、9も109も同じ結果になる
図：設例のため作成。ステータス列を「進行中」でオートフィルターし、さらにNo.6を手動非表示にした状態（実機Excelで検証）。

次に進捗率列でエラー値の扱いを確認します（以降は上記のオートフィルター＋No.6手動非表示の状態を前提とします）。No.3は表示されている行ですが値が#DIV/0!であるため、SUBTOTAL(1,…)で平均を取ってもSUBTOTAL(101,…)で平均を取っても、結果はどちらも#DIV/0!のまま返ってきます。SUBTOTALの関数番号にはエラー値を無視する選択肢がないため、非表示行の扱いを変えても意味がありません。ここでAGGREGATEを使うと、AGGREGATE(1,6,範囲)（平均・エラー値のみ無視）はオプション6が「エラー値だけを無視し、フィルターで除外された行も手動非表示の行も区別せず計算に含める」という設定であるため、エラー行のNo.3を除く11行すべて、つまりNo.1・2・4・5・6・7・8・9・10・11・12の45%・100%・0%・60%・70%・100%・30%・0%・55%・100%・40%を対象に平均を取り、合計600%を11件で割った約54.5%を返します。フィルターで除外されているはずのNo.2・4・7・9・11や、手動非表示にしたNo.6の値まで計算に紛れ込んでしまう点に注意してください。これに対してAGGREGATE(1,7,範囲)（平均・非表示行とエラー値を無視）は、SUBTOTALの101〜111と同じように「フィルターで除外された行・手動非表示の行・エラー値」をまとめて無視するため、表示されているNo.1・5・8・10・12の45%・60%・30%・55%・40%、合計230%を5件で割った46%を返します。SUBTOTALと違い、AGGREGATEは非表示行を無視させたければオプションを1・3・5・7のいずれかに明示的に選ぶ必要があり、オプション6のようにそれ以外を選んだ場合は、フィルターも手動非表示もいっさい考慮されません。

よくある落とし穴：入れ子のSUBTOTALと二重集計

地域別に小計行を挟みながら合計行を作るレポートで、うっかりSUM関数を使うと二重集計が起きます。以下は説明用の仮設例です。東日本・西日本・海外の3地域それぞれに案件が2件ずつあり、各地域の下にSUBTOTAL(9,…)で小計行を作っているとします。東日本は120と80で小計200、西日本は90と60で小計150、海外は70と40で小計110です。ここで一番下の合計行を、生データ6件と小計3行を含む範囲全体に対して単純にSUM関数で作ると、120+80+200+90+60+150+70+40+110=920となり、各地域の小計を生データと二重に数えてしまいます（本来の合計460の、ちょうど2倍です）。同じ範囲をSUBTOTAL(9,…)で合計すると、Microsoftのヘルプが説明するとおり「範囲の中にある他の集計（入れ子の集計）は二重計算を避けるため無視される」ため、3つの小計行を自動的に除外して生データ6件だけを合算し、正しい460を返します。この仕様のおかげで、途中に小計行を挟んだレポートでも、一番下の合計セルにSUBTOTALを使ってさえいれば、地域の増減や小計行の追加・削除があっても数式を書き換える必要がありません。

入れ子のSUBTOTALと二重集計（百万円） 東日本 案件1　120百万円 案件2　80百万円 小計 SUBTOTAL(9)＝200 西日本 案件3　90百万円 案件4　60百万円 小計 SUBTOTAL(9)＝150 海外 案件5　70百万円 案件6　40百万円 小計 SUBTOTAL(9)＝110 合計行を単純SUMで作った場合 結果 920百万円（誤り） 生データ6件（460）に加えて 3つの小計行（460）まで合算し 正しい合計の2倍になっている 同じ範囲をSUBTOTAL(9,…)で作る場合 結果 460百万円（正しい） 範囲内にある入れ子の小計行を 自動的に無視するため、生データ 6件だけを正しく合算する
図：設例のため作成。地域別小計3行と生データ6行を含む範囲で、単純SUMとSUBTOTAL(9)の結果を比較。

SUMIFS・SUMPRODUCTとの使い分け早見表

条件に応じた集計を扱う関数としてはSUMIFSやSUMPRODUCTが代表格ですが、これらとSUBTOTAL・AGGREGATEは判定基準がまったく異なります。SUMIFSは「地域が東日本」「ステータスが進行中」のようにセルの値を条件として合計する関数で、行がフィルターや手動操作によって非表示になっているかどうかは一切考慮しません。フィルターで絞り込んでいても、SUMIFSは非表示の行の値も含めて計算してしまいます。これに対してSUBTOTAL・AGGREGATEは「今画面に表示されている行かどうか」を基準にする関数で、セルの値そのものによる条件分岐はできません。両者は競合するものではなく、実務では組み合わせて使う場面もあります。条件による集計の作法はxl-020-sumifs-financeとxl-021-sumproduct-financeで詳しく扱っています。

観点SUMIFS・SUMPRODUCTSUBTOTAL・AGGREGATE
判定の基準セルの値（地域・ステータスなどの条件）画面上の表示状態（フィルター・手動非表示）
フィルターの影響（SUBTOTAL）受けない（非表示行も計算に含む）受ける（関数番号が1〜11でも101〜111でも常に除外）
フィルターの影響（AGGREGATE）受けない（非表示行も計算に含む）オプション1・3・5・7を選んだときだけ除外。6など他のオプションでは除外されない
手動非表示の影響受けない関数番号・オプションで含める／除外を選択
エラー値のあるセル条件式によっては伝播しにくい場合もあるSUBTOTALは伝播、AGGREGATEはオプションで無視可能
向いている用途条件別の集計・加重平均・複数条件の突合フィルター連動レポート・テーブルの集計行
集計行の関数はどれを選ぶか 集計から除きたい行の基準は？ セルの値（地域・ステータス等） → SUMIFS・SUMPRODUCT 画面表示（フィルター・非表示） → SUBTOTAL・AGGREGATE エラー値なし SUBTOTAL (1〜11／101〜111) エラー値あり AGGREGATE (オプション6・7)
図：設例のため作成。集計行の関数選択を、判定基準（セルの値か画面表示か）とエラー値の有無で整理したもの。

よくある質問（FAQ）

Q. SUBTOTALとAGGREGATE、結局どちらを使えばよいですか。
A. 非表示行やエラー値を気にしないなら、関数番号だけで済むSUBTOTALで十分です。参照範囲にエラー値が混じる可能性があり、かつ手動非表示とフィルターを個別に制御したい場合はAGGREGATEを使います。Excelテーブルの集計行は既定でSUBTOTALを使うため、まずSUBTOTALの挙動を理解してからAGGREGATEに進むと整理しやすくなります。

Q. SUMIFS関数でも同じことができませんか。
A. できません。SUMIFSはセルの値による条件で集計する関数で、行がフィルターや手動操作によって非表示になっているかどうかは判定材料に含まれません。フィルターの表示状態に連動した集計行を作りたい場合は、SUBTOTAL・AGGREGATEを使う必要があります。条件による集計はxl-020-sumifs-financeを参照してください。

Q. AGGREGATE関数のオプションを7にしておけばいつも安全ですか。
A. 「非表示の行とエラー値を無視する」設定は多くの場面で安全ですが、監査目的でエラーの存在そのものを確認したい集計では、エラーを無視すると問題を見逃すおそれがあります。集計セルとは別に、COUNTIFなどでエラー件数を数える検算セルを用意しておくと安全です。

Q. ピボットテーブルを使えばSUBTOTALは不要になりますか。
A. 用途が異なります。ピボットテーブルは集計軸を組み替えながら分析する機能で、SUBTOTAL・AGGREGATEは表形式のレポートの特定セルに集計値を持たせる関数です。ピボットテーブルをスライサーでボタン化してフィルターする方法はxl-033-pivot-slicer-timelineで解説しています。

まとめ

SUBTOTAL関数は関数番号を1〜11か101〜111かで切り替えることで、手動非表示行を含めるか除外するかを選べます。ただしこの違いが意味を持つのは範囲にフィルターが一切かかっていないときだけで、同じ範囲に有効なオートフィルターが存在する場合は、関数番号9も手動非表示行を自動的に無視してしまい109と同じ結果を返します。フィルターで除外された行自体はどちらの系統でも常に無視されるため、フィルター連動のレポート作りの基本はまずSUBTOTALで押さえられます。AGGREGATE関数は関数番号とオプションを分けて持つことで、エラー値の混じった範囲でも計算を止めずに済むという拡張性を持っていますが、非表示行を無視させるにはオプション1・3・5・7を明示的に選ぶ必要があり、6のようなオプションではフィルター除外行も手動非表示行も計算に含まれてしまいます。地域別小計のように範囲の中に別の集計を含む場合は、入れ子の集計が自動的に無視される仕様のおかげで二重集計を避けられます。条件による集計はSUMIFS・SUMPRODUCTの役割であり、表示状態による集計はSUBTOTAL・AGGREGATEの役割だと切り分けておくと、レポート設計で迷いません。

フィルター連動の集計行を自分の手で組んでみる

本記事で扱ったSUBTOTAL・AGGREGATEの関数番号とオプションの使い分けは、読むだけでは実務で定着しません。自分のデータでテーブルの集計行を作り、フィルターや手動非表示を切り替えて結果が追随するかを確かめることが理解の近道です。関数の実装を練習できる環境は無料会員登録で利用できます。

→ Excel関数を練習する

次に読む

出典・参考（2026年9月確認）

※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための仮設例です。