【Excel】SUBTOTAL関数の9と109の違いとは？非表示行の扱いを徹底比較

Excelで集計作業をしていて、SUBTOTAL関数の結果が思った通りにならなかった経験はありませんか。

実はSUBTOTAL関数には「9」と「109」という2種類の引数があり、非表示行の扱い方が全く異なります。この違いを理解していないと、フィルター機能を使った集計や、手動で行を非表示にした際の計算結果に誤差が生じてしまうでしょう。

特にデータ分析や報告書作成では、正確な集計が求められます。9と109の使い分けを知っておくことで、集計ミスを防ぎ、業務効率も大幅に向上するでしょう。

本記事では、SUBTOTAL関数の9と109の違いを実例付きで詳しく解説します。それぞれの特徴や使用場面、実務での判断基準まで、すぐに活用できる知識をお届けしますので、ぜひ最後までご覧ください。

SUBTOTAL関数の基本と9・109の違いとは

それではまずSUBTOTAL関数の基本と、9と109の違いについて解説していきます。

SUBTOTAL関数の役割と基本構文

SUBTOTAL関数は、指定した範囲に対して合計や平均などの集計を行うExcelの関数です。

SUM関数やAVERAGE関数と似ていますが、他のSUBTOTAL関数を無視して計算できるという特徴があります。これにより、小計と総計を同じ列に配置しても、二重計算を避けられるのです。

基本的な構文は以下の通りです。

第一引数の「集計方法」に数値を指定することで、合計、平均、個数など11種類の計算が可能になります。この数値こそが、今回のテーマである「9」や「109」に該当するでしょう。

引数「9」と「109」が持つ意味

SUBTOTAL関数の第一引数には、1から11までの数値、または101から111までの数値を指定できます。

引数	集計方法	引数（100番台）
1	AVERAGE（平均）	101
2	COUNT（数値の個数）	102
3	COUNTA（空白以外の個数）	103
9	SUM（合計）	109
4	MAX（最大値）	104
5	MIN（最小値）	105

「9」はSUM関数と同じ合計を求める機能を持ちます。一方「109」も同じく合計を求めますが、非表示行の扱いが異なる点が最大の違いです。

100を足した数値（101～111）は、比較的新しいバージョンのExcelで追加された機能といえます。

非表示行に対する動作の違い

9と109の最も重要な違いは、非表示にした行をどう扱うかという点にあります。

具体的には、SUBTOTAL(9)は手動で行を非表示にしても、その数値を集計に含めます。一方でオートフィルター機能で絞り込んだ場合は、非表示行を除外するのです。

対してSUBTOTAL(109)は、手動による非表示もフィルターによる非表示も、すべて集計から除外します。この動作の違いを理解することが、正確な集計への第一歩でしょう。

実務では、データの見せ方や集計目的によって使い分ける必要があります。

SUBTOTAL(9)の特徴と使用場面

続いてはSUBTOTAL(9)の特徴と具体的な使用場面を確認していきます。

手動で非表示にした行も集計に含める仕組み

SUBTOTAL(9)は、ユーザーが手動で非表示にした行のデータを集計対象として扱い続けます。

例えば売上データで、一時的に特定の支店の行を非表示にしたとしましょう。この場合でもSUBTOTAL(9)を使えば、全支店の合計売上を正しく計算できるのです。

これは「見た目は整理したいが、計算には全データを使いたい」というニーズに応えています。行を削除せずに非表示にするだけで、元のデータを保持したまま表示を調整できるでしょう。

ただし、オートフィルター機能で絞り込んだ行は除外されるため、フィルター使用時には注意が必要です。この特性を理解していないと、予期しない集計結果になることがあります。

SUBTOTAL(9)が適している実務シーン

SUBTOTAL(9)が特に役立つのは、以下のような場面です。

まず、詳細データを一時的に隠して概要だけを見せたいケースが挙げられます。プレゼンテーション資料で、細かい内訳は非表示にしつつ、総計は全データで計算したい場合に最適でしょう。

次に、段階的な集計を行う表での使用です。例えば月別・四半期別・年間の3段階で集計する場合、各レベルでSUBTOTAL(9)を使えば、上位の集計が下位の小計を重複してカウントしません。

また、印刷時に特定の行を非表示にしたいが、数式には影響させたくない場合にも有効です。

これらのシーンでは、データの完全性を保ちながら表示を最適化できます。

実際の計算例と注意点

具体的な計算例を見てみましょう。

注意すべき点は、フィルター機能との組み合わせです。オートフィルターを適用した状態でSUBTOTAL(9)を使うと、絞り込まれた行だけの合計になります。

また、行の非表示方法によって結果が変わるため、チーム内で集計ルールを統一することが重要でしょう。特に複数人で同じファイルを編集する場合、どちらの関数を使うか事前に決めておくべきです。

さらに、ネストした非表示（グループ化機能で折りたたんだ行）も、手動非表示と同じ扱いになる点に留意してください。

SUBTOTAL(109)の特徴と使用場面

続いてはSUBTOTAL(109)の特徴と活用方法を確認していきます。

すべての非表示行を除外する動作

SUBTOTAL(109)は、非表示の原因に関わらず、すべての非表示行を集計から除外します。

手動で非表示にした行も、オートフィルターで絞り込んだ行も、どちらも計算対象外になるのです。この動作により、「今画面に表示されているデータだけを集計する」という直感的な結果が得られます。

