【エクセル】フィルターで抽出した結果の合計を求めよう！簡単に集計できる便利技

エクセル初心者

エクセルのフィルターで抽出した結果の合計を求める方法が知りたいな。データの件数をカウントしたり、平均を求めたり、いろいろな集計が簡単にできる方法があると便利なんだけど。。。

フィルターで抽出した結果の集計をするには、『SUBTOTAL関数』が便利だよ。これから詳しく解説するね。

Dr.オフィス

今回は、フィルターで抽出した結果の合計を求める方法について、解説します。

フィルターの結果を集計する場合、どのような方法を使っていますか。

ステータスバー上で『合計』『平均』『データの個数』を確認することもできますが、フィルターで抽出するたびに、対象のセルを繰り返し範囲選択するのは、少し面倒ですよね。

この記事を読むと、フィルターで抽出した対象のデータだけを集計できる方法について、理解することができますよ。

PCサポート歴10年以上の私が、フィルターで抽出した結果を簡単に集計する方法について、詳しく解説するよ。

Dr.オフィス

フィルターで抽出した結果を集計するには、SUBTOTAL関数を使うと便利です。

SUBTOTAL関数は、集計方法の引数を指定するだけで、合計の他にも平均やデータの件数なども簡単に求めることができます。

本記事は下記の動画でも解説しているので、ぜひ合わせてご覧ください。

エクセルのフィルター機能の基本的な使い方については、こちら≫【エクセル】フィルター機能の設定と解除方法！範囲を指定してデータ抽出！初心者必見で詳しく解説しています。

※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を使用しています。

【エクセル】フィルターで抽出したデータを集計しよう！

例えば、『商品分類』が『飲料水』のデータをフィルターで絞り込み、抽出された『数量』の合計をSUM関数で求めたとします。

フィルターで抽出された『数量』のセルD5、D11、D12の合計は『970』となるはずですが、SUM関数の結果は『2320』と表示されていますね。

これは、フィルターで抽出したデータ以外も含めて、計算されてしまうことが原因です。

そこで、フィルターで抽出した対象のデータだけを集計できる、SUBTOTAL関数を使います。

SUBTOTAL関数

SUBTOTAL関数は、合計だけではなく、平均や件数をカウントする場合にも使用できます。

指定できる集計方法について、下記にまとめました。

集計方法1：非表示にしている値を含める場合に指定します。
集計方法2：非表示にしている値を含めない場合に指定します。

合計（SUM）を求める

それでは、SUBTOTAL関数を使って『数量』の合計を求めてみましょう。

