Excel。データの行を非表示にしても合計値が変わらないので、合致するようにしたい。
＜SUBTOTAL関数＞
Excelの経験値が少ない方向けの講座というものありまして、
そこで次のようなご質問がありまして、なるほど、それは確かに困るよね。
という実務ならではのご質問がありましたので、今回はそれをご紹介します。
データ量は違うのですが、下記のような表があります。
10行目に合計行がありまして、SUM関数の数式が設定されています。
C10には、=SUM(C4:C9) というようにです。
それで、上野店と品川店を非表示にしてみます。
非表示をしてみると、合計値は変わっていませんよね。
Excelの経験値が多い方なら当たり前だろうってことになりますが、
Excelの経験値の少ない方ですと、
この合計値も連動して変わると思っている方も多いようでして、
今回のご質問は、今回のように行を非表示にしても、
合計値が連動して変わってほしいというものなのです。
確かに、テーブルとかを使えばいいのですが、
今回は、テーブルにしないで対応する方法をご紹介したいので、登場するテクニック。
SUBTOTAL関数
を使うと希望通りに対応できるようになります。ではC10をクリックして、SUBTOTAL関数を作っていきますので、
SUBTOTAL関数ダイアログボックスを表示しましょう。
集計方法には、9
参照1には、C4:C9
と入力して、OKボタンをクリックしましょう。
SUM関数と同じように合計値が算出されましたね。
ところで、SUBTOTAL関数の集計方法が9とはいったいどういう事なのか？説明しましょう。
集計方法101にはAVERAGE関数が対応されていて、平均を算出します。
集計方法102にはCOUNT関数が対応されていて、数値の件数を算出します
集計方法103にはCOUNTA関数が対応されていて、データの件数を算出します。
集計方法104にはMAX関数が対応されていて、最大値を算出します
集計方法105にはMIN関数が対応されていて、最小値を算出します
集計方法106にはPRODUCT関数が対応されていて、積を算出します。
集計方法107にはSTDEV関数が対応されていて、標本標準偏差を算出します
集計方法108にはSTDEVP関数が対応されていて、標準偏差を算出します。
そして
集計方法109にはSUM関数が対応されていて、合計値を算出します
集計方法110にはVAR関数が対応されていて、不偏分散を算出します
集計方法111にはVARP関数が対応されていて、分散を算出します
まぁ、関数がアルファベット順に割り振られているのです、
今回は合計値を算出するので、109を入力したわけです。
では、本題に戻りまして、上野店と品川店を非表示にしてみましょう。
合計値が連動して変わりましたね。そして当然ですが、合計値。合っていますよね。
今回のように行を非表示にするような場合は、
SUM関数ではなくて、SUBTOTAL関数を使うといいですよ。
ところで、このSUBTOTAL関数の集計方法。
109ではなくて9ではないの？と思う方もいらっしゃるかもしれませんが、
100番台でないと、非表示では対応してくれません。
この一桁の場合は、オートフィルターを使う時には、ちゃんとリアクションしてくれるのですが、
単なる非表示ではリアクションしてくれません。
そこで、100番台を使うと、オートフィルターでも、非表示でもリアクションしますので、
100番台を使うほうがよろしいかと思います。
