Excelを使っていて、「データにフィルターをかけたのに、合計値が変わらない…」「なぜか集計結果が合わない！」と悩んだことはありませんか？
SUM関数とSUBTOTAL関数の決定的な違いを理解してスキルアップ
この問題のほとんどの原因は、SUM関数とSUBTOTAL関数の決定的な違いにあります。この違いを理解すれば、あなたの集計作業は劇的に改善します。
1. 集計結果が異なる理由：集計対象の「スコープ」の違い
SUM関数とSUBTOTAL関数が返す結果が異なる最大の理由は、「どのセルを集計の対象とするか」という、それぞれの関数の集計対象のスコープ（範囲）が違うからです。
SUM関数：すべてを集計する
SUM関数は非常にシンプルです。指定されたセル範囲内のすべての数値を合計します。
-
特徴: セルが非表示になっていようが、フィルターで絞り込まれていようが関係なく、指定範囲内の値はすべて合計に含まれます。
-
用途: データセット全体の総合計や、条件を無視した絶対的な合計を知りたいときに使います。
SUBTOTAL関数：表示されているデータだけを集計する
SUBTOTAL関数は、集計前にデータの表示状態をチェックします。
-
特徴: フィルターによって非表示になった行のデータは、集計から自動的に除外されます。
-
用途: データにフィルターを適用し、「現在画面に表示されているデータだけ」の合計、平均、個数などを求めたいときに使います。これこそが、SUBTOTAL関数がExcelの「小計機能」で使われる理由です。
2. 違いが顕著になる２つのケース
ケース 1: オートフィルターを適用したとき
これが最もよく発生する問題です。
| 関数 | フィルター適用後（一部非表示） | 結果 |
| SUM関数 | 非表示の行の値を含めて合計 | データ全体の合計が表示され、結果が変わらない |
| SUBTOTAL関数 | 非表示の行の値を除外して合計 | 表示されているデータだけの合計が表示され、結果が変わる |
データを絞り込んで「今見えている分」だけを集計したい場合は、必ずSUBTOTAL関数を使う必要があります。
ケース 2: 手動で「行を非表示」にしたとき
SUBTOTAL関数は、さらに奥深い集計対象のコントロール機能を持っています。これは、SUBTOTALの最初の引数である「機能番号」によって決まります。
| 機能番号 | 行の非表示（手動） | フィルター非表示 |
| 9 (SUM) | 含める | 除外する |
| 109 (SUM) | 除外する | 除外する |
もし、手動で非表示にした行（右クリックで「非表示」にした行）も集計から除外したい場合は、機能番号に100番台（109など）を使用する必要があります。
3. 一発解決！「適切な集計関数」の選び方
集計する際の目的を明確にすれば、どちらの関数を使うべきかすぐにわかります。
| 目的 | 適切な関数 | 推奨される集計方法 (SUMの場合) |
| データの全体像を知りたい | SUM関数 | =SUM(A:A) |
| フィルター後の表示データの合計を知りたい | SUBTOTAL関数 | =SUBTOTAL(9, A:A) または =SUBTOTAL(109, A:A) |
| 小計や総合計の集計で二重集計を防ぎたい | SUBTOTAL関数 | =SUBTOTAL(109, A:A) |
特に理由がなければ、フィルター操作と連動するSUBTOTAL関数 (109)をメインの集計関数として使うことを習慣づけると、「集計結果が合わない！」という問題は劇的に減少するはずです。
ぜひ、あなたのExcelシートのSUM関数をSUBTOTAL関数に置き換えて、その違いを実感してみてください！
この記事を書いているのは、【Excel問題解決Lab.】筆者「16時間」を「5秒処理」化。 Excelユーザー歴25年の経歴を持つ筆者が解説。過去には、事務員さんの年間約192時間の工数削減を実現。削減できた時間は、より付加価値の高い分析業務に充てられるようになり、社内の生産性向上と業務省力化に貢献。
