【Excel】条件付き書式で数式を使用する、条件を満たしたセルのすべての行・列に書式を適用する方法

PC

今回は、Excelの条件付き書式において、数式を用いてルールを作成してみたいと思います。また、条件を満たしたセルのすべての行、列に書式を適用する方法についても説明します。

条件付き書式について、よくわからないよという方は、下記の記事を先にご覧いただくことをおすすめします。また、数式(関数)を使用しますので、Excelの関数を使ったことがある方向けになります。

はじめに

図1のような場合を考えます。図1は、2021年5月上旬の巨人、阪神、中日の対戦予定表です。

図1:例

A列が日付、B列が巨人の対戦相手、C列が阪神の対戦相手、D列が中日の対戦相手といったようなイメージです。

tayuyu
tayuyu

巨人、阪神、中日を選んだ理由は、2020年の1位、2位、3位だからです。セ・リーグの他球団、パ・リーグファンの方ごめんなさい。

はじめに、日付が入力されているセルの曜日が日曜日とき、そのセルの背景色を薄い赤色にしてみましょう。これをルール1とします。もちろんルールは、数式を使って作成していきます。

ルール1 日付が入力されているセルの曜日が日曜日とき、そのセルの背景色を薄い赤色

数式のルールについては、式の結果がTrue(1)のとき、設定した書式が適用されます。False(0)のとき、設定した書式は適用されません。

今回は、日付から曜日を求める必要があるので、日付から曜日を求める関数であるWEEKDAY関数を使用します。WEEKDAY関数については、下記に書いておきますので、ご確認ください。

WEEKDAY関数

WEEKDAY関数は、日付に対応する曜日を求める関数です。

WEEKDAY関数の書式

WEEKDAY(シリアル値, [週の基準])

シリアル値必須日付を指定します。
週の基準任意戻り値の種類を指定します。表参照
表1:WEEKDAY関数の引数
週の基準日曜日月曜日火曜日水曜日木曜日金曜日土曜日
1(または省略)1234567
27123456
36012345
117123456
126712345
135671234
144567123
153456712
162345671
171234567
表2:各曜日の戻り値の数値
tayuyu
tayuyu

日付を渡してあげるとその日付に対応する曜日の番号が戻ってくる関数ということだね。

WEEKDAY関数の使用例

下記の数式は、表2より、週の基準が「1(または省略)」、2021年5月2日が日曜日なので、結果は、「1」(日曜日)となります。

=WEEKDAY(2021/5/2)

数式を使用してルールを作成

例1

さて、条件付き書式の例題に戻りましょう。

ルール1 日付が入力されているセルの曜日が日曜日とき、そのセルの背景色を薄い赤色

図1:例

条件付き書式(ルール)を適用したい範囲を選択します。今回は、図1の表の範囲であるA1からD10セルを選択します。(日付が入力されているセルは、A2からA10なので、A2からA10を選択しても良いですが、今回は説明の都合上、A1からD10としました。)

条件付き書式から、新しいルールをクリックします。(図2)

図2:新しいルール

画面が出てくるので、「数式を使用して、書式設定するセルを決定」を選択します。(図3)

図3:書式ルールの編集

「次の数式を満たす場合に値を書式設定」の欄(図3赤枠)に次のように入力します。

=WEEKDAY(A1)=1

WEEKDAY関数については、第1引数である「シリアル値」の日付が日曜日、第2引数である「週の基準」を省略した場合、「1」が戻り値となります。そのため、曜日の判定の式は、WEEKDAY(A1)=1となります。もし、土曜日を判定したい場合は、WEEKDAY(A1)=7といったような感じで使います。

tayuyu
tayuyu

図4の赤枠には、論理式を入力するイメージだね。この論理式がTrue(1)のとき、設定した書式が適用されるってことかあ。

条件を満たした場合に適用する書式は、図3の青枠のようにしてみました。

今回、注意したい点は、「次の数式を満たす場合に値を書式設定」の欄に入力したセル番地(参照)と条件付き書式の選択範囲の一番左上のセルが番地(参照)が相対的に対応するということです。

図4は、「次の数式を満たす場合に値を書式設定」の欄を=WEEKDAY(A1)=1と入力した場合の各セルの判定条件を示しています。それぞれの式について、式の結果がTrue(1)のとき、設定した書式が適用されます。False(0)のとき、設定した書式は適用されません。

