【Excel】条件付き書式の参照がずれる時|固定する行・列と適用先を確認

条件付き書式をコピーした時のずれは、数式の参照方法と適用先を組み合わせて確認します。すべてへ$を付けるのではなく、同じセルを読み続ける部分と、行や列に合わせて変える部分を分けます。

1. 判定するセルと色を付けるセルを分ける

A列の状態で行全体を色付けする場合、判定するのはA列、色を付けるのはA〜E列です。この2つを同じものとして扱うと、B列やC列の値を誤って判定することがあります。

最初にコピーを保存し、対象範囲の先頭セルを選択してルールを確認します。新しくルールを作る時は、範囲の左上セルをアクティブにしてから数式を入力すると対応を追いやすくなります。

2. 行全体なら列だけを固定する

A2:E100を対象に、A列が完了の行へ色を付ける例では、数式を=$A2="完了"、適用先を=$A$2:$E$100にします。A列を固定し、行番号は各行に合わせて変化させます。

=$A$2="完了"では、どの行もA2だけを判定します。反対にA2だけの相対参照では、横へ進んだ時にB列やC列を参照するため、行全体の色がそろわなくなる場合があります。

3. 共通の基準値は行と列を固定する

A2:A100の数値をZ1の目標と比べる例なら、=A2>$Z$1を使います。対象が下へ進むとA3、A4へ変わり、基準のZ1は固定されます。

数式内の参照先と「適用先」の$は役割が異なります。適用先が絶対参照で書かれていても、数式のA2まで自動で固定されるわけではありません。

4. コピー後は数式と適用先を照合する

「ホーム」→「条件付き書式」→「ルールの管理」で、対象シートのルールを確認します。どの範囲に適用され、どの行を基準にした式かを見比べてください。

A10からA1へコピーするような移動では、相対参照も動きます。コピーしただけで正しいと判断せず、先頭、中間、末尾の行で実際の値と色を確認します。複数範囲がある場合は、それぞれ点検します。

5. ルールを統合する前に違いを調べる

同じように見えるルールでも、優先順位、適用先、表示形式、「条件を満たす場合は停止」の有無が異なる場合があります。数式が似ているだけで削除しないでください。

目的も設定も同じと確認できた場合は、1つのルールの適用先へまとめる方法があります。変更前の設定を控え、警告色や期限の強調が失われていないか確認します。

6. 必要な範囲だけに適用する

使わない広い範囲に複雑な条件がある場合は、必要な範囲へ絞ることで管理しやすくなります。ただし将来追加する行を含める必要があるなら、その運用も考慮します。

重さの原因を条件付き書式だけと決め付けず、変更前後の同じ操作で比較します。最少ルール数にするためだけに異なる目的の条件を1つへ詰め込まないようにします。

7. 代表例で判定を確かめる

完了、未完了、空欄の行や、目標値と同じ値、上回る値を使って確認します。色が付く例と付かない例の両方を試すことが大切です。

数式とルール管理はMicrosoftの条件付き書式を参照できます。参照方法、適用先、優先順位をセットで点検します。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】