エクセルでフィルターをかけた状態の値のみを合計する方法

歩君

エクセルでフィルターをかけた状態の値のみを合計することはできますか？SUM関数を使って合計してみたんですけど、答えが間違っていたんです。

ライクさん

オートフィルターで抽出したデータは、SUM関数で合計することができないんだ。フィルターをかけた状態の値のみを正しく合計する方法を教えるね。

エクセルでフィルターをかける方法は、「オートフィルター」を使う方法とFILTER関数を使う方法の2通りのやり方があります。

フィルターで抽出したデータのみを合計したい場合は、フィルターのかけ方に合わせた正しい手順で計算しなければ誤った答えが表示されてしまうので注意しましょう。

本記事では、オートフィルターとFILTER関数で抽出した値を正しく合計する方法を紹介していますので、ぜひ参考にしてみてください。

フィルターで抽出した値を合計する方法以外にも、エクセルのフィルターに関する関連記事がありますので、こちらもぜひご覧ください。

エクセルのオートフィルターで抽出したデータのみを合計したいときはSUBTOTAL関数を使う

エクセルのオートフィルターを使って抽出したデータのみの合計を計算したい場合は、SUBTOTAL関数を使います。

歩君

SUBTOTAL関数はどんなときに使える関数なんですか？

ライクさん

SUBTOTAL関数は、集計方法と計算したい範囲を指定して計算したいときに便利な関数なんだ。

合計を計算したい場合、SUM関数を使うことが一般的です。

しかし、SUM関数は非表示セルの値を取り除いた計算ができないため、フィルターをかけた状態の値の計算はできません。

フィルターをかけたデータの値のみを計算したいときは、SUBTOTAL関数を使いましょう。

SUBTOTAL関数は、非表示セルを除いた合計値を計算できるのはもちろんですが、引数を変えると「最大値」、「最小値」、「平均値」などの他の計算を行うことも可能なので、使い方を覚えておくと便利です。

次の項目で、SUBTOTAL関数とSUM関数の違いを詳しく解説します。

SUBTOTAL関数とSUM関数の違い

フィルターがかかっていないセルの合計値を計算したい場合は、SUM関数を使うのが一般的です。

しかし、SUM関数で合計値を求めようとすると、フィルターをかけて非表示になっているセルの値も含んだ結果が表示されてしまいます。

つまり、フィルターで見えなくなっているセルの値もすべて合計されてしまうということです。

歩君

だから、間違った計算結果が表示されてしまったんですね。

ライクさん

フィルターをかけたあとにセルの値の合計をしたいときは、SUM関数は使えないから注意しよう！

SUM関数とSUBTOTAL関数の主な違いは、以下の通りです。

SUM関数とSUBTOTAL関数は、どちらも合計を計算できますが、フィルターをかけたまま計算したい場合はSUBTOTAL関数を使いましょう。

SUBTOTAL関数を使ってフィルターをかけたまま合計する手順

SUBTOTAL関数の関数構文は、「=SUBTOTAL(集計方法,参照)」です。

引数の集計方法は、計算内容に応じて特定の数字が決まっています。

SUBTOTAL関数の代表的な集計方法には、以下のようなものがあります。

合計を計算したい場合は、「9」または「109」を選択してください。

ライクさん

集計方法の数字の「1桁」と「3桁」の違いは、次の項目で詳しく解説するね。

「参照」は、合計したい数字が入力されているセル範囲を指定します。

例えば、営業1課の実績結果の小計をSUBTOTAL関数で計算する場合、参照の引数はオレンジの枠の範囲になります。

このデータの商品名の「みかん」にフィルターをかけ、SUBTOTAL関数を使って合計を計算してみましょう。

集計方法には、合計の引数の「109」を使用しています。

STEP

商品名のフィルターボタンを使って「みかん」でフィルターをかける

• 「商品名」のフィルターボタンを押す
• すべてのチェックを外し、「みかん」のチェックボックスだけにチェックを入れる
• 「OK」をクリック

STEP

SUBTOTAL関数を挿入

• 合計を表示したいセルを選択して「=SU」と入力
• 表示された関数の候補から「SUBTOTAL」をダブルクリックする

STEP

集計方法を選択

• スクロールバーを下方向にスクロールする
• 「109-SUM」をダブルクリック

ライクさん

データをグループ化していないときは、「9-SUM」を選択してもOKだよ。

STEP

合計範囲を選択

• 「,(カンマ)」を入力
• 合計したい範囲を選択
• 「)」を入力してEnterを押す

STEP

フィルターをかけた「みかん」の合計を計算できた

フィルターをかけた「みかん」の金額を合計することができました。

SUBTOTAL関数の集計方法の「9」と「109」の違い

歩君

