楽天リーベイツで楽天ポイント還元最大20%!今なら登録して初回購入で500ポイントもGET!

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

5 min 714 views
エクセル初心者

エクセル初心者

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

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

Dr.オフィス

Dr.オフィス

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

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

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

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

【エクセル】フィルターの結果の合計を求める簡単ステップ
  1. 数式バーに『=SUBTOTAL(』を入力
  2. 『fx(関数の挿入)』ボタンを選択
  3. 集計方法の引数を入力
    (合計値の場合は『9』を指定)
  4. 集計したいセル範囲を選択

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

Dr.オフィス

Dr.オフィス

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

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

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

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

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

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

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

SUM関数では意図した合計値とならない
SUM関数では意図した合計値とならない

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

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

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

SUBTOTAL関数

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

ポイント

SUBTOTAL関数とは

=SUBTOTAL(集計方法,範囲 1,[範囲 2],…)

意味:集計方法を指定して、さまざまな集計値を求める関数です。

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

集計方法1
(非表示も含める)
集計方法2
(非表示は含めない)
集計の種類(関数)
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

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

合計(SUM)を求める

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

  1. STEP

    SUBTOTAL関数を入力

    SUBTOTAL関数で合計を求める
    SUBTOTAL関数で合計を求める
    1. セルD14を選択
    2. 数式バーに『=SUBTOTAL(』を入力
    3. 『fx(関数の挿入)』ボタンを選択
  2. STEP

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

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

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

    集計値(合計)の確認

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

    データ抽出後の合計を計算できる
    データ抽出後の合計を計算できる

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

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

    フィルターを変更すると集計の範囲はすぐに反映される
    フィルターを変更すると集計の範囲はすぐに反映される

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

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

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

表の6行目から10行目までを手動で非表示
表の6行目から10行目までを手動で非表示

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

集計方法『9』と『109』の比較
集計方法『9』と『109』の比較

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

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

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

Dr.オフィス

Dr.オフィス

データの個数(COUNT)を求める

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

  1. STEP

    SUBTOTAL関数を入力

    SUBTOTAL関数でデータの個数を求める
    SUBTOTAL関数でデータの個数を求める
    1. セルB1を選択
    2. 数式バーに『=SUBTOTAL(』を入力
    3. 『fx(関数の挿入)』ボタンを選択
  2. STEP

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

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

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

    集計値(データの個数)の確認

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

    データ抽出後のデータの個数をカウントできる
    データ抽出後のデータの個数をカウントできる

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

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

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

  1. STEP

    SUBTOTAL関数を入力

    SUBTOTAL関数で空白ではないセルの個数を求める
    SUBTOTAL関数で空白ではないセルの個数を求める
    1. セルB1を選択
    2. 数式バーに『=SUBTOTAL(』を入力
    3. 『fx(関数の挿入)』ボタンを選択
  2. STEP

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

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

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

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

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

    データ抽出後の空白ではないセルをカウントできる
    データ抽出後の空白ではないセルをカウントできる

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

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

Dr.オフィス

Dr.オフィス

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

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

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

表と同じ行に関数を入力すると合計のセルが消えてしまう
表と同じ行に関数を入力すると合計のセルが消えてしまう

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

フィルターを使用する場合は表と同じ行には何も入力しない
フィルターを使用する場合は表と同じ行には何も入力しない

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

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

Q

エクセルのフィルターで、抽出した件数を表示する方法を教えてください。

A

抽出したセルを範囲選択すると、ステータスバー上で『合計』『平均』『データの個数』などを確認することができます。また、SUBTOTAL関数でも確認できます。詳しくは、『【エクセル】フィルターで抽出したデータを集計しよう!』で解説しています。

Q

エクセルのフィルターで、抽出したデータだけをカウントする方法を教えてください。

A

SUBTOTAL関数を使って、集計方法の引数を『2』と指定し、集計したいセル範囲を選択します。詳しくは、『データの個数(COUNT)を求める』で解説しています。

Q

エクセルのフィルターで抽出した、空白ではないセルを数える方法を教えてください。

A

SUBTOTAL関数を使って、集計方法の引数を『3』と指定し、集計したいセル範囲を選択します。詳しくは、『空白ではないセルの個数(COUNTA)を求める』を参考にしてみてください。

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

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

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

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

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

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

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

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

おさらい
  1. 数式バーに『=SUBTOTAL(』を入力
  2. 『fx(関数の挿入)』ボタンを選択
  3. 集計方法の引数を入力
    (合計値の場合は『9』を指定)
  4. 集計したいセル範囲を選択

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

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

カテゴリー:

40万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に40万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事