Excel。列を非表示にしても、合計値が変わるようにしたい
＜CELL+SUMIF関数＞
計算表で、列を非表示にしても、合計値が連動して変わるようにしたいと思うわけですが、簡単に算出できないようです。
【行の非表示対応は？】
行を非表示した時に連動して合計値を算出するには、SUBTOTAL関数かAGGREGATE関数を使うことで算出することができます。今回は、B6にSUBTOTAL関数を使って、確認してみましょう。
B6をクリックして、直接手入力で、SUBTOTAL関数を作っていきましょう。
関数挿入ダイアログボックスを使うと、集計方法の引数を設定するのがわかりにくくなってしまいます。
B6に作る数式は、
=SUBTOTAL(109,B2:B5)
集計方法の引数でSUMが9と109の２つが用意されていますが、非表示に対応しているのが、109なので、引数は109を使用します。
範囲選択は、B2:B5です。
オートフィルを使って数式をコピーしたら、3行目を非表示にしてみましょう。
6行目の合計行の数値が変わったことがわかります。
これを列でも行いたいのが今回の目的です。
【列には対応していない！】
H2にSUBTOTAL関数で算出すればいいのではと考えると思いますが、そう簡単にいきません。SUBTOTAL関数かAGGREGATE関数ともに、列の非表示には対応していません。
この2つの関数とも、レコードの小計を算出する関数なので、基本、上から下へのデータ、つまりレコードに対応しているので、左から右へのフィールドの計算には対応していません。
H2にSUBTOTAL関数を作って確認してみましょう。
H2には、次の数式を作ります。
=SUBTOTAL(109,B2:G2)
では、4月を非表示にしてみましょう。
合計値に変化は見られません。
Excelでは、列の非表示に対応した数式を作ることは出来ないのでしょうか？
自力で対応しなければならないのでしょうか？
【CELL関数を使ってみる】
非表示といっても、消えてしまうわけではありません。列幅が0（ゼロ）になっているだけです。
だから計算されてしまうわけです。
ここで着目したいのは、列幅が0（ゼロ）ということ。
つまり、0（ゼロ）より大きければ表示されているわけですから、列幅が0より大きかったら計算する計算式を作れればいいわけです。
条件付きで合計値を算出するので、SUMIF関数を使えばいいことに気づきます。
ただ、列幅はどうやったら算出することができるのでしょうか？
そこで登場するのが、CELL関数。
CELL関数は、セルの情報を算出してくれる関数です。
CELL関数をつかって、列幅を算出してくれれば、なんとかなりそうです。
【列幅を算出する】
B7に=CELL("width",B1)
という数式を作って、オートフィルを使って数式をコピーしましょう。
このCELL関数も直接入力で数式を作ることをお勧めします。
すると、列幅を表示してくれます。
このCELL関数は、算出した時点の情報を算出していますので、数式を設定してから、列幅を変えても、算出された数値は変動されません。
その場合は、F9キーか、数式タブにある「再計算実行」をクリックする必要があります。
H2にSUMIF関数で数式を作っていきます。
範囲には、$B$7:$G$7
検索条件は、”>0”
合計範囲は、B2:G2
として、OKボタンをクリックします。
H2の数式は、
=SUMIF($B$7:$G$7,">0",B2:G2)
それでは、列を非表示にして、必ず、再計算実行しましょう。
ちょっと、面倒くさい方法ですが、列の非表示に対応して算出することができました。