SUBTOTALで合計を出すとき、集計方法が「9-SUM」と「109-SUM」があるんですけど、この2つは何が違うんですか？

ライクさん

表をグループ化していた場合、「9-SUM」と「109-SUM」では答えが変わってしまうことがあるんだ。

エクセルのグループ化とは、関連性のあるデータをまとめて表示したり、折りたたんだりできる機能のことです。

表示されたボタンを使って、必要なデータだけを表示させたり、不要な部分を折りたたんだりすることができるので、データを見やすくしたいときに使うと便利な機能です。

データがグループ化されていた場合でも、フィルターをかけた値だけを合計したいときは「9」を使っても「109」を使っても同じ結果が表示されます。

ただし、グループ化されていたデータのフィルターをかけずにデータが折りたたまれていた場合は、引数の違いによって計算結果が変わってしまうことがあるので注意してください。

実際のデータを使って、この2つの違いを確認してみましょう。

これは、行方向にグループ化したデータです。

行方向にグループ化すると画面の左側にアウトラインの線と「+」と「－」のボタンが表示されます。

ライクさん

このデータは、すべてのデータが展開されているから「+」のボタンは表示されていないよ。

データの「展開」または「折りたたみ」は上記の赤枠のボタンを使って切り替えます。

これは、SUBTOTAL関数の集計方法に「9」と「109」を入力し、1月の3店舗の合計金額の計算結果を比較した画像です。

データがすべて展開されているときは、どちらも同じ結果が表示されています。

今度は、A店にフィルターをかけて計算結果を比較してみましょう。

フィルターをかけた場合も、どちらも同じ結果が表示されています。

次は、グループ化のボタンを使ってA店のデータを非表示にしてみましょう。

グループ化のボタンを使ってA店を非表示にすると、集計方法の「9」のほうは、A店の金額も含めた合計が表示されていますが、「109」のほうは、B店とC店の合計が表示されています。

通常は、1桁の「9」を選択すれば特に問題はありません。

ただし、上記のようにデータがグループ化されていた場合は注意してください。

フィルターをかけた値を合計から除く

歩君

すべてのデータの合計からフィルターをかけた値を引いた金額を計算したい場合は、どうしたらいいですか？

ライクさん

SUM関数とSUBTOTAL関数を使えば、簡単に計算できるよ！

すべてのデータの合計からフィルターをかけた合計を引いた金額を計算したい場合は、SUM関数とSUBTOTAL関数を使います。

「すべてのデータの合計」と「フィルターをかけた値の合計」を計算し、最後に、「すべてのデータの合計」から「フィルターをかけた値の合計」を引くと答えを出すことができます。

STEP

データを準備する

フィルターを挿入した表とそれぞれの計算結果を入力するためのセルを準備してください。

STEP

SUM関数を使ってすべてのデータの合計を計算する

「Fの12」に「=SUM(C3:C9)」と入力して、すべての合計を計算します。

=SUM(C3:C9)

STEP

必要なデータにフィルターをかける

「商品名」のフィルターを使って、「みかん」のデータを抽出しましょう。

STEP

SUBTOTAL関数を使ってフィルターで抽出したデータの合計を計算する

1. 「Fの14」をクリック
2. 「=SUBTOTAL(9,C3:C9)」と入力
3. Enterで確定

=SUBTOTAL(9,C3:C9)

STEP

すべてのデータの合計からフィルターをかけた値の合計を引く

1. 「Fの17」をクリック
2. 「=F12-F14」を入力
3. Enterで確定

=F12-F14

STEP

すべての計算結果が表示された

すべての合計からフィルターをかけた値を除いた合計を計算することができました。

参考

SUM関数とSUBTOTAL関数の意味がわかれば、1つの式にまとめて計算をすることも可能です。

すべての合計を算出するSUM関数から、フィルターをかけた合計値を計算するSUBTOTAL関数を引くと同じ計算をすることができます。

=SUM(C3:C9)-SUBTOTAL(9,C3:C9)

どちらも結果は同じになるので、使いやすいほうを試してみてください。

FILTER関数で抽出したデータはSUM関数で合計できる

FILTER関数を使って抽出したデータは、SUM関数で合計することができます。

これは、左の青枠の表からFILTER関数を使って「Fの2」の「みかん」の文字を使って、データを抽出したあとの画像です。

このデータを使ってFILTER関数で「みかん」のデータを抽出し、単価の合計を計算してみましょう。

STEP

FILTER関数で必要なデータを抽出

「Fの2」に入力した「みかん」の文字を使い、FILTER関数を挿入してデータを抽出します。

STEP

SUM関数を挿入

「Gの7」のセルをクリックして「=SUM(G3:G5)」と入力し、Enterで確定してください。

=SUM(G3:G5)

STEP