図4:各セルの判定条件のイメージ(A1基準)

もし仮に「次の数式を満たす場合に値を書式設定」の欄を=WEEKDAY(B2)=1と入力した場合を考えます。このとき、選択範囲の一番左上のセル(今回はA1)の判定条件は、WEEKDAY(B2)=1となり、選択範囲の一番左上のセルの右隣のセル(今回はB1)の判定条件は、WEEKDAY(C2)=1となります。(図5)

図5:各セルの判定条件のイメージ(B2基準)

「次の数式を満たす場合に値を書式設定」の欄に入力するセル番地(参照)によって、判定条件のセル番地(参照)も相対的に変わっていることがわかります。(相対参照)

少し長くなりましたが、図3の状態で「OK」を押すと図6のように表示されます。

図6:ルール1適用後

条件を満たしたセルのすべての行・列に書式を適用する場合

例2(すべての行に条件付き書式を適用)

次は、図7のように、日付が日曜日のセルと同じ行について、セルの背景色を薄い赤色にしたいと思います。図7は、カレンダーのような表なので、この方が見やすいですよね。

ルール2 日付が日曜日のセルと同じ行について、セルの背景色を薄い赤色

図7:ルール2適用後

さて、図7のように、日付が日曜日のセルと同じ行すべてに書式を適用するにはどうしたらいいのでしょうか。先に、結果だけ書いておきましょう。

=WEEKDAY($A1)=1

「A」の前に「$」を付けるだけです。

この「$」は、絶対参照や複合参照で使用される「$」です。セルの参照については、記事がありますので、「よくわからないよ。」という方は、下記をご覧いただければと思います。

絶対参照や複合参照がよくわからないよという方は、「$」を付けると「$」の右に付いている文字が固定されるというイメージだけ知っておいていただきたいと思います。今回は、「A」の前に「$」が付いているので「A」が固定されます。

「A」が固定されたことにより、各セルの判定条件が図8のようになります。例えば、B1セルの判定条件は、「次の数式を満たす場合に値を書式設定」の欄の式が=WEEKDAY(A1)=1の場合、WEEKDAY(B1)=1となりましたが、今回は=WEEKDAY($A1)=1なので、「A」が固定され、WEEKDAY($A1)=1となっています。

図8:各セルの判定条件のイメージ($A1基準)

少し細かい話になりましたが、英字(列を表す英字)の前に「$」を付けると、選択した範囲において、条件を満たしたすべての行に書式が適用されるという認識でも大丈夫です。

例3(すべての列に条件付き書式を適用)

ルール3 阪神と入力されているセルと同じ列について、背景色をすべて薄い黄色

最後に、阪神と入力されているセルと同じ列について、背景色をすべて薄い黄色にしてみたいと思います。(以前に使用したルールは消去しています。)

図9:ルール3適用後

今回も、先に結果を書いておきましょう。

=A$1=”阪神”

「1」の前に「$」を付けるだけです。今回の例も、先程と同じで、「1」の前に「$」が付いているので「1」が固定されます。

「1」が固定されたことにより、各セルの判定条件が図10のようになります。例えば、B2セルの判定条件は、「次の数式を満たす場合に値を書式設定」の欄の式が=WEEKDAY(A1)=1の場合、B2となりましたが、=WEEKDAY(A$1)=1なので、「1」が固定され、B$1となっています。

図10:各セルの判定条件のイメージ(A$1基準)

数字(行を表す数字)の前に「$」を付けると、選択した範囲において、条件を満たしたすべての列に書式が適用されるという認識でも大丈夫です。

tayuyu
tayuyu

条件を満たしたセルの行と列に条件付き書式を適用したい場合は、「$」を英字、数字の両方につけます。例えば、「$A$1」のような感じで使用します。

おわりに

今回は、条件付き書式のルールを数式を用いて作成してみました。例では、主にWEEKDAY関数という数式を使用しましたが、使い方次第ではいろいろなルールを作成することができます。

条件付き書式(数式の使用)を使用した記事がありますので、興味のある方はご覧ください。

コメント

タイトルとURLをコピーしました