サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
条件付き書式が効かない/重くなるときの整理手順
記事IT・テクノロジー
条件を満たしているはずのセルに色がつかない。逆に、色はついているのに条件が合っていない。
あるいは、条件付き書式を設定してから、ファイルを開くたびに動作がもたつくなった。
条件付き書式は「範囲」「数式」「優先順位」の3つが絡み合う機能なので、
1か所だけ見て直そうとすると、別の設定に足を取られます。
確認する順番を決めておくと、原因にたどり着くのが早くなります。
■ 手順1：数式が対象セルを正しく指しているか確認する
条件付き書式で数式を使う場合、基準は「選択範囲の先頭セル」だけです。
そこに書いた相対参照が、範囲内の他のセルに自動でずれながら適用されます。
たとえばB2:B100を選択して、B列がC列より大きいセルに色をつけたい場合、
先頭セルであるB2を基準に数式を書きます。
=B2>C2
ここで列を固定するために $B2 や B$2 と書くべきところを、$B$2 のように行も列も
固定してしまう間違いがよく起こります。行まで固定すると、
B3以降もすべてB2とC2だけを見て判定するため、2行目以外はすべて同じ結果になります。
・行だけ動かしたい（列は固定、行は動く）→ $B2
・見た目通りに全セルが独立して動く → 通常は列だけ$を付ける
適用範囲のうち何行かを選んで、期待通りに色が変わっているか目視で確認してください。
判定用に別のセルへ結果を出す方法も有効です。空いている列にB2>C2をそのまま入力し、
TRUE/FALSEが行ごとに正しく変化しているかを見れば、条件付き書式側の数式が原因なのか、
それとも別の要因なのかを切り分けられます。
■ 手順2：適用範囲がずれていないか確認する
数式は正しいのに色がつかない場合、適用範囲そのものがずれていることがあります。
「ホーム > 条件付き書式 > ルールの管理」を開くと、
ルールごとに「適用先」という範囲が表示されます。ここが $B$2:$B$100 のはずが、
過去の編集で $B$2:$B$50 のように縮んでいたり、別の列を指していたりすることがあります。
行を挿入・削除した後、適用範囲が自動で追従しないケースで起こりやすい症状です。
特に、表の途中に行を挿入した場合は範囲が広がることもありますが、
表の末尾に行を追加した場合は適用範囲の外側になるため、色がつきません。
新しく増えた行だけ条件付き書式が効いていない、というときはまずここを疑ってください。
■ 手順3：ルールの重なりと「停止」を確認する
同じセルに複数のルールが設定されていると、上にあるルールが優先されます。
ルールの管理画面でルールが上から順に並んでいて、
一番上のルールに「これ以上ルールを適用しない場合は停止」にチェックが入っていると、
そのルールの条件に一致した時点で、下のルールは無視されます。
「新しく追加したルールの色が反映されない」というときは、
たいてい上に別のルールが先に条件を満たして止めています。
優先させたいルールを一番上にドラッグで移動するか、
「停止」のチェックを外して両方のルールが評価されるようにしてください。
なお、「停止」を外して両方のルールを有効にすると、条件が重なった行では
後から評価されたルールの書式が上書きされる点に注意してください。
背景色は片方しか勝てません。両方の色を同時に見せたい場合は、
文字色と背景色のようにルールごとに変える書式の種類を分けておくと事故が減ります。
■ 手順4：範囲が広すぎて重くなっていないか確認する
原因は分かったが、今度は動作が重い、という場合はここを見てください。
列全体（$A:$A のような指定）に条件付き書式をかけていると、
実データが1000行しかなくても、Excelは100万行以上を対象に毎回判定を繰り返します。
ファイルを開くたび、セルを編集するたびにこの再計算が走るので、動作が遅くなります。
対処は、適用範囲を実データの行数に絞ることです。
ルールの管理画面で適用先を $A$2:$A$1000 のように、
実際に使っている範囲プラス余裕分くらいまで縮めてください。
もう1つ、TODAY() や NOW() のような再計算のたびに値が変わる関数を
条件付き書式の数式内で多用していると、他の操作のたびに毎回全セルが再評価されます。
日付判定が必要な場合でも、範囲を広げすぎないことで負荷を抑えられます。
■ やってはいけないこと
効かないルールの上に、新しいルールを重ねて追加すること。
うまく色がつかないときに、原因を探さずに似た条件のルールをもう1つ追加すると、
ルールの管理画面には似たようなルールが何本も並び、
どれが実際に効いているのか分からなくなります。次に触る人（未来の自分を含む）が
最初からやり直すことになります。原因が分かるまでは、新しいルールを足さないでください。
もう1つ、使わなくなった過去のルールを削除せずに放置することも避けてください。
古いルールが手順3の「停止」で新しいルールをブロックしていることがあり、
見た目には残っていないルールほど原因として見落とされます。
ルールの管理画面は、定期的に棚卸しして使っていないものを消しておくと調査が早くなります。
■ それでも解決しないとき
・ルールの管理画面を確認しても、適用範囲と数式のどちらにも問題が見当たらない
・ルールが10本以上重なっていて、どれが有効なのか整理できない
・重さの原因が条件付き書式かどうかも切り分けられていない
このあたりまで来たら、ルールを棚卸ししながら組み直した方が早いです。
私はExcel・スプレッドシートの条件付き書式や数式の整理を承っています。
どのルールが何を判定しているかを一覧にしてお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
