ソフトウェア

Excel(エクセル)で条件付き書式のやり方をマスター!複数条件の設定方法について合わせて解説

Excel条件付き書式記事サムネイル画像 ソフトウェア
この記事は約17分で読めます。
記事内に広告が含まれています。
まーけ太くん
まーけ太くん

前回の記事でExcel(エクセル)でドロップダウンリスト(プルダウン)の作り方はわかったけど、完了した項目は手動ではなく自動でグレーアウトできるようにしたいなあ。それに複数条件の指定が可能かも知りたい。

Kotack<br>(ZACK IT編集)
Kotack
(ZACK IT編集)

Excelでは特定のセルに入っている値を条件に、

セルの書式設定を自動で変更する機能を持っているから

わざわざ手動でセルの色を変更する必要は無いよ。

またカスタム数式でANDやORで指定すれば複数条件も可能なので詳しく説明するね。

本記事はExcelで様々な表を管理する際、セルの値を基準にセルの背景色やフォントなどを自動で変更する機能を紹介する。

また、条件に複数指定できるのかどうかも応用編では説明しているのでぜひ最後まで読んで覚えて業務に生かそう。

スポンサーリンク

セルの背景色を自動で変更する方法

条件付き書式を使う

条件付き書式とは、例えば値が100以下だったら色付きの背景にしたり、選択範囲の中で最も大きい値を太字(Bold)にしたりすることができ様々な設定項目があるほか、カスタム数式を選択することによってANDやOR、IF関数といった数式や関数を使用できるため、複雑な条件指定にも対応している便利な機能だ。

対象生徒の中で上位1名(つまり1位)を書式したい場合、”上位または下位に入る値だけを書式設定”を選ぼう。%指定も可能

条件付き書式を使うメリット

  • ”上位/下位”が分かりやすい
  • ”指定の値より大きい/小さい”が一目でわかる
  • ”完了/未完了”等の状況をすぐ確認できる

まず、ただ罫線を引いて作成しただけの表に比べて視認性が上がるため、どこの値が上位/下位であるかや、平均より下なのか上なのか完了済みなのか未完了なのか即座に確認することができるため数値を扱うデータを頻繁に扱うユーザーは覚えて損はない。

条件付き書式の設定方法

まず、テスト受験者のリストで、スコアが表示されている場合に、成績優秀者1名と最下位の受験者1名を書式設定したい場合と仮定する。

  1. 書式を設定したいセル範囲を選択
  2. エクセルのホームタブを開く
  3. 右の方にある条件付き書式をクリック
  4. 「セルの強調表示ルール」を選択し、一番下の「その他のルール」をクリック
  5. 「上位または下位に入る値だけを書式設定」を選択
  6. 今回はそれぞれ上位/下位1名抽出なので1と入力し、つけたい背景色を設定しOKボタン

1.まず、書式を設定したいセルを選択(範囲指定可能)

2,3,4.次に、ホームタブの中にある条件付き書式をクリックし、セルの強調表示ルールのリスト下部にある「その他のルール」を押下。

条件付き書式のメニューアクセス方法解説

5,6.「上位または下位に入る値だけ書式設定」を選択し、上位/下位の何名を抽出するか設定した後、「書式」のボタンからセルの文字色や背景色等を設定したらOKボタンで確定。

セルに設定した背景色やフォント書式が反映されていれば成功だ。

今回は生徒の定期テストの5教科総合点を管理し、上位1名と最下位者を抽出するという想定の表で説明した。したがって現在は上位と下位、それぞれ複数の書式が同じ指定の範囲に同時反映されていることが確認できる。

「条件付き書式ルールの管理」ウィンドウは「条件付き書式」タブの「ルールの管理」をクリックすることで確認が可能だ。

現在有効になっている条件付き書式を確認するにはExcelのリボンメニューから「条件付き書式」をクリックし、下部にある「ルールの管理」をクリックすることで確認が可能だ。

セル単体ではなく、行全体に反映したい場合はどうしたらいいの?

条件付き書式はセル1マスだけではなく行全体や表全体に書式を反映することもできる。

今回の場合は、プロジェクト管理で”状況”の項目が”完了”になったらそのセルを含む表の行全体がグレーアウトするように設定する。

表の行がグレーアウトするように書式設定した事例
完成例。”状況”のカラムで使用しているドロップダウンリストの設定方法は別記事で紹介している。
  1. 書式を設定したいセルを選択しておく(画像の場合はB3:G3)
  2. エクセルのホームタブを開く
  3. 右の方にある「条件付き書式」タブをクリック
  4. 「セルの強調表示ルール」を選択し、一番下の「その他のルール」をクリック
  5. 「数式を選択して、書式設定するセルを決定」を選択
  6. 「次の数式を満たす場合に値を書式設定」の項目内に=$B3=”完了”と入力し、つけたい背景色を設定しOKボタン
数式を使用して、書式設定するセルを決定を選択し設定

上手く設定が反映されている場合、B3セルが未対応や対応中といった文字列から、完了フラグに変更された場合に自動で選択されていた範囲が一気にグレーアウトされる。勿論、完了から対応中に戻せばグレーアウトは自動で解除される

=$B3="完了"

”B3からG3”までのセルを範囲選択して右クリックしコピー後、下の行へ反映させたい範囲を選択しながら再度右クリックをして”書式のコピー”をすれば、表全体に同じ書式設定が反映されるため他の行で完了/未完了を切り替えれば該当の行がグレーアウトされる。

ちなみに、セル指定のB3に$マークがついているのは、B列は選択を固定するためだ。いわゆる”絶対参照”と呼ばれるものとなる。”3”も絶対参照にしたいのであれば、”$B$3”と$マークをそれぞれつけることとなる。

つまり、書式や関数を別のセルにコピー&ペーストした場合に、参照元となるセル(B3)もずらした分だけずれてしまうのが相対参照

列レベルで固定したい場合、行レベルで固定したい場合、どちらも固定したい場合で$マークの位置をずらしたり追加したりすることが絶対参照では可能だ。

列の相対参照列の絶対参照
行の相対参照B3$B3
行の絶対参照B$3$B$3
VLOOKUP関数では列方向で絶対参照($)することが多い。

応用編-複数条件で条件付き書式を設定したい

条件付き書式は複数の条件を設定することが可能なので、例えば前で説明した条件を残しつつ、対応期限切れの場合はアラートの為に行を赤くしたいとする。

その場合はカスタム数式に関数も併用可能なので下記のようにAND関数を設定できれば、複数条件を基に期限切れの際のアラート設定も可能だ。

=AND($G3<TODAY(),$B3<>"完了")

数式の意味をざっくり解説すると、G3セルの期限日が今日の日付(TODAY関数)より前かつ状況が完了ではない(<>”完了”)の場合、既に期限切れなのに未完了なので表の行全体を赤く設定してアラートを出すようにするためのカスタム数式設定だ。

ちなみに、今回の主題ではないが、NOTを表す記号が”<>”だ。”=(イコール)”とともにExcelではよく使う記号なので覚えておくといいだろう。

カスタム数式で、期限切れ活状況が対応中の場合にアラートを出す設定
期限の3/14を過ぎても対応中のままなのでアラートを出ししたい場合の設定だ。

今回はTODAY関数を用いてファイルを開いている日より期限日が古かった場合に書式変更がされる設定になっているが、期限の例えば3日前にアラートを出すということも可能だ。

=AND($G3<=TODAY()+3,$B3<>"完了")

上の画像でいうと、本日(TODAY関数)が3/11だとして3日足すと3/14になる。期限日も同様3/14の為3日前であるお知らせの為、書式が変更される設定だ。次の日(3/12)以降も3日足すと3/14を過ぎることになるため当然条件が書式有効だ。

条件付き書式を解除したいのに削除できない

条件付き書式の設定解除は、条件付き書式を削除したい範囲を選択した状態で「ルールの管理」から「ルールの削除」をクリックするとクリアすることができる。

また、もし罫線などの他の書式も不要でクリアしたい場合、Excelのホームタブの右端にあるピンクの消しゴムツールをクリックすると、「書式のクリア」というメニューが表示されるのでクリックすると選択している範囲にある設定済みの書式全てが削除される。

まとめ

今回説明した条件付き書式は前回説明したデータと入力規則と、ドロップダウンリスト(プルダウン)の設定方法と組み合わせることで非常に視認性の高いリスト・表を作成可能だ。

完了した行をグレーアウトするのに使ったり、期限切れを教えてくれる書式設定を作ったりと割と何でもできるのがこの条件付き書式設定だ。

仕事でToDoやプロジェクトの管理でExcelを使用する状況がある場合はぜひとも覚えておきたい。

タイトルとURLをコピーしました