今回は「条件付き書式の使い方」についてです。
目次
１．この記事を読めばできるようになること
タイトルの文字制限があり漠然とした言い回しになっているので、まずはどんなことが可能になるかを書いておきます。
調べごとあるあるの『結局求めている内容載ってないんだけど？』という状況にならないための措置ですね。
《できること例》
・特定のセルに自動で色を付ける。
・特定の文字の書式を自動で変更する。
・特定のセルを羅線で囲む。
《条件設定例》
・特定の文字列を含んでいる。
・特定の文字列を含んでいるセルを参照している。
・条件(A1=A2のような数式)を満たしている。
つまり、“〇”や”△”のような特定の文字列を含むセルだけ自動で色を付けたり、条件を満たしたセルの文字列に取り消し線を付けたりできるという感じです。
どうしても言葉での説明ではわかりづらいので、ケース①～③の実際の使用例を見るのがなんだかんだで早いと思います。
２．使用する機能
使用する機能は、「条件付き書式」です。
「ホーム」の「スタイル」の中に含まれている機能です。
色々やり方はありますが全部説明しようとすると情報過多になってしまいます。
なので、この記事では「新しいルール」を設定する方法の派生のみ説明していこうと思います。
これだけ知っていれば応用が利くので。
ということで、以下のケースについて順番に説明していきます。
ケース①：特定の文字列を含むセルを塗りつぶす
ケース②：特定の文字列を含むセルを参照して別のセルを塗りつぶす
ケース③：数式を満たす範囲を塗りつぶす
どのケースでもやろうとしていることはルールに則って自動でExcelに動いてもらうだけです。
あまり難しく考えないようにしましょうね。
この記事ではあくまで「条件付き書式」の説明をするので、セルの参照について詳しく説明はしません。
その辺りから不安がある場合やちょっと読み進めてよくわからない表記があった場合は、以下の記事も確認してみてください。
３．ケース①：特定の文字列を含むセルを塗りつぶす
以下のように”〇”と”✕”が入力されたセルが存在するとします。
試しに、A1:K4のセル範囲で「〇」と入力されたセルだけ青く塗りつぶす設定にしてみます。
まず、A1:K4の範囲を選択します。
ここで選択した範囲がルールの反映される範囲です。
この状態で「ホーム」から「条件付き書式」の中の「新しいルール」を選択します。
すると、ルールの種類を選択する画面が表示されます。
ここでルールを設定していくわけです。
項目を見た時点で色々できそうなのがわかるかと思います。
今やろうとしていることは「〇が入力されたセルだけを塗りつぶす」なので、「指定の値を含むセルだけを書式設定」でルール設定が可能です。
これを選択しましょう。
ちなみに、「数式を使用して、書式設定するセルを決定」が一番応用が利くので、そちらを用いた設定方法も後で説明しますね。
では、早速ルールを設定していきましょう。
まず、デフォルトで「次の値の間」となっている部分を「次の値に等しい」に変更します。
そして、「次の値」の設定として「=”〇”」と入力します。
これで、セルの値が次の値(〇)に等しい時にセルの書式を変更するというルールが出来上がります。
「〇」のような文字列を入力する場合は、「”(ダブルクォーテーション)」で囲む必要があることに注意です。
次に、「書式」をクリックします。
ここでは、文字の太さ・色・セル枠の書式変更・セルの塗りつぶしなどの設定が可能です。
今の例では色しか変えませんが、もちろん『強調表示にしたいから太字で赤文字にしよう！』などの書式変更も可能です。
後は「OK」をクリックすれば設定完了です。
これで「〇」と入力されたセルがルールに則り塗りつぶされました。
このルールは一番最初に指定したA1:K4の範囲に反映されているので、ルール設定後に範囲内で「〇」を消したり追加したりしてもしっかり反応してくれます。
ちなみに、ルールは1つずつしか設定できません。
Aの場合はBに、Cの場合はDに書式を変更…といった場合分けはできないんです。
なので、例えば「✕」も違う色で塗りつぶしたくなったらまた別のルールを設定する必要があります。
ということで、「数式を使用して、書式設定するセルを決定」で「✕」を塗りつぶす設定もしてみましょう。
先程と同様の手順で新しいルールを開き、ルールの種類を「数式を使用して、書式設定するセルを決定」にします。
ここで以下のように数式を入力すると「✕」が赤く塗りつぶされるようになります。
…なんで「=A1=”✕”」で設定ができたのか疑問に思いませんでしたか？
これは、「=A1=”✕”」というルールを最初に選択した範囲であるA1:K4の各セルに対して自動で解釈してくれているからです。
つまり、A1セルの場合は「=A1=”✕”」の時に赤く塗りつぶす、B1セルの場合は「=B1=”✕”」の時に赤く塗りつぶす…という具合にExcel側が勝手に判断してくれるんです。
その為、「=A1=”✕”」というルールで設定ができたわけです。
この辺りの仕組みは相対参照されているだけです。
４．ケース②：特定の文字列を含むセルを参照して別のセルを塗りつぶす
ケース①では特定の文字列を含むセル自体を塗りつぶしていましたので、今度は特定の文字列を含むセルがあったら他の場所のセルを塗りつぶしてみようという試みです。
やることはルールの結果を反映するセル範囲と数式が参照するセル範囲を別々にしようというだけです。
言葉にすると相変わらず何を言っているのかわかりづらくなるので、早速例を見ていきましょう。
今、B2:K2に適当に数字が並んでいます。
B2:K2に5以下の数字が入力されていた場合、それぞれの真下のセル(B3:K3の何れか)を青く塗りつぶしてみます。
特定の文字列を含む場合と言っていましたが、数式を満たす場合でもルールの設定は可能なんですよ。
まず、B3:P3の範囲を選択します。
ここで選択した範囲=ルールの結果が反映される範囲です。
ケース①との大きな違いはぶっちゃけここだけです。
「指定の値を含むセルだけを書式設定」だとB2:K2の書式変更しかできないので、ここでは「数式を使用して、書式設定するセルを決定」でルールを設定します。
※ 「指定の値を含むセルだけを書式設定」でルール設定できない理由は、試しに設定してみればわかります。
数式を「=B2<=5」に設定して塗りつぶしの色を青にします。
これだけです。
こうすることで「=B2<=5」ならB3セルを青く塗りつぶす、「=C2<=5」ならC3セルを青く塗りつぶす…というルールになります。
これも相対参照の結果ですね。
このように、最初に設定したルールの結果が反映される範囲(B3:K3)と数式が参照する範囲(B2:K2)は別々に設定することが可能です。
仮に、ルールの結果が反映される範囲を1セル分左にズラしてA3:J3に変更した場合、塗りつぶされる範囲も同様に1セル分左にズレます。
ルールの結果を反映するセル範囲と数式を参照させるセル範囲の始点がリンクしているだけと覚えておくと良いのではないですかね？
ちなみに、Excelでの「<=」は「≦」と同じ意味になります。
５．ケース③：数式を満たす範囲を塗りつぶす
最後の例は、1以上3以下、4より大きく8より小さいなどの範囲を指定したらそれに対応したセルを塗りつぶそうという試みです。
この方法を応用すれば、「金額が2000円以上4000円未満なら塗りつぶす」、「2020/1/1～2020/1/5の範囲を塗りつぶす」といった地味に使い道のあるルール設定ができるようになります。
図6のように、B2:J2に1～9の数字が順番に入力されています。
今から、B4セルとD4セルに入力した数字に対応してB2:J2の範囲を塗りつぶすルールを設定してみます。
まず、ルールを反映する範囲をB2:J2に設定します。
この状態で「新しいルール」を開いて「数式を使用して、書式設定するセルを決定」を選択します。
ここで入力する数式を「=AND(B2>=$B$4,B2<=$D$4)」にすればルールの設定は完了です。
この数式はAND関数を使用していて、B2セルがB4セル以上且つB2セルがD4セル以下という条件を表しています。
AND関数を詳しく知らない方は、条件を複数入力できる関数だと思っておいてください。
ちょっと違うけど。
「$」が付いていると絶対参照になって参照するセルが固定されます。
これによりどう変化するかというと、ケース①②のようにルールが反映されるセルがズレても「$」が付いたセルはそのまま同じ位置を参照し続けるようになります。
その為、C2セルのルールは「=AND(C2>=$B$4,C2<=$D$4)」、D2セルのルールは「=AND(D2>=$B$4,D2<=$D$4)」…という具合に数式が変化していきます。
以上、「条件付き書式の使い方」についてでした。
