この記事は更新して新しいブログに引っ越しました。
↓↓↓
データを入力するとセルの色が消える設定|Excel条件付き書式|PCワークLABO
データを入れると色が消えるってどういうこと?
下の申込書のように、必須項目のセルに分かりやすく色をつけたい。
(※表の作り方はこちらから)
それだけなら簡単だけど、入力が終わったセルの色が消えたらどうかな?
もっと分かりやすくて便利だよね。
下の赤枠の中は文字を入力したところ。
セルの色(水色)が消えてる!
(※青の破線枠の中は【B2】の数字入力から自動表示されるように設定済み)
こちらの設定を知りたい方はVLOOKUP関数のやり方へ
この、文字を入れるとセルの色が消える設定は条件付き書式を使うよ。
条件付き書式とは
ある条件やルールを決めて、それを満たすものに対して書式の設定をするもの。
IF関数に似ているね。
視覚的にぱっと理解できるというのが最大のメリット。
数字の大小を条件付き書式で表したりするのによく使われるんだ。
さて、今回でいう条件とは「何も入力されていなかったら・・・」というものだね。
そして、書式は「セルを水色に塗る」ということになる。
この書式というのは、表示形式・フォント・罫線・塗りつぶしなどがあるよ。
(※フォントの種類やサイズは変えられない)
後で確認してみよう。
条件付き書式の設定方法
まずは、設定したいところを全て選択する。
離れたセルは≪Ctrlキー≫を押して選択。
ここでは、【B2】をクリック、≪Ctrlキー≫を押しながら【B5】から【B8】までドラッグ。
(厳密には、Ctrlキーは離れた【B5】を押す瞬間だけ押しておけばOK)
範囲選択された状態で、
[ホーム]タブ、スタイルグループの[条件付き書式」のボタンを押す。
ドロップダウンリストでたくさん出てくる。
どういったものがあるか見てみよう。
セルの協調表示ルール
これだけでも色々できそうだ。
例:100,000を超えるセルに色を付ける。
上位/下位ルール
平均より上などもここに入る。10項目となっているけれど、5項目に変更も可能。
例:下位3%を赤くする。
データバー
選択範囲の数字から、大きい数字ほど長いバーで表示される。
セルの一つひとつに小さな横棒グラフのような感じで表示されてくる。
カラースケール
これも選択範囲の中の数字の大小を表すもの。
個人的にはオススメしない。
赤が少ないと感じる人もいれば、赤は大きいと感じる人もいるからね。
自分だけで使う分にはいいけど。
アイコンセット
いろんなデザインがある。
これも、色だけのものは人によって感じ方が違うから避けたほうがいいかも。
ここまで紹介した以外のもの、例えば計算式を使いたいなど。
それは、アイコンセットの下にある新しいルールを使って設定する。
条件付き書式で計算式を入れる時、見落としがちな注意点
[新しいルール]は条件付き書式ボタンの[アイコンセット]の下にある。
[新しいルール]ボタンを押すと、ダイアログボックスが出てくる。
数式を使いたい時は「数式を使用して、書式設定するセルを決定」を選択し、
赤枠の中に”=”で始まる式を入れ、右下の書式ボタンから書式を設定する。
(※最初にセルをクリックした場合は自動で”=”が出てくる。)
1.セルをクリックすると絶対参照になるので相対参照に変える。
2.選択範囲でアクティブになっているセルの計算式を行うこと。
セルをクリックすると、勝手に絶対参照になってしまうから気をつけないといけない。
ここは意外と忘れがち。
完成直後の見た目は同じなので要注意!
相対参照にするには、≪F4≫を3回押せばいいね。
絶対参照と相対参照についてはこちらから。
そしてもう一つ、アクティブセルでの計算式にすること。
これも忘れがち。
アクティブセルとは、範囲選択の中で白くなっているセルのこと。
すぐに入力ができる状態になっているセルのことだよ。
今回のように離れたセルを選択した場合、アクティブセルは最後に≪Ctrlキー≫を
使った場所になるんだ。
つまり、【B5】ということになる。セルの色が白だね。
だから、計算式は【B5】のセルにいるつもりで組み立てないといけない。
更に相対参照で行うことで他のセルも同様に設定したことになるんだ。
分からない人は、白いセル位置で計算・出てくる$マークは消す!
と覚えておこう。
データを入れるとセルの色が消える設定
今回は、「新しいルール」を使って4通りの設定の仕方を説明するよ。
最初の範囲選択はどれも同じ。ここからスタートだよ。
ここでは、【B2】をクリック、≪Ctrlキー≫を押しながら【B5】から【B8】までドラッグ。
(厳密には、Ctrlキーは【B5】を押す瞬間だけ押しておけばOK)
指定の値を含むセルだけを書式設定
「新しい書式ルール」ダイアログボックスの「指定の値を含むセルだけを書式設定」を選択。
下の、「次のセルのみを書式設定」で
「セルの値」「次の値に等しい」を選択し、右側のボックスに「=””」と半角で入力。
※ “”(半角ダブルクォーテーション2つ)で空白の意味になる。
これで、「セルが空白なら・・・」という条件を入れたことになる。
空白の時だけ水色にする設定ができそうだね。
あとは書式ボタンで進むだけ。書式ボタンのことは後で説明。
数式で=””(イコール空白)を使う設定
「新しい書式ルール」ダイアログボックスの
「数式を使用して、書式設定するセルを決定」を選択。
「次の数式を満たす場合に値を書式設定」でアクティブセル【B5】の計算式を入れる。
【B5】をクリックすると「=$B$5」となるので≪F4≫を3回押して「=B5」に直す。
続けて「””」と半角で入力し、「=B5=””」とする。
※ “”(半角ダブルクォーテーション2つ)で空白の意味になる。
≪F4≫についてはこちらから。
これで、「B5が空白なら・・・」という条件を入れたことになる。
数式でISBLANK関数を使う設定
「新しい書式ルール」ダイアログボックスの
「数式を使用して、書式設定するセルを決定」を選択。
「次の数式を満たす場合に値を書式設定」でアクティブセル【B5】の計算式を入れる。
今回使うのはISBLANK関数。
ISBLANK関数は、セルが空白かどうか確認する関数で、単体で使うことはあまりなく、
他の関数との組み合わせで使うことが多い。
セルは空白だという前提の式なので、空白の場合正しいという意味の「TRUE」が
結果として表示される。
「=ISBLANK( 」まで入力し、【B5】をクリックする。「$B$5」となるので≪F4≫を3回押して「B5」に直し、後ろに「 ) 」を入力。
「=ISBLANK(B5)」とする。
これで、「B5が空白なら・・・」という条件を入れたことになる。
数式LEN関数を使う設定
「新しい書式ルール」ダイアログボックスの
「数式を使用して、書式設定するセルを決定」を選択。
「次の数式を満たす場合に値を書式設定」でアクティブセル【B5】の計算式を入れる。
今回使うのはLEN関数。
LEN関数は、セルに入っている文字の数を数える関数。
なぜ数えるのか?
文字の数が0なら空白ってことだから、それを利用する。
セルの中の文字数を数える関数。
半角全角関係なく1文字は1としてカウントする。
「=LEN( 」まで入力し、【B5】をクリックする。「$B$5」となるので≪F4≫を3回押して「B5」に直し、後ろに「 )=0 」と入力。
「=LEN(B5)=0」とする。
これで、「B5の文字数が0なら・・・」という条件を入れたことになる。
最後に書式を設定する
条件の入力が終わったら、右下にある[書式]ボタンを押すと、
[セルの書式設定]ダイアログボックスが出てくる。
書式設定では、表示形式・フォント・罫線・塗りつぶしなどが設定できる。
ただし、フォント名やフォントサイズは設定できない。
設定できないものはグレー表示になっていて触れないようになっているよ。
ここでは[塗りつぶし]タブで「背景色」を「水色」に設定する。
どんな表示になるかは下の「サンプル」で確認できる。
最後は右下の[OK]ボタンを押す。
文字を入れてみて。
セルの色がなくなれば完璧!