・フィルターで正しく集計できない…その悩み、SUBTOTALで解決できます
Excelでフィルターを使ってデータを絞り込んだとき、
「SUM関数だと非表示の行まで合計されてしまう…」
と困った経験はありませんか？
そんなときに便利なのが SUBTOTAL関数 です。
この記事では、SUBTOTAL関数の基本・使い方・SUMとの違い・実務での活用例 を、初心者の方にも分かりやすく解説します。
実際に私が取引先の業務改善で使用した事例も紹介しますので、ぜひ参考にしてください。
・この記事は5分で読めます
・この記事で分かること
- SUM関数との違い
- フィルター使用時に正しく集計する方法
- テーブル化による自動拡張のメリット
- 実務で役立つ活用例
・SUBTOTAL関数とは？
SUBTOTAL関数は、フィルターで表示されている行だけを対象に集計できる関数です。
=SUBTOTAL（集計方法,対象範囲）例としてセルC1～C5に入力されている数字の合計を算出してくれたり、件数の集計をしてくれます。
例えば、=SUBTOTAL(9, C4:C15)
と入力すると、C4〜C15の「表示されている行だけ」の合計を計算します。
・SUM関数(他 AVERAGE、COUNT)とSUBTOTAL関数の違い
| 項目 | SUM関数 | SUBTOTAL関数 |
| 非表示行の扱い | 含めて集計する | 含めない（フィルターで非表示の行は除外） |
| フィルターとの相性 | 不向き | 最適 |
| 集計できる種類 | 合計のみ | 合計・平均・件数など11種類 |
フィルターを使う業務では、SUMよりSUBTOTALの方が正確な集計が可能です。
・SUBTOTAL関数の使い方（基本）
▼例：合計を求める場合(セルC4～C15まで)
=SUBTOTAL(9, C4:C15)
▼平均を求める場合(セルC4～C15まで)
=SUBTOTAL(1, C4:C15)▼件数を求める場合
=SUBTOTAL(3, C4:C15)・フィルターを使ったときの動作例
支払日を「1〜3月」で絞り込むと、
表示されている行だけを対象に合計が自動で変わります。
SUM関数では非表示の行も含まれてしまうため、
フィルターを使う業務ではSUBTOTALが必須です。
条件を指定しフィルターをかけてみると、表示されている内容だけの集計になります。
・テーブル化するとさらに便利
対象範囲を「テーブル」に変換すると、
行を追加しても自動で範囲が広がるため、集計漏れを防げます。
▼テーブル化の手順
- 範囲を選択
- 挿入 → テーブル
- 好きなデザインに変更可能
テーブル化すると見た目も整い、実務でのミス防止にも役立ちます。
範囲を指定し、挿入 → テーブル
また、テーブルを選択中に「テーブルツール」→「テーブルデザイン」で自分好みにカスタムできます。
詳しくは記事の最後に「テーブル化のメリット(参考資料)」がございます。
・実務での活用例（私の体験談）
取引先から「月ごとにファイルを分けないと正しい合計が出ない」と相談を受けました。
原因は SUM関数を使っていたため、非表示行まで集計されていた こと。
SUBTOTAL関数とテーブル化を提案したところ、
- ファイルを1つに統合
- 集計ミスがゼロに
- 作業時間も短縮
と、大きな改善につながりました。
・SUBTOTALの集計方法(参考資料)
| 集計方法 | 集計方法名 | 集計の種類 |
| 1 | AVERAGE | 平均値 |
| 2 | COUNT | 数値の個数 |
| 3 | COUNTA | データの個数 |
| 4 | MAX | 最大値 |
| 5 | MIN | 最小値 |
| 6 | PRODUCT | 積 |
| 7 | STDEV.S | 不偏標準偏差 |
| 8 | STDEV.P | 標本標準偏差 |
| 9 | SUM | 合計値 |
| 10 | VAR.S | 不偏分散 |
| 11 | VAR.P | 標準分散 |
・まとめ
- SUBTOTAL関数はフィルターと相性が良い
- 非表示行を除外して正確に集計できる
- テーブル化と組み合わせるとさらに便利
- 実務の集計ミスを大幅に減らせる
- Excelでフィルターを使う機会が多い方は、ぜひSUBTOTAL関数を活用してみてください。
今回の相談で集計は「SUM」以外にもあることを知らない方がまだまだいるのではないかと私は思っております。同じように悩んでいる方の参考になれば幸いです。
ここまでご覧いただき、誠にありがとうございます。
・テーブル化のメリット(参考資料)
Excelでデータを扱うときは、範囲を「テーブル」に変換しておくと作業がとてもスムーズになります。特に、SUBTOTAL関数と組み合わせる場合はメリットが大きいため、実務でも積極的に活用したい機能です。ここでは、テーブル化の主な利点をまとめます。
① 行を追加しても自動で範囲が広がる
通常のセル範囲では、行を追加するとSUMやSUBTOTALの範囲がずれてしまうことがあります。
テーブル化しておけば、新しい行を追加しても自動でテーブル範囲に含まれるため、集計漏れを防げます。数式も自動でコピーされるので、入力ミスも減らせます。
② フィルターが最初から付いている
テーブル化すると、ヘッダーにフィルターが自動で設定されます。
並べ替えや絞り込みがすぐに使えるため、データ分析がスピーディーになります。
③ 見た目が整い、データ範囲が分かりやすい
テーブルは交互の色付けやヘッダー固定など、視認性が高いデザインになっています。
「どこがデータ範囲なのか」が一目で分かるため、初心者でも扱いやすく、資料としても見栄えが良くなります。
④ 構造化参照で数式が読みやすくなる
テーブル化すると、数式が「列名」で表示されるようになります。
例：
=SUM(売上[金額])このように、数式の意味が直感的に理解しやすくなるため、他の人が見ても分かりやすい表になります。
⑤ SUBTOTALとの相性が抜群
テーブル化＋SUBTOTALは、実務で非常に強力な組み合わせです。
フィルターで絞り込んだときに、表示されている行だけを正しく集計できるため、集計ミスがほぼゼロになります。
⑥ ピボットテーブルの更新が簡単
テーブルを元データにしておくと、ピボットテーブルの更新がワンクリックで完了します。
通常の範囲だと「データ範囲の変更忘れ」が起きやすいですが、テーブルならその心配がありません。
⑦ 複数人で作業してもミスが起きにくい
テーブル化すると「ここがデータ範囲です」という明確な区切りができます。
複数人で作業する場合でも、入力場所や編集範囲が分かりやすくなり、ミスを大幅に減らせます。
・ご注意
・掲載されている内容には細心の注意をしてるつもりですが、
間違いやご指摘等がありましたら、「お問い合わせ」からご連絡をいただけますと幸いです。
・掲載されている入力VBAコード等は動作を保証するものではなく、あくまでサンプルとして掲載しております。
・掲載されている入力VBAコード等は自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
・対応バージョン
Excel2021、2019、2016
コメント