Excel。列を非表示にしたら、合計値も連動させるには【Column hidden】

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

あまり列の非表示の合計をすることはないかもしれませんが、やり方の一つとして、紹介させていただきました。

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい【Protecting a Sheet】

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい

＜シートの保護・条件付き書式・CELL関数＞

シートの保護をすることで、セルに入力することを防ぐことができます。

ただ、単純にシートの保護をおこなってしまうと、シート全体に保護がされてしまうので、全く入力することができません。

そこで、一部だけ入力することができるようにするのが、「セルのロック」をオフにします。

なお、シートの保護も、セルのロックも、ホームタブの書式の中にあります。

セルのロックをオフにして解除したら、シートの保護をおこないます。

これで、セルのロックをオフにしたセルのみ入力することができるのですが、見た目、どこのセルに入力することができるのか、全くわかりません。

そこで、セルのロックをオフにしているセルがわかるように、セルを塗りつぶしたいわけですが、どのようにしたいいのでしょうか。

セルのロックがオフという条件ということなので、条件付き書式をつかいます。

問題となるは、条件付き書式の条件をどのようにしたらいいのかということです。

セルの状態を確認するには、CELL関数をつかうことで対応することができます。

では、条件付き書式を設定していきましょう。

シート全体を範囲選択します。

ホームタブの条件付き書式にある、新しいルールをクリックします。

新しいルールダイアログボックスが表示されます。

数式を使用して、書式設定するセルを決定　を選択します。

条件式を設定します。

=cell(“protect”,a1)=0

あとは、塗りつぶしたい色を設定したら、OKボタンをクリックします。

これで、シートの保護がされても、入力することができる、ロックの解除がオフのセルを見つけることができました。

条件式にCELL関数をつかっていますが、その数式を確認しておきましょう。

CELL関数は、セルの状況を確認することができる関数です。

引数に、protect　をつかうことで、セルのロックがオンかオフかを確認することができます。

1ならば、セルのロックはオンになっています。

よって、今回はオフなのを探したいので、「=0」とします。

Excel。セルを選択するたびに、行全体を塗りつぶして見やすくしたい。【active cell】

Excel。セルを選択するたびに、行全体を塗りつぶして見やすくしたい。

＜条件付き書式＋ROW+CELL関数＞

行全体を見ていくと、上下別のデータを見てしまう恐れがあります。

そこで、アクティブにしたセルの行全体を塗りつぶすことができれば、定規を当てたようにわかりやすくなります。

全自動というわけにはいきませんが、条件付き書式とROW関数。

そしてCELL関数をつかった条件式を設定することで対応することができます。

では、A2:F10を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

ルールの内容に、

=row(a2)=cell("row")

と設定します。

書式ボタンをクリックして、塗りつぶす色を設定します。

あとは、OKボタンをクリックして設定完了です。

範囲選択した中のセルをどれかアクティブにしたら、「F9」キーの再計算を実行します。

すると、そのアクティブセルのある行全体が塗りつぶされることが確認できます。

残念なのは、F9キーを押さないと変わってくれないことですね。

では、設定した条件式を確認しておきましょう。

ROW関数は、行番号を算出する関数です。

範囲選択の左端のセル番地を設定します。

今回はA2なので、2と算出されます。

CELL関数は、セルのステータスというか、情報を算出する関数です。

引数に、”ROW”と設定すると、アクティブセルの行番号を算出されます。

この2つが、等しい＝TRUEとなるので、塗りつぶしが実行されるというわけです。

Excel。シートの保護後、入力できるセルがわかるように、セルを塗りつぶしたい。【sheet protection】

Excel。シートの保護後、入力できるセルがわかるように、セルを塗りつぶしたい。

＜条件付き書式＋CELL関数＞

シートの保護をつかうと、セルのロックを解除したところ以外は、入力することができません。

ただ、セルのロックを解除したところ、つまり、入力できるセルがどこにあるのか、わかりやすいように、セルを塗りつぶしたい場合、アチラコチラに散らばっていたりすると、面倒です。

そこで、条件付き書式とCELL関数を使うと、手早く入力することができるセル。

つまり、セルのロックを解除したセルだけに塗りつぶしの設定を行うことができます。

シートの保護を解除してから、条件付き書式の設定を行います。

該当する箇所を範囲選択します。

シートの広範囲が対象のようでしたら、シート全体を選択するといいでしょう。

今回は、シート全体を選択しました。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

ルールの種類は「数式を使用して、書式設定するセルを決定」を選択します。

ルールの内容にはCELL関数をつかった条件式を設定します。

=CELL("protect",A1)=0

あとは、書式ボタンをクリックして、塗りつぶすセルの色を設定します。

