期限が近づいている案件や、基準を超えた数値を、毎回自分の目で探して確認している。そんな作業は、条件付き書式を使うことで自動化できます。ただし、条件付き書式は設定項目が多く、思った通りの色分けにならずに諦めてしまう方も少なくありません。
ChatGPTを使えば、強調したい条件を日本語で伝えることで、条件付き書式に必要な判定式や設定手順を整理する手助けを得られます。この記事では、条件付き書式を設定する前に決めておきたいことから、数値・日付・重複といった条件別の設定方法、そのまま貼って使える数式と指示文、そして正しく動くかの確認方法までを解説します。
Excelの条件付き書式を設定する前に決めること
設定を始める前に、何を、なぜ強調したいのかを明確にしておきます。
何を見逃したくないのか決める
期限超過なのか、基準を超えた数値なのか、見逃したくない対象を具体的に決めます。「納期が過ぎているのに未完了のまま残っている案件」「粗利率が20%を下回った取引」のように、条件を数値と項目名で言い切れる状態にしておくと、この後の設定が迷いません。
強調する条件と対象範囲を整理する
どのような条件のときに強調するのか、そしてどの範囲のセルに適用するのかを整理します。ここで決めるのは次の3点です。
- 判定に使う列（例：C列の納期）
- 強調する範囲（例：A2からF100の行全体か、C列のセルだけか）
- 判定の基準（例：今日より前、7日以内、20%未満）
行全体を塗るか、該当セルだけを塗るかで、後で使う数式が変わります。
色を付ける目的を一つに絞る
一つの表に多くの条件を詰め込みすぎると、かえって見づらくなります。色を付ける目的を一つに絞って考えます。目安として、1つの表に設定するルールは3つまでに収め、それ以上必要になったら表そのものを分けることを検討します。
絞りきれないときは、候補を並べて優先順位を付けさせると判断が早くなります。以下はAIに送るプロンプトです。コピペして使ってください。
Excelの表に設定したい条件付き書式の候補を渡します。この中から、日々の確認で本当に必要な3つを選んでください。条件は3点です。(1)選んだ理由を「見逃したときの影響」で説明する、(2)外した候補は「なぜ色にしなくてよいか」を1行で添える、(3)3つを同じ表に置いたときに色が重なる組み合わせがあれば指摘する。新しい候補の追加は不要です。
【以下、追加したい内容を記載してください】
※【 】の行は削除し、自分の情報に置き換えてから送信してください。
よく使う条件と、それぞれに適した設定方法は次のとおりです。
| 強調したいこと | 使うルール | 数式または設定 |
|---|---|---|
| 期限が過ぎた行 | 新しいルール（数式） | =$C2<TODAY() |
| 期限が7日以内に迫った行 | 新しいルール（数式） | =AND($C2>=TODAY(),$C2<=TODAY()+7) |
| 基準を超えた数値 | セルの強調表示ルール | 「指定の値より大きい」に基準値を入力 |
| 重複している値 | セルの強調表示ルール | 「重複する値」を選択 |
| 予算を実績が上回った行 | 新しいルール（数式） | =$E2>$D2 |
数値条件でセルを自動強調する方法
数値の大小によって強調したい場合の設定です。
以上・以下・範囲など判定条件を決める
「〇〇以上」「〇〇以下」「〇〇から〇〇の範囲」など、判定条件を決めます。単純な大小であれば数式を書く必要はありません。[ホーム]タブの[条件付き書式]から[セルの強調表示ルール]を開くと、「指定の値より大きい」「指定の範囲内」といった選択肢があり、基準値を入力するだけで設定できます。
「上位10項目」「平均より上」のように相対的な基準で強調したい場合は、[上位/下位ルール]を使うと、件数が変動しても自動で追従します。
対象セルへ条件付き書式を設定する
決めた条件をもとに、対象となるセル範囲へ条件付き書式を設定します。手順は次のとおりです。
- 対象範囲を選択する（例：D2からD100）
- [ホーム]タブ→[条件付き書式]→[セルの強調表示ルール]→[指定の値より大きい]
- 基準値を入力し、書式を選んで[OK]
行全体を塗りたい場合は、この方法ではなく[新しいルール]→[数式を使用して、書式設定するセルを決定]を選び、=$D2>100 のように判定列だけを $ で固定した数式を入れます。
複数条件がある場合は優先順位を確認する
複数の条件を設定する場合、どの条件が優先されるのかを確認しておきます。[条件付き書式]→[ルールの管理]を開くと、設定済みのルールが一覧で表示されます。上にあるルールが優先され、同じセルに複数のルールが当たると、後から適用されたものが上書きされることがあります。
「赤が付いたら黄色は付けない」といった排他的な動きにしたい場合は、優先したいルールの行にある[条件を満たす場合は停止]にチェックを入れます。
設定済みのルールが増えてきたら、並び順と停止の要否をまとめて整理できます。以下はAIに送るプロンプトです。コピペして使ってください。
Excelの条件付き書式のルールを複数設定しています。[ルールの管理]で上から順に評価される前提で、並び順と[条件を満たす場合は停止]の要否を整理してください。条件は3点です。(1)同じセルに2つ以上当たるルールの組み合わせを挙げる、(2)その組み合わせでどちらを優先すべきかを理由付きで示す、(3)停止にチェックを入れるべき行を明示する。表は「順番」「ルール」「停止の要否」の3列でお願いします。
【以下、追加したい内容を記載してください】
※【 】の行は削除し、自分の情報に置き換えてから送信してください。
期限・日付条件でセルを自動強調する方法
期限管理でよく使われる、日付にもとづく強調です。
今日以前・期限間近など日付条件を決める
期限を過ぎているものか、期限が近づいているものかなど、日付に関する条件を決めます。数式で書く場合は次のようになります。
- 期限が過ぎている：
=$C2<TODAY() - 期限が今日を含む7日以内：
=AND($C2>=TODAY(),$C2<=TODAY()+7) - 空欄を除外したうえで期限超過：
=AND($C2<>"",$C2<TODAY())
3つ目の書き方が実務では重要です。空欄のセルは内部的に0として扱われるため、条件を =$C2<TODAY() だけにすると、まだ日付を入れていない行まで期限超過として真っ赤になります。
日付が文字列ではなく日付形式か確認する
日付が文字列として入力されていると、条件付き書式が正しく機能しないことがあります。日付形式になっているかを確認します。判定は簡単で、空いたセルに =ISNUMBER(C2) と入力してFALSEが返れば文字列です。
文字列だった場合は、=DATEVALUE(C2) で変換した列を作るか、[データ]タブの[区切り位置]を開いて最後の画面で列のデータ形式に[日付]を指定すると、まとめて日付形式へ直せます。
日付が変わると自動更新される条件にする
今日の日付を基準にする場合は、日が変わるたびに自動で判定が更新される条件にしておきます。TODAY() を使えば、ファイルを開き直した時点の日付で再計算されるため、手作業での更新は不要です。
固定の日付を直接書き込んでしまうと、翌日以降は正しく判定されません。基準日を変えたい場合は、表の外に基準日セル（例：H1）を作り、=$C2<$H$1 のように絶対参照で指定すると、1か所の入力で全体を切り替えられます。
重複値を条件付き書式で見つける方法
同じ値が複数存在していないかを確認したい場合の設定です。
重複判定する列や範囲を決める
どの列やセル範囲を対象に、重複を判定したいのかを決めます。対象範囲を広げすぎると、比較する必要のないセル同士まで一致と判定され、意図しない箇所が強調されることがあります。
1列の中だけで重複を見たい場合は、その列だけを選択してから[条件付き書式]→[セルの強調表示ルール]→[重複する値]を選びます。表全体を選択したまま設定すると、氏名と部署のように無関係な列の値同士まで比較されてしまいます。
意図して重複する値まで強調しない条件を確認する
意図的に同じ値が複数存在してよい場合は、それらまで強調されてしまわないよう、条件を確認します。たとえば取引先名が何度も出てくるのは自然で、問題なのは「同じ取引先に同じ日付で同じ金額の行がある」といった組み合わせの重複です。
この場合は、判定用の列を1つ作って =B2&C2&D2 のように値を連結し、その列に対して =COUNTIF($F$2:$F$100,$F2)>1 という数式ルールを設定します。連結した値が2件以上あるときだけ色が付くため、正当な重複は強調されません。
連結する列の組み合わせを決めるところから相談できます。以下はAIに送るプロンプトです。コピペして使ってください。
Excelで「組み合わせが重複している行」だけを条件付き書式で強調したいです。表の列構成を渡すので、判定用の列に連結すべき列の組み合わせと、条件付き書式に入れる数式を作ってください。条件は3点です。(1)単独では重複してよい列と、組み合わせで重複したら問題になる列を分けて説明する、(2)判定用の列に入れる数式と、条件付き書式に入れる数式を別々に示す、(3)渡していない業務ルールは推測で補わない。
【列構成】A列=受注日、B列=取引先、C列=商品コード、D列=金額、E列=担当者（データはA2からE100）
数式を使った条件付き書式をChatGPTで作る方法
より複雑な条件には、数式を使った設定が必要になります。
強調条件を日本語で具体的に伝える
「A列がB列より大きい場合に強調したい」のように、条件を日本語で具体的にChatGPTへ伝えます。次の形で渡すと、そのまま貼れる数式が返ってきます。以下はAIに送るプロンプトです。コピペして使ってください。
Excelの条件付き書式で使う数式を作ってください。
・表の範囲：A2からF100（1行目は見出し）
・判定に使う列：C列（納期の日付）、E列（進捗）
・強調したい条件：納期が今日より前で、かつE列が「完了」ではない行
・強調する範囲：該当する行全体
・条件：数式だけを1行で返し、なぜその参照形式にしたかを1文で補足してください。空欄の行は強調しないでください。
基準セルと対象範囲を指定する
判定の基準となるセルと、書式を適用する対象範囲を指定します。数式ルールの参照は、選択範囲の左上のセルを基準に書くという決まりがあります。A2からF100を選んで設定するなら、数式の中では2行目を指す $C2 と書きます。ここを $C$2 と書いてしまうと、すべての行が2行目の値で判定され、全部塗られるか全く塗られないかのどちらかになります。
相対参照と絶対参照を確認する
数式の中で、セル参照が相対参照になっているか絶対参照になっているかによって、範囲全体への適用結果が変わります。意図した通りになっているかを確認します。判断の基準は次の3つです。
- 行全体を塗る：列だけ固定する（
$C2） - 特定の1列だけを塗る：そのまま（
C2） - 表の外にある基準値と比べる：完全に固定する（
$H$1）
ChatGPTに条件付き書式の設定手順を聞く方法
数式だけでなく、実際の操作手順もChatGPTに相談できます。
Excelのバージョンと対象範囲を伝える
使用しているExcelのバージョンと、条件付き書式を適用したい範囲を伝えます。バージョンによってメニューの名称が異なるためです。
以下はAIに送るプロンプトです。コピペして使ってください。
Excelの操作手順を教えてください。
・使用環境：Microsoft 365版のExcel（Windows）
・やりたいこと：A2からF100の表で、納期が過ぎた行全体を薄い赤で塗る
・使う数式：=AND($C2<>"",$C2<TODAY())
・条件：クリックするタブとボタンの名前を、画面に出てくる表記のまま順番に書いてください。
画面操作を順番に説明させる
メニューのどこを開き、どのボタンを押せばよいかを、順を追って説明してもらいます。数式ルールの流れは共通しているため、次の手順を覚えておくと確認にも使えます。
- 対象範囲（A2からF100）を選択する
- [ホーム]タブ→[条件付き書式]→[新しいルール]
- [数式を使用して、書式設定するセルを決定]を選ぶ
- 数式欄に
=AND($C2<>"",$C2<TODAY())を入力する - [書式]から塗りつぶしの色を選び、[OK]で確定する
期待どおりにならない状態を伝えて修正する
設定してみて期待通りの結果にならなかった場合は、実際にどうなったかを伝え、修正方法を相談します。伝えるのは、入力した数式・適用範囲・実際の結果の3点です。
伝えるときの形は次のとおりです。以下はAIに送るプロンプトです。コピペして使ってください。
条件付き書式が意図どおりに動きません。原因と修正後の数式を教えてください。
・入力した数式：=$C$2<TODAY()
・適用範囲：=$A$2:$F$100
・実際の結果：納期に関係なく、全部の行が赤くなる
・期待する結果：納期が今日より前の行だけを赤くしたい
条件付き書式が正しく動くか確認する方法
設定が完了したら、実際に正しく動作しているかを確認します。
境界値で色が付くかテストする
条件の境界となる値を入力し、意図した通りに色が付くかをテストします。「7日以内」なら、今日・7日後・8日後の3つを空いている行に入れて、今日と7日後には色が付き、8日後には付かないことを確かめます。
数値条件なら、基準ちょうどの値で確認します。「100より大きい」と「100以上」では100の扱いが変わるため、ここを確かめずに運用を始めると、境界の案件だけが漏れ続けます。
テストに入れる値そのものを出させると、確認の抜けが減ります。以下はAIに送るプロンプトです。コピペして使ってください。
Excelの条件付き書式が正しく動くかを確かめるためのテストデータを作ってください。渡した条件について、色が付くべき値と付かないべき値を境界を挟んで並べます。条件は3点です。(1)各行に「色が付く／付かない」の期待結果を書く、(2)空欄・文字列・0・マイナスの値も1行ずつ入れる、(3)なぜその値を入れるかを1行で添える。表は「入力する値」「期待結果」「確認したいこと」の3列でお願いします。
【以下、追加したい内容を記載してください】
※【 】の行は削除し、自分の情報に置き換えてから送信してください。
ルールの適用範囲がずれていないか確認する
条件付き書式の適用範囲が、意図した範囲からずれていないかを確認します。[条件付き書式]→[ルールの管理]を開き、[適用先]の欄を見ます。行を追加したり並べ替えたりすると、ここが =$A$2:$F$50,$A$60:$F$100 のように分断されていることがあります。
ルールが重なって表示が競合していないか確認する
複数のルールが同じセルに適用されている場合、表示が競合していないかを確認します。確認は[ルールの管理]の一覧で行い、上から順に評価されることを踏まえて並べ替えます。
期限超過を赤、期限間近を黄色にしている場合、期限超過のルールを上に置き、[条件を満たす場合は停止]にチェックを入れておくと、赤が付いた行に黄色が重ならなくなります。
まとめ
条件付き書式を設定する際は、何を見逃したくないのかを明確にしたうえで、数値・日付・重複といった条件の種類に応じて設定方法を選ぶことが重要です。単純な大小や重複は[セルの強調表示ルール]で足り、行全体を塗る場合や複数条件を組み合わせる場合だけ、=AND($C2<>"",$C2<TODAY()) のような数式ルールを使います。
ChatGPTには、表の範囲・判定列・条件・塗る範囲の4点を渡すと、そのまま貼れる数式が返ってきます。うまく動かないときは、入力した数式・適用範囲・実際の結果の3点を伝えれば原因を切り分けられます。
まずは、日頃見逃したくないと感じている数値や期限をひとつ選び、この記事の数式をコピーして範囲だけ差し替えてみてください。
関連記事
- AIでExcel業務を効率化する方法｜関数・分析・グラフ・VBAの活用：Excel業務全体の効率化を確認したい方向けの記事です。
- ChatGPTでExcel表の設計を考える方法｜項目・入力ルール・集計しやすい形に整理：条件付き書式を設定する前の表設計を見直したい場合に役立ちます。
- ChatGPTでピボットテーブルを作る方法｜集計軸と項目配置を整理：強調した数値をさらに集計・分析したい場合に活用できます。
