tamaです。

先日もお伝えした通り10月の勉強会で『令和最新版⚡️Excel再入門』の発表をしてきたのですが、
同日のLTでも、資料なしで Excel を操作して条件付き書式の話をしたのでその内容を紹介します。

※スクショは mac版Excel ですが、Windows版でも Web版でも（表示は違えど）結果は同じです

問題

こんな条件付き書式があるとします。

真上のセルと同じ値のとき色を付ける、というよくある条件付き書式です。
=B2=B1 に = が2回出てくるのが気持ち悪いときは、=(B2=B1) とすると理解しやすくなります。
括弧の中 B2=B1 は B2セルが B1セルと同じかどうか判定する式（結果はTRUE or FALSE）、
先頭の = はルールは「この式を使え」という指示です。

このとき、例えば、9行目を削除しても新しく B9 になったセルの 4 に色が付きません。
また、6行目に新しい行を挿入しても、新しく B7 になったセルの 7 に色が付いたままです。

ワークシート全体の条件付き書式を見ると、ルールが増殖していることがわかります。

原因

何が起こっているのか、D列に式を加えて見てみましょう。

D10 セルはすぐ上のセルを参照 =D9 していましたが、9列目を削除すると参照先を見失います。

B10（からB9になった）セルの条件付き書式も同様に、参照していた B9 セルが削除されて参照先を見失ってしまいました。
これが条件付き書式に =B10=#REF! というルールができてしまった原因です。

また、D6 セルもすぐ上のセルを参照 =D5 していました。
6行目に新しい行を挿入しても同じセルを参照し続けるので、（新しい）D7 セルの式は =D5 のままです。

これは D7 セルにとっては2つ上のセルということになります。
B6（からB7になった）セルの条件付き書式も B5 への参照を維持したままになるので、
=B7=B5 という例外ルールが作られてしまったのです。

対策

対策には OFFSET()関数 が使えます。
OFFSET()関数は、指定したセルから上下（列）左右（行）に好きなだけ移動したセルを返す関数です。

• OFFSET( 基準, 行数, 列数, [高さ], [幅] )
  • 基準 ： 起点（出発点）となるセル
  • 行数 ： 基準 から何行下を指すか（マイナスなら上を指す）
  • 列数 ： 基準 から何列右を指すか（マイナスなら左を指す）

例えば =OFFSET(B2,3,2) であれば、B2 セルから下に3行、右に2列行った D5 セルを参照することになり、
D5 セルの abc という文字列が表示されます。

これを踏まえて条件付き書式のルールを =B2=OFFSET(B2,-1,0) に変更します。

この式は B2 すなわち自分自身しか参照しないので、行や列の挿入／削除をしても式が変わってしまうことはありません。
9行目を削除すれば新しく B9 になったセルの 4 に色が付き、
6行目に新しい行を挿入すれば新しく B7 になったセルの 7 は色が消えます。
条件付き書式のルールが増殖することもなくなりました。

応用

これを応用すれば、表の大分類と中分類が同じときは表示しない、という条件付き書式も壊れないように書くことができます。
（表示を消す場合は文字色を白くするのでなく表示形式を ;;; にします）

シンプルだけれど応用次第で痒いところに手が届く孫の手関数 OFFSET()、ぜひお試しください。