今回は、Excelの条件付き書式で数式を使い、条件に合うデータの行全体を色分けする方法と、うまく設定するためのコツを紹介します。
この記事は、Windows版のExcel（Microsoft 365、Excel 2024／2021など）を前提にしています。Mac版でも数式を使ったルールは作れますが、[新しい書式ルール]の画面構成が異なります。Web版のExcelでは、ダイアログボックスではなく[条件付き書式]作業ウィンドウでルールを作成し、ルールの種類で[数式]を選ぶ形になります。ボタンの名前や配置は、バージョンや画面の幅によって変わることがあります。
数式を使った条件付き書式でできること
条件付き書式には、「指定の値より大きい」「重複する値」などの組み込みルールが用意されています。ただし組み込みルールは、基本的にそのセル自身の値をもとに書式を決めます。
一方、[数式を使用して、書式設定するセルを決定]というルールを使うと、次のような設定ができます。
- 「状況」列が「完了」の行だけ、行全体をグレーにする
- 期限の日付が今日より前の行を、行全体で赤くする
- 2つ以上の条件をAND関数やOR関数で組み合わせて判定する
- 1行おきに色を付けて、縞模様にする
数式は、結果がTRUE（1）またはFALSE（0）になるものであれば使えます。TRUEになったセルに、指定した書式が適用される仕組みです。
行全体を色分けする基本の手順
ここでは、A列からE列にタスクの一覧があり、D列が「状況」になっている表を例にします。D列が「完了」の行全体をグレーにしてみます。
- 色を付けたい範囲を選択します。見出し行を除いて、データの1行目から最終行まで、A列からE列を選びます（例：A2:E50）。
- [ホーム]タブの[条件付き書式]から[新しいルール]を選びます。
- [ルールの種類を選択してください]の一覧で、[数式を使用して、書式設定するセルを決定]を選びます。
- [次の数式を満たす場合に値を書式設定]のボックスに、=$D2=”完了”と入力します。
- [書式]を選び、[塗りつぶし]タブで色を選んで[OK]を選びます。
- [新しい書式ルール]ダイアログボックスで[OK]を選びます。
これで、D列に「完了」と入力された行だけ、A列からE列までがまとめて色付けされます。
数式は「選択範囲の左上のセル」を基準に書く
数式の中のセル参照は、選択範囲の左上のセル（この例ではA2）から見た位置として書きます。そのため、データが2行目から始まる場合は「$D2」のように2行目を指定します。ここを「$D1」や「$D3」にすると、色が付く行が1行ずれてしまいます。
また、数式の先頭には必ず等号（=）を付けます。文字列を条件にする場合は、「”完了”」のようにダブルクォーテーションで囲みます。
行全体に色を付けるカギは「$」の付け方
行全体の色分けで最も大切なのが、列だけを固定する複合参照です。
条件付き書式の数式は、選択範囲の各セルに対して、位置をずらしながら評価されます。A2:E50に「=$D2=”完了”」を設定した場合、次のように判定が行われます。
- A2〜E2の各セル → どれもD2を見て判定
- A3〜E3の各セル → どれもD3を見て判定
- 以降の行も同じように、その行のD列を見て判定
列の前に「$」を付けているため、どの列のセルでも常にD列を参照します。一方、行番号には「$」を付けていないので、行ごとに参照先が下にずれていきます。この組み合わせによって、同じ行のセルがすべて同じ結果になり、行全体に色が付きます。
よくある失敗パターン
- =D2=”完了”（$なし）：列もずれてしまうため、D列のセルしか正しく判定されず、行全体に色が付きません。
- =$D$2=”完了”（行も固定）：すべてのセルがD2だけを見るため、D2が「完了」なら表全体に色が付き、そうでなければどこにも色が付きません。
ここで注意したいのが、数式を入力するときにワークシートのセルをマウスでクリックすると、絶対参照（$D$2のような形）で挿入される点です。クリックで入力した場合は、F4キーを押して参照の形式を切り替えるか、手入力で「$D2」の形に直してください。
実務で使いやすい数式の例
基本の形がわかれば、数式を変えるだけでさまざまな色分けができます。いずれもA2:E50を選択した状態で設定する例です。
期限が過ぎた行を目立たせる
C列に期限の日付が入っている場合、=$C2<TODAY()とすると、期限が今日より前の行に書式が付きます。TODAY関数を使っているので、ファイルを開いた日に合わせて判定が自動で更新されます。
期限が近い行に別の色を付けたい場合は、=$C2<TODAY()+7のように日数を足した数式で、もう1つルールを追加します。
複数の条件をまとめて判定する
AND関数を使うと、「すべての条件を満たす行」だけに色を付けられます。
- =AND($B2=”営業部”,$E2<500)：B列が「営業部」かつE列が500未満の行
OR関数を使うと、「いずれかの条件を満たす行」が対象になります。
- =OR($D2=”保留”,$D2=”差し戻し”)：D列が「保留」または「差し戻し」の行
1行おきに色を付けて縞模様にする
=MOD(ROW(),2)=1とすると、奇数行に色が付きます。ROW関数は行番号を返し、MOD関数は割り算の余りを返します。行番号を2で割った余りが1になる行、つまり奇数行がTRUEになる仕組みです。偶数行に色を付けたい場合は「=0」に変えます。
空白の行には色を付けない
今後データを追加する予定の空行まで範囲に含めている場合、条件によっては空行に色が付いてしまうことがあります。そのようなときは、=AND($A2<>””,$C2<TODAY())のように、「A列が空白でない」という条件を加えておくと安心です。
複数のルールを使うときの優先順位
1つの範囲に複数のルールを設定した場合、ルールは[条件付き書式ルールの管理]ダイアログボックスの一覧で上にあるものから順に評価されます。
- 太字と塗りつぶしのように書式が競合しない場合は、両方の書式が適用されます。
- どちらも塗りつぶしの色を指定しているなど書式が競合する場合は、一覧で上にあるルールが優先されます。
たとえば「期限切れは赤」「期限が7日以内なら黄色」という2つのルールを作った場合、期限切れの日付は「7日以内」の条件も満たします。赤のルールを上に置いておくと、期限切れの行は赤、まだ期限内で近い行は黄色、と意図どおりに分けられます。
優先順位を変更するには、次の手順で操作します。
- [ホーム]タブの[条件付き書式]から[ルールの管理]を選びます。
- [書式ルールの表示]で、対象のシートや範囲が選ばれていることを確認します。
- 順番を変えたいルールを選び、[上へ移動]または[下へ移動]で並べ替えます。
新しく作ったルールは一覧の先頭に追加されるため、ルールを追加したあとは順番を確認しておくと安心です。
設定をスムーズにするためのTips
ルールの修正は[ルールの管理]からまとめて行う
数式や適用範囲を直したい場合は、[ルールの管理]でルールを選び、[ルールの編集]から変更します。適用範囲は、一覧の[適用先]の欄で直接修正することもできます。データの行が増えたときは、ここで範囲を広げておきます。
似たルールを作りたいときは、[ルールの複製]で既存のルールをコピーし、数式や色だけを変えると手早く作成できます。
エラー値があるセルに注意する
判定に使う列にエラー値（#N/Aなど）を返す数式が含まれていると、そのセルには条件付き書式が適用されません。判定に使うセルの数式をIFERROR関数などで囲み、エラーの代わりに別の値を返すようにしておくと、色分けが途切れにくくなります。
数式はあらかじめセルで試しておく
条件付き書式の数式ボックスは小さく、間違いに気付きにくいことがあります。表の空いている列のセル（たとえばF2）に同じ数式を入力し、TRUEとFALSEが意図どおりに返るかを確かめてから設定すると、失敗を減らせます。確認後は、試しに入力した数式を削除しておきます。
書式のコピーで別の範囲にも使える
[ホーム]タブの[書式のコピー/貼り付け]を使うと、条件付き書式を別の範囲にも適用できます。ただし、数式を使ったルールの場合は、貼り付けたあとに数式内のセル参照を調整する必要があることがあります。貼り付け後は[ルールの管理]で数式と適用先を確認してください。なお、条件付き書式の数式では、別のブックを参照することはできません。
まとめ
Excelの条件付き書式で[数式を使用して、書式設定するセルを決定]を選ぶと、組み込みルールではできない、行全体の色分けや複数条件での判定ができるようになります。
ポイントは次のとおりです。
- 色を付けたい表全体を選択してからルールを作る
- 数式は選択範囲の左上のセルを基準に書く
- 判定する列に「$」を付け、行には付けない「$D2」の形にする
- AND関数・OR関数・TODAY関数・MOD関数を組み合わせて条件を広げる
- 複数のルールは[ルールの管理]で優先順位を確認する
タスク管理表や在庫表、提出状況の一覧などに取り入れると、確認したい行が見つけやすくなります。まずは1つの条件から試してみてください。