Excel(エクセル)のデータが指定した条件に合致した場合、セルに自動で色を塗りたい、と思ったことはないでしょうか。
セルの値に合わせて書式を自動で変更したい時は、『条件付き書式』という機能を利用します。
例えば、「B列の値が100以上」に当てはまる場合はセルの背景色を黄色にする、といった場合です。
また、『条件付き書式』をOR関数やAND関数やIF関数と利用すると、複数条件などの、より高度な条件を設定することができます。
この記事では、『条件付き書式』に関する操作方法を詳しく解説します。
この記事で紹介する内容
１．条件付き書式で出来ること
２．条件付き書式で自動で色を付ける方法
３．条件付き書式を数式&複数条件で設定する方法
４．条件付き書式のその他のルールの使い方
５．条件付き書式のセルをコピー/クリア/検索する方法
エクセルの条件付き書式とは？
まずは、Excelの「条件付き書式」とは、どんな機能なのか紹介します。
「条件付き書式」は、『設定した条件を満たした場合、セルの書式を自動で変更する機能』です。
例えば、下のスケジュール表のE列に「■」を入力すると、対象行のセルが自動で緑色に色付けされる、といった機能です。
エクセルの「書式」とは、セルの色や罫線など以下の項目のことをいい、条件付き書式を使ってこれらを自動で設定することができます。
書式とは
セルの背景色、フォントの色、フォントスタイル、罫線の種類、太字、下線、表示形式
条件付き書式で自動で色を付ける方法
それでは、条件付き書式を使って、スケジュール表にチェックが入ったセルを自動で色付けする方法を紹介します。
以下の手順に沿って、条件付き書式を設定してください。
まず、自動で色を付けたいセル範囲をドラッグして選択します(図１)。セル範囲は、見出し列を除くB3:E9のセル範囲を選択します。
次に、「ホーム」タブの「条件付き書式」コマンドの「新しいルール」を選択します。(図２)
「新しい書式ルール」画面が表示されます。「数式を使用して、書式設定するセルを決定」というルールを選択してください。(図３)
次に、チェックボックスの先頭のセルE3が「■」かどうか判定するため、数式入力欄に以下の数式を挿入します(図４)。E2セルの列番号の先頭だけに絶対参照の$マークを付けてください。入力したら右下の「書式」ボタンをクリックします(図5)。
=$E3="■"
「セルの書式設定」画面が開きます。「塗りつぶし」タブの中から好きな色を選択して(図6)、「OK」で画面を閉じます。(図7)
「新しい書式ルール」画面に戻ります。書式のプレビュー欄で、罫線が表示されていることを確認して、「OK」を押してください。(図8)
そうすると、E列の値が「■」の列は自動で色が付きます。以上で条件付き書式の設定の完了です。
設定した色やセル範囲を変更したい場合は、「ホーム」タブの「条件付き書式」コマンドの「ルールの管理」ボタンから編集します。
上の例では、条件付き書式を「数式を使用して、書式設定するセルを決定」というルールを使用しました。
それ以外にも「指定の値を含むセルだけを書式設定」といったルールも便利です。例えば、下のように条件を入力することで、「セルの値が10以上100未満」といった条件を設定することも可能です。
他にも、値の大きさで色の濃淡を変えたり、平均より上か下で色を付けたり、重複データに色を付けることもできます。
条件付き書式のルール
▶セルの値に基づいてすべてのセルを書式設定
▶指定の値を含むセルだけを書式設定 ※おすすめ！
▶上位または下位に入る値だけを書式設定
▶平均より上または下の値だけを書式設定
▶一意の値または重複する値だけを書式設定 ※おすすめ
▶数式を使用して、書式設定するセルを決定 ※おすすめ
条件付き書式を数式で使用する方法
条件付き書式を数式を使ってルールを作成すると、複数条件などの高度なルールを設定することができます。
この見出しでは、条件付き書式の数式を使った事例を４つ紹介します。
条件付き書式/事例１．IF関数を使用
１つ目の事例は、条件付き書式をIF関数で使用する事例です。
例えば、下の図の「算数」列が80点以上の場合は、対象の行全体に自動で色を付ける場合です。
まず、見出しを除く表全体を選択して、「条件付き書式」の「新しいルール」を選択します。
「新しい書式ルール」画面で以下のIF関数を挿入します。
=IF($C3>=80,TRUE,FALSE)
C列が80点以上であれば”TRUE”(=条件を満たす)、それ以外は”FALSE”(=条件を満たさない)という内容の数式です。数式に使用する「C3」は、列番号だけ絶対参照にして「$C3」と設定してください。
「書式」ボタンから色を指定してから「条件付き書式」画面を確定します。
そうすると、下の図のように、「算数」列が80点以上の場合は、対象行に色が自動で塗られます。
下の図のように、「算数」列のセルだけに色を付けたい場合は、表全体ではなく、C列だけを選択してから条件付き書式を設定してください。
条件付き書式/事例２．AND関数で複数条件を設定
２つ目の事例は、条件付き書式をAND関数で使用する事例です。
AND関数は「条件Aなおかつ条件B」の複数の条件が満たされているかを判定する関数です。
今回は、下の図の「算数」列が80点以上で、なおかつ「国語」列が60点以上の場合に、対象の行全体に自動で色を付ける方法を紹介します。
まず、見出しを除く表全体を選択して、「条件付き書式」の「新しいルール」を選択します。
「新しい書式ルール」画面で以下のAND関数を挿入します。
=AND($C3>=80,$D3>=60)
C列が80点以上で、なおかつ、D列が60点以上かを判定する数式です。数式に使用する「C3」「D3」は、列番号だけ絶対参照にして「$C3」「$D3」と設定してください。
「書式」ボタンから色を指定してから「条件付き書式」画面を確定します。
そうすると、下の図のように、「算数」列が80点以上で、なおかつ「国語」列が60点以上の場合は、対象行に色が自動で塗られます。
このように、AND関数は条件付き書式で複数条件を設定したい時に非常に役立つ関数です。
AND関数の使い方を詳しく知りたい方は、以下の記事も合わせて参考にしてください。
条件付き書式/事例３．OR関数で複数条件を設定
３つ目の事例は、条件付き書式をOR関数で使用する事例です。
OR関数は「条件Aまたは条件B」の複数の条件が満たされているかを判定する関数です。
今回は、下の図の「算数」列が80点以上、または「国語」列が80点以上の場合に、対象の行全体に自動で色を付ける方法を紹介します。
まず、見出しを除く表全体を選択して、「条件付き書式」の「新しいルール」を選択します。
「新しい書式ルール」画面で、以下のOR関数を挿入します。
=OR($C3>=80,$D3>=80)
C列が80点以上、またはD列が80点以上かを判定する数式です。数式に使用する「C3」「D3」は、列番号だけ絶対参照にして「$C3」「$D3」と設定してください。
「書式」ボタンから色を指定してから「条件付き書式」画面を確定します。
そうすると、下の図のように、「算数」列が80点以上、または「国語」列が80点以上の場合は、対象行に色が自動で塗られます。
条件付き書式/事例４．OR関数を使用(自動で罫線)
条件付き書式とOR関数を使った応用テクニックを紹介します。
下の図のように、B列の最下行にデータを入力すると、対象行に罫線が自動で引かれるように設定する方法です。
まず、罫線を自動で引きたいセル範囲を選択して、「条件付き書式」の「新しいルール」を選択します。
「新しい書式ルール」画面で、以下のOR関数を挿入します。
=OR($B2:$E14<>"")
比較演算子の”以外”を表す「<>」を使って、「B2:E14」のセル範囲が空白以外であるかどうかを判定しています。数式に使用する「B2」「E14」は、列番号だけ絶対参照にして「$B2」「$E14」と設定してください。
「罫線」タブの「線スタイル」を選択し(図6)、プリセット欄のセルの四辺をクリックすることで格子罫線を設定します(図7)。設定が終わったら、画面下の「OK」を押します(図８)。
「新しい書式ルール」画面に戻るので、「OK」で設定を確定させてください。
表の最下行に文字を入力して確定すると、自動で表に罫線が追加されます。もちろん、B列以外に文字を入力しても、どれか１つのセルが埋まっていれば、行全体に罫線が自動で引かれます。
罫線を自動で追加するその他のバリエーションを以下の記事で紹介しています。興味のある方は、参考にしてください。
条件付き書式のその他のルールの使い方
条件付き書式は「新しいルール」ボタン以外から設定することができます。
「条件付き書式」コマンドの直下にある「セルの強調表示ルール」などのメニューを使うと、さまざまなルールを設定することができます。
それでは、上の図の赤枠のメニューを使うと、どんなことが出来るのか１つずつ紹介していきます。
セルの強調表示ルール(重複)
「条件付き書式」の「セルの強調表示ルール」を使用すると、簡単に重複データに自動で色付けを行うことができます。
まず、重複データを確認したいセル範囲を選択して、「条件付き書式」の「セルの強調表示ルール」の「重複する値」を選択します。
「重複する値」ダイアログボックスが表示されます。「重複値」を選択して、書式欄から好みの色を選択し、「OK」を押して画面を閉じます。
そうすると、選択したセル範囲に重複値がある場合は、指定した書式が自動で設定されて色が塗られます。
重複値をチェックする方法は以下の記事で詳しく紹介しています。合わせて参考にしてください。
上位/下位ルール
「条件付き書式」の「上位/下位ルール」を使用すると、上位10％や下位10項目、平均値より大きい値などに、自動で色付けすることができます。
まず、数値の大きさをチェックしたいセル範囲を選択して、「条件付き書式」の「上位/下位ルール」の「平均より上」を選択します。
書式を設定するダイアログボックスが表示されるので、好みの色を選択し、「OK」を押して画面を閉じます。
そうすると、平均値以上の値には、指定した書式(色)が自動で設定されます。
データバー
「条件付き書式」の「データバー」を使用すると、セル内に数値の大きさに合わせたデータバー(棒グラフ)を表示させることができます。
数値の大きさをバーで表示したいセル範囲を選択して、「条件付き書式」の「データバー」の中から好みの色やグラデーションを選択します。
そうすると、選択したセル範囲の数値の大小を自動で判定し、セル内に棒グラフのようなデータバーが表示されます。
カラースケール
「条件付き書式」の「カラースケール」を使用すると、数値の大きさに合わせて、濃淡の異なる色を設定することができます。
セル範囲を選択して、「条件付き書式」の「カラースケール」の中から好みの色やグラデーションを選択します。
そうすると、選択したセル範囲の数値の大小を自動で判定し、セル内に濃淡の異なる緑色が塗られます。
配色を後から調整したい場合は、「条件付き書式」の「ルールの管理」ボタンから、色や条件などを編集してください。
アイコンセット
「条件付き書式」の「アイコンセット」を使用すると、数値の大きさに合わせて、セル内に矢印(方向)やインジケーター、評価などの図形を設定することができます。
セル範囲を選択して、「条件付き書式」の「アイコンセット」の中から、好みの図形を指定します。
そうすると、選択したセル範囲の数値の大小を自動で判定し、セル内に矢印などの図形を挿入することができます。
矢印の色や配色や図形を後から調整したい場合は、「条件付き書式」の「ルールの管理」ボタンから、色や条件などを編集してください。
条件付き書式を編集・削除する方法
条件付き書式は、設定した後から色やセル範囲などを編集したり、ルール自体を削除することができます。
「条件付き書式」の「ルールの管理」ボタンを押します。
「条件付き書式ルールの管理」画面が表示され、ワークシート内に設定されているルールの一覧が表示されます。
「ルールの編集」ボタンで書式やセル範囲などを編集、「ルールの削除」ボタンで削除することができます。
条件付き書式をコピーする方法
条件付き書式をコピーする方法を紹介します。
条件付き書式は、通常のセルのコピーと同様に、「Ctrl＋C」キーでコピーして、「Ctrl＋V」キーで貼り付けすることができます。
しかし、条件付き書式で指定しているセル範囲やルールに使っている数式が崩れて不具合を起こす場合があります。
そのため、条件付き書式のセル範囲を拡大する場合はコピーではなく、「ルールの管理」ボタンからセル範囲やルールを編集するようにしましょう。
条件付き書式のセルを検索する方法
セルに色を付けることができない場合、条件付き書式を設定していることが原因です。
条件付き書式が設定されているセルを見つけたい場合、残念ながら見た目で見つけることはできません。
そういった時、条件付き書式のセルを検索して見つける方法を紹介します。
まず、「Ctrl」キーを押しながら「G」キーを押して「ジャンプ」画面を開き、左下の「選択」ボタンを押します。
「選択オプション」画面が開きます。「条件付き書式」にチェックを入れて「OK」を押します。
そうすると、条件付き書式を設定したセルが選択されるので、対象のセルを確認することができます。
条件付き書式のショートカットキー
条件付き書式の「新しいルール」ボタンのショートカットキーを紹介します。
以下の順番でキー操作をすると、条件付き書式を新規に設定する画面を開くことができます。
Alt⇒H→L⇒N
