- 01.はじめに
- 02.この記事でできること
- 03.なぜ普通のSUM関数ではダメなのか
- 04.SUBTOTAL関数の基本的な使い方
- 05.SUBTOTAL関数の実践テクニック
- 06.AGGREGATE関数の特徴と使い方
- 07.AGGREGATE関数の実践テクニック
- 08.SUBTOTAL・AGGREGATEを使い分けるコツ
- 09.実務でよく使うシチュエーション別サンプル
- 10.フィルター集計を極める！さらに便利なExcel操作
- 11.まとめ｜今日からできる集計の一歩
01.はじめに
Excelを使っていて「フィルターをかけたら合計が変になった」「必要な数値だけ集計したいのにうまくいかない」といった経験はありませんか？実は、この問題、多くのExcelユーザーが一度は悩むポイントです。
たとえば、商品リストにフィルターをかけて特定のカテゴリだけ表示したのに、SUM関数で合計すると非表示の行まで含めた値が出てしまう。これは、SUM関数が「見えている行」ではなく「すべてのセル」を対象にしてしまうからです。
このような状況を解決してくれるのが「SUBTOTAL関数」と「AGGREGATE関数」です。これらの関数を使えば、フィルターや非表示の行を自動で除外し、今見えているデータだけを正確に集計できます。
この記事では、これら2つの関数の基本的な使い方から、実務に役立つ応用テクニックまでを初心者にもわかりやすく解説していきます。Excelでの集計力をグッと引き上げるヒントが満載です。
02.この記事でできること
このブログ記事を読み終える頃には、以下のようなことができるようになります。
1. SUBTOTAL関数とAGGREGATE関数の違いがわかる
似ているようで実は使い方や特徴が異なる2つの関数。それぞれの得意分野や使い分けのポイントを解説します。
2. フィルターをかけた状態でも正確な集計ができる
見えているデータだけを集計するには？フィルターとの連動方法や、非表示データの扱い方が理解できます。
3. 実務で役立つ応用テクニックが身につく
売上や勤怠、プロジェクト管理など、日々の業務で使える活用事例を豊富に紹介します。ショートカットやネストなどの応用技も習得可能です。
4. 関数のミスを防ぐ考え方がわかる
ありがちなエラーや集計ミスの原因と、その防止策も紹介。効率的かつ正確なExcel集計力を身につけましょう。
03.なぜ普通のSUM関数ではダメなのか
フィルターをかけると何が起こる？
Excelの「フィルター」機能は、特定の条件でデータを絞り込み表示する便利なツールです。しかし、フィルターをかけた状態でSUM関数など通常の関数を使うと、非表示のデータまで含めて集計されるという落とし穴があります。
つまり、見た目には10行しか表示されていなくても、SUM関数は全ての行（たとえ非表示でも）を合計してしまうのです。
SUM関数の落とし穴
SUM関数は非常に基本的で便利な関数ですが、表示・非表示の区別ができないという特徴があります。以下のような式を使うと、
=SUM(B2:B100)
この範囲内のすべての値を合計します。たとえフィルターで一部が非表示になっていても、関係ありません。
そのため、たとえば「営業部門だけの売上合計を知りたい」という場合に、フィルターをかけたのに意図しない結果が出てしまうのです。
可視セルだけを集計する重要性
フィルターや非表示行を無視し、「今見えているデータ」だけを対象に集計したい。そんなときに役立つのが、可視セル（見えているセル）だけを集計できる関数です。
これを実現してくれるのが、これから解説する「SUBTOTAL関数」と「AGGREGATE関数」。どちらも、見えているデータだけを的確に集計するために設計された関数です。
次の章では、まずSUBTOTAL関数の基本的な使い方を解説していきます。
04.SUBTOTAL関数の基本的な使い方
SUBTOTAL関数の構文と意味
SUBTOTAL関数は、フィルターや非表示の行を考慮して集計してくれるExcelの便利な関数です。構文は以下の通りです。
=SUBTOTAL(集計方法の番号, 範囲)
この「集計方法の番号」によって、合計・平均・最大値などを指定できます。
関数番号（集計方法）の一覧
SUBTOTAL関数では、1〜11および101〜111の番号が用意されています。違いは以下の通りです。
- 1〜11：手動で非表示にした行も含めて集計
- 101〜111：手動で非表示にした行を除外して集計
よく使う番号の例：
- 9：合計（SUM）
- 1：平均（AVERAGE）
- 4：最大値（MAX）
- 5：最小値（MIN）
「フィルターに対応する」とはどういうこと？
SUBTOTAL関数は、Excelのフィルターで「非表示になっている行」を自動で除外してくれます。つまり、フィルターで絞り込んだデータだけを集計することができるのです。
たとえば以下のように記述すれば、
=SUBTOTAL(9, B2:B100)
フィルターで表示されている行の合計のみを求めてくれます。
よく使うケース：売上集計、経費計算など
たとえば、月別の売上データをフィルターで「4月だけ」に絞り、SUBTOTAL関数で合計すれば、4月の売上だけが正確に出せます。経費一覧でも同様に、「交通費」だけ、「会議費」だけを絞って集計するのに便利です。
このように、SUBTOTAL関数は実務で非常に重宝される集計関数なのです。
05.SUBTOTAL関数の実践テクニック
フィルターと連動して集計する方法
SUBTOTAL関数の最大の強みは、Excelのフィルターと自動で連携してくれることです。データにフィルターをかけ、特定の条件に絞り込むだけで、SUBTOTAL関数はその結果に応じて数値を自動で再計算します。
例：
=SUBTOTAL(9, C2:C100)
この関数は、C列の見えているセルの合計だけを常に反映してくれます。
空白や非表示行を除外する方法
SUBTOTAL関数の関数番号を「101〜111」にすることで、フィルターで非表示になった行に加え、手動で非表示にした行も除外できます。
たとえば、
=SUBTOTAL(109, C2:C100)
とすれば、手動で非表示にした行（右クリック→非表示）も集計対象外となります。これにより、より正確なデータ集計が可能です。
列ごとに違う集計を出したいときの工夫
同じ表の中で、列Aでは平均、列Bでは合計を出したい場合は、それぞれの列に適した関数番号を使えばOKです。
- 列A：
=SUBTOTAL(1, A2:A100)（平均） - 列B：
=SUBTOTAL(9, B2:B100)（合計）
このように、集計方法を柔軟に変えられる点も大きな魅力です。
ショートカットキーと連携して効率アップ
フィルターの表示・非表示を手早く操作するにはショートカットキーも活用しましょう。
- Ctrl + Shift + L：フィルターのオン・オフ切り替え
- Alt + ↓：フィルターボタンを開く
これらを組み合わせることで、SUBTOTAL関数の効果を即座に確認・活用できます。
06.AGGREGATE関数の特徴と使い方
SUBTOTALとの違い
AGGREGATE関数は、SUBTOTAL関数と同様にフィルターや非表示行を考慮できる集計関数ですが、さらにエラー値の無視や手動非表示の制御など、より柔軟な集計オプションを備えています。
AGGREGATE関数の構文と引数の意味
構文は以下の通りです：
=AGGREGATE(関数番号, オプション, 範囲, [k])
- 関数番号：どんな集計をするか（例：1=AVERAGE、9=SUM、4=MAXなど）
- オプション：0〜7で表示やエラーの扱いを指定
- 範囲：集計対象のセル範囲
- [k]：一部の関数（SMALLやLARGE）で順位指定に使用
除外オプションで柔軟な集計が可能に
オプション引数では、以下のような表示・非表示状態・エラー値への対処が指定できます：
- 0：何も除外しない
- 1：隠し行を無視
- 2：エラー値を無視
- 3：隠し行とエラー値を無視
- 5：フィルターで非表示行を無視
- 7：フィルター非表示＋エラー値を無視
たとえば、
=AGGREGATE(9, 7, B2:B100)
と記述すると、フィルターで非表示になった行とエラー値を同時に除外して合計します。
なぜAGGREGATEが「上位互換」と言われるのか
SUBTOTALができる「フィルター・手動非表示除外」だけでなく、エラー値を無視する機能や、SMALL・LARGEなど順位関数にも対応している点がAGGREGATE最大の強みです。
特にデータにNAや#DIV/0!などのエラーが混ざる場面では、SUBTOTALでは対応できずAGGREGATEでの処理が必要になります。
07.AGGREGATE関数の実践テクニック
フィルター＋エラー処理にも強い！
AGGREGATE関数の最大の利点は、エラー値を除外しながら集計できる点です。たとえば、数式に#DIV/0!などのエラーが含まれていても、AGGREGATEなら問題なく処理できます。
例：
=AGGREGATE(1, 6, C2:C100)
この場合、「平均（1）」を算出しつつ、エラー値（オプション6）を除外してくれます。
中間集計・外れ値除外などの応用例
AGGREGATE関数は、順位に基づく関数（LARGEやSMALL）との組み合わせも可能です。
=AGGREGATE(14, 6, B2:B100, 3)：3番目に大きい値（LARGE関数）をエラー無視で抽出=AGGREGATE(15, 6, B2:B100, 1)：最小値を除いたデータから2番目に小さい値を取得
このように外れ値を除いた中間値分析などにも応用できます。
データ分析での使い所
AGGREGATE関数は、分析用のデータに多くの欠損や異常値がある場合でも有効です。特定の範囲内で最大値・最小値・順位などを抽出する場面では、SUBTOTALよりも精度高く使えるケースが多いです。
関数のネスト（入れ子）活用法
AGGREGATE関数は、IFやINDEXなど他の関数と組み合わせることでさらに高度な処理も可能です。
=AGGREGATE(15, 6, IF(A2:A100="営業", B2:B100), 1)
このように、条件付きで集計範囲を絞ることもできます（Ctrl+Shift+Enterが必要な場合も）。
08.SUBTOTAL・AGGREGATEを使い分けるコツ
どっちを使うべき？選び方の基準
SUBTOTAL関数とAGGREGATE関数は、似ているようで得意分野が異なります。基本的なフィルター対応の集計ならSUBTOTALで十分ですが、エラー値が含まれる場合や順位抽出など高度な集計をしたい場合はAGGREGATEが適しています。
選び方の目安：
- 単純な合計・平均・最大最小値など→SUBTOTAL
- エラーを無視したい、順位を使いたい→AGGREGATE
実務での使い分け事例
経費表の集計： フィルターで「交通費」などを絞って合計したい → SUBTOTAL
分析レポート： 欠損やエラーを除外して正確な統計を取りたい → AGGREGATE
営業成績ランキング： 特定部署の上位成績を取り出したい → AGGREGATE（LARGE関数）
よくあるミスとその防止策
- SUBTOTALで101〜111番を使わず、手動非表示が含まれてしまう
- AGGREGATEのオプションを間違えてエラーを含んでしまう
- 関数番号とオプション番号を混同してしまう
特にAGGREGATEは構文が複雑なので、公式ドキュメントや一覧表を手元に置くと安心です。
09.実務でよく使うシチュエーション別サンプル
売上・仕入れ管理
月ごと、商品カテゴリごとに売上や仕入れを集計する場面では、フィルターとSUBTOTAL関数の組み合わせが効果的です。
例：
=SUBTOTAL(9, D2:D500)
カテゴリでフィルターをかければ、該当商品の売上合計がすぐに出ます。
勤怠・労務管理
社員の出勤日数、残業時間などを部署別に集計する場面では、非表示行や特定条件を無視した集計が必要になります。
AGGREGATEを使えば、エラーを含む勤怠記録でも正しく集計可能です。
=AGGREGATE(9, 6, F2:F300)
プロジェクト収支の把握
プロジェクトごとの収支を管理する際は、部門別や期間別での絞り込み集計が必須です。
フィルター＋SUBTOTALで簡易集計し、詳細分析はAGGREGATEでという組み合わせが便利です。
複数条件での絞り込みと集計
複数条件でデータを絞り込んで集計したい場合、Excelのテーブル機能と組み合わせると操作性が上がります。
さらに、IF関数とAGGREGATEをネストすれば、条件に合うデータだけを抽出して分析することも可能です。
10.フィルター集計を極める！さらに便利なExcel操作
テーブル機能と併用するメリット
Excelのテーブル機能（Ctrl + T）は、データを視覚的・機能的に整理するのに最適です。テーブルに変換することで、フィルターが自動で付き、列の追加や行の増減にも対応しやすくなります。
SUBTOTAL関数は、テーブル内の列に対しても問題なく動作するため、見やすさと柔軟性を兼ね備えた集計が可能になります。
表示形式・条件付き書式との合わせ技
たとえば、数値が一定以上の場合に強調表示する条件付き書式を使うことで、重要な数値が一目で分かるようになります。
また、通貨記号やパーセントなど、表示形式を設定することで集計結果に対する理解も深まります。
ピボットテーブルとの違いと使い分け
ピボットテーブルは大規模データのクロス集計に強いですが、柔軟な見た目調整や、フィルターと連動したリアルタイム集計には不向きなこともあります。
その点、SUBTOTALやAGGREGATE関数を使えば、自分の思い通りに表を構成しながら集計ができるというメリットがあります。
ピボットテーブルは「まとめて分析」、SUBTOTAL・AGGREGATEは「自由な表で集計」という使い分けがおすすめです。
11.まとめ｜今日からできる集計の一歩
SUBTOTALとAGGREGATEを使いこなすコツ
Excelでフィルターを使った集計を正確に行いたいなら、SUBTOTAL関数とAGGREGATE関数の使い分けがカギになります。
シンプルな条件ならSUBTOTAL、複雑な条件やエラー処理を含む集計にはAGGREGATE。これらの特徴を押さえれば、集計ミスを減らし、作業効率を大きく向上させることができます。
フィルター集計が変わると仕事が変わる
多くの業務で必要になる「正確な集計」。ここで紹介した関数を活用すれば、報告資料や日々の分析の精度も格段に向上します。
Excelの関数は、単なる道具ではなく、仕事の質を高めるパートナーです。今日から一歩踏み出して、フィルター集計の達人を目指してみてください。
コメント