休日テーブルは、進捗管理表や就業計画表など日付が関係する様々なドキュメント休日を判断するために必要となります。
テーブルとして必要な情報は日付を羅列するだけで充分です。
また、今年(2019年)のように特別な理由がない限り、国民の祝日自体は決まった日でありますし、日曜日と重なった場合の振り替え休日の有無が変わるぐらいであり、わざわざ条件付き書式を作ってもご購入いただくのは難しいことと存じます。
そこで単なる日付の羅列だけではなく、条件付き書式を使い12ヵ月カレンダーの形でビジュアルに表現する形にしてみました。
なおExcelのファイルメニュー→新規で表示されるテンプレート一覧の中に「学校用カレンダー(任意の年)」というテンプレートがあります。
これはシート上一列に10月から9月までのカレンダーを並べた形ですが、それをギュッと圧縮して1画面4列×3行で12ヵ月分のカレンダーをコンパクトに表示しています。
書式の仕様について
- 数式と条件付き書式を使用して2019年から2045年の12か月カレンダーを表示することができます。
- 単に国民の祝日だけではなく、年末年始休暇や夏季休暇など様々な休日をテーブルに登録できるようにしています。
- 国民の祝日とそれ以外の休日が区別できるような項目を設定しています。
- 国民の祝日は太赤字
- その他の休日は太オレンジ色
- 条件付き書式設定により土曜日の場合は青字、日曜日の場合は赤字で表示されます。
- カレンダーの週始めの曜日は指定できるようにしています。
- 日曜日でも月曜日でもお好きな曜日から表示できます。
- 下記のサイトを参考にして、2019年から2045年までの国民の祝日をテーブルにセットしています。
カレンダー表示に必要な数式
12ヵ月のカレンダーは数式だけで表現されていますので、基本動作としては万年カレンダーになります。
条件付き書式につきましては共通で設定できる部分と、同じ内容でも各月ごとに設定しなければならない部分があります。
それに対して使っている数式は各月基本的に同じものになるので、1ヵ月分作れば数式についてはぼほぼコピペすることが可能です。
唯一変えなければならない部分は、各月を表示している最初の行になります。
幅の狭いセルに日付型の項目を表示される際の注意点は「短い幅のセルに関数で日付を表示させる方法と注意事項」をご参照ください。
実際の1月の数式は下記になります。
=TEXT(DATE($B$1,1,1),"yyyy年m月")
このDATE関数での月指定の引数が変更しなければならない部分です。
週始めの曜日を変えることができるようにしているため、曜日の表示はカレンダー最初の行の日付をTEXT関数で曜日に変換させています。
=TEXT(F3,"aaa")
各月のカレンダーを表示するにあたり、一番ポイントとなるのは各月の最初のセルの数式です。
各月の1日の情報は最初に掲示した数式で求められているので、WEEKDAY関数で1日が何曜日にあたるのか?を調べることでカレンダー最初のセルの日付を求めています。
実際の数式は下記になります。
=F1-(WEEKDAY(F1,$B$2)-1)
後は、「前日+1」をすることで1ヵ月分のカレンダーの日付をすべて埋めることができます。
なお今回は、当月外の日付の部分は条件付き書式で文字色を背景色と同じ色にすることで見えなくしています。
日付として残しておきたくない場合は、IF関数で前日と当日で月が変わっていたら空文字にするような数式を設定する必要がありますが、今回はしていません。
2045年までの国民の祝日データを集めていて気が付いたこと
Excelの話からそれますが、国民の祝日が日曜日と重なる場合は、1年間の祝日の日数は増減がありませんが、土曜日がお休みの方にとっては、土曜日が国民の祝日になると、トータルでの休日日数が減少することになります。
そこでまず年間を通して土曜日と祝日が重なることが無い年を調べてみました。
該当するのは「2020年、2026年、2027年、2037年、2044年」で、2045年までに5回あります。
それ以外の年は土曜日が国民の祝日と重なる日が1回以上存在します。
その中でもっとも重なる回数が多いとしては2040年でした。
回数はなんと5回です。
話を元に戻します。
カレンダーで休日の日付の色を変える条件付き書式
休日テーブルの項目は「No、日付、曜日、休日名」の4項目にしています。
これはコピペがし易くなると考えて、国民の祝日を参考にさせていただいているサイトの表示の仕方に合わせているためです。
そのため、国民の祝日かそれ以外かを判断するための項目としてはNo(数値)が「入っているか、いないか」で実装しています。
実際に条件付き書式で設定している数式は下記になります。
=IF(COUNTIF($B$4:$B$500,F3)=1,ISNUMBER(INDEX($A$4:$B$500,MATCH(F3,$B$4:$B$500,0),1)),FALSE)
※$B$4:$B$500は日付のセル範囲、$A$4:$B$500はNoと日付のセル範囲になります。
テーブルの項目の並び順から、VLOOKUP関数では日付からNoを引くことはできません。
そこで下記のサイトを参考にさせていただき、MATCH関数とINDEX関数を使用して「No」の情報を取得しています。
以上、最後までご一読いただきありがとうございました。