フィルターをかけた値の合計を計算することができた

FILTER関数で抽出した値を合計することができました。

エクセルでフィルターをかけた値の合計が合わないときは「合計範囲」または「集計方法」を確認する

歩君

フィルターをかけた値の合計が合わないんですけど、どうしたらいいですか？

ライクさん

計算結果が合わないときは、「合計範囲」または「集計方法」に間違いがないか確認してみよう！

フィルターをかけた値の合計が間違っている場合、以下の3つが原因になっている可能性が高いです。

それぞれの内容について、詳しく解説します。

合計範囲が間違っていた場合

SUM関数またはSUBTOTAL関数は、範囲を指定して合計を計算しますが、関数に挿入する引数の範囲が間違っていた場合、間違った計算結果が表示されてしまいます。

SUM関数で計算した合計が間違っていた場合は、ドラッグするときに選択範囲を間違えてしまっている可能性があります。

関数を挿入したセルをダブルクリックすると、上記のように選択した範囲が枠線で囲まれるので、選択した範囲に誤りがないか確認してみましょう。

SUBTOTAL関数の合計範囲は、見出しを除いた範囲を選択します。

範囲を選択する際に、見出しを入れないように注意してください。

また、フィルターをかけると非表示になってしまう行がありますが、引数に入力する合計範囲は変わらないので間違えないように注意しましょう。

データがグループ化されている

データがグループ化されていて、グループ化のボタンで非表示になっているデータがあった場合、フィルターを解除すると数字が変わってしまうことがあります。

グループ化されているデータを使って計算している場合は、すべてのデータが表示されているか確認してみましょう。

ライクさん

データのグループ化による計算結果の違いは、SUBTOTAL関数の集計方法の「9」と「109」の違いで詳しく解説しているから、参考にしてみてね。

使用している関数が間違っている

オートフィルターを使ってフィルターをかけたまま合計を計算したい場合は、SUM関数は使えません。

フィルターをかけた状態の値だけを合計したい場合は、SUBTOTAL関数で計算しましょう。

SUBTOTAL関数の合計の集計方法は、「9」または「109」です。

集計方法の数字を間違えると、合計以外の関数で計算されてしまうので注意してください。

ライクさん

SUBTOTAL関数を使ってフィルターをかけたまま合計する手順の項目で、集計方法の引数について詳しく解説しているよ。

エクセルでフィルターをかけた値のみを合計するに関するQ＆A

エクセルで条件に一致したセルの値だけ合計する関数はありますか？

フィルターをかけて条件に一致するセルを抽出したあとに、SUBTOTAL関数を使って合計を計算します。

SUM関数も合計が計算できる関数ですが、SUM関数の場合は非表示になっているセルの値も合計してしまうため、フィルターをかけたデータの計算はできません。

SUBTOTAL関数の集計方式の「9」または「109」を使って合計を計算すると、非表示セルを除いた合計を計算することが可能です。

詳しい操作方法は、SUBTOTAL関数を使ってフィルターをかけたまま合計する手順で解説しています。

エクセルでフィルターをかけた値だけの合計が表示されないのはなぜですか？

フィルターをかけた値だけをうまく合計できない場合は、使用している関数が間違っているか、または指定した計算範囲が間違っている可能性があります。

SUM関数を使うと、非表示セルも含めた合計が計算されてしまうので注意しましょう。

SUBTOTAL関数を使用しているにもかかわらず合計が計算できない場合は、計算範囲が間違っている可能性があります。SUBTOTAL関数を挿入したセルをダブルクリックして、計算範囲に誤りがないか確認してみてください。

作成したデータに合う手順で抽出したデータの合計を計算しよう！

フィルターをかけた値の合計を計算したい場合、条件と一致するデータをどのように抽出しているかによって正しい計算方法が違います。

FILTER関数を使用している場合はSUM関数で計算できますが、フィルターボタンを使用している場合は、SUBTOTAL関数を使って計算しましょう。

また、データがグループ化されており、グループ化のボタンで非表示になっているデータがあった場合は、計算結果が変わってしまうことがあるので注意してください。

最後に、フィルターボタンで抽出したデータの値のみを合計する方法をおさらいしておきましょう。

SUBTOTAL関数は、非表示セルを除いた計算ができる便利な関数です。

特に、Excel2021以前のバージョンのエクセルを使用している場合は、SUBTOTAL関数の使い方をマスターしておくと便利です。

Office365またはExcel2021以降のバージョンのエクセルを使用している場合は、FILTER関数で条件に合うデータを抽出するとSUM関数で簡単に合計ができるのでぜひ活用してみてください。

フィルターで抽出した値を合計する手順以外にもエクセルにフィルターに関連する記事があります。

こちらの記事もぜひご覧ください。