条件付き書式が思ったところに付かないときは、式を直す前に適用先の範囲を見る。次に、式の基準になっている行が適用先の先頭行と合っているかを見る。そのあとで列の$を見る。この順で見ると、たいてい3つ目までに当たる。
原因は式の書き方そのものよりも、式と適用先の組み合わせにある。適用先がA3:E16なら、式は3行目のセルを指して書く。ここがずれていると、式だけを見ても正しく見える。
以下の数字は、Microsoft 365のExcel（バージョン16）で実際に式を入れて、セルの表示色を読んで数えたものだ。確認日は2026年9月22日。表は10行5列で、E列に「要確認」が3行入っている。
行全体が塗られない。$の付け方で15セルが3セルになる
A2:E11を適用先にして、3通りの式を入れて塗られたセルを数えた。
式を=$E2=“要確認”にすると、15セル塗られた。3行が丸ごと、A列からE列まで色が付く。
$を外して=E2=“要確認”にすると、3セルになった。E列の3つだけで、行全体は塗られない。同じ条件のつもりでも、塗られるセルは5分の1になる。
仕組みはこうなっている。Excelは条件付き書式の式を、適用先の左上のセルを基準にして、ほかのセルに読み替える。A2:E11に=$E2=“要確認”と書くと、A3では=$E3=“要確認”、B2では=$E2=“要確認”になる。列の前の$がその読み替えを止めるので、どの列にいてもE列を見に行く。行番号には$を付けないから、行が下がるぶんだけ見に行く行も下がる。これで行全体が塗られる。
$を外すと列の読み替えも起きる。B2では=F2=“要確認”、C2では=G2=“要確認”になり、空の列を見に行って外れる。結果として、たまたまE列に来たセルだけが塗られる。
覚え方は1つだけでいい。見に行く列の前に$を付けて、行番号には付けない。
適用先とずれた式を書くと、2行しか塗られない
同じ適用先A2:E11に、式を=$E5=“要確認”と書いた。A5のセルを見ていたときに書式を作ると、こうなりやすい。
このとき塗られたのは4行目と7行目だった。本当に「要確認」が入っているのは4行目、7行目、10行目だ。1行足りない。
3行ずれた式が、そのまま3行ずれて展開される。適用先の先頭が2行目で、式が5行目を指しているので、差の3行ぶん下を見に行く。4行目のセルは7行目を、7行目のセルは10行目を見る。10行目のセルは13行目を見て、そこは空なので外れる。逆に、1行目相当の位置が無いため、上のほうの行は判定されない。
この間違いは、合っている行もあるせいで気づきにくい。3行のうち2行が塗られるので、見た目には効いている。数が合っているかを確かめないと、そのまま残る。
直し方は、式を書き直すよりも、いったんルールを消して、適用先の先頭セルを選んでから作り直すほうが早い。ルールの管理を開いて、適用先の欄と式の行番号が同じ数字になっているかを見る。
空のセルが塗られる
差額が0より大きい行を塗りたくて=$B2>0と書くと、空のセルまで塗られることがある。
10行の表で試した。B列には数値が8行、=““を返す式が2行入っている。=$B2>0で塗られたのは7行で、そのうち2行はその空文字の行だった。正しく塗りたいのは5行だけだ。
原因は比較の決まりにある。Excelは文字列と数値を比べると、文字列のほうを大きいと判定する。=““が返すのは長さ0の文字列で、数値ではない。だから0より大きいと判定される。
空でないことを先に見てから数値として比べる形に替えると、塗られたのは5行になった。
=AND($B2<>"",$B2>0)この形は、IFで空文字を返している列すべてに要る。売掛金の年齢表のように、経過日数を式で出して未入金だけ空にしている表では、空でないことを見ないと使っていない行まで全部塗られる。500行ぶん式を入れてあると、500行が色で埋まる。
セルを見た目で空だと思っても、式が入っていれば文字列だ。Delキーで消したセルとは別のものになる。
ルールを2本重ねると、先に作ったほうが勝つ
同じ範囲に2本のルールを入れた。1本目が=$C2>=50000で青、2本目が=$E2=“要確認”で赤だ。
両方に当てはまる行は青になった。あとから作った赤ではない。条件付き書式は上にあるルールから順に見て、当たったところで色を決める。あとから作ったルールは下に付くので、先にあるほうが勝つ。
順番を入れ替えると結果も変わる。赤のルールを1番に上げたら、両方に当てはまる行が赤になった。
だから「色が変わらない」の原因が、式ではなくルールの並び順にあることがある。ルールの管理を開くと上から順に並んでいるので、上下の矢印で動かす。当てはまる行が多いルールほど下に置くと、細かい条件が先に効く。
条件を止めたいときは、停止のチェックを使う。上のルールが当たったら下のルールを見ない、という指定だ。塗りつぶしだけなら上が勝つので要らないが、片方が文字色、もう片方が塗りつぶしだと、両方が重なって付く。
行をコピーすると、ルールが増える
適用先A2:E11にルールを1本だけ入れた表で、6行目を選んでコピーし、20行目に貼った。
貼ったあとにルールの数を数えると2本になっていた。増えたほうの適用先は$A$20:$E$20だった。中身は同じ式なのに、別のルールとして登録される。
これを何度かやると、同じ式のルールが10本以上並ぶ。適用先が$A$5:$E$5,$A$12:$E$12のように飛び飛びになっていき、あとから範囲を足したい行が抜ける。行を増やしたのに色が付かない、という状態はこれで起きる。
見分け方は、ルールの管理を開いてルールの数を数えることだ。1つの表に同じ式が何本も並んでいたら、全部消して、表全体を選んでから付け直す。VLOOKUPが合わないときの原因を数えたときと同じで、まず数を数えると何が起きているかが分かる。
貼るときに「値の貼り付け」を選べばルールは増えない。書式ごと移したい場面でなければ、値だけ貼るほうが表は壊れにくい。
形だけを入れた見本のブックを配った
4つの形を、1枚のシートに1つずつ入れたブックを置いた。行全体を塗る、空を除く、ルールを2本重ねる、重複している行を塗る、の4枚だ。
シートの右側に、適用先の範囲と式をそのまま書いてある。自分の表に移すときは、適用先の範囲と、式の中の列だけを書き換える。
重複の1枚には、次の式を入れてある。
=AND($A3<>"",COUNTIF($A$3:$A$16,$A3)>1)数える範囲を$で固定していないと、下の行ほど範囲が狭くなって数え落とす。この式でも、空でないことを先に見ている。空行どうしが重複と数えられるからだ。
配ったブックで塗られる行を数えると、行全体の1枚は4行が丸ごと20セル、空を除く1枚は差がプラスの2行だけ、重複の1枚は番号が同じ2行だった。
効かないときに見る順番
1つずつ見る。順番を変えると遠回りになる。
まず適用先の範囲を見る。ホームタブの条件付き書式からルールの管理を開くと、適用先の欄が出る。ここが思っている範囲と違っていたら、式は関係ない。
次に、式の基準行が適用先の先頭行と同じ数字かを見る。適用先がA3:E16なら、式は3行目で書く。
そのあとで列の$を見る。行全体を塗りたいなら、見に行く列の前だけに$を付ける。
ここまでで当たらなければ、空のセルと数値を比べていないかを見る。=““を返す式が入っている列なら、ANDで空でないことを先に見る。
最後にルールの数と並び順を見る。数が増えていたら全部消して付け直し、並び順は当てはまる行が少ないものを上にする。
経費の突合表のように、色で見つける前提の表を配るときは、この5つを作った本人が先に通しておく。受け取った側は、色が付かない理由を式からは追えない。
確かめていないこと
Excel 2016以前と、Web版のExcelでは試していない。式の展開の決まりは同じはずだが、ルールの管理の画面の見た目は違う。
条件付き書式のルールを大量に入れたときの動きの重さも測っていない。数千行に何本もルールを入れると表示が遅くなる、という話はあるが、どのくらいで遅くなるかは数えていない。
行のコピーでルールが増える動きは、貼り付けの種類によって変わる可能性がある。今回試したのは通常の貼り付けだけで、挿入貼り付けやフィルハンドルでの複写は確認していない。