特にフィルター機能を頻繁に使う業務では、この特性が非常に便利でしょう。条件を変更するたびに、表示されているデータの合計が自動的に更新されるためです。

この明確なルールにより、集計結果の予測がしやすくなります。データ分析の際に、様々な条件で絞り込みながら即座に結果を確認できる点が大きなメリットです。

フィルター機能との相性が抜群な理由

SUBTOTAL(109)とオートフィルターの組み合わせは、動的な集計を実現する最強のコンビといえるでしょう。

例えば商品別の売上データで、特定のカテゴリーだけを表示したいとします。フィルターで絞り込むと、SUBTOTAL(109)は自動的にそのカテゴリーの合計だけを計算してくれるのです。

この機能により、以下のような作業が劇的に効率化されます。

作業内容	SUBTOTAL(109)の効果
地域別の売上確認	フィルター変更だけで即座に集計
期間指定の分析	日付フィルターで自動計算
条件付き抽出	複数条件でも柔軟に対応
ダッシュボード作成	見た目と集計が常に一致

さらに、複数のフィルター条件を組み合わせた複雑な分析でも、数式を変更する必要がありません。フィルターを操作するだけで、求める集計結果が得られます。

データ探索や仮説検証を繰り返す分析業務では、この柔軟性が時間短縮に直結するでしょう。

実際の計算例と活用テクニック

先ほどと同じデータで、SUBTOTAL(109)の動作を確認しましょう。

活用テクニックとして、条件付き書式との組み合わせが効果的です。SUBTOTAL(109)で算出した値に応じて、セルの色を変更するルールを設定すれば、視覚的に分かりやすいダッシュボードが作成できます。

また、複数の集計行を設けて、異なる条件での合計を並べて表示する方法もあります。例えば「全体の合計」をSUBTOTAL(9)で、「表示中の合計」をSUBTOTAL(109)で計算し、両者を比較することで、フィルターの影響度が一目で分かるでしょう。

ピボットテーブルと併用する際も、SUBTOTAL(109)を使えば、より柔軟な集計が可能になります。

9と109の使い分け判断基準

続いては実務での使い分け判断基準を確認していきます。

フィルター使用時はどちらを選ぶべきか

フィルター機能を使う場合、ほとんどのケースでSUBTOTAL(109)が適切です。

なぜなら、フィルターをかける目的は「条件に合うデータだけを見て集計したい」というニーズだからです。SUBTOTAL(109)を使えば、画面に表示されているデータの合計が自動的に計算されるため、直感的で分かりやすいでしょう。

一方、フィルターで絞り込んでも全体の集計値を表示し続けたい場合は、SUBTOTAL(9)を選択します。ただしこのケースは実務では稀で、むしろ混乱を招く可能性があるため注意が必要です。

売上分析、顧客リストの集計、在庫管理など、データベース的な使い方をする表では、基本的に109を使うのがセオリーといえます。

迷った場合は、「ユーザーが見ているものと集計結果が一致するか」という視点で考えると良いでしょう。

複雑な集計での組み合わせ方

実務では、9と109を同じシート内で併用するケースもあります。

例えば、月次売上表で以下のような構成が考えられます。

このように使い分けることで、多角的な分析が一つの表で完結します。全体像を把握しながら、特定条件での集計も同時に確認できるでしょう。

また、レポート用とデータ確認用で異なる関数を使うという方法もあります。印刷用の集計セルにはSUBTOTAL(9)を、画面確認用のセルにはSUBTOTAL(109)を配置すれば、用途に応じた最適な表示が実現できます。

ただし、複雑になりすぎると管理が難しくなるため、セルに分かりやすいラベルを付けるなどの工夫が必要です。

よくあるトラブルと解決方法

SUBTOTAL関数を使う際によく発生するトラブルと、その解決方法を紹介します。

まず「集計結果が合わない」という問題です。これは9と109の選択ミスが原因であることが多いでしょう。非表示行の扱いを確認し、目的に合った関数に変更してください。

次に「フィルターを変更しても集計が更新されない」というケースがあります。これは計算方法が手動になっている可能性があるため、「数式」タブから「計算方法の設定」を「自動」に変更しましょう。

トラブル	原因	解決方法
合計が予想より大きい	SUBTOTAL(9)で手動非表示行も計算	109に変更
合計が予想より小さい	SUBTOTAL(109)で非表示行を除外	9に変更、または行を再表示
更新されない	手動計算モード	自動計算に設定変更
エラー表示	範囲指定ミス	参照範囲を確認

また、他の人が作った表を引き継ぐ際は、どちらの関数が使われているか必ず確認しましょう。引数を見れば一目で判別できますが、見落としがちなポイントです。

チーム内でルールを統一し、テンプレートを作成しておくことで、これらのトラブルを未然に防げるでしょう。

まとめ

SUBTOTAL関数の9と109は、どちらも合計を求める機能ですが、非表示行の扱いが大きく異なります。

SUBTOTAL(9)は手動で非表示にした行も集計に含め、フィルターで絞り込んだ行のみを除外します。一方、SUBTOTAL(109)はすべての非表示行を集計から除外するため、画面に表示されているデータだけを集計できるのです。

実務では、フィルター機能を使ったデータ分析にはSUBTOTAL(109)が適しており、全体の集計を保持しながら表示を調整したい場合にはSUBTOTAL(9)が便利でしょう。用途に応じて適切に使い分けることで、正確かつ効率的な集計作業が実現します。

この違いを理解し、目的に合った関数を選択することで、Excelでの集計作業がより快適になるはずです。