Excelでフィルターを使ってデータを絞り込んだあと、「表示されているデータだけを合計したい」と思ったことはありませんか？
ところが、SUM関数で合計を求めると、フィルターで非表示になっているデータまで集計されてしまいます。
そのような場合に便利なのが SUBTOTAL関数です。
SUBTOTAL関数を使えば、フィルター後に表示されているデータだけを対象に合計や平均を計算できます。
この記事では、フィルター後の合計を正しく計算する方法と、SUBTOTAL関数の使い方をわかりやすく解説します。
SUM関数ではフィルター後の合計を正しく計算できない
まずはSUM関数で合計を求めるケースを見てみましょう。
下記のような売上データがあるとします。
この表にフィルターを設定し、「東京支店」だけを表示したとします。
見た目では東京支店のデータしか表示されていません。
しかし、SUM関数で売上を集計すると、非表示になっている大阪支店や名古屋支店の売上も含めて計算されます。
つまり、SUM関数はフィルターの状態を考慮しないのです。
この問題を解決するのが SUBTOTAL関数です。
SUBTOTAL関数の使い方
SUBTOTAL関数の基本構文は次のとおりです。
合計を求める場合は次のように入力します。
この数式では、F3からF10までの範囲を合計します。
そしてフィルターによって非表示になった行は自動的に除外されます。
フィルター条件を変更すると、集計結果も自動的に更新されます。
SUBTOTAL関数の「9」と「109」の違い
SUBTOTAL関数を調べると、「9」と「109」の2種類を見かけることがあります。
どちらも合計を求める指定ですが、対象となる行が異なります。
| 指定 | フィルター非表示 | 手動非表示 |
|---|---|---|
| 9 | 除外 | 含める |
| 109 | 除外 | 除外 |
例えば次の数式です。
こちらはフィルターで非表示になった行だけを除外します。
一方で、
は手動で非表示にした行も集計対象から除外します。
実務では109を使用するケースが増えています。
迷った場合は109を覚えておくとよいでしょう。
SUBTOTAL関数で利用できる主な集計方法
SUBTOTAL関数は合計だけではありません。
平均や件数なども求められます。
| 番号 | 内容 |
|---|---|
| 1 | AVERAGE（平均） |
| 2 | COUNT（数値の個数） |
| 3 | COUNTA（空白以外の個数） |
| 9 | SUM（合計） |
フィルター後の平均売上や件数を集計したい場合にも活用できます。
SUBTOTAL関数を使うメリット
フィルター結果だけを集計できる
最大のメリットは、表示されているデータだけを集計できることです。
営業データや売上管理表などで非常に役立ちます。
フィルター変更に自動対応する
条件を変更するたびに数式を書き直す必要がありません。
フィルターの状態に応じて自動で再計算されます。
集計表の作成が簡単になる
月別や支店別の集計を確認するときにも便利です。
ダッシュボードや管理表でもよく利用されています。
よくある質問
SUBTOTAL関数とSUM関数の違いは何ですか？
SUM関数は非表示のデータも集計しますが、SUBTOTAL関数はフィルター後に表示されているデータだけを集計できます。
9と109はどちらを使うべきですか？
手動で非表示にした行も除外したい場合は109がおすすめです。実務では109を使うケースが増えています。
平均や件数も計算できますか？
可能です。集計方法の番号を変更することで平均や件数なども求められます。
テーブル機能でも使えますか？
はい。Excelのテーブル機能と組み合わせても問題なく利用できます。
まとめ
Excelでフィルター後のデータだけを合計したい場合は、SUM関数ではなくSUBTOTAL関数を利用しましょう。
特に実務では次の数式を覚えておくと便利です。
フィルターで非表示になったデータだけでなく、手動で非表示にした行も除外して集計できます。
売上管理や顧客データの分析など、日常業務で役立つ関数なのでぜひ活用してみてください。
関連記事・広告
併せて読んでおくとさらに便利
EXCELの行や列を折りたたむ「グループ化」をマスターしよう
グループ化
行や列を折りたたむ「グループ化」の機能について解説。たたむ・展開するの切替えはワンタッチで簡単！この グループ化 で見た目もすっきり、そして機能的な作表に挑戦しましょう。
EXCELで1行おきに色をつける｜条件付き書式
条件付き書式・MOD関数・ROW関数
Excelでセルを１行おきに色づけする方法について解説します。条件付き書式とMOD関数・ROW関数をつかってデータの並べ替えに強い色付けを解説します。
エクセルで見やすい表の作り方｜テーブルの使い方と少しの小技
テーブル
エクセルで見やすい表の作り方について解説。見やすい色や罫線、そのほか小さな工夫で表は見違えるほど見やすい表に変わります。テーブルスタイルなど便利な機能についても説明します。
エクセルの先頭行を全ページ固定で印刷する方法
ページ設定・タイトル行
エクセルで件数の多いデータを扱っていると、１行あるいは複数行の「タイトル行」を固定して印刷したいときがあります。タイトル行の設定により解決する方法について解説！
エクセル「入力規則」完全マスター
入力規則
エクセルの入力規則をマスターしよう。結果による色分けや入力規則の解除方法、ユーザー設定を使用した数式による条件指定や入力規則をコピーする方法など完全網羅しました。
※その他、関連記事はこの記事の下の方にもあります。
