Excel。列を非表示にしたら、合計値も連動させるには
＜CELL+SUMIF関数＞
行を非表示にしたときに、見えているデータだけの合計値に連動させたい場合には、SUBTOTAL関数やAGGREGATE関数をつかうことで、求めることができます。
そこで、次のような横長の表があります。
H列には、
=SUM(B2:G2)
のように、SUM関数で合計を求めています。
やりたいことは、列を非表示にしたら、その分を除いた合計値を求めたい。
例えば、1～3月の数値を除いた合計を求めたいわけです。
当然、列を非表示にしたところで、SUM関数は、非表示に対応していないので、合計値はかわりません。
また、SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応しますが、列の非表示には対応できません。
そこで、少し考え方を変えてみます。
そもそも、列の非表示とはどういうことなのか。
列幅が0ということです。
つまり、列幅がわかるようなものがあれば、いいわけです。
そこでCELL関数をつかうことで、セル情報の列幅を求めることができます。
ただ、このCELL関数。スピル機能に対応したため、今までのような数式ではうまく求めることができません。
B5にCELL関数の数式をつくります。
CELL関数の最初の引数は、検査の種類。
列幅の情報を知りたいので、 width を選択します。
2つ目の引数の参照は、見出しのB1を選択します。
=CELL("width",B1)
で、数式を確定すると…
スピル機能によって、勝手にゴーストが発生してしまいます。
これでは、オートフィルで数式をコピーすることができません。
なので、スピル機能にならないように数式を修正します。
=@CELL("width",B1)
「@（アットマーク）」をいれることで、スピル機能をとめることができます。
※@ = implicit intersection operator
あとは、横方向に、オートフィルで数式をコピーします。
B列の列幅を短くしても、8のままですが、F9キーを押すと、再計算されます。
F9キーをおさないと、算出結果は変わりませんが、列幅を求めることができました。
I列に列の非表示に連動した合計を求める数式を用意します。
I2にSUMIF関数を使った数式を設定しました。
=SUMIF($B$5:$G$5,">0",B2:G2)
B列を非表示にしたら、F9キーを押します。
これで、非表示を除いた合計を求めることができます。
では、数式を確認しておきましょう。
単一条件の合計を求めたいので、SUMIF関数をつかいます。
最初の引数は、「範囲」。
検索のデータ範囲です。
CELL関数の結果をつかいますので、$B$5:$G$5
オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。
2つ目の引数は、検索条件。
”>0”。
セルの幅が0より大きいならば、非表示じゃないという意味ですね。
最後の引数は、合計範囲。B2:G2
あまり列の非表示の合計をすることはないかもしれませんが、やり方の一つとして、紹介させていただきました。