エクセルで祝日に色がつかないため、質問いたします。 シフト表で土日祝日に色をつけたいので、条件付書式

エクセルで祝日に色がつかないため、質問いたします。
シフト表で土日祝日に色をつけたいので、条件付書式から行い、土日は色がついたのですが、祝日に色がつきません。 関数はcount if( $AQ $9:＄AS＄24,B＄2)＝1としています。
シフト表はA2セルに月の表示があります。
2と打てば2月と表記されます。
B2セルは＝A2&"/1"の式を入れており、2/1と表示。B3セルは＝TEXT(B2,"aaa")で曜日が自動表示されます。
C2セルは＝B +1で以降AF2までコピーしており、A2セルに次の数字を入力すれば、日付と曜日が自動表示され、土日に色がつくようになっています。しかし祝日に色がつかず、欄外AQ9からAS24まで祝日リストを作成していますが反映されません。どなたか詳しい方、ご教示頂きたくお願い致します。

No.3 ベストアンサー 回答者： virgo_o_99 回答日時： 2022/01/09 13:34

書かれているとおりに再現したところ、問題なく書式が反映されましたが？

もしかするとですが、条件式のに不要な空白が混在したり、全角文字を使っていたり、不要な " が入り込んでいたりしませんか？

この回答へのお礼

おっしゃる通り、不要なダブルクォーテーションが入っていました。勝手に入っちゃうんですね。"を外して祝日がきちんと反映されました。ありがとうございました。

お礼日時：2022/01/13 15:43

No.5 回答者： goomania 回答日時： 2022/01/09 23:24

fujillinさんのご指摘のように、

>C2セル以降は日付になっているので、祝日の判定もできているのではありませんか？
というはずなのに、ご質問者は
>しかし祝日に色がつかず、欄外AQ9からAS24まで
>祝日リストを作成していますが反映されません。
とおっしゃっています。
だとすると考えられる原因は以下の２つです。

(1)条件付き書式の数式に誤りがある
ご質問者が投稿された、「条件付き書式」の数式は「頭部イコールなし」「半角スペースあり」「全角文字・半角文字混在」というもので、常識的には数式では使用されない形です。投稿時のタイプミスだと思います。
しかし、この誤った数式は、「条件付き書式」の「数式」として設定してもエラーにならず、意味のない条件として設定されてしまいます。
AQ9～AS24の祝日一覧に(日付と曜日のように)シリアル値が２つ以上ある場合も考慮して、条件付き書式の数式はB2セルに以下のものを正しく設定して、右方向へ書式をコピーしてください。

=COUNTIF($AQ$9:$AS$24,B$2)>0

(2)祝日一覧の日付が「シリアル値」ではない、または「年号」が2022年でない、またはAQ9～AS24に「シリアル値」が２つ以上ある
祝日一覧の作成方法が不明ですが、こちらもB2セルのように「文字列」となっている可能性があります。C2セルから右は「シリアル値」になっているはずなので、祝日一覧が「文字列」だとCOUNTIF関数でカウントできません。
また、祝日一覧を「月/日」で昨年中に作成した場合、年号は2021年です。
EXCELでは年号を省略した入力の場合、「システム日付の年号が補完される」からです。
また、日付の隣に「曜日」を表示したりして、同じシリアル値が範囲内に２つ以上になっている場合も、ご質問者の数式では色が付きません。

祝日一覧を作成しなおして、B2セルをfujillinさんの回答のように「=(A2&"/1")+0」あるいは「=(A2&"/1")*1」に修正すれば解決するのではないかと思います。

以下は、このご質問の本質とは関係ない話になるので、スルーしていただいても結構ですが、B２セルを

=("2022/"&A2)*1

という数式にすることを提案します。

仮に、この祝日色付け問題が解決したとして、fujillinさんのご指摘のように、12月になって来年１月のシフト表を作成するときはどうするのでしょう?

>2と打てば2月と表記されます。

ということは、12月になって来年１月のシフト表を作成するとき、「1」と打てば「1月」と表示されるとは思うのですが、これでは「2022年1月」になってしまうと思います。

カレンダーが関係した作表を行うときは「年またぎ」を意識した数式にする必要があります。

という訳で上記のような問題を考慮すると、
(ア)A2に「月」を手入力して「日」を補完して「A2&"/1"」とする
という考えよりも、
(イ)A2に「月」を手入力して「年」を補完して「"2022/"&A2」にする
という考えの方が好ましい気がします。

(イ)のようにしておけば、A2が2のとき「=("2022/"&A2)*1」とすれば「2022/2/1」と認識されますし、「=("2023/"&A2)*1」とすれば「2023/2/１」と認識されます(つまり、必ず月初日と認識されます)ので「年またぎ」の修正が容易です。
そこで、AQ9～AS24に祝日一覧があり、ご質問者はこれを欄外とよんでいるので、AQ1も「欄外」だとすればAQ1に「2022」を入力しておけば(表示されるのが嫌なら文字色は「白色」で可)、B2セルの数式は

=(AQ1&"/"&A2)*1

とすればよいことになります。
こうすることにより、「年またぎ」の場合もAQ1の数値を新年号に修正すればよいことになりさらにメンテナンス性が高まります。

この回答へのお礼

ありがとうございました。たくさん教えて頂いた貴方をベストアンサーにしたいのですが、先の方でピンポイントのアドバイスがあったので、其方をベストアンサーにしたいと思います。ありがとうございました。

お礼日時：2022/01/13 15:41

No.4 回答者： fujillin 回答日時： 2022/01/09 13:38

こんにちは

既に指摘があるように、B2セルの値は日付ではなく、文字列になっているものと推測します。
（これに対して、祝日リストは日付で入力されている）
同様に推測ですけれど、C2セル以降は日付になっているので、祝日の判定もできているのではありませんか？

対処法としては、B2セルの値を日付にすればよいので
　=(A2&"/1")+0
あるいは
　=(A2&"/1")*1
等に変えて、表示書式を「日付」にしておけば良いのではないでしょうか？


※ ご質問には関係ありませんけれど、「月」の指定しかないので、翌年の表を作成することが難しそうですけれど・・
（年末に困ったりしませんか？）

No.2 回答者： mygoonickname 回答日時： 2022/01/09 13:11

こんにちは。

推測になりますが、
B2セルは＝A2&"/1"　→　これは、Excelが文字として認識しているのでは？
見た目は、2/1ですが、日付ではなく、ただの文字として。

＝TEXT(B2,"aaa")
曜日は、Excelが関数で自動変換してくれるから、表示されているかと。

AQ9～AS24は、日付で入力されているのでは？
例えばテストとして、AQ9～AS24をセルの書式設定を文字にして、日付を
手入力で入れてどうでしょうか？

No.1 回答者： くんこば 回答日時： 2022/01/09 13:05

日付型に変換して、それ同士で比較する。

ま、頑張って。