Excel。列を非表示にした時の合計をどうやって算出したらいいの？
＜SUBTOTAL関数：CELL関数+SUMIF関数＞
行を非表示にしても、それに合わせて、合計値を変えるとしたら、SUBTOTAL関数や、AGGREGATE関数を使って算出させますが、
列を非表示にしても、それに合わせて合計値を変えるようにするには、
どのようにしたらいいのでしょうか？
まずは、行の非表示に合わせて算出することができる、
SUBTOTAL関数で確認してきましょう。
【行の非表示対応：SUBTOTAL関数】
次のような表があります。
B6は、各月の合計を算出していますので、SUM関数を使って算出しますよね。
では、3-4行目を非表示にしてみましょう。
非表示にしても、合計値はそのままですよね。
SUM関数は、可視と異なり、あくまでも指定してある範囲を合計します。
そこで、SUBTOTAL関数を使うことで対応することが可能になります。
それでは、再表示しておきましょう。
SUBTOTAL関数は、関数ダイアログボックスで数式を作るよりも、
直接入力で作ると楽なので、直接入力で作っていきます。
SUBTOTAL関数を直接入力で作る理由は、
集計方法の番号が直接入力でないと、
覚えておかないといけないということになります。
なお、9番のSUMでは、非表示の対応をしていませんので、
109番のSUMを使います。
B7の数式は、
=SUBTOTAL(109,B2:B5)
と設定してありますので、これをオートフィルでコピーします。
それでは、改めて3-4行目を非表示にしてみましょう。
このように、行を非表示にしても合計値が対応されて算出されていますね。
また、SUBTOTAL関数ではなくて、
AGGREGATE関数でも算出することができます。
では、行を再表示して、7月～9月を非表示にしてみましょう。
【SUBTOTAL関数は行しか対応しない】
H2には、SUM関数を使った数式がありますが、先ほど非表示では対応しないことがわかっていますので、
SUBTOTAL関数でH2を作り直してみます。
H2には、
=SUBTOTAL(109,B2:G2)
という数式を設定しておりますのでH5までオートフィルでコピーします。
本題から脱線しますが、H7のSUBTOTAL関数の総計ですが、
SUBTOTAL関数の結果をSUBTOTAL関数で集計することができませんので、
H7は0（ゼロ）と算出されています。
では、E:G列を非表示にしてみましょう。
残念ながら、非表示に対応してくれませんでした。
原因は、SUBTOTAL関数もAGGREGATE関数も、
【行】に対応した関数だからです。
【列】には対応していないのです。
しかしながら、今回のように列を非表示にしても合計値を、
表示されているデータで再計算してほしいわけです。
では、どのようにしたらいいのでしょうか？
【CELL関数を使った力技】
例えば、列の幅が5だったら、5文字分の幅という意味ですから、列の幅が0（ゼロ）ということが列の非表示という意味になります。
要するに、列の幅が0（ゼロ）より大きい場合は、
合計するというような式を作れれば算出することができそうですね。
では、どうやって、
列の幅の情報を求めたらいいのでしょうか？
そこで登場するのが、CELL関数です。
CELL関数は、セルの様々な情報を知ることができる関数です。
そしてこのCELL関数も直接入力したほうがわかりやすい関数なので、
直接入力で数式を作っていきましょう。
B9にCELL関数を作っていきます。
=CELLと入力すると、引数を選べますので、
セルの幅の情報を算出できるwidthを選択し、
B1をクリックして、数式は完成です。
B9の数式は、
=CELL("width",B1)
となっていますので、これを9月のG9まで数式をコピーします。
合計するための関数をI列に作りますので、
I2をクリックして、SUMIF関数ダイアログボックスを表示します。
範囲には、$B$9:$G$9
数式をコピーしますので、絶対参照を忘れずに設定しましょう。
検索条件には、
">0"
比較演算子+直接入力（セル番地を使わない）ときには、
前後に[“（ダブルコーテーション）]を忘れずに入力しましょう。
合計範囲には、
B2:G2
あとは、OKボタンをクリックして、オートフィルで数式をコピーします。
では、E:G列を非表示にしてみましょう。
あれ？I列の合計値が変わっていません！
原因は、CELL関数。非表示にしましたが、リアルタイムに更新しません。
そのため、再計算させる必要があります。
F9キーを押すか、数式タブの「再計算実行」をクリックしましょう。
I列の合計値が変わりましたね。
このように、列を非表示にした場合に連動して、
合計値を算出したい場合には、
力技ですが、CELL+SUMIF関数という合わせ技で、
算出することができます。
