今回は先日公開した、万年利用できるカレンダーに祝日を自動表示する方法のご紹介です。
年と月を入力するだけで自動的に日付・祝日を表示します。
祝日の色を赤に変更し、該当月ではない日にち・祝日はグレーになります。
万年カレンダーは下記の記事をご覧ください。
今回は上記の記事から、祝日をデータベースとVLOOKUP関数を利用して自動表示し、祝日のセルのフォントを赤色に変更する「条件付き書式」の解説の記事になります。
今回の記事は「条件付き書式」を多用していきます。
noteにて、こちらの万年カレンダーに予定を書き込み・削除可能な1シートで月の移動ができる完全版カレンダーを公開中です。
土日の日付に色を付ける
前回の記事のときは、土日の表示はセルの色で対応しましたが、今回はセルの書式をコピーしていくため、土日のフォント色の変更も「条件付き書式」で設定していきます。
まずは元々日曜日であるA6セル、2020年4月5日のセルに日曜日の場合と土曜日の場合の「条件付き書式」を設定していきます。
A6セルをクリックし、「ホーム」「条件付き書式」「新しいルール」をクリックします。
「書式ルールの編集」で「数式を使用して、書式設定するセルを決定」を選択し、「次の数式を満たす場合に値を書式設定」欄に「=WEEKDAY(A6)=1」と入力します。
そして「書式」でフォントの色を赤に設定します。
WEEKDAY関数の戻り値は下記のようになります。
日曜日 WEEKDAY=1
月曜日 WEEKDAY=2
火曜日 WEEKDAY=3
水曜日 WEEKDAY=4
木曜日 WEEKDAY=5
金曜日 WEEKDAY=6
土曜日 WEEKDAY=7
同様にA6セルに土曜日の場合にフォントの色をブルーにする「条件付き書式」を設定します。
「書式ルールの編集」は下図のようになります。
「=WEEKDAY(A6)=7」とWEEKDAY関数を「7」にすることと、書式でフォントカラーをブルーにするだけです。
A6セルの「条件付き書式設定」完了後、「書式のコピー/貼り付け」を該当セル（日付セル）に貼り付けるだけです。
下図は書式のコピー/貼り付けを完了した状態です。
土日のフォントの色が変わっているのが分かりますでしょうか。
日付の下に祝日を表示する
それでは今回のメインでもある祝日を自動的に表示する設定です。
まずは祝日データを準備する必要があります。
祝日データは下記のサイトを参考にしました。
VLOOKUP関数で利用できるようにするために、日付を左端に設定し、範囲に「祝日」という名前を定義します。「祝日」に表示する名称は範囲内で「2」列目であることを確認しておいてください。
それでは、2020年4月29日の一つ下のセルD15に祝日を表示する数式を入力します。
数式は「=IFERROR(VLOOKUP(D15,祝日,2,FALSE),””)」です。
数式の意味は「もし次の数式がエラーなら非表示（””）で、エラーでなければ数式の結果である、祝日データからD15セルの値に一致する行の2列目の祝日の名称を表示してください」という意味です。
数式を入力し、確定すると「昭和の日」が表示されました。
さらにフォントの色を赤に設定しておきます。
右クリックメニューからセルのフォントの色は変更できます。
D16セルには、祝日を自動表示する数式とフォントカラーが赤に設定されています。D16セルを今度はコピーして日付の下のセルに貼り付けた結果が下図となります。
祝日が自動表示されているのがお分りいただけるでしょうか。
祝日表示の場合に日付を赤くする
続いては祝日表示された日付を赤くします。
こちらの「条件付き書式」の数式は工夫が必要です。
D15セルを選択し、「条件付き書式」「新しいルール」です。
数式は「=countif(祝日,d16)>=1」と入力します。
数式の意味は、一つ下のセルであるD16に含まれている文字列がセル範囲「祝日」に一つ以上含まれていればセルの書式を設定する、という意味です。数式を「一つ以上」とした理由は、祝日の中に振替休日を表す「休日」が複数存在するためです。
「書式」は赤いフォントを設定します。
下記のルールを確認してください。
下図のように「昭和の日」の2020年4月29日が赤く表示されました。
それではD15セルの書式を各日付セルにコピー/貼り付けをします。
見事に祝日の日付が赤くなったのが確認できます。
当該月ではない場合は祝日であってもグレー表示にする
いよいよ最後の「条件付き書式」設定です。
A18セルに下記の条件を設定します。
A1セルで設定した年月以外の日付のセルをグレー表示に書式設定します。
「指定の値を含むセルだけ書式設定」、セルの値に「次の値の間以外」を選択します。
値の範囲は、A1セル（入力した年月）とちょうど1ヶ月後-1日です。
「EDATE関数」とは、正確な1ヶ月後、3ヶ月後の日付を取得する関数です。
ご存知のように、月によっては31日であったり30日であったり、28日であったり（今年閏年で29日）と異なりますから単純に1ヶ月後を求めることはできません。その計算を自動で計算してくれるのがエクセルに標準で搭載されている「EDATE関数」です。
EDATE関数の引数は下記のようになります。
=EDATE(基準となるセル,月数)
1ヶ月後であれば月数を「1」、2ヶ月前は「-2」といったように2つ目の引数を設定します。
数式の意味はお分りいただけたでしょうか。
A19セルには、これまた数式を利用します。
ここでは「OR関数」を利用します。
一つ上の日付の値が該当月の1日より小さい日付か、もしくは該当月末より大きい日付かを満たせば書式を変更する、という意味です。
言葉で数式を説明すると「日付が（ここでは2020年4月1日）よりも小さいか、月末（ここでは2020年4月30日）より大きければ書式を変更」する設定です。
OR関数の中の数式のどれかを満たせば、OR関数は「TRUE」の戻り値を返します。これを利用して書式設定をしています。
うまく条件付き書式設定ができれば、下図のようになります。
日付をグレーにする条件付き書式を設定したセル（A18とA19）の書式を該当セルに貼り付けた状態が下図になります。
もちろん、A1セルの年月を変えれば自動で祝日が表示され、該当月以外の日付はグレー表示されます。
予定を書き込んでボタンで月を変更するにはマクロが必要
この祝日自動表示万年カレンダーに予定を書き込んで、矢印ボタンで月の移動をしても予定を表示（保存）させる方法もあります。
ただ、それにはマクロが必要になってきます。
少し記事も少し複雑になりそうですので、そちらはまた後日記事にして投稿したいと思います。
追記：まずは簡単なマクロを利用して、ボタンによる年月の移動を可能にする記事を作成しました。
追記：さらに、マクロを利用して予定を書き込むことができるエクセルファイルを作成しました。
追記：noteにて、予定が書きやすい全自動カレンダーを2種類紹介中です。
一つのシートで予定を記憶、自由自在に使いこなせるエクセル万年完全カレンダーです。
ぜひご検討ください！
コメント