Office Hack

Excelの条件付き書式とは?基本的な使い方やできること例

  • by kenji
  • Release
  • Update

ご自身が決めた条件に従って書式を反映させる条件付き書式、あらかじめルールを決めることで表の更新が非常に楽になります。

条件付き書式を初めて使う方へ向けて、まず条件付き書式でできることの事例を紹介し、その後に基本的な使い方の説明します。

後半には、塗りつぶしのちょっとした小技の紹介から、関数を使って少し複雑な設定をする記事の紹介まで、条件付き書式に絞った幅広い内容となっています。

条件付き書式とは?

条件付き書式の説明

条件付き書式とは、指定した範囲に対して条件を決め、その条件を満たしたセルに対して定めた書式を反映させるものです。

例えば、費用のデータが入っている列に対して、その費用が1,000円以上のものを赤文字と赤背景にする。といった機能になります(上図参考)。

条件の設定では数式(関数)を利用することができ、関数を組み合わせることにより様々な条件を指定することが可能です。さらに書式も文字に対する指定、罫線に対する指定、セルの背景塗りつぶしもできるようになっており、覚えるととても便利な機能です。

条件付き書式でできること

ここでは条件付き書式でできることをまとめました。条件付き書式は実際にどう利用されているのか、頻度が高いものをピックアップしております。

曜日(土/日/祝日)に色をつける

曜日に色をつける

条件付き書式でよく利用されているのが曜日の文字に色を加えることです。カレンダーでは、土曜日は青色の字、日曜日と祝日は赤色の字で表現されています。

この条件を、関数を利用して表すことが可能です。こちらの設定方法については、【下記で詳しくご説明】いたします。

○日前になったら色を変える

タスク管理表

Excelでタスクの管理をすることもあるでしょう。その際に、タスクの期日が迫っているものを色分けすると管理がはかどります。

数式で日付の計算をし、5日前になったらこの色、3日前になったらこの色と条件付き書式で設定することが可能です。

1行おきに色を変えて表を見やすくする

1行ごとの条件付き書式

セルの背景を1行ごと、または1列ごとに色を変えてしましまにすると、表の視認性が格段に良くなります。特に横に長くなってしまった表になると、目を追っていくとずれて違うデータを参照しがちですよね。

1行1行手動で色を塗りつぶす方法もあるのですが、手間もかかりますし、行を1行削除した際にしましまが崩れてしまいます。

そんなときは条件付き書式で関数を利用すれば、あっというまにしましまが作成できます。しかも1行削除してもしっかり維持してくれます。

こちらの詳しい内容は以下のページでご確認ください。

表の新しい行にデータを追加すると罫線を引く

条件付き書式で自動的に罫線

表に新しくデータを追加する時に、罫線をその度に引くのは大変ですね。条件付き書式で関数を利用すれば、表にデータを入力しただけで自動的に罫線を引く設定が可能です。

例えば上記の画像では、B10セルに披露宴の「被」という文字を入力しただけで、その行の罫線が自動的に引かれる設定になっております。

データバーやカラースケール、アイコンセットを使って見やすくする

データバー、カラースケール、アイコンセットの選択

データバーやカラースケール、アイコンセットを利用するには、【ホーム】タブの【条件付き書式】から選択することができます。

条件付き書式を使えば、表の数字の大小を装飾で可視化することができます。これらの機能は、範囲を選択したのちにボタン1クリックで反映可能なので、特に難しい設定をする必要がありません。

データバー

こちらはデータバーといいます。セル内に横棒グラフで表示することが可能であり、数字のボリュームがひと目で分かるようになっております。

カラースケール

こちらはカラースケールといいます。セルの背景色を数字の比率で変化させることにより、数字の高い/低いセルがどこに分布しているのか分かるようになっています。

アイコンセット

こちらはアイコンセットといいます。数字の横にアイコンを置くことにより、その数字が相対的に良いのか悪いのかアイコンで判断できます。様々な形のアイコンが用意されているので、目的にあったアイコンを選びましょう。

条件付き書式の設定方法

それでは条件付き書式の設定方法をご説明します。大きく分けて3つの設定があるので、カレンダーの曜日の土/日/祝日の文字に色を塗っていくのを例にいたします。

STEP1:範囲を指定する

まずはじめに、条件付き書式を反映させるセルの範囲を指定します。

