Excelでスケジュール

たまにこのような表を作成する機会があります。

土日と祝日の色を青や赤に自動で変える方法どうだったかなと
毎回忘れてしまうのでここで整理したいと思います。

まずはこのように手動で入力

次に日付の入力です。

1日(月の初日)を表示させるセルに下記を入力します。
=DATE(B2,D2,1)

その次の日からは+1で対応していきます。
31日までコピーしてこうなります。

続いて、日付の隣に曜日を表示させます。

曜日のセルにこのように記載します。

=CHOOSE(WEEKDAY(F4,1),"日","月","火","水","木","金","土")

WEEKDAY("2015/8/1",1)で、日曜日なら１、月曜日なら２、～～、土曜日なら７を取得できます。
CHOOSE(1,"日","月","火","水","木","金","土")としてやると"日"が取得できます。
一番目の引数が5なら"木"になります。
(セルの書式をaaaとしても同じことが可能です。)

作成したものを31日目までコピーするとこんな感じ

続いて土日に色を付けます。
条件付き書式を使います。

土曜日の色を水色にします。
=$G4="土"

適用範囲を日付と曜日の表示セル全てにします。

そうするとこんな感じ。

日曜日も同様に設定してやりましょう。

次は祝日設定です。
祝日を定義してやりましょう。
邪魔にならないセルに移動します。

お盆の週は全て休みという設定にします。
そして定義したセルに名前をつけてやります。

セルにつけた名前の管理は数式バーの名前の管理から行うことが出来ます。

それでは設定です。
条件式は下記です。

=NOT(ISNA(MATCH($F4,Holidays,0)))

まずMATCH($F4,Holidays,0)ですが
F4のセルがHolidaysにマッチすると2015/8/10なら1が、2015/8/14なら5が返ります。
もし存在しなかったら#N/Aが返ります。

次にISNA("")ですが括弧内の値が#N/Aであればtrue、そうでなければfalseが返ります。

NOTは否定ですね。

つまり=NOT(ISNA(MATCH("2015/8/1",Holidays,0)))の場合はfalseとなるわけです。
=NOT(ISNA(MATCH("2015/8/10",Holidays,0)))はtrueですね。

反映するとこうなります。

日付のフォーマットを変更しましょう。
dにするだけです。

31日が無い月の判定対応をしていきます。
まず邪魔にならないセルに先ほどおこなった日付出力を行います。

元々の日付のセルの式を修正します。
=IF(MONTH(AZ4)<>MONTH(DATE($B$2,$D$2,1)),"",AZ4)
これで、指定した月でない値が入っている場合表示されないことになります。

これを31日まで反映させると、うるう年にも対応したものになります。

曜日のほうもちょっと直せばすぐ対応できると思います。
日付が空であれば曜日を空にする条件を追加するだけですね。

以上で完成です。