【Excel】SUBTOTAL関数を使ってフィルターで絞り込んだデータのみ集計する方法

2025年版実務で役立つExcel新機能・新関数

2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。

コンサル流実務で役立つExcelテクニック

コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ?どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。


Excelでは、SUM関数やAVERAGE関数で集計を行うことができますが、参照するデータをフィルターで絞り込んでしまうと正しい計算ができなくなります。そのような場合、SUBTOTAL関数を使うことで絞り込んだデータのみを対象として計算することができます。

SUBTOTAL関数とは

SUBTOTAL関数とは、指定した範囲で表示されているセルだけを対象に集計する関数です。集計方法は数字で指定することができます。

関数役割
SUBTOTAL(集計方法,参照1,[参照2]…)参照した範囲の表示されているセルのみを指定した集計方法で計算する。

集計方法は以下の通り指定することができます。

集計方法計算
「1」または「101」平均を計算
「2」または「102」数値のセルを数える
「3」または「103」データのあるセルを数える
「4」または「104」最大値を計算する
「5」または「105」最小値を計算する
「9」または「109」合計を計算する

SUBTOTAL関数を使ってフィルターで絞り込んだデータのみ集計する方法

以下のような営業成績表で、フィルターで絞り込んだデータのみを合計するようにしたいと思います。

合計を表示するセルG3に以下のように数式を入力します。

=SUBTOTAL(9,D4:D23)

数式役割
SUBTOTAL(9参照した範囲の表示されているセルの合計(指定方法「9」)を計算する。
D4:D23参照する範囲はD4~D23の「営業成績」の列。

そうすると以下のように営業成績の合計「170」が表示されます。現時点ではフィルターで絞り込んでいないので、すべてのデータが集計されています。

次にこのデータをフィルターで「営業一課」だけに絞り込んでみます。

そうすると以下のように「営業一課」だけの合計「52」になります。

SUBTOTAL関数を使わないと

仮にSUBTOTAL関数を使わないで、SUM関数で合計すると以下のようにフィルターで絞り込んでも合計値は変更されません。

以下はSUM関数を用いて合計した結果で、「170」となります。

フィルターで「営業一課」に絞り込んでも合計値は「170」のままです。

これはフィルターで絞り込んだデータは表示はされていないものの、隠れたまま存在しているからです。

2025年版実務で役立つExcel新機能・新関数

2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。

コンサル流実務で役立つExcelテクニック

コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ?どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。


よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

Junyaと申します。本ブログではExcelなどビジネススキルを発信しています。
Youtubeで動画投稿もしていますので、是非フォローお願いします。

コメント

コメントする