読者です 読者をやめる 読者になる 読者になる

orz.conf

技術メモ✍

Excelでスケジュール

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

f:id:toshtone:20150802104301j:plain


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

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

f:id:toshtone:20150802104313j:plain

次に日付の入力です。

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

f:id:toshtone:20150802104325j:plain

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

f:id:toshtone:20150802104334j:plain

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

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

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

f:id:toshtone:20150802104343j:plain

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

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

f:id:toshtone:20150802104350j:plain

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

f:id:toshtone:20150802104357j:plain

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

f:id:toshtone:20150802104404j:plain

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

f:id:toshtone:20150802104409j:plain
f:id:toshtone:20150802104416j:plain


そうするとこんな感じ。

f:id:toshtone:20150802104422j:plain

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

f:id:toshtone:20150802104431j:plain

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

f:id:toshtone:20150802104445j:plain

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

f:id:toshtone:20150802104452j:plain

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

f:id:toshtone:20150802104526j:plain

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

=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ですね。

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

f:id:toshtone:20150802104555j:plain

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

f:id:toshtone:20150802104605j:plain

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

f:id:toshtone:20150802104613j:plain

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

f:id:toshtone:20150802104622j:plain

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

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

以上で完成です。

f:id:toshtone:20150802104631j:plain