【条件付き書式】数式を使用して独自のルールを設定できる「新しいルール」の使い方
AさんAさん

条件付き書式を覚えて表の色付けが楽になりました!
ただ、特定の列に値があるレコード全体を色付けする等、条件付き書式の既存ルールにない条件で色付けもできると、もっと便利なのですが・・・。
何か実現する方法はありますかね?

森田森田

そんな場合、条件付き書式の「新しいルール」という機能を使うと良いですよ!
では、詳細を解説していきますね。

はじめに

この記事は条件付き書式の概要を把握していることが前提です。

参考記事

条件付き書式の概要については、以下の記事をご参照ください。


特に、条件付き書式の共通操作(ルールの適用先変更/表示順変更/複製/停止/削除/クリア)は上記記事を参照のこと。

既存ルール以外の条件を設定したい場合は「新しいルール」が有効

条件付き書式は既存の5ルール(セルの強調表示ルール、上位/下位ルール、データバー、カラースケール、アイコンセット)があり、実務での大半のケースに対応できます。

しかし、時には既存の5ルール以外の条件を設定したいケースも発生します。

たとえば、「終了予定日」列が「空白に等しくない」という条件に一致する場合、該当レコードの全列を塗りつぶしにする等です。

上記例は絶対的な基準での色付けのため、「セルの強調表示ルール」がまず思い浮かびますが、その中にある条件では実現できません。
「セルの強調表示ルール」は、自セル(条件付き書式を設定するセル)との比較が前提のため、同じ行の特定の列を基準にすることが不可。

こうした場合、条件付き書式の「新しいルール」を活用しましょう。

「新しいルール」を使うことで、数式を使用して独自の新しいルールを作成することが可能です。

これにより、条件付き書式の活用範囲が広がり、書式設定の工数削減につながります。

「新しいルール」の使用イメージ

表のレコード部分(A2~F11セル)に対し、「新しいルール」で「終了予定日」列が「空白に等しくない」場合のセルの背景色をグレーにしたイメージが以下です。

条件(「終了予定日」列が空白に等しくない)に一致した2~7行目のレコードの背景色をグレーで色付け(グレーアウト)できています。

この後、データによって「終了予定日」列のセルの値の有無(空白か否か)が変わったとしても、それに合わせて書式も自動で反映されます。

なお、今回のルールを数式で表すと=$F2<>""という論理式でした。

この論理式がTRUEの場合に設定した書式(今回ならセルの背景色をグレーへ色付け)が反映します。

よって、条件付き書式で新しいルールを設定する際は、どういう場合に書式を反映したいか(TRUEの条件)を整理し、それを論理式に表現しましょう。

また、今回のようにセル範囲に対して条件付き書式を設定する際、「$F2」のように参照の種類の工夫が必須です。
$F2は複合参照(行:相対参照、列:絶対参照)。

今回の場合、「終了予定日」列(F列)は固定なので参照セルの列は固定、レコードによって参照セルの行はスライドさせたいため、この複合参照にしたわけですね。

範囲選択した左上隅のセル(A2~F11セルならA2セル)を規準に、セル参照をスライドさせるか否かで相対参照/絶対参照/複合参照を使い分けましょう。

参考記事

論理式、参照の種類(相対参照/絶対参照/複合参照)の詳細は以下の記事をご参照ください。


「新しいルール」の設定手順

「新しいルール」を設定したい場合は、以下の手順となります。

  1. 新しいルールを設定したいセル範囲を選択
    ※今回はA2~F11セル
  2. リボン「ホーム」タブをクリック
  3. 「条件付き書式」をクリック
  4. 「新しいルール」をクリック
  5. 「数式を使用して、書式設定するセルを決定」をクリック
  6. 任意の数式を入力
    ※今回は「=$F2<>""
  7. 任意の書式を設定
    ※今回はセルの塗りつぶし(背景色)を「白、背景1、黒+基本色15%」のみ設定
  8. OK」をクリック

手順①は左上隅のセルから範囲選択すること(右下隅から範囲選択すると手順⑥の数式の参照セルが意図しないずれ方をするリスクあり)。
手順⑥でセル参照する場合、手順①のセル範囲の左上隅のセルを基準にすること。
※例:A2~F11セルの場合、A2セルが基準(今回はA2セルと同じ行のF列(=F2セル)を数式で使用)。

手順⑥の数式は関数も使用可能。ただし、ワークシート上のように関数名がサジェストされないため、直接入力が必要(自信がない場合、ワークシート上で数式を検証の上、手順⑥のボックスへコピペを推奨)。
手順⑦はセルの塗りつぶしやフォントの色がメインだが、表示形式や罫線、フォントサイズ等も変更可能(実務での使用頻度は低い)。

「新しいルール」の設定ケース

「新しいルール」は数式の発想次第で、さまざまな条件を設定できます。

ぜひ、既存のルールだと条件設定が難しい場合に工夫してみてください。

参考までに、本記事以外で実務で頻出だと思われる「新しいルール」を設定するケースを4つほど挙げておきます。

  • 土日祝日のセル//列が分かるように色付け
  • 特定のフラグ(値)に一致する行/列が分かるように色付け
  • 表が縞模様になるように奇数行or偶数行を色付け
  • 指定のセル範囲内で重複する値がある場合、2回目以降に登場したセルの値が目立たない(背景色に同化する)ように色付け
参考記事

土日祝日、特定のフラグで色付けするケースの詳細は、以下の記事をご参照ください。


サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_条件付き書式_新しいルール.xlsx

サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります(上記リンクから登録フォームへ遷移します)。

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 新しいルールを設定したいセル範囲を選択
    ※今回はA2~F11セル
  2. リボン「ホーム」タブをクリック
  3. 「条件付き書式」をクリック
  4. 「新しいルール」をクリック
  5. 「数式を使用して、書式設定するセルを決定」をクリック
  6. 任意の数式を入力
    ※今回は「=$F2<>""
  7. 任意の書式を設定
    ※今回はセルの塗りつぶし(背景色)を「白、背景1、黒+基本色15%」のみ設定
  8. OK」をクリック

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

「新しいルール」は、数式を使用して独自の条件付き書式ルールを設定できる機能です。

既存の5ルールで対応できないケースで条件付き書式を活用することも実務では発生するため、簡単な数式のものから少しずつチャレンジしてみてください!

なお、条件付き書式以外にもExcelでのデータ収集/分析の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。



ご参考になれば幸いですm(_ _)m

森田森田

「新しいルール」をきちんと使いこなすには、論理式の基本を押さえることが大事です。
その上で、論理式をAND条件やOR条件にできるANDORといった論理関数も活用できるようになると、応用範囲がさらに広がります。
複雑な論理式になって、数式の結果が分かりにくい場合は、ワークシート上で検証しながら行うと整理しやすいのでおすすめです。
ぜひ、「新しいルール」も使いこなして実務を少しでも楽にしていきましょう!