エクセルでフィルターで絞り込んだ表を合計する方法!SUBTOTAL関数でフィルターと連動させて合計値を表示しよう

定時でカエル
フィルターで絞り込んだ表の数値を合計すると、計算結果があわないのですが。。。
オフィスうさぎ
もしかしてSUM関数を使ってない?フィルターで絞り込んだ表を計算したいときは、SUBTOTAL関数を使うのが正解だよ!

エクセルで大量のデータから特定のデータを探すときに便利なのが、様々な条件でデータを絞り込むことができる「フィルター」という機能です。

色んなシーンで活用される便利な機能なので、フィルターで絞り込んだ表のデータを合計したいときがあるでしょう。

合計を計算するにはSUM関数を使うのが一般的ですが、フィルターで絞った表を合計するときはSUBTOTAL関数を使うなど少し手順が異なります。

そこでこの記事では、エクセルのフィルターと連動して合計値を計算する方法について、初心者にも分かりやすく解説していきたいと思います。

関連:エクセルのフィルターの使い方を徹底解説

関連記事

定時でカエルExcelのフィルターの使い方を教えてください。オフィスうさぎ了解!フィルターは色んな用途に使える便利な機能なので、基本的な使い方から応用技まで幅広く解説していくね。エクセルはデー[…]

フィルターが設定された表ではSUM関数は使えない

下記のようなフィルターが設定された表から、データを絞った状態で在庫数の合計値を計算したいとします。

フィルターが設定された表ではSUM関数は使えない

通常であれば合計を計算する時はSUM関数を使いますが、フィルターで絞り込んだ表をSUM関数で計算すると異なる結果が表示されてしまいます。

フィルターが設定された表ではSUM関数は使えない

フィルターで絞り込むと対象ではないデータは非表示になりますが、SUM関数は非表示のデータも含めて計算してしまうためです。

エクセルでフィルターと連動して合計を計算する方法

ここからはエクセルでフィルターと連動して合計値を計算する方法を解説していきます。

前述したようにフィルターが設定された表では、合計を計算するときにSUM関数を使うことはできません。

フィルターで絞り込んだ表で合計を計算するには、SUM関数ではなく「SUBTOTAL」という関数を使います。

SUBTOTAL関数とは

SUBTOTAL関数の書式は以下の通りです。

=SUBTOTAL(集計方法,計算範囲)

第1引数では集計方法を指定します。1~11の数値のいずれかを入力することで、指定した方法で集計することができます。

入力する値集計方法
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP

今回のケースでは合計値を計算したいので、第1引数には9を入力してSUMを指示しましょう。

では、詳しい手順を解説していきます。

SUBTOTALを使ってフィルターと連動して合計値を計算する

既にフィルターでデータが絞り込まれている場合、まず最初に表内のセルを選択した状態で、データタブにあるクリアをクリックしてください。

エクセルでフィルターでの絞り込みを戻す方法

するとフィルターにかけていた条件をリセットすることができました。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

続いて合計値を表示したいセルを選択して、=と入力してください。

SUBTOTALを使ってフィルターと連動して合計値を計算する

次にSUBTOTAL(と入力して、=SUBTOTAL(という状態にします。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

第1引数には合計を指示する9を入力し、=SUBTOTAL(9,という状態にします。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

2引数には合計値を計算したいセル範囲を選択して、=SUBTOTAL(9,D3:D16)という状態にします。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

最後キーボードのEnterをクリックすれば、SUBTOTAL関数で合計値を計算できました。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

試しにフィルターを使ってデータを絞り込むと、フィルターと連動して合計値を自動で計算することができました。

エクセルでSUBTOTALを使ってフィルターと連動して合計値を計算する

SUBTOTALを使ってフィルターと連動する連番をふる

上記でご紹介したSUBTOTAL関数を使うことで、フィルターと連動して自動で連番を振ることも可能です。

ここからは、SUBTOTAL関数を使ってフィルターと連動する連番の設定方法を解説します。

まずは連番を振りたいセルを選択して、先頭のセルに=と入力してください。

SUBTOTALを使ってフィルターと連動する連番をふる

次にSUBTOTAL(と入力して、=SUBTOTAL(という状態にします。

SUBTOTALを使ってフィルターと連動する連番をふる

第1引数にはデータの個数を計算したいので、COUNTAを指示する3を入力して=SUBTOTAL(3,という状態にします。

SUBTOTALを使ってフィルターと連動する連番をふる

第2引数にはどの項目でもいいので、表頭とその1つ下のセルを選択します。

SUBTOTALを使ってフィルターと連動する連番をふる

表頭部分のみをつけて絶対参照に変更します。

SUBTOTALを使ってフィルターと連動する連番をふる

)を入力して、=SUBTOTAL(3,$E$2:E3)という状態にします。

SUBTOTALを使ってフィルターと連動する連番をふる

最後に-1を入力して、=SUBTOTAL(3,$E$2:E3)-1という状態にします。

SUBTOTALを使ってフィルターと連動する連番をふる

以上の手順で、先頭のセルに連番1を入力することができました。

SUBTOTALを使ってフィルターと連動する連番をふる

連番1が入力されたセルの右下にあるを、左クリックしながら他のセルにスライドしていきます。

エクセルでSUBTOTALを使ってフィルターと連動する連番をふる

するとオートフィル機能によって、他のセルにも連番を振ることができました。

エクセルでSUBTOTALを使ってフィルターと連動する連番をふる

試しにフィルターで絞り込みかけてみると、フィルターと連動して連番を自動で切り替えて表示することができました。

エクセルでSUBTOTALを使ってフィルターと連動する連番をふる