excelの条件付き書式の使い方が良く分からないためお伺いしたい

Question

お世話になっております。

今現在

　　A　B　C　　D　　E　　F　　G　　H　I　J　K……　　　R　　S

１　　　　　　　　　　　　　　　　　　　　　　　　　　55期　8月

２

３　　　　4月　5月　6月　7月　8月

４　　50期

５　　51期

６　　52期

７　　53期

８　　54期

９　　55期

10

上記のようなexcelがあった際に

R1の期とS1の月によって（この場合は　55期8月　つまりG9）
のセルを黄色くしたいと思っております。

実装方法を知りたくてグーグルさんで調べたのですが

検索方法が悪いのかイマイチ分からないのです……

とりあえず　B列の55期　の部分を黄色くしてみようと
B列を選択した後　ホーム＞条件付き書式＞新しいルール＞数式を使用して、書式設定するセルを決定　で　次の数式を満たす場合に値を書式設定

にて　数式:＝＄R$1　書式　黄色　　適用先=$B:B

なのですが　B　列で　55期の部分が　黄色くなってくれません……

将来的にはG9を黄色くしたいのですが

まずどうしてB9の55期が黄色くならないのかお伺いしてよろしいでしょうか？

大変基本的な事で申し訳無いのですが何か情報をお持ちな方はコメント頂けますと嬉しく思います。

Answer 1

以下、少し厳しい書き方になりますが、お許しください。

まず、気になった問題点と対策を列挙してみます。

1. 月の判定部分で A5 を指定しているのが意味不明です。
   A3 ならまだ分かるのですが、A5 でうまく機能するようには思えないのですが・・・。
   「適用先」の指定を間違えてはいませんか？。
   もしかして、最初に提示されている表ではなく、1行目はそのままで 2行目以降を 2行分ほど下にずらした新たな表をお使いなのでしょうか？。それならば A5 でも良いかと思うのですが・・・。

取り敢えず、A5 で正しいとして（新たな表を対象としているとして）、話を先に進めます。 2. 新たに提示されている数式中で相対参照を使っている箇所は、複合参照にしなければなりません。
数式がうまく機能しなかった最大の原因は、ここにあるかと思います。

・相対参照は、セル参照箇所で $ を一切付けずに参照指定する方法です。
例えば、A1、B3、C5 などのように指定します。
「条件付き書式」に書き込む数式のどこかに A1 と指定した場合、「適用先」のセル参照位置が 2行下かつ 3列右に移動すると、A1 は D3 に変化（移動）することになります。

・複合参照は、セル参照箇所で列指定部分または行指定部分にのみ $ を付けて、列位置または行位置を固定して参照する方法です。
例えば、$A1、$B3、$C5 などのように指定すると列位置のみ固定して参照され、A$1、B$3、C$5 などのように指定すると行位置のみ固定して参照されます。
「条件付き書式」に書き込む数式のどこかに $A1 と指定した場合、「適用先」のセル参照位置が 2行下かつ 3列右に移動したとしても、$A1 は $A3 に変化（移動）するだけです。
また、「条件付き書式」に書き込む数式のどこかに A$1 と指定した場合、「適用先」のセル参照位置が 2行下かつ 3列右に移動したとしても、A$1 は D$1 に変化（移動）するだけです。

もうお分かりかと思いますが、提示されている数式を以下の数式１のように書き直すことで、取り敢えず、「書式」が設定できるようになるはずです。
・数式１
=AND($B1=$R$1,A$5=$S$1)

※ただし、A$5 の箇所は、最初の表を対象とした場合には A$3 としなければ正常に機能しません。
※この場合の「適用先」は、お書きになられているようにシート全体を指定しています。

これ以外に気になる点ですが、「適用先」にシート全体を指定し、上記の数式１を組み合わせた場合、どうしても無駄な計算処理が多くなるかと思います。

そこで、表中の「月」の並びが 4月から 3月の 12ヵ月分だとして、「期」のほうは多めに指定するとして例えば 1000行分で良いのでしたら、「適用先」を『 $C$6:$N$1005 』と指定し、数式も以下の数式２のように書き直せば、かなり無駄が省けるかと思います。

・数式２

=AND($B6=$R$1,C$5=$S$1)

