エクエルでフィルターをかけて合計を表示する場合や、手動で非表示にした行などを除外してカウントする方法を紹介します。
意外と、表示されている数値のみを合計・カウントする方法を知らない人が多いので、ぜひとも覚えておいて欲しいです。
非表示は除外する
表示されている数値のみ合計・カウントする方法
表示されている数値の合計・カウントを行うには、SUBTOTAL関数やAGGREGATE関数を使用します。
SUBTOTAL関数を使用する
表示されている数値の合計・カウントを行うだけであれば、SUBTOTAL関数で簡単に求めることができます。
SUBTOTAL関数は、デフォルトでフィルター後の見えている数値のみを計算するようになっていますが、
手動で非表示にした行を除外して合計・カウントするには、集計方法を100以降の数値（合計の場合は109）で指定する必要があります。
SUBTOTAL関数の使い方と集計方法の指定
そのため、
- =SUBTOTAL(109, 範囲)：合計
- =SUBTOTAL(102, 範囲)：カウント
と、入力規則に従いって集計方法に「109」の数値の合計を指定し、集計範囲を指定すればOKです。（カウントの場合は102）
AGGREGATE関数を使用する
AGGREGATE関数でも求めることができますが、オプションの指定が必要になり、ほんの少しだけややこしいです。
AGGREGATE関数で非表示にした数値を除外して計算する場合は、オプションに1、3、5、7のいずれかの指定が必要です。
実際には、
- =AGGREGATE(9,3,集計範囲)：合計
- =AGGREGATE(2,3,集計範囲)：カウント
というような入力になります。
オプションの詳しい内容は、AGGREGATE関数の使い方の記事をご覧ください。
AGGREGATE関数の使い方と集計方法
非表示行を除外するときの注意点
SUBTOTAL関数とAGGREGATE関数を使用しているので、これらの関数ができないことはできないということになります。
列が非表示の除外はできない
SUBTOTAL関数とAGGREGATE関数共に、横方向の合計で列が非表示になっていても除外することができません。
フォーマットを変更でるのであれば、縦向きの集計となるように作っておきましょう。
まとめ
SUBTOTAL関数やAGGREGATE関数を用いれば、非表示行を除外して計算できるということを覚えておきましょう。
それぞれ、便利な機能なので使い方を知っておいて損はありません。
SUBTOTAL関数の使い方と集計方法の指定
AGGREGATE関数の使い方と集計方法
