【Excel】SUBTOTAL関数でフィルター適用後のデータだけを集計する方法

Excelで大量のデータを扱う際、特定の条件でフィルターを適用し、その表示されているデータだけを集計したい場面は頻繁にありますね。

SUM関数など通常の集計関数では、非表示の行も集計対象に含まれてしまい、意図しない結果になってしまうことがあります。

この記事では、SUBTOTAL関数を使ってフィルター後のデータだけを正確に集計する方法を詳しく解説します。

この機能を使えば、データ分析の効率が格段に向上します。

SUBTOTAL関数とは?フィルター後のデータを集計できる仕組み

SUBTOTAL関数は、指定した範囲内のデータに対してさまざまな集計を行うExcelの関数です。

特に、フィルターで絞り込まれた表示行や、手動で非表示にされた行を考慮して集計できる点が大きな特徴です。

この関数は2つの必須引数を持っています。

関数コードと参照範囲の指定

SUBTOTAL関数は、以下の形式で記述します。

=SUBTOTAL(関数コード, 参照範囲)

関数コード(第一引数): これはどのような集計を行うかを指定する数値です。たとえば、9はSUM(合計)、1はAVERAGE(平均)を表します。

さらに、この関数コードには重要な違いがあります。

• 1〜11のコード: フィルターで非表示になった行は集計対象から除外しますが、手動で非表示にした行は集計対象に含みます。
• 101〜111のコード: フィルターで非表示になった行も、手動で非表示にした行も、両方とも集計対象から除外します。ビジネスではこちらのコードを使う機会が多いでしょう。

参照範囲(第二引数): これは集計したいデータが入力されているセル範囲です。例えば、B2:B100のように指定します。

この機能により、データにオートフィルターを適用するだけで、SUBTOTAL関数が自動的に表示されているデータのみを再集計してくれるのです。

SUBTOTAL関数を使ったフィルター後集計の操作手順

ここでは、SUBTOTAL関数を使ってフィルター適用後のデータを集計する具体的な手順を解説します。

Excel for Microsoft 365を基準にしていますが、Excel 2019や2021でも同様の操作が可能です。

ステップ1: 集計対象データの準備とオートフィルター設定

1. データのテーブル化(推奨)
   集計したいデータ範囲を選択します。次に、「挿入」タブをクリックし、「テーブル」グループの「テーブル」をクリックします。「テーブルの作成」ダイアログが表示されたら、「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認し、「OK」をクリックします。
2. オートフィルターの設定
   テーブル化しない場合は、データ範囲の任意のセルを選択します。次に、「データ」タブをクリックし、「並べ替えとフィルター」グループの「フィルター」をクリックします。各列の先頭にドロップダウン矢印が表示されます。

ステップ2: SUBTOTAL関数による集計数式の入力

1. 集計結果を表示したいセルを選択
   集計結果を表示したいセル(データの上下どちらでも構いません)をクリックします。
2. 数式の入力
   選択したセルに、以下の数式を入力します(例としてB列の合計を集計する場合)。
   =SUBTOTAL(109,B2:B100)
   「109」は、フィルターで非表示になった行と手動で非表示になった行の両方を除外して合計(SUM)を計算する関数コードです。参照範囲は実際のデータ範囲に合わせて変更してください。
3. 数式の確定
   Enterキーを押して数式を確定します。この時点で、フィルターを適用していない場合は全データの合計が表示されます。

ステップ3: フィルター適用時の挙動確認

1. フィルターの適用と集計値の変化を確認
   データ範囲の見出し行にあるドロップダウン矢印をクリックし、フィルター条件を選択して「OK」をクリックします。数式を入力したセルに表示されている集計値が、フィルターで絞り込まれた表示データのみの合計に自動的に変化することを確認します。
2. フィルターの解除と集計値の復元
   フィルターを解除すると、集計値が再び全データの合計に戻ることを確認します。

SUBTOTAL関数利用時の注意点とよくある失敗

SUBTOTAL関数は非常に便利ですが、使い方を誤ると意図しない結果になることがあります。

ここでは、よくある失敗例と対処法を解説します。

SUBTOTAL関数で隠れた行が集計されてしまう

原因: 第一引数(関数コード)の選択が適切でない可能性があります。関数コード1〜11を使用した場合、フィルターで非表示になった行は除外されますが、ユーザーが手動で非表示にした行は集計対象に含まれてしまいます。

対処法: 手動で非表示にした行も集計対象から除外したい場合は、関数コード101〜111の範囲を使用してください。例えば、合計の場合は109を指定します。これにより、フィルターおよび手動で非表示にされた行が正確に無視されます。

ネストされたSUBTOTAL関数が正しく機能しない

原因: SUBTOTAL関数は、引数として指定した参照範囲内に他のSUBTOTAL関数が含まれていても、そのSUBTOTAL関数の結果は集計しません。この「自己参照を回避する」という性質は、意図しない二重集計を防ぐための仕様です。そのため、SUBTOTAL関数をネスト(入れ子)にして使っても、期待通りの多段階集計はできません。

対処法: 複数の集計項目が必要な場合は、それぞれ別のセルで独立したSUBTOTAL関数を使用してください。より複雑な階層集計が必要な場合は、Power QueryやVBAなどの高度な機能の検討が必要です。

Excelテーブルでの挙動の違い

原因: Excelのテーブル機能には、自動的に集計行を追加する機能があります。この集計行では、デフォルトでSUBTOTAL関数が使われることが多く、通常のセルに入力したSUBTOTAL関数と混同しやすいことがあります。

対処法: テーブルの集計行でSUBTOTAL関数が使われている場合、その集計行自体はSUBTOTAL関数の参照範囲に含めないでください。テーブルの構造化参照(例: [#Data],[数量])を使用することで、範囲のずれを防ぎ、より堅牢な数式を作成できます。

コピー&ペースト時の参照範囲のずれ

原因: SUBTOTAL関数を含むセルをコピー&ペーストする際、参照範囲が相対参照で指定されていると、ペースト先の位置に合わせて参照範囲が自動的にずれてしまいます。

対処法: 数式を作成する際に、F4キーを押して絶対参照(例: $B$2:$B$100)を使用してください。これにより、コピー&ペーストしても参照範囲が固定され、意図しないずれを防げます。

Excel2019/2021との違い

補足: SUBTOTAL関数自体はExcelの非常に古いバージョンから存在するため、Excel for Microsoft 365とExcel 2019、2021の間で機能上の大きな違いはありません。全てのバージョンで同様の集計が可能です。

SUM関数とSUBTOTAL関数の機能比較

SUBTOTAL関数とSUM関数はどちらも合計を計算する関数ですが、その挙動には重要な違いがあります。

それぞれの特徴を理解し、適切な場面で使い分けることが重要です。

項目	SUM関数	SUBTOTAL関数
集計対象	全てのセル(非表示の行も含む)	表示されているセルのみ
フィルター連携	連動しない	連動する
手動非表示行の扱い	集計対象になる	第一引数で制御可能(1-11は集計、101-111は無視)
ネスト	可能	不可能
主な用途	全体の合計計算	フィルター後の集計、部分的な集計

まとめ

SUBTOTAL関数を活用することで、Excelでフィルターを適用した際に表示されているデータだけを正確に集計できるようになります。

特に、関数コードの第一引数で「101〜111」の範囲を選ぶことで、手動で隠した行も集計から除外できます。

この機能は、ビジネスにおける日々のデータ分析やレポート作成の効率を大きく向上させます。

ぜひSUBTOTAL関数をマスターし、よりスマートなデータ集計を実践してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】