エクセルで大量のデータから特定のデータを探すときに便利なのが、様々な条件でデータを絞り込むことができる「フィルター」という機能です。
色んなシーンで活用される便利な機能なので、フィルターで絞り込んだ表のデータを合計したいときがあるでしょう。
合計を計算するにはSUM関数を使うのが一般的ですが、フィルターで絞った表を合計するときはSUBTOTAL関数を使うなど少し手順が異なります。
そこでこの記事では、エクセルのフィルターと連動して合計値を計算する方法について、初心者にも分かりやすく解説していきたいと思います。
定時でカエルExcelのフィルターの使い方を教えてください。オフィスうさぎ了解!フィルターは色んな用途に使える便利な機能なので、基本的な使い方から応用技まで幅広く解説していくね。エクセルはデー[…]
フィルターが設定された表ではSUM関数は使えない
下記のようなフィルターが設定された表から、データを絞った状態で在庫数の合計値を計算したいとします。
通常であれば合計を計算する時はSUM関数を使いますが、フィルターで絞り込んだ表をSUM関数で計算すると異なる結果が表示されてしまいます。
フィルターで絞り込むと対象ではないデータは非表示になりますが、SUM関数は非表示のデータも含めて計算してしまうためです。
エクセルでフィルターと連動して合計を計算する方法
ここからはエクセルでフィルターと連動して合計値を計算する方法を解説していきます。
前述したようにフィルターが設定された表では、合計を計算するときにSUM関数を使うことはできません。
フィルターで絞り込んだ表で合計を計算するには、SUM関数ではなく「SUBTOTAL」という関数を使います。
SUBTOTAL関数とは
SUBTOTAL関数の書式は以下の通りです。
第1引数では集計方法を指定します。1~11の数値のいずれかを入力することで、指定した方法で集計することができます。
入力する値 | 集計方法 |
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN |
6 | PRODUCT |
7 | STDEV |
8 | STDEVP |
9 | SUM |
10 | VAR |
11 | VARP |
今回のケースでは合計値を計算したいので、第1引数には9を入力してSUMを指示しましょう。
では、詳しい手順を解説していきます。
SUBTOTALを使ってフィルターと連動して合計値を計算する
既にフィルターでデータが絞り込まれている場合、まず最初に表内のセルを選択した状態で、データタブにあるクリアをクリックしてください。
するとフィルターにかけていた条件をリセットすることができました。
続いて合計値を表示したいセルを選択して、=と入力してください。
次にSUBTOTAL(と入力して、=SUBTOTAL(という状態にします。
第1引数には合計を指示する9を入力し、=SUBTOTAL(9,という状態にします。
2引数には合計値を計算したいセル範囲を選択して、=SUBTOTAL(9,D3:D16)という状態にします。
最後キーボードのEnterをクリックすれば、SUBTOTAL関数で合計値を計算できました。
試しにフィルターを使ってデータを絞り込むと、フィルターと連動して合計値を自動で計算することができました。
SUBTOTALを使ってフィルターと連動する連番をふる
上記でご紹介したSUBTOTAL関数を使うことで、フィルターと連動して自動で連番を振ることも可能です。
ここからは、SUBTOTAL関数を使ってフィルターと連動する連番の設定方法を解説します。
まずは連番を振りたいセルを選択して、先頭のセルに=と入力してください。
次にSUBTOTAL(と入力して、=SUBTOTAL(という状態にします。
第1引数にはデータの個数を計算したいので、COUNTAを指示する3を入力して=SUBTOTAL(3,という状態にします。
第2引数にはどの項目でもいいので、表頭とその1つ下のセルを選択します。
表頭部分のみ$をつけて絶対参照に変更します。
)を入力して、=SUBTOTAL(3,$E$2:E3)という状態にします。
最後に-1を入力して、=SUBTOTAL(3,$E$2:E3)-1という状態にします。
以上の手順で、先頭のセルに連番1を入力することができました。
連番1が入力されたセルの右下にある点を、左クリックしながら他のセルにスライドしていきます。
するとオートフィル機能によって、他のセルにも連番を振ることができました。
試しにフィルターで絞り込みかけてみると、フィルターと連動して連番を自動で切り替えて表示することができました。