条件付き書式の範囲選択

条件付き書式を反映させたい範囲は曜日の文字の列のみなので、C2から2019/9/30のC31まで選択します。

STEP2:条件を設定する

次に色を塗りたい条件を指定します。ここではWEEKDAY関数を使って「日」を抽出します。

新しいルールの選択

選択された状態で、【ホーム】タブの【条件付き書式】、【新しいルール】と選択します。

数式を入力する部分を選択

「新しい書式ルール」ダイアログボックスが開かれるので、【数式を使用して、書式設定するセルを決定】を選択します。

数式を入力

テキストボックスに数式を打ち込んでいきます。日曜日を抽出するにはWEEKDAY関数を利用します。数式は『=WEEKDAY($B2)=1』です。

$B2の指定は、2019/9/1の日付を指定します。最後の「=1」の意味は、「1=日曜 2=月曜 3=火曜 4=水曜 5=木曜 6=金曜 7=土曜」となっておりますので、今回は日曜を取り出すために「=1」と設定しております。

数式が入力し終わったら、【書式】ボタンを押して書式設定に参ります。

STEP3:書式を設定する

次に色を塗る書式を指定します。日曜日の文字を赤色に塗る設定をします。

書式の設定

「セルの書式設定」ダイアログボックスが開かれるので、【フォント】タブを選択し、色の部分を【赤】色に設定し、【OK】ボタンを押します。

これで書式設定は完了です。

反映の確認

最後にこれまで設定した条件付き書式の反映を確認しましょう。

条件付き書式の確定

「新しい書式ルール」ダイアログボックスに戻りますので、ルールの確定をするため【OK】ボタンを押します。

条件付き書式の反映

「日」が赤色に設定されました。さらに土曜/祝日と設定を行っていきましょう。

条件付き書式で土曜日を青色にする

基本的には今行った手順を繰り返すだけです。違う点としては数式が異なってきます。

先ほどと同様、範囲指定C2からC31まで選択し、【ホーム】タブの【条件付き書式】、【新しいルール】と選択します。

数式を入力

先ほどと同じような数式ですが、最後の数字が「7」となります。「1=日曜 2=月曜 3=火曜 4=水曜 5=木曜 6=金曜 7=土曜」で、土曜を取り出すために「=7」と設定しております。【書式】ボタンを押して書式設定に参ります。

書式の設定

今度は文字色を【青】に設定しましょう。設定したら【OK】ボタンを押します。

その後に出てくる「新しい書式ルール」ダイアログボックスの【OK】ボタンも押しましょう。

条件付き書式の反映

「土」が青色に設定されました。

条件付き書式で祝日を赤色にする

祝日を赤色にするには手順が少し複雑になりますし、利用する関数も異なってきます。

祝日の確認

2019年9月には祝日が2回あります。この祝日の文字色を赤にしていきましょう。

2019年9月の祝日のデータ

2019年の祝日のデータを用意します。用意する場所は同じシート内でも別のシートでも構いません。9/16は敬老の日、9/23は秋分の日です。

新しいルールの選択

先ほどと同様、範囲指定C2からC31まで選択し、【ホーム】タブの【条件付き書式】、【新しいルール】と選択します。

関数の説明

カレンダーの日付と祝日の日付を一致させたいので、COUNTIF関数を使います。COUNTIF関数の書式は=COUNTIF(範囲,検索条件)です。

祝日の日付全体を範囲に設定し、カレンダーの日付を検索条件に設定します。最後の「=1」は、COUNTIFが1を返してくるので条件を満たすために「=1」を設定しております。『=COUNTIF($E$2:$E$23,$B2)=1』と入力しました。

【書式】ボタンを押して書式設定に参ります。

書式の設定

今度は文字色を【赤】に設定しましょう。設定したら【OK】ボタンを押します。

その後に出てくる「新しい書式ルール」ダイアログボックスの【OK】ボタンも押しましょう。

条件付き書式の反映

「祝日」が赤色に設定されました。これで完了です。

ルールの管理について

条件付き書式の「ルールの管理」を使えばシートや選択範囲に適用しているルールの一覧を表示できたり、新規ルールを作成・編集・削除ができます。

ルールの管理の選択

ルールの管理を表示するには、【ホーム】タブ、【条件付き書式】、【ルールの管理】の順に選択します。

