Excel。行を非表示にしても、累計が計算できるようにしたい。
＜SUBTOTAL関数＞
前回、行を非表示にしても、見えている行で通し番号順になるようにするには、
どうしたらいいのか？
という質問に対して、その対応方法をご紹介しましたが、
今回は、そのバリエーションになりますが、累計の列があって、
その累計が表示されている行のみで累計を算出する方法をご紹介していきます。
下記の表があります。
今回は、H列にある、合計の累計を算出していきます。
累計を効率よく算出する方法は、以前書いたこともありますが、
まず、通常の累計の算出方法を確認してみましょう。
H4をクリックして、SUM関数ダイアログボックスを表示しましょう。
数値1には、$G$4:G4と入力をして、OKボタンをクリックしましょう。
スタートとなる、起点を絶対参照にすることによって、オートフィルハンドルを使って、
数式をコピーした際に、絶対参照を設定していない終点のセル番地は、
下方向にスライドしていきますので、その間の合計値を算出してくれます。
では、結果はこのようになりました。
ただ、これは通常の累計の求め方なので、数行を非表示にしてみると、
B列は前回設定した、非表示にしても通し番号順になっていますが、
H列の累計は残念ながら、非表示の対応が出来ていません。
そこで、今回も活躍してくれるのは、SUBTOTAL関数。
しかも、集計方法は100番台を使用するタイプでの設定になりますね。
前回同様に、集計方法は100番台を使用しないと非表示にしても対応してくれません。
では、非表示を元に戻して、H列に合計の累計を算出していきますので、H4をクリックして、SUBTOTAL関数ダイアログボックスを表示しましょう。
まずは、集計方法ですが、これは、合計を算出したいので、109を入力します。
数式バーに直接入力した場合は、確認することができます。
続いて、参照1には、$G$4:G4 と入力します。
これは、先程算出しました、SUM関数を使った累計の算出方法と同じ範囲選択方法になります。
そして、OKボタンをクリックしましょう。オートフィルハンドルで数式のコピーもしておきましょう。
ちなみに、数式は、
=SUBTOTAL(109,$G$4:G4)
まずは、合計の累計を算出することはできましたね。
それでは、行を非表示にしてみましょう。
このように、ちゃんと、非表示にしても、合計の累計も連動して算出してくれていますよね。
これで、完成しました。
ところで、B列のNOも、今回のH列の累計も、非表示にしても、対応してくれますが、
並び替えを行っても、ちゃんと連動してくれます。
G列の合計を降順にしてみると、それがよくわかります。
ちゃんと、連動していますよね。
このように、SUBTOTAL関数はアイディア次第で色々使えますので、
覚えておくといいかもしれませんね。