1. STEP

   SUBTOTAL関数を入力

   

   1. セルD14を選択
   2. 数式バーに『=SUBTOTAL(』を入力
   3. 『fx（関数の挿入）』ボタンを選択
2. STEP

   集計方法『9』を指定する

   『関数の引数』ダイアログボックスが表示されたら、下記を設定します。

   

   1. 集計方法の引数に『9』を入力
   2. 集計したいセル範囲を選択
      （ここではセルD2からセルD13を選択）
   3. [OK]を選択
3. STEP

   集計値（合計）の確認

   フィルターで『商品分類』が『飲料水』のデータだけを抽出してみましょう。

   

   フィルター前の合計は『3485』でしたが、フィルター後は『970』に変わりましたね。

   このように、フィルターで抽出された結果の合計を求めることができます。

   

   また、フィルターの条件を変更すると連動して対象の範囲が反映されるため、すぐに合計を求めることができます。

集計方法『9』と『109』の違い

ここで集計方法の指定を『9』にした場合と『109』に指定した場合を例にして、結果の違いを簡単に解説します。

上記のように、表の6行目から10行目までを手動で非表示にしたとします。

その表で、集計方法を『9』と指定した結果と、集計方法を『109』と指定した結果を比較すると、合計の値が『790』と『390』で異なることが確認できますね。

このように、手動で行を非表示にした値を含めて計算する場合は『9』と指定し、含めない場合は『109』と指定する、という使い分けになります。

フィルターをだけを使用している場合は、『9』でも『109』でも同じ結果になるよ。ポイントは、手動で行を非表示にしたときだね！

Dr.オフィス

データの個数（COUNT）を求める

それでは、SUBTOTAL関数を使って、データの個数をカウントしてみましょう。

1. STEP

   SUBTOTAL関数を入力

   

   1. セルB1を選択
   2. 数式バーに『=SUBTOTAL(』を入力
   3. 『fx（関数の挿入）』ボタンを選択
2. STEP

   集計方法『2』を指定する

   『関数の引数』ダイアログボックスが表示されたら、下記を設定します。

   

   1. 集計方法の引数に『2』を入力
   2. 集計したいセル範囲を選択
      （ここではセルD4からセルD15を選択）
   3. [OK]を選択
3. STEP

   集計値（データの個数）の確認

   フィルターで『月』が『1月』と『2月』のデータだけを抽出し、データの個数を求めます。

   

   フィルターで抽出された、データの個数『8』を求めることができましたね。

空白ではないセルの個数（COUNTA）を求める

それでは、SUBTOTAL関数を使って、空白ではないセルの個数をカウントしてみましょう。

1. STEP

   SUBTOTAL関数を入力

   

   1. セルB1を選択
   2. 数式バーに『=SUBTOTAL(』を入力
   3. 『fx（関数の挿入）』ボタンを選択
2. STEP

   集計方法『3』を指定する

   『関数の引数』ダイアログボックスが表示されたら、下記を設定しましょう。

   

   1. 集計方法の引数に『3』を入力
   2. 集計したいセル範囲を選択
      （ここではセルD4からセルD15を選択）
   3. [OK]を選択
3. STEP

   集計値（空白ではないセルの個数）の確認

   フィルターで『月』が『1月』と『3月』のデータだけを抽出し、空白ではないセルの個数を求めます。

   

   フィルターで抽出された、空白ではないセルの個数『4』を求めることができましたね。

フィルターで抽出した対象のデータだけを集計する場合は、ステータスバー上で確認するよりもSUBTOTAL関数が便利だね！

Dr.オフィス

また、フィルターで抽出したデータだけをコピーして貼り付ける場合は『可視セル』の選択がポイントになります。

『可視セル』については、こちら≫【エクセル】フィルター後のセルをコピーする方法！可視セルのみ貼り付けとは？で詳しく解説しているので、ぜひ参考にしてみてください。

関連

エクセルでフィルター後のセルをコピーする方法！可視セルのみ貼り付けとは？

集計した合計が消える場合の対処方法

上記のように、表と同じ行に『SUBTOTAL関数』を入力してしまうと、フィルターの結果次第では、セルが消えてしまいます。

そのため、フィルターで抽出した結果の集計をする場合は、表と同じ行には数式や関数を入力しないように注意しましょう。

【フィルターで抽出した結果の集計】に関するQ＆A

SUBTOTAL関数でフィルター後の集計がスムーズに！

今回は、フィルターで抽出した結果の合計を求める方法について、解説しました。

フィルターで抽出した結果を集計するには、SUBTOTAL関数を使うと便利でしたね。

合計値の他にも、平均やデータの個数など、集計方法の引数を指定するだけで簡単に求めることができます。

ステータスバーでも合計値や平均などを確認することができますが、フィルターの条件を繰り返し変更する場合には、SUBTOTAL関数がおすすめです。

また、フィルターによってSUBTOTAL関数を入力したセルが非表示になり、集計の確認することができない場合があります。

SUBTOTAL関数を入力するセルの場所は、フィルターをかける表の同じ行には入力しないようにしましょう。

最後に、エクセルでフィルターの結果の合計を求める簡単ステップについて、おさらいします。

フィルターでデータ抽出後に、さまざまな集計値求める場合は、ぜひSUBTOTAL関数を参考にしてみてください。

エクセルのフィルター機能の基本的な使い方については、こちら≫【エクセル】フィルター機能の設定と解除方法！範囲を指定してデータ抽出！初心者必見で詳しく解説しています。

関連

【エクセル】フィルター機能の設定と解除方法！範囲を指定してデータ抽出！初心者必見