ルールの管理

「条件付き書式ルールの管理」ダイアログボックスが表示されます。「書式ルールの表示」では「現在の選択範囲」の他にも「このワークシート」、その他のシートを選択することができます。

上記の例では、「C3セルからG7セルまでの範囲」で「セルの値が50より小さい」に対してルールを適用していることが分かります。

優先順位

複数の条件付き書式のルールがある場合、どれが優先して適用されるのでしょうか?例えば「5より小さい値」と「3より小さい値」という2つのルールがあった場合、「2」と書かれたセルはどちらのルールが適用されるのかということです。

優先順位の確認

実は「ルールの管理」の一覧で上にある方が、優先順位が高くなります。

まず「3より小さければ濃いオレンジ」を先に処理し、次に「5より小さければ薄いオレンジ」を処理します。優先順位の高い順に条件付き書式が反映されます。

順番の移動

優先順位は入れ替えることができます。下の方にあるルールを上に持ってきます。【移動させたいルール】を選択し、【上へ移動】ボタンを押します。

順番の移動結果

ルールの並び順が入れ替わり、優先順位が逆になりました。今度は「5より小さければ薄いオレンジ」を先に処理し、次に「3より小さければ濃いオレンジ」を処理します。

条件付き書式の優先順位については具体例をもとに下記の記事で説明しています。「条件付き書式の数式を複数利用するときの優先順位」の項目をご覧ください。

満たす場合は条件の停止

「条件付き書式ルールの管理」ダイアログボックスには「条件を満たす場合は停止」というチェックボックスがあります。これは複数のルールを設定したときに使用します。

「条件を満たす場合は停止」がチェックされると、そのルールより下位のルールの書式は適用されなくなります。例を見ながら「条件を満たす場合は停止」をチェックしたときの動作を確認していきましょう。

複数のルールがある場合

上記のように「セルの値が50より小さい」ときに赤字の太字にするというルールと、「セルの値が30より小さい」ときに薄いオレンジにするというルールを設定しました。30より小さいセル(D5、C7セル)には2つのルールが適用されています。

1つ目のルール右にある【「条件を満たす場合は停止」のチェックボックスをオン】にします。続いて【適用】ボタンを押します。

条件を満たす場合は停止

書式が適用された表を見ると、2つ目のルール(「セルの値が30より小さい」)が反映されなくなりました。このように「条件を満たす場合は停止」をチェックするとそれ以降のルールが反映されなりますのでご注意ください。

設定した条件によってはそれ以降、優先順位が低いルールを適用させたくないという状況もあるのでそのような際は「条件を満たす場合は停止」を使ってみましょう。

塗りつぶしの小技

条件付き書式の設定で一番多いのがやはり塗りつぶしです。塗りつぶしの中からよく使用するテクニックをご紹介します。

空白セルを塗りつぶす

データ入力で空白がないようにしたいとき、空白セルに色が付くと見落としを防ぐことができます。条件付き書式であれば簡単に空白セルに色を付けることが可能です。

元になるデータ

上記のような教科別の点数が書かれた成績表のデータがありますが、入力されていないセルが存在します。条件付き書式を使って空白のセルに色を付けていきます。

範囲の選択

まず条件付き書式を設定する範囲を選択します。C3からG7セルまでの範囲を選択します。

ルールの管理

【ホーム】タブ、【条件付き書式】、【ルールの管理】の順に選択します。

新規ルール

「条件付き書式ルールの管理」ダイアログボックスが表示するので【新規ルール】ボタンを押します。

ルールの設定

「書式ルールの編集」ダイアログボックスが表示されたら、【指定の値を含むセルだけを書式設定】を選択し、「次のセルのみを書式設定」で【空白】を選択し、【書式】ボタンを押します。

塗りつぶし

「セルの書式設定」ダイアログボックスが開いたらタブから【塗りつぶし】を選択し、【薄いオレンジ】を選んで【OK】ボタンを押します。

ルールの設定

設定した書式が問題なければ【OK】ボタンを押します。

ルールの適用

「条件付き書式ルールの管理」ダイアログボックスで【適用】ボタン、【OK】ボタンの順に押します。

空白セルに色が付いたことでどこが入力されていないかが一目でわかるようになりました。便利なテクニックなので使ってみてくださいね。

