Excel条件付き書式の数式における相対参照と絶対参照の使い分け

「知っていると得するかもしれないExcelの癖」シリーズ第十七回、テーマは「条件付き書式の参照設定」になります。
Excel 2016バージョン1903を使用して確認しています。

なかなか説明を読んだだけで条件付き書式の参照設定について理解するというのは難しい事であると認識しています。

となりますと読んで覚えるというよりも「トライ&エラー」で試行錯誤を繰り返しながら「やりたいと思う事にたどり着く」のが手っ取り早い方法であると存じます。

という事で、実際に試しながらご一読をいただければ幸いです。

条件付き書式を使う問題－その1

突然の話で恐縮ですが、つぎのような問題を条件付き書式を使って実現したいのですが、そのための数式はどのようになるでしょうか?

一行目の偶数列に1から順番に数字が入っています。
(左図のように1列おきに数字が並びます。)

一列目A列には二行目A2セルから行番号に従い1から順番に数字が入っています。
(左図のように「行番号-1」の数字が並びます。)

左図のように、この2つの数列の交わるセルに背景色を付けるための条件付き書式の数式はどのようになるでしょうか?

【答え】

=B$1=$A2

答えは一通りではないことと存じますが、本テーマの説明で使用する数式は上記になります。

なお、この例題では「数字が順番に並んでいる」としましたが、上記の数式を使うと数字の順番は関係がなくなります。
同じ数式を使って下図も実現することができます。

ところで上図、左図ともに条件付き書式の「適用先」は下記のように設定しています。

=$B$2:$K$6

適用先の指定の仕方も一通りでは無い事と存じますが、細かく適用先のセルを指定するのではなく、セル範囲で指定できた方が維持管理する上では楽です。

このように、数式とそれを適用する適用先との組み合わせが条件付き書式における相対参照と絶対参照の使い方を理解する上でのポイントとなります。

数式の書き方

そもそも「何でイコールが2つ並ぶような数式の書き方なのか?」しっくりこない方もいらっしゃることと存じます。

上記の数式をIF関数で書くとすると下記にようになります。

=IF(B$1=$A2,TRUE,FALSE)

「このような省略を良しとするか?」ですが、これは「可読性」、「数式の解り易さ」、「数式を簡潔にまとめる」など様々に視点から答えを出す必要がありますが、「良い」「悪い」というよりもコーディングのルールとして省略形を「認めるか」「認めないか」で決めるべき話であると認識しています。

なお本サイトのコンテンツでは省略形を採用していますが、その理由としては

• IF関数よりも早く書ける
• 慣れるとシンプルで解り易い

という点になりますが、「認めるか」「認めないか」は、数式をメンテナンスする人が同じレベルで省略形を理解しているか?どうかで決めるのが良いと思います。

適用先をなぜセル範囲にするのか?

適用先をセル範囲にすると、そのセル範囲に存在するすべてのセルで条件付き書式に設定されている数式が働くことになります。
となると、それなりの数式処理の負荷がかかることになり、「なぜそのような無駄な動きをさせるのでしょうか?」と疑問に思われるかもしれません。

そもそもの前提の話になりますが、適用先を細かく指定するのであれば、条件付き書式を使うまでもなく、特定セルに書式を設定すればよい話になります。

つまり条件付き書式を使う必要があるのは、条件によって書式を動的に設定する必要があるケースであると認識しています。

そういう意味では、今回の例題の出し方が悪いとは思いますが、いきなりランダムな例題で始めてしまうと、少し敷居が高くなってしまうので、ランダムの話は後出しにいたしましたこと、ご容赦ください。

条件付き書式において数式を設定した時のセル位置は重要

基本的な話で恐縮ですが、「どの場所で数式を設定するか?」は、セルとの相対参照と絶対参照の関係を決めるうえでとても重要な要素になります。

数式を設定した場所は、適用先のセル範囲での一番左上隅のセルになりますが、実は先ほどの数式のなかにも左上隅のセル番地が含まれています。

これはたまたま数式に左上隅のセル番地が含まれているだけで、常に含まれる訳ではありませんのでご注意ください。
この数式を最初の問題で読み解いてみること下記のようになっています。
左から順に1、2、3の場合について説明しています。

赤いマーカーの部分は絶対参照になりますから、どれも変わりがありません。
それに対して相対参照の太字の部分は、それぞれの適用先セルのセル番地によって変わって行きます。

実際に試してみながらご確認いただければ幸いです。

条件付き書式を使う問題－その2

つぎに、先ほどの問題では「2つの数列の交わるセルに背景色を付ける」でしたが、今回はそれに加えて、左図のように、その右隣のセルにも背景色を付ける時の数式はどのようになるか?を考えてみてください。

【答え】

=OR(B$1=$A2,A$1=$A2)

IF関数を使用する場合は、次のようにすることもできます。

=IF(B$1=$A2,TRUE,IF(A$1=$A2,TRUE,FALSE))

答えとしては、このように1つの条件にまとめるやり方と、もう一つ別の条件付き書式を作り、同じ適用先のセル範囲に設定するやり方と2つのパターンがあります。

新たに作る場合の数式は下記になります。

=A$1=$A2

どちらでも結果は同じになりますが、まとめられる場合はまとめてしまった方が管理は楽です。
しかし、説明としてはまとめてしまうと解り難くなりますので、「=A$1=$A2」を使います。

いきなり、この数式だけでてくると「訳が分からない」ことになりますが、「現在カーソル(フォーカス)が当たっているセルがどこか?」を抑えて置けば、理解し易いことと存じます。

現在のセルはB2セルです。
従ってB2セルではこの数式成り立ちません。(A$1セルの値は空、$A2セルの値は「1」で等号にはなりません)

この数式が成り立つのは、その右隣にカーソル(フォーカス)が移動した時でつまり、C2セルでこの数式がどうなるか?を考えてみてください。
C2セルでは次のようになっているはずです。

=B$1=$A2

これは最初の数式の答えになりますから、数式が成立してC2セルの背景が設定されることになります。

数式に相対参照しか設定されていない時は、条件付き書式はひとつにまとめることができます

シート上に複数の表が配置されている時に、それぞれの表に設定する条件付き書式が、もしも相対参照だけを使用した数式になっている場合は、ひとつの条件に対して適応先を複数指定することで実装することができます。

一つのシートに複数の表が配置される事は少ないかもしれませんが、知っていると便利です。

なお適応先にセル範囲を複数にするためは、セル範囲をカンマで区切る必要があります。

=セル範囲1,セル範囲2,セル範囲3…

まとめ

説明の仕方が悪くして恐縮ですが、今回の例題の数式の意図することが何となく腑に落ちれば、条件付き書式における相対参照と絶対参照の使い方の糸口がつかめることと存じます。

実際に本サイトのコンテンツを作成する時も、「こんな数式になるのでは?」というざっくりした形からスタートして、試行錯誤をしながら仕上げて行くというのが本音です。

皆様もぜひ何かの折に条件付き書式を試していただければ幸いです。
以上、最後までご一読いただきありがとうございました。