こんにちは、HARUです！

Excelで集約したデータをオートフィルターなどで絞り込んだとき、今見えてるセルの値だけを合計したりカウントしたりしたいことがよくあります。

下図は会員ごとの利用金額をB13セルのSUM関数で合算している表です。

試しにフィルターでSSクラスの会員だけに絞り込んでみても、SUM関数の結果は変わりません。

様々な切り口からデータ分析をしたいのに、情報を絞りこむたびに計算対象のセル（範囲）を変更するのは大変ですよね。

そこでこの記事では、非表示となっている行を含まずにデータを集計するテクニックをご紹介します。

SUM関数やCOUNT関数の次のステップとして、確実に身に付けておきましょう！

非表示行を除いて集計する

非表示行を除いてデータを集計するには、SUBTOTAL関数を使います。
前述のSUM関数との違いがわかるように、すぐ下に入力します。

SUBTOTAL関数

①SUBTOTAL関数を挿入する。
②第１引数「集計方法」から「9-SUM」を選択する。

③第２引数「参照１」に集計対象のデータ範囲を参照する。

この段階では、SUM関数と同じ結果が返されます。

SUBTOTAL関数は、第１引数「集計方法」で選択できる様々な形式で集計ができる関数です。
SUM（合計）以外にも、AVERAGE（平均）やCOUNTA（データの個数を数ええる）などの機能もあります。

では、それぞれの関数を使う場合とSUBTOTAL関数を用いた場合の動作の違いを見ていきます。

フィルターによる非表示行の処理

※表の見出しで[Ctrl]+[Shift]+[L]を押すと、各セルの右下に下向き三角マークが表示されます。このボタンをフィルターボタンといいます。
これを用いた「オートフィルター」という機能によって、ワークシート上に表示する情報を絞り込むことができます。

フィルターでSSクラスの会員だけに絞り込むと、SUBTOTAL関数の結果が更新されます。

SUBTOTAL関数はこのように、フィルターによる非表示行を除いて集計してくれるのです。

参考までに、データの個数を数えるCOUNTA関数と集計結果を比べたのがこちらです。

SUBTOTAL関数は現在表示されているデータの個数のみを数えています。

手動による非表示行の処理

さて、SUBTOTAL関数の第１引数「集計方法」には、１～１１の種類と１０１～１１１の種類があります。

↓SUBTOTAL関数で実行できる集計の種類↓

１～１１と１０１～１１１とでそれぞれの集計形式は同じですが、どのような手順で行が非表示となっているかで結果が変わります。

比較しやすいように、SUM関数、SUBTOTAL関数(9-SUM)、SUBTOTAL関数(109-SUM)を並べて入力します。

この段階では、どれも結果は同じです。

フィルターでSSクラスの会員だけに絞り込むと、２つのSUBTOTAL関数の結果が更新されます。

ここで、フィルターを解除し、SSクラス以外の会員の行をグループ化します。

グループ化した行を非表示にします。
すると、SUBTOTAL関数(109-SUM)の結果だけが更新されました。

このように、フィルターで絞り込んだ場合の非表示行はいずれの集計方法を選択しても無視されますが、手動で非表示にした行を無視できるのはSUBTOTAL関数の100番台の集計方法だけなのです。

参考までに、データの個数を数えるCOUNTA関数と集計結果を比べたのがこちらです。

SUBTOTAL関数(103-COUNTA)だけが、手動で非表示にした行を集計対象から除外しています。

小計と総合計を正しく求める

SUBTOTAL関数のsubtotalは「小計」を意味します。

たとえば各支店の販売実績をブロックごとに小計し、最終行で総合計を求めた表があったとします。

B列とC列の支店別実績は同じですが、小計と総合計をそれぞれSUM関数とSUBTOTAL関数で求めています。

総合計を求めるセルでは、支店別の実績とブロックごとの小計を含めて一括でセル範囲参照しています。

このような参照をしてしまうと支店別実績とブロックごとの小計が重複しますので、誤った数値が返ります。

ただしSUBTOTAL関数なら、参照元にSUBTOTAL関数で集計（今回は小計）したセルが混ざっている場合、そのセルを集計対象から除外してくれます。

営業拠点が追加されたり各ブロックが管轄する支店の改廃が発生したりした場合でも、SUBTOTAL関数なら総合計の参照元を１つずつ手直しする必要がなく対象範囲を一括選択するだけなので、非常にラクちんです！

いかがでしたか？

今回は、非表示となっている行を含まずにデータを集計してくれるSUBTOTAL関数をご紹介しました。
最後に解説した小計と総合計を求めるシーンでも、積極的に活用していきましょう。

ちなみに、表にテーブルを適用したときに集計行を追加すると、自動的にSUBTOTAL関数が挿入されます。

フィルターによる絞り込みがなされる前提で、非表示行を無視して集計できるSUBTOTAL関数が採用されているということですね！

↓テーブルの活用法はこちら↓

↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。

↓↓Excel操作をとにかく高速化したい方へ↓↓