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のセルに塗りつぶしを設定することができました。