※ただし、最初の表を対象とした場合には、「適用先」を『 $C$4:$N$1003 』とし、数式も『 =AND($B4=$R$1,C$3=$S$1) 』としなければ正常に機能しません。

最後に、もう一つ気になることがあります。

「条件付き書式」では、場合によってですが、数式中の AND 関数や OR 関数が正常に機能しない場合があるようです。（特に OR 関数にはご注意ください。）

数式２を例にとると、

=AND($B6=$R$1,C$5=$S$1)

といった書き方ではなく、

=($B6=$R$1)*(C$5=$S$1)

という書き方のほうが無難です。

今回の場合は大丈夫なようですが。

以上です。

ご参考になれば幸いです。

＜文章の修正＞2021/10/6 追記

上記の文章中で「列」と書かなければならない箇所を「桁」と書いてしまっていました。

該当箇所を修正いたしました。大変失礼いたしました。

Answer 2

ひまじん　様

コメントありがとうございます。

仰るように

　期の方は　=B1=$R$1　の書式　

　月の方は　=A5=$S$1　の書式

と期と月の書式設定は出来るようになったのですが

その交差した所の書式設定したいのですが

その場合は

シート全部選択して

条件付き書式＞数式を使用して、書式設定するセルを決定

=AND(B1=$R$1,A5=$S$1)

としても上手く行きませんでした……

考え方がどのように誤っているかアドバイス等頂けないでしょうか？

お忙しい所大変申し訳無いのですが上記何かコメント頂けますと嬉しく思います。

Answer 3

こんにちは。

「条件付き書式」で「数式を使用して、書式設定するセルを決定」を選択した場合、「次の数式を満たす場合に値を書式設定」欄に入れる数式が TRUE の場合のみ「書式」が設定されます。

なので、「次の数式を満たす場合に値を書式設定」欄に入れる数式は、TRUE か TRUE 以外（ FALSE やエラーなど）を返す何らかの判定式である必要があります。

提示されてる数式を修正するならば、『 =B1=$R$1 』というような形の判定式（ B1=$R$1 が TRUE か FALSE かの判定式）にすれば、正常に「書式」が設定されます。

数式中で $ を付けずに単に B1 と書いているように、セル参照を相対参照（場合によっては複合参照）にすることで、「適用先」が指し示す全てのセルに対応した数式となりますので、上記の数式１つを書き込んだ「条件付き書式」１つを作成するだけで、ご希望のように B列のセル範囲から R1セルの値と等しいセルを選び出して背景色を付ける、といったことが可能になります。

手元の環境（ Windows10 と Excel2016 の組み合わせ）で動作確認済みです。

以下、「条件付き書式」の動作の考え方について書いてみましたが、あくまで私見を交えた考え方ですので、興味がなければ読み飛ばしてください。

「条件付き書式」では、「適用先」と数式は以下のように関連して動作すると考えてみてください。

• 「適用先」が指し示すセル範囲のセル１つずつが順番に書式設定するかどうか評価される。
• 「適用先」で評価されるセルが移動するのに合わせて数式中の相対参照箇所も移動する。
• 数式が TRUE となった場合のみ、その時「適用先」で評価されているセルに対して「書式」が設定される。

例えば、数式が『 =A1=$R$1 』で「適用先」が『 $B:$B 』だった場合、

1. 「適用先」の B1セルが評価され、A1=$R$1 が TRUE の場合のみ、B1セルに「書式」が設定される。
2. 「適用先」の B2セルが評価され、A2=$R$1 が TRUE の場合のみ、B2セルに「書式」が設定される。
   この場合、数式中の相対参照箇所が移動していることにも注目してください。
3. 「適用先」の B3セルが評価され、A3=$R$1 が TRUE の場合のみ、B3セルに「書式」が設定される。
   この場合、数式中の相対参照箇所が移動していることにも注目してください。
4. 「適用先」の最後のセルまで同様の評価が行われる。

ただし、目に見えるのは最終結果だけです。こういった動作の一つずつが目に見えるわけではありません。

なので、ここに書いていることは最終結果から推測される動作の考え方の一つにすぎませんので、その点はご了解ください。

最初にご紹介している『 =B1=$R$1 』という数式は、こういった考え方に基づいて組んでいます。

ご参考になれば幸いです。