Excel。列を非表示にしたら合計値も連動して再計算させたい
＜CELL関数・SUMIF関数・スピルストップ＞
本当ならば、元データから直接ピボットテーブルで算出させたほうが楽だろうと思う資料は、概ねどの現場にもあると思います。
例えば、次のような表。
なんてことない表ですが、G列を非表示にした時に、H列の合計値も表示されているデータだけで再計算してほしいというのが、やりたいことなんですね。
なお、F2に設定されている数式は、
=SUM(B2:E2)
というお馴染みのSUM関数をつかっております。
また、念のために、G列を非表示にしても、合計値が連動しないことを確認しておきましょう。
残念ながら、連動してくれません。
SUBTOTAL関数を使えばいいのでは？と考えるかもしれませんが、SUBTOTAL関数は、『行』の非表示には対応しますが、『列』の非表示には対応してくれません。
昔から使っている帳票類なので、ピボットテーブルも使えない。
ピボットテーブルならば、オートフィルターで抽出したアイテムのみで合計値を算出することができますが、普通のExcelの表では、非表示も含めた範囲で算出してしまいます。
そこで、力技ですが、次のような方法で問題を解決することができます。
考え方ですが、「列の非表示」とはどういう状態なのかといえば、列幅が「ゼロ」ということですね。
要するに、列幅がゼロより大きければ、計算対象になるようにすればいいわけですね。
「列幅がゼロよりも大きいものを総和する」。
どうやらSUMIF関数でいけそうですね。
あと問題なのは、「列幅ゼロ」というのどうやったら算出することが出来るのでしょうか？
そこで、登場するのが、『CELL関数』です。
CELL関数は、対象のセルのステータスを確認できる関数です。
このCELL関数の引数に列幅がどのぐらいなのかを算出するものが用意されています。
B6に次の数式を設定して、列幅を算出します。
=CELL("width",B1)
ところが、上手く算出してくれません。
原因は、Excelの新機能の、「スピル」が影響したためです。
スピルがないExcelのバージョンでしたら問題ありませんが、Microsoft365のExcelだと、スピルの影響でオートフィルで数式をコピーすることができません。
もし、スピル機能があるExcelを使っている場合には、次のように式を変更すれば大丈夫です。
B6に設定した数式は、
=@CELL("width",B1)
「@」をつけることで、スピル機能を停止した数式を作ることが出来ます。
あとは、E6までオートフィルで数式をコピーします。
これで、列幅を算出することができました。
F2の数式をSUM関数から次のように変更します。
=SUMIF($B$6:$E$6,">0",B2:E2)
オートフィルで数式をF5までコピーします。
E列を非表示にしてみましょう。
あれ？合計が変わっていません。実はこのCELL関数。
再計算させないといけない関数なので、「F9キー」を押して、再計算させましょう。
これで、非表示を除いて合計値を算出することができました。
今回のように、簡単そうに見えて、なかなか面倒という場合もありますが、少しずつ改良して便利にしていけるといいですね。
