m-eye blog

ノンプログラマによるVBAとPythonの学習記録

カレンダーの土日祝日に色をつける方法

f:id:m-eye:20181014133332p:plain

 

祝日一覧表を作ったので

1ヶ月のカレンダーの休み(土日祝日)のセルに色をつける方法を書きます。

 

まず年月と最初の日付を入力します

どこでもいいのですが、左上のほうに入力しました。
f:id:m-eye:20181015183352p:plain

 

31日分の「日」の欄と「内容」の欄を用意して、1日の欄に関数を入力しました。

=DATE($B$2,$D$2,1)

DATE関数は「年」「月」「日」の数字から、日付型の値を作成します。「年」のB2セルと「月」のD2セルは絶対参照にして、どこから参照しても、参照するセルが変わらないようにします。
f:id:m-eye:20181015184936p:plain

 

「日」の欄には日付だけを表示したいので、「セルの書式設定」から「ユーザー定義」を選択して、「種類」の欄にdと入力すると、日付だけが表示されます。
f:id:m-eye:20181015192255p:plain

 

このように「日」の欄に日付だけが表示されました。
f:id:m-eye:20181015192828p:plain

 

次に日付を31日分入力します

2日のセルに次のように入力します。
f:id:m-eye:20181015194102p:plain

=IF(MONTH(B5)<>MONTH(B5+1),"",B5+1)

この式の意味は、B5セルの日付が表す「月」と、その1日後の日付の「月」が違うときは何も表示せず、同じときはその日付を表示する、ということです。こうすることで、表示したい「月」と違う「月」の「日」を表示しないことができます。

よくあるのは作業列を用意する方法ですが、この方法だと作業列の必要はありません。

 

最後に29日以降を修正します。

この式をコピーして、31日目まで貼りつけます。
f:id:m-eye:20181015195520p:plain いまは例として「2018年10月」を表示しているので、「日」は31日までありますが、これが「2019年2月」だったら、「日」は28日までしかありません。また、「2020年2月」のようにうるう年の2月だったら29日までしかありませんね。

 

これは実際に「2019年2月」や「2020年2月」を入力して、試してみればわかります。
f:id:m-eye:20181015201122p:plain どちらの場合も、末尾にエラーがでますが、「2019年2月」のほうがエラーが多いので、それに対応しましょう。

 

エラーの発生したセルの1つ上のセルが空白なら、エラー発生セルに空白を表示するようにしましょう。
f:id:m-eye:20181015202828p:plain

=IF(B33="","",IF(MONTH(B33)<>MONTH(B33+1),"""",B33+1))

これで日付に関するエラーは発生しなくなりました。

 

次に土日祝日の色付けをします

 

まずは日曜日

ここでのコツは、「1日の日付のセルを最初に選択すること」です。必ずそうしてください。選択したら、そのままマウスでドラッグして、31日まで選択範囲をひろげてください。
f:id:m-eye:20181015203948p:plain

ここで条件付き書式を設定します。「リボン」→「ホーム」→「スタイル」→「条件付き書式」をクリックします。
f:id:m-eye:20181015204823p:plain

 

次に、「新しいルール」をクリックします。
f:id:m-eye:20181015205452p:plain

 

「数式を使用して、書式設定するセルを決定」をクリックします。
f:id:m-eye:20181015210231p:plain

 

「次の数式を満たす場合に値を書式設定(O)」に次の式を入力します。
f:id:m-eye:20181015211415p:plain

=weekday($B5)=1

WEEKDAY関数はその日付が日曜日のときに1を、土曜日のときに7を返します。

 

書式で好きな色を選択します。
f:id:m-eye:20181015212112p:plain

 

日曜日に色がつきました。
f:id:m-eye:20181015212722p:plain

 

つぎに土曜日

土曜日も、日曜日とまったく同じように進めます。唯一ちがうのは、数式です。

=weekday($B5)=7

土曜日に色をつけたい場合は、WEEKDAY関数の戻り値が7になるようにします。

 

祝日に色をつけるには

別シート「holiday」に祝日一覧表を用意し、MATCH関数で祝日を探します。

=MATCH($B5,holiday!$A$1:$A$2000,0)>0

調べたい日付が「holiday」シートに存在すれば(MATCH関数の戻り値が0より大きければ)、色をつけます。
このとき、検索範囲は将来祝日が増えることを見越して、多めにとります。
現在、1529日ありますので、2000日もあれば大丈夫でしょう。

 

これで完成

実際のカレンダーはもっと複雑ですが、基本はこれで十分なはずです。
f:id:m-eye:20181015215037p:plain

 

会社ではよろこばれます

会社ではこういう単純だけど、かならずしなければならない作業を効率化するとたいへんよろこばれます。

エクセルの基本の条件付き書式もけっこう役にたちますね。

これで何年の何月のカレンダーでも自在につくれます。

 

Copyright 2018 m-eye blog All Rights Reserved.