複数セルを塗りつぶす

条件付き書式ではセルに適用することが多いですが、条件に合った行全体の色を変えたいという声もよく挙がります。例えばカレンダーの土曜日や日曜日を含む行だけ文字の色を変更して見やすくしたいという状況です。

既存のルールだとセルだけしか書式を適用できませんが、数式を使用することで行全体に書式を適用することができます。詳しくは下記でステップごとに説明していきます。

条件付き書式の元となる表

上記のような教科別に点数が書いてある成績表を用います。条件付き書式を用いて「国語」が80点以上の行に色を付けるとします。

範囲の選択

まず条件付き書式を設定する範囲を選択します。B3からI7セルまでの範囲を選択します。条件に合った行全体に書式を適用させたいのでB列も範囲に含めます。

ルールの管理

【ホーム】タブ、【条件付き書式】、【ルールの管理】の順に選択します。

新しいルールの作成

「条件付き書式ルールの管理」ダイアログボックスが表示するので【新規ルール】ボタンを押します。

数式の入力

「書式ルールの編集」ダイアログボックスが表示されたら、【数式を使用して、書式設定するセルを決定】を選択し、「次の数式を満たす場合に値を書式設定」に『=$D3>=80』を入力し、【書式】ボタンを押します。

D列の国語の点数が80点以上であるかを判定しています。行全体に書式を適用するためにD列を絶対参照、行を相対参照にします。式では「$D3」のように「$」をDの前に付けます。

塗りつぶし

「セルの書式設定」ダイアログボックスが開いたらタブから【塗りつぶし】を選択し、【薄いオレンジ】を選んで【OK】ボタンを押します。

OKボタンを押す

設定した書式が問題なければ【OK】ボタンを押します。

ルールの適用

「条件付き書式ルールの管理」ダイアログボックスで【適用】ボタン、【OK】ボタンの順に押します。

ルールの適用結果

「国語」が80点以上の行に色を付けることができました。条件付き書式の数式を使用することでセルだけなく行にも書式を適用することができます。

条件付き書式で数式(関数)を使う

条件付き書式にはあらかじめ用意されているルールとして特定の文字列を含むセルや一定以上の数値に書式を適用できるものがあります。既存のルールの他にも自分で数式を書いてルールを作成できます。数式の中では関数が使えますので指定できる条件の幅が広がります。

IF関数やCOUNTIF関数を使う

条件付き書式とよく使われる関数はIF関数とCOUNTIF関数です。既存のルールにない条件のセルに書式を適用したいときに便利です。COUNTIF関数と組み合わせれば重複しているデータに自動的に色をつけるといったことも可能です。

AND関数やOR関数を使って複数条件を指定する

条件付き書式で複数の条件を指定する際、1つずつルールを追加するのは大変です。条件付き書式では数式が使用できるのでOR関数やAND関数と組み合わせることで複数の条件に合ったセルに書式を適用することができます。

条件付き書式をコピーする

設定した条件付き書式は他のセルなどにコピーすることができます。条件付き書式をコピーする方法は複数あるのでそれぞれの使い方を覚えていきましょう。
また条件付き書式をコピーするとルールの管理が増えることがありますのでその対策も必要です。

条件付き書式の解除

条件付き書式を解除(クリア)したい場合4つの方法がありますが、ここでは選択範囲から解除する方法と、シート全体から解除する方法の2つをお伝えします。

選択範囲から解除

条件付き書式を解除したい範囲を選択

条件付き書式を解除したい範囲を選択します。

選択したセルからルールをクリア

【ホーム】タブの【条件付き書式】、【ルールのクリア】、【選択したセルからルールのクリア】を選択します。

クリアした反映結果

範囲を選択した部分のみ、ルールが解除されました。

シート全体から一括で解除

アクティブセルはA1

シート全体から一括で解除するので、アクティブセルはどこでも構いません。ここではA1のセルを選択している状態です。

シート全体からルールをクリア

【ホーム】タブの【条件付き書式】、【ルールのクリア】、【シート全体からルールをクリア】を選択します。

クリアした反映結果

シート内の全ての条件付き書式が解除されました。

文字を入れることはできない

条件付き書式はあくまで「書式」の設定なので、条件を設定して新たに文字列を入力することはできません。

よろしければ参考にならなかった点をお聞かせください