土日祝日の色を変える条件付き書式 カレンダー スケジュール表(サンプルファイル有)

Excelでスケジュールを管理する時に便利なカレンダーの作り方です。

月を変更するだけで曜日や土日祝日の塗りつぶしが自動的に変わるように作れますので、ひとつ作成しておけばシートコピーで何か月ぶんも作成できますので楽チンです。

完成系は下図ように作成してみたいと思います。(一番下にファイル添付あり)

20183021

それでは作成してみます。




まず初めにその月を入力するセルを決めますが、ここでは「A1」とします。

次に「A3」に「=A1」を入力して「A1」セルがそのまま表示されるようにします。

「A4」には「=A3+1」と入力すると次の日付が表示されるようになります。

「A4」セルだけをアクティブにし、下方へオートフィルコピーをして31日ぶんまで作成します。

20183021a

31日ぶん作成すると4月のように30日までしかない月は、翌月の1日が表示されてしまいますが、まだここでは気にしなくて大丈夫です。

日付の形式が〇月〇日だと少ししつこいので、日だけの表示に変更します。

1日から31日まで入力したセル、ここでは「A3:A33」までを範囲選択します。

右クリックして「セルの書式設定」を表示し、「表示形式」タブの「ユーザー定義」を選択します。
「種類」のところに何か入力されてると思いますので、それを一旦削除し半角で「d」とだけ入力しOKします。

20183021b

これで「〇月〇日」表示だったのが「日」だけの表示になってすっきりしました。

ついでに「A1」に入力しているセルも「〇月」だけの表示に変更してしまいましょう。

「A1」を選択して右クリックしセルの書式設定を表示、先ほどと同様に「ユーザー定義」を選択します。
「種類」のところに入力されているものは一旦削除し、「m"月"」と入力しOKします。(mは半角入力)
「4月」だけの表示に変更することができました。

次に「B3」セルに「=A3」と入力し、「A3」の内容をそのまま表示させます。

「B3」セルをアクティブにしてオートフィルコピーで下方へコピーします。

20183021c

それぞれのセルはA列と同じ「日」がそのまま表示されていますので、これを曜日に変更していきます。

「B3:B33」を範囲選択して右クリックし「セルの書式設定」を表示し、先ほどと同じように「ユーザー定義」を選択します。
「種類」のところに何か入力されてると思いますので一旦削除し「aaa」と半角入力しOKします。
もし曜日にカッコをつけたい場合は「(aaa)」と入力します。

これで曜日の部分が「水」などと表示されるようになりました。

ここで一旦表の体裁を整えておきます。
列幅を調整したり罫線を入れてみたりと、好きなように調整してください。
ここでは下図のように適当に時間を分けました。
仕事で使う場合に担当者ごとや案件ごとにセルを分けるのも良いと思いますので、自由にしてください。

20183021d

次に、土日祝日が自動的に塗りつぶしされるように条件付き書式を設定していきますが、その前に別シートに祝日を用意しておかないといけません。
これについては手動で書き出すのもよし、Outlookからコピーしてくるのもよし、自由にしてください。
「祝日」シートを作成しました、こんな感じです。B列の説明は自分が分かりやすく入れてるだけなので無くてもOKです。

20183021e

では元のカレンダーのシートに戻ります。

条件付き書式で色を付けたい範囲を選択します、ここでは「A3:P33」を範囲選択します。

「ホーム」タブから「条件付き書式」の「新しいルール」をクリックしてダイアログを表示します。

20183021f

下図のように、「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定」のところに以下の式を入力します。

=WEEKDAY($A3,2)>5

「書式」ボタンをクリックして「セルの書式設定」が表示されたら「塗りつぶし」タブから好きな色を選択してOKし、最初のダイアログもOKして閉じます。
これで土日のところが色がついた状態になりました。

20183021g

※ 式を説明すると、カレンダーの「1日」にあたるセルを指定し、A列に絶対参照「$」をかけます。
絶対参照の位置が違うと、B列より右側まで色が付かなかったりしますので注意。
「,2」で種類を指定していますが、これは月曜日を「1」~日曜日が「7」までの数値に変換してることになりますので、「>5」(5より大きい)を最後につけることで土曜日と日曜日だけがTRUEとなります。
条件付き書式はTRUEのところで反応しますので、これで土曜日と日曜日だけ色が付けられるようになるということです。

もし土曜日と日曜日の色を変えたい場合は、それぞれ以下のように式を作って好きな色を指定すればできます。
土曜日 =WEEKDAY($A3,2)=6
日曜日 =WEEKDAY($A3,2)=7

 

次に祝日も色が付くように設定します。

上記と同じようにセルを範囲選択したら「条件付き書式」の「新しいルール」をクリックしてダイアログを表示し「数式を使用して、書式設定するセルを決定」のところで以下の式を入力します。
絶対参照「$」に注意です。

=COUNTIF(祝日!$A$1:$A$23,$A3)=1

こちらも上記と同じように塗りつぶしから好きな色を選択してください。
これで祝日にも色が付くようになりました。

※式を説明すると、事前に作成しておいた「祝日」シートの中にA列と同じ日付が1つあればTRUEを返す、というものです。

ここで気になるのが祝日と土日の色の重なりです。
下図を見て頂くと分かりますが、日曜日が祝日の色になっています。

20183021i

これは土日に祝日が重なってる場合に、祝日の色設定の方が優先されてしまっている状態です。
気にしない人はこれでも良いですが、土日はあくまでも土日の色にしておきたい場合は簡単に調節できます。

下図のように、「条件付き書式」のところから「ルールの管理」をクリックしてダイアログを表示します。
「書式ルールの表示」のところから「このワークシート」を選択しますと、シート内で作成した全ての条件付き書式が表示されます。(誤って不要なものを作成してないか確認するときにも便利です)

最初に設定した土日の条件付き書式が一番下にありますので、これを選択し「▲」ボタンをクリックして順番を入れ替えます。
OKします。

20183021h

優先順位が変更されましたので、土日の色が反映するようになりました。

20183021j

ここまでで完成に近くなりましたが、最後に「31日に満たない月」の日の表示をどうにかしたいと思います。

翌月の日が表示されないように、これも条件を設定していきます。

セルを範囲選択したら、ここでは「条件付き書式」の「ルールの管理」を表示してしまいましょう。
「書式ルールの表示」では「現在の選択範囲」のままで良いです。(最初にセルを範囲選択してるので)
「新規ルール」をクリックします。

20183021k

すると下図のように、「新しい書式ルール」ダイアログが表示されるので、「数式を使用して、書式設定するセルを決定」のところで以下の式を入力します。
絶対参照「$」に注意です。

=MONTH($A$3)<>MONTH($A3)

※ 式を説明すると、「1日」の「月」と、それぞれのセルの「日」の「月」が異なっていたらTRUEを返します。

20183021l

「書式」ボタンをクリックして、「塗りつぶし」タブでを選択し、さらに「フォント」タブでも文字の色をに選択してOKします。
「新しい書式ルール」のダイアログもOKすると「ルールの管理」ダイアログに戻ってきます。

20183021m

条件の順番が上図のように間違いなければOKします。

これで土日祝日と、翌月の日、すべてに色が反映するようになりました。
下図を見ると翌月の日の部分が消えましたね。

20183021n

「A1」セルに、5月を表示したかったら「5/1」、6月を表示したかったら「6/1」などと入力すると、日や曜日や塗りつぶしの箇所が自動的に変更されることを確認してください。

これで出来上がりです。

★以下のリンクをクリックし、次のページでも「karenda」をクリックするとExcelのサンプルファイルをダウンロードできます。

karenda