以前、条件付き書式をうまく使って土日のフォントカラーを自動的に変更する手法を紹介しました。
しかしこれだけでは祝日がカバーできていない！！
できれば祝日も日曜日のように自動的に赤くならないものか・・・
ということで、祝日も自動的に曜日が赤いフォントになるようなカレンダーを作ってみました。
まずは休日のリストを作る
内閣府のHPなどから国民の休日データがCSVファイルとしてダウンロードできるので、そういったデータなどを用いて、まずは休日の一覧を作ります。
とりあえず、2024年度分の休日を準備しましたが、会社などによっては、お盆や年末年始の休日などを追記すると良いでしょう。
カレンダーの基礎を作る
1年度分のカレンダーを作っていくのであれば、まずはそのベースを作ってから条件付き書式の設定をしていくと良いかと思いますが、説明の都合上、5月分だけ用意して進めていきます。
上図は以前に作ったもので、既に土日の条件付き書式が設定されています。
これだけでは物足りないので、ここからさらに休日に該当する曜日のフォントも赤くしようと思ったのが事の始まり。
条件付き書式の設定をする（休日の曜日のフォントを赤くする）
準備が整ったところで、休日の曜日のフォントを条件付き書式を使って赤くしていきます。
条件付き書式を設定するには、設定したいセルを選択した状態で、
表示形式 → 条件付き書式
を選択します。
条件付き書式を選択するとサイドメニューが下図のように表示されます。
そして、「+条件を追加」を選択して条件を設定していきます。
まずは、復習も兼ねてですが、土日に該当する場合にフォントカラーを青や赤にするには・・・
（例えば土曜日を青くするとするには）
「＋条件を追加」を選択したあとに上図のように設定します。
重要なポイントは、書式ルール
カスタム数式を選択し、その下の欄に
=WEEKDAY(B1)=7
と記述し、書式設定のスタイルでフォントカラーを青に設定すると、土曜日となるセルのフォントが青くなります。
これは、WEEKDAY関数の引数で指定した日付が土曜日の場合「７」という値を返すことを利用した設定になります。
同様に、日曜日の場合はWEEKDAY関数は「１」を返すので、
=WEEKDAY(B1)=1
とすることで、日曜日となるセルの書式を変更することができます。
WEEKDAY関数などの日付系関数の使い方は以下の記事でも掲載しています。
前段が長くなりましたが、条件付き書式のカスタム数式を使うことで、条件に一致したセルの書式を変更することができます。
では、休日の条件をどのように設定するか？
これに気付けば設定は簡単です。
『最初に作った「休日のリスト」と指定した日付が一致したら特定の値を返す』
休日のリストには同じ日付は存在しません。
つまり、COUNTIF関数を使えば、日付が一致したときに必ず「1」という値を返します。
COUNTIF関数は「範囲」で指定したセルの範囲の中から、検索条件で指定した条件に当てはまるセルの数をカウントしてくれる関数です。
そこで、条件付き書式のカスタム数式を下図のように設定します。
範囲に適用の部分は、今回は曜日の列を指定したいのでB2:B32とします。
そして、書式ルールをカスタム数式にして、その下の欄に下記のように記述します。
=COUNTIF($E$2:$E$22,$B2)=1
「$E$2:$E$22」は休日のリストの範囲です。「$」付けて絶対参照にしましょう。
「$B2」は曜日が入っているセルです。
実際には曜日しか表示しないように表示設定をしているだけで、中身は日付が入っています。
※曜日のみ表示しているセルは、上図のような表示形式の設定をしています。
なので、日付どうしが比較され、一致した場合にはその個数をCOUNTIF関数は返してくれます。
そして、前述のとおり、休日のリストには同じ日付が複数存在しないので、
一致する値がある ＝「1」を返すということになります。
最後に =1 としているので、「1」が返されたら（休日のリストと一致した日付があったら）
という条件を満たしたときに書式設定を変更する。
という設定ができます。
これで、土日だけではなく、休日も曜日の入ったセルのフォントを赤くすることができます。
条件の順番に注意
一つのセルに対し、条件付き書式の設定が複数ある場合は少し注意が必要です。
下図のように土曜日を青くする設定が上にあると・・・
このように、上にある条件が先に満たされるので、5月4日を祝日で赤くしたくても、青いフォントに設定されてしまいます。
なので、祝日の場合は赤くなるように優先順位を上げたい場合は、下図のように設定する必要があります。
条件付き書式設定ルールは、上位に設定されているものが優勢となるので、注意してください。
カスタム数式の注意点
私の書き方が悪いのか？とだいぶ悩みましたが、どうやらカスタム数式では別なシートを指定した書き方は出来ないようです。
今回の例の場合、休日のリストは別なシートで管理したい（メインのシートには邪魔な感じが）と思ったのですが、
シート名をつけてCOUNTIFの「範囲」を指定すると、下図のように「計算式が無効です」と表示されてしまいます。
エクセルでは別シートでも問題なく設定できるのですが、どうやらGoogleスプレッドシートではできないようです。
（何か特殊な書き方があるのか・・・）
まとめ
最後に「カスタム数式では別シートを参照できない」というモヤモヤが残りましたが、休日の曜日も自動的にフォントが赤くなるように条件付き書式の設定が出来たのでとりあえず良しとします。
多用しすぎると煩雑になりかねない条件付き書式ですが、土日休日の色付けが自動的に行われる設定は、いろんな場面で活かせるのではないでしょうか？
他にも応用を利かせた使い方がいろいろ考えられそうですね！
コメント