OKボタンをクリックして、完成です。

セルのロックを解除したセルだけが塗りつぶされていることが確認できます。

なぜ、CELL関数をつかった数式で、セルのロックに対応することができたのかを、確認しておきましょう。

CELL関数は、セルの情報を返す関数です。

CELL関数の最初の引数は、様々なセルの情報を取り出すものが用意されています。

その中に、「protect」というのがあります。

このprotectは、セルのロックに関係します。

セルのロックがON。

すなわち、初期状態の場合だと、「TRUE」を返してくれます。ロックを解除すると、「FALSE」を返してくれます。

Excelでは、TRUEを1。

FALSEを0と定義されています。

=CELL("protect",A1)=0

と0と等しいという条件式を設定すれば、セルのロックが解除されているセルということが判断できるという仕組みです。

Excel。シートの保護をした時に入力できるセルだけを手早く塗りつぶしたい【sheet protection】

Excel。シートの保護をした時に入力できるセルだけを手早く塗りつぶしたい

＜シートの保護・条件付き書式・CELL関数＞

見積書など、数式に間違えて入力をしてしまうと、数式が壊れてしまいます。

そこで、「シートの保護」をすることで、数式を保護することができます。

「シートの保護」の前に、入力したいセルには、「セルのロック」をオフにしておかないと、シートの保護をした後、セルに入力することができません。

 

「セルのロック」をオフにしたセル。

つまり入力できるセルがわかりやすいように、セルを塗りつぶしたいのですが、どのようにしたら、手早く塗りつぶすことができるのでしょうか？

箇所が多く、範囲も飛び飛びになっていると、範囲選択を自分自身で選ぶのは、面倒です。

「セルのロック」がオンかオフかという条件で塗りつぶすかどうかを判断させるわけですから、条件付き書式をつかって対応していきます。

ただ、どのような条件を設定したらいいのかが問題となってくるわけです。

まずは条件付き書式の設定を紹介します。

範囲選択をします。今回は、A2:F4とします。

ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

 

「数式を使用して、書式設定するセルを決定」を選択して、条件の数式を入力します。

設定する数式は、

=CELL("protect",A2)=0

最後に、書式ボタンをクリックして、塗りつぶしたい色を選択します。

新しい書式ルールダイアログボックスに戻ったら、OKボタンをクリックして、完成です。

塗りつぶしたセルは、シートの保護をしても入力できるセルということが、セルを塗りつぶしたことで、わかりやすくなりました。

条件付き書式に設定した数式を確認しておきます。

改めて、設定した数式です。

=CELL("protect",A2)=0

使用している関数は、「CELL関数」です。

CELL関数はセルの情報を表示できる関数です。

今回は、引数に「"protect"」を設定することで、「セルのロック」がオンなのか、オフなのかを知ることができます。

「CELL("protect",A2)」で算出結果が「0（ゼロ）」の場合は「セルのロック」が解除、つまりオフになっていることを意味しています。

セルのロックがオフになっているものを塗りつぶしたいので、

「=CELL("protect",A2)=0」という数式を条件として設定したわけです。

ちなみに、算出結果が「1」ならば、セルのロックはオンになっていることを意味します

Excel。CELL関数をつかえば、セルの書式・位置・内容に関する情報を得られます【CELL】

Excel。CELL関数をつかえば、セルの書式・位置・内容に関する情報を得られます

＜関数辞典：CELL関数＞

CELL関数

読み方： セル　　

分類： 情報 

CELL(検査の種類,[参照])

セルの書式・位置・内容に関する情報を得る　

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい【Column total】

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい

＜CELL＋SUMIF関数＞

簡単そうに思えるのですが、次のような表があって、

店舗を非表示にしても、F列の合計値の値を連動して算出したいのですが、これが、簡単にできません。

F2の数式は、

=SUM(B3:E2)

です。

B列を非表示にしてみましょう。

F列の合計値は、変わっていません。

つまり、列の非表示に対応していないわけですね。

SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応していますが、列の非表示には対応していないので、つかえません。

では、どのようにしたら、手早く、列の非表示に連動した合計値を算出できるのでしょうか？

着目点を変えてみましょう。

そもそも、非表示ということは、列幅がゼロということです。

列幅が0だったら計算範囲から除外することができればいいわけですね。

列幅を算出するならば、CELL関数をつかえば、算出することができます。

B6にCELL関数をつかった数式を設定します。

=@CELL("width",B1)

オートフィルで数式をコピーしています。

CELL関数の前に「＠（アットマーク）」をつけないと、スピル機能が使いされたことで、セルごとに算出できません。

あとは、F列の数式をSUMIF関数に変更します。

F2の数式を、

=SUMIF($B$6:$E$6,">0",B2:E2)

と設定します。

範囲には、$B$6:$E$6

列幅を算出した範囲です。オートフィルで数式をコピーしますので、絶対参照を忘れないようにします。

検索条件は、「">0"」。

比較演算子と数値を組み褪せて使うときには、「”（ダブルコーテーション）」で囲む必要があります。

合計範囲は、B2:E2

列を非表示にして確認してみます。

しかし、F列の数値が変わっていません。

ダメじゃんというわけではなくて、CELL関数は、自動的に再計算されないので、「F9」キーを押すか、数式タブの「再計算実行」をクリックします。

これで、再計算されましたので、F列の合計値が変わったことが確認できました。

このように、列を非表示にしても、合計値を連動して算出するには、簡単に算出することができないようですので、ちょっとアイディアが必要なようです。

Excel。新機能のスピルが邪魔なので、スピルにならない数式をつくりたい【Spill】

Excel。新機能のスピルが邪魔なので、スピルにならない数式をつくりたい

＜Spillと「@」・CELL関数＞

Excelに追加された新機能の「Spill」（スピル）。

確かに、便利な機能ではあるのですが、勝手に「Spill」（スピル）を使用した数式を変わってしまって、算出したいのに、邪魔をされることが多々あります。

例えば、次のようなケース。

BからD列の列幅を知りたいので、CELL関数をつかった数式を今まで通りにつくってみました。

=CELL("width",B1)

ところが、希望していないにもかかわらず、勝手に「Spill」（スピル）が、追加されてしまいました。

B6の数式をオートフィルで横方向にコピーしても、エラーが出るなど、せっかくの便利な新機能も迷惑でしかありません。

では、どのようにしたら、「Spill」（スピル）にならず、今まで通りの数式を作ることができるのでしょうか。

実は、「=（イコール）」のあとに「@（アットマーク）」を追加するだけで、今まで通りに算出することができます。

 

B6の数式は、

=@CELL("width",B1)

とCELL関数の前に「@（アットマーク）」を追加しました。

あとは、オートフィルで数式をコピーすれば、今まで通りに算出することができました。

このように、「Spill」（スピル）で邪魔された場合は、「@（アットマーク）」で対応することができそうなので、イライラが少し緩和できるかもしれませんね。

Excel。条件付き書式で入力するセルの入力忘れを予防するにはどうしたらいい？【Forgot to enter】

Excel。条件付き書式で入力するセルの入力忘れを予防するにはどうしたらいい？

＜条件付き書式+CELL関数とシートの保護＞

請求書や納品書をExcelでつくると、IFERROR＋VLOOKUP関数をネストにした数式などを設定して書類を作成していきます。

例えば、次の表

B2には、

=IFERROR(VLOOKUP(A2,$G$2:$I$4,2,FALSE),"")

という数式を設定しています。

自分以外のスタッフがこのファイルを使った場合、せっかく作成した数式を、無残にも削除されたら、怒り心頭になるのは間違いありません。

そこで、「セルのロック解除とシートの保護」をおこなうことで、数式の削除を防止することができます。

あとは入力するセルに、データを入力するだけで、作業としては完了するはずなのですが、「データの入力を忘れる」と、元も子もないわけですね。

今回やりたいことは、入力しないといけないセルをわかりやすくしたいので、塗りつぶしの書式を設定したいというわけです。

下記のようにしたいわけです。

A2:A4とC2:C4は、「セルのロック」を解除しているセルなので、入力することができるセルというわけです。

今回はわかりやすいように、表全体が小さいので、自力で塗りつぶしをしてもいいのですが、請求書や納品書のように、アチコチに入力するセルがある場合、いちいち、範囲選択して、塗りつぶしの書式を設定するのは、面倒です。

このような場合、「条件付き書式」をつかうことで、簡単に入力可能なセルに書式を設定することができます。

問題となるのは、どうやったら、セルのロックが解除されているのかを判断することができるのかというのが、ポイントとなります。

セルの状況を確認するには、CELL関数をつかうと、そのセルの情報を把握することができます。

A6にCELL関数を作っていきます。

このCELL関数は、関数挿入ダイアログボックスを使うのではなく、手入力することをおススメします。

手入力だと、引数の候補を表示してくれるので、数式の設定が効率的につくることができます。

検査の種類は、「protect」を選択します。

protectは、文字通り、セルのロックのONとOFFを確認するためのものです。

A6の数式は、

=CELL("protect",A2)

として、オートフィルを使って数式をコピーします。

算出結果は、「1」になっています。

セルのロックがONになっていると、1。つまりTUREを算出しています。

OFFならば「0」。

つまりFALSEを算出するようになっています。

このCELL関数を条件付き書式でつかうことで、入力することができるセルに塗りつぶしを設定することができるというわけです。

A2:A4とC2:C4に「セルのロック」をOFF。

解除の処理をして確認しています。

CELL関数は、再計算させないと、情報が反映されないので、「F9キー」を押すと、数値が0に変わったことが確認できます。

確認ができましたので、条件付き書式で設定していきます。

A2:E4を範囲選択して、ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

ルールの種類を選択してくださいには、「数式を使用して、書式設定するセルを決定」を選択し、次の数式を満たす場合に値を書式設定のボックスに、

=CELL("protect", A2)=0

と数式を設定したら、書式も設定します。

設定後OKボタンをクリックします。

条件付き書式をつかうことで、セルのロックがOFFのセルに塗りつぶしを設定することができました。

Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。【INDIRECT】

Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。

＜INDIRECT関数/INDIRECT＋SUBSTITUTE＋(LEFT＋CELL関数＞

月ごとに集計されているシートがあります。

それぞれのシートの合計値をまとめた、「年間」シートに数値を設定して年間合計を算出した集計シートをつくりたいわけです。

今回のケースは、サンプルなので、2枚のシートですんでいますが、年間となれば12枚のシートですし、もっと多くのシートからデータをコピーする必要があるとすれば、面倒な作業となってきます。

Excel VBAでプログラムをつくってもいいのですが、数式レベルで、効率よくコピーする方法はないのでしょうか？

このような場合、どのようなパターンがあるのか、ないのかを見つけるところから考えていくといいですね。

集計先のシートのB2に、セル参照の数式を設定してみましょう。

='1月'!B5

となっています。

横方向に、オートフィルで数式をコピーすれば、

='1月'!C5

='1月'!D5

と、横方向は、うまく参照結果が表示されていますが、縦方向にオートフィルで数式をコピーしても、当たり前ですが、うまくいきません。

理由は、シート名が違うからです。

シート名を変更する”だけ”かもしれませんが、とても面倒な作業です。

よくみると、シート名と、A列のデータが同じになっています。

シート名をA列に入力されている値そのものを使うことができれば、上手くいきそうです。

そこで、登場するのが、「INDIRECT関数」です。

B2にINDIRECT関数をつかって、次のような数式に変更してみましょう。

B2の数式は、

=INDIRECT($A2&"!b5")

この関数は、

='1月'!B5

という数式をどうやったらつくれるのかをイメージして作る関数です。

A2に1月という文字があるので、それをつかいたいわけです。

オートフィルで数式をコピーすることを前提としていますので、列固定の複合参照にしています。

そして、「&（アンパサンド）」で「"!b5"」という文字を結合させています。

縦方向に、オートフィルで数式をコピーすると、きちんと参照してくれたのですが、横方向にオートフィルで数式をコピーしたら、うまくいきません。

原因は、「"!b5"」。

文字になってしまっているので、オートフィルで数式をコピーしても「b」が「C」に自動的に変わってくれることはありません。

本当は、変わってくれれば、いいのですが…

そこで、どうやったら、「b」を「c」に出来るのかを考えて、B2の数式を次のように修正しました。

=INDIRECT($A2&"!"&SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")&5)

なんか、長くなっちゃいましたが、この数式を、縦方向。

横方向にオートフィルで数式をコピーすると、綺麗に、参照することができました。

 

では、この長くなった数式の説明をしてきます。「b」を「c」にするための数式が、

SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")

です。

内側から説明しないと、わからないので、最初は、CELL("address",B1)

CELL関数は、セルの情報を算出してくれる関数で、引数にaddressをつかうと、絶対参照がついたセル番地を文字として、算出してくれます。

B1のセル情報なので、「$B$1」という文字が算出されます。

LEFT(CELL("address",B1),2)

は、LEFT(“$B$1”,2)ということなので、LEFT関数をつかって、左から2文字分を抽出します。

これで、「$B」という文字が算出されています。

「$AA$1」だったら3文字分を抽出する必要があります。

SUBSTITUTE($B,"$","")　という状態になっていることがわかります。

SUBSTITUTE関数は、置換することが出来る関数なので、「$」を空白に置換させます。

これで、「B」だけを抽出することができるます。

ということで、シート名を参照できるセルをつくることで、INDIRECT関数をつかうことで、別シートから必要なデータを参照することができます。

Excel。列を非表示にしたら合計値も連動して再計算させたい【Column total】

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キー」を押して、再計算させましょう。

これで、非表示を除いて合計値を算出することができました。

今回のように、簡単そうに見えて、なかなか面倒という場合もありますが、少しずつ改良して便利にしていけるといいですね。