AIで条件付き書式のルールを作る方法｜判定条件を整理

条件付き書式をAIで作るときは、色やIconから考えるより「どのRangeに」「どんな条件でTRUEになったら」「どの見た目にするか」を分けた方がうまくいきます。特に数式Ruleでは、Columnを固定するのか、Rowを動かすのかを曖昧にすると、1行目だけ合って途中から判定がずれることがあります。

この記事では、期限切れTaskを赤くする例を中心に、Apply Range、判定式、相対/絶対参照、Blank、境界日、複数Ruleの競合、Test Matrixまで整理します。読後には、AIが提案したRuleをそのまま貼るのではなく、各RowでTRUE/FALSEが意図通りか確認してから適用できる状態を目指します。

最初にRule Contractを作る

条件付き書式のRuleは、少なくともApply to / Condition / Formula / Formatの4つに分けます。

表は横にスクロールできます

要素	例	確認すること
Apply to	A2:F100	どの行・列を塗るか
Condition	期限切れかつ未完了	日本語条件
Formula	TRUE/FALSEを返す	先頭Row基準
Format	赤背景＋文字色	色だけに依存しないか

AIへは「赤くしたい」ではなく、この4要素を渡します。

架空のTask表で条件を決める

A列=Task、C列=Status、F列=期限、Dataは2行目からとします。A2:F100全体を強調し、「期限が今日より前で、Statusが完了ではなく、期限が空欄ではない」Rowを対象にします。

この条件なら、候補式は次のように考えられます。

=AND($F2<TODAY(),$C2<>"完了",$F2<>"")

重要なのはFormula自体より、なぜ$F2と$C2なのかを説明できることです。

Columnを固定し、Rowは動かす

Apply RangeがA2:F100の場合、判定する期限は常にF列、Statusは常にC列です。一方で2行目、3行目、4行目とRowは変わります。

そのため$F2は「F列を固定し、Rowは相対参照」、$C2も同じ考え方です。$F$2にすると全RowがF2だけを見てしまい、F2だけだと横方向に適用されたときColumnがずれる可能性があります。

FormulaはApply Rangeの左上Cellを基準に作る

条件付き書式の数式Ruleは、Apply Rangeの先頭位置を基準に参照を設計します。

A2:F100に適用するなら2行目を基準にし、式の参照も2行目から始めます。Apply RangeがA5:F100なら、式側も$F5や$C5を基準にする必要があります。

AIへRuleを作らせるときは、FormulaだけでなくApply Rangeを必ず一緒に渡します。

AIへ渡すPrompt

「Excelの条件付き書式用の数式を作ってください。適用範囲はA2:F100。C列がStatus、F列が期限です。期限が今日より前、Statusが『完了』以外、期限が空欄ではないRow全体を強調したいです。数式、相対/絶対参照の理由、Apply Range、境界Testを説明してください。色の設定は最後に分けてください。」

Ruleの意味まで説明させると、参照ずれを見つけやすくなります。

Blankを明示的に扱う

期限Cellが空欄のRowを強調したくないなら、その条件をFormulaに含めます。

Blankの扱いを決めずに日付比較だけを書くと、Data状態によって意図しない判定になる可能性があります。「空欄は未設定なので別扱い」「空欄も期限違反として警告」など、Business Ruleを先に決めます。

今日ちょうどの期限をどう扱うか

「今日より前」なら期限当日は対象外です。「今日以前」なら当日も対象です。日本語では似ていますがFormulaは変わります。

<TODAY() と <=TODAY() のどちらが必要か、締切の定義に合わせます。AIへ「期限切れ」とだけ伝えず、当日を含むかを明記します。

完了Statusの表記ゆれを確認する

Status列に「完了」「Done」「対応済み」が混在していると、$C2<>"完了"だけでは意図通り除外できません。

条件付き書式側で複雑に吸収する前に、Statusの入力値を統一できるか確認します。Dropdown等で状態を標準化できるなら、Ruleも読みやすくなります。

複数条件は日本語で分解してから式にする

複雑なRuleほど、最初に条件を箇条書きにします。

例：

1. 期限が入力済み

2. 期限 < 今日

3. Status ≠ 完了

4. 優先度 = 高

この4つをANDで結ぶのか、一部をORにするのかを先に決めます。AIにいきなりFormulaを書かせない方が論理ミスを見つけやすくなります。

OR条件の例

「期限切れ、またはPriorityが緊急なら強調」のようなRuleでは、どこまでをANDでまとめ、どこをORにするか括弧が重要です。

まず自然文を「(期限切れ AND 未完了) OR 緊急」のような論理構造へ直し、その後Formulaへ変換します。括弧が違えば判定対象も変わります。

RuleをCellで先にTestする

条件付き書式へ直接入れる前に、空いているColumnへ同じFormulaを通常式として置き、TRUE/FALSEを表示するとDebugしやすくなります。

たとえばG2へ =AND($F2<TODAY(),$C2<>"完了",$F2<>"") を入れ、G100までCopyします。期待したRowだけTRUEなら、そのロジックを条件付き書式へ移します。

見た目だけでDebugするより、判定結果を直接見られるので原因を追いやすくなります。

Boundary Testを作る

表は横にスクロールできます

Case	期限	Status	Expected
過去	昨日	未完了	TRUE
当日	今日	未完了	Rule次第
未来	明日	未完了	FALSE
完了	昨日	完了	FALSE
Blank	空欄	未完了	FALSE

最低でもこの5種類を用意し、色を適用する前にTRUE/FALSEを確認します。

1行目だけでなく途中行も確認する

相対参照のミスは先頭Rowでは気づけないことがあります。

2行目、10行目、最終Rowなど離れた位置で、FormulaがそのRowのC列/F列を見ているか確認します。特にApply Rangeを後から変更した場合は再Testします。

Apply Rangeを広げたらFormulaも再確認する

A2:F100で正しく動いたRuleをA2:H500へ広げると、参照Column自体は同じでも新しいRowにBlankや別Formatが混ざることがあります。

Range拡張は単なる表示範囲の変更ではありません。先頭Row、追加された境界Row、最終Rowで判定を再確認し、既存Dataと新規DataのSchemaが同じかを見ます。

複数Ruleが重なるときは順序を見る

同じCellに「期限切れは赤」「高Priorityは黄」「完了はGray」など複数Ruleがあると、Ruleの順序や設定によって見え方が変わります。

「どのRuleが優先か」を決め、Rule Managerで適用順とStop If True等の設定を確認します。AIに新Ruleを作らせる前に既存Ruleの有無も見ます。

数式Ruleが不要なケースもある

単純な「100以上」「特定Textを含む」「上位10件」のような条件は、Excelに用意された標準Ruleで十分なことがあります。

複雑なFormulaを使うほど保守しにくくなるので、Row全体を複数Column条件で判定するときなど、数式Ruleが必要な場面に絞ります。AIには「標準Ruleで足りるか」も先に判断させるとよいです。

Formatは意味を伝える最後の層

Ruleが正しくても、薄すぎる色や色だけの区別では利用者が意味を理解できないことがあります。

必要なら文字、Icon、Label等も検討します。ただし先に判定ロジックを確定し、見た目の調整でRuleの誤りを隠さないようにします。

Data BarやColor Scaleとは目的が違う

数式Ruleは「条件に当てはまるか」を判定するのに向きます。一方、Data BarやColor Scaleは値の大小や分布を見せる用途があります。

「売上上位を視覚化したい」のに複雑なTRUE/FALSE式を作る必要はない場合もあります。Reader Jobに合う表示方式を選びます。

変更前に既存Ruleを記録する

既存Workbookでは、Apply Range、Formula、Format、Priorityをメモしてから変更します。

新Ruleで見た目が変わったとき、元Ruleと競合したのかFormulaが間違ったのかを区別できます。複雑なSheetほど、追加前の状態を残す方が安全です。

Rollbackできる状態で変更する

既存Ruleを直接上書きするより、元Formula・Apply Range・Formatをメモしてから1つずつ変更します。

大量のRuleをAI案で一括置換すると、どの変更で崩れたか追いにくくなります。まず1RuleでPilotし、期待した5ケースが通ったら同じPatternへ展開します。

AIが出したRuleの採用Checklist

• Apply Rangeが明示されている

• Formulaの基準RowがRange先頭と一致

• 固定するColumnに$が付いている

• Blankの扱いが決まっている

• 当日を含む/含まないが明確

• Status表記が統一されている

• 5つのBoundary CaseをTestした

• 既存Ruleとの競合を確認した

• Formatは判定後に設定した

AIものさしの判断

条件付き書式は「色を付ける機能」と考えるより、各RowへTRUE/FALSEの判定Ruleを適用する仕組みとして考えると整理しやすくなります。Apply RangeとFormulaの基準位置をセットで決めることが最重要です。

まず条件付き書式へ入れず、通常Cellで判定式を作ってください。過去/今日/未来/完了/Blankの5ケースでTRUE/FALSEを確認し、途中Rowでも参照がずれないことを確認してからRuleへ移す。この順なら、AIが作った式でも「一部だけ色が合う」状態を避けやすくなります。