Excel。行を非表示にしても合計を出すにはSUBTOTAL。では列を非表示にしたら？
＜CELL関数とSUMIF関数＞
以前BLOGで、行を非表示にしても、合計値や累計を算出させる方法をご紹介しております。
その時には、SUBTOTAL関数を使えば、
求めることが出来ることをご紹介しているのですが、
今回は、なんと、列を非表示にしたらSUBTOTAL関数を使っても算出できないので、
どうにかなりませんか？というご質問がありました。
では、実際に確認してみましょう。下記の表があります。
J3にSUBTOTAL関数を使って算出してみましょう。
SUBTOTAL関数は手入力したほうがいい関数でしたね。
出来れば、手入力していきましょう。
数式は、
=SUBTOTAL(9,C3:H3)
オートフィルで数式をコピーしてみましょう。
では、E:G列を非表示にしてみましょう。
ホントだ！SUBTOTAL関数を使っても、列の非表示は対応してくれませんね。
では、どうしたらいいのでしょうか？
数式を手入力するのは大変ですし、効率も悪化してしまいます。
改めて、やりたいことを確認してみましょう。
列が非表示されたらのぞいて合計したいということですよね。
非表示ということは、”見えなくする”ことでもありますが、考え方を変えると、
表示の幅を0（ゼロ）ということでもありますよね。
セルの幅を求めることは出来ないものでしょうか？
そこで、登場するのが、
CELL関数
なのです。このCELL関数は、セルの情報を算出してくれる関数なのです。
このCELL関数を使って、セルの幅を求めて、0（ゼロ）でなければ、
合計という数式を作ってあげればいいわけです。
では、早速やってみましょう。
C6をクリックして、CELL関数を作っていきます。
CELL関数も手入力をオススメする関数ですね。
手入力ですと、検査の種類を選ぶことが出来ますが、ダイアログボックスだと、
今回使用するwidthを入力しなければいけません。
数式は、
=CELL("width",C2)
参照は、C列でしたら、どのセルでもかまいません。7と表示されました。数式をH列までオートフィルでコピーしましょう。
この数値を使って、J列にSUMIF関数で合計値を算出していきましょう。
J3をクリックして、SUMIF関数ダイアログボックスを表示しましょう。
範囲は、$C$6:$H$6
検索条件は、">0"
合計範囲は、C3:H3
そして、OKボタンをクリックして、数式をオートフィルでコピーしましょう。
では、先程と同様に、E:G列を非表示にしてみましょう。
あれれ？J列変わってないじゃないか！
と思われたかとしれませんが、先程のCELL関数。非表示にしても、
自動的に計算しなおしてくれないのです。つまり先ほどのセル幅の情報のままなのです。
ですので、ここで、ポイント。再計算してくれるボタン。
F9
を押してみましょう。
ちゃんと、列を非表示にしても、計算してくれましたよね。
このように、CELL関数というのは、どうでしょうか？
