Excel関数でシフト表の曜日祝日つきカレンダーを瞬時に作成する

チーム運営やバイトなどの管理でExcelでシフト表を作る機会は多いですよね。

そんな中で面倒だなと思うことは、

・都度カレンダーを作成しなければならない

・祝日設定を手動でしなければならない

ということではないでしょうか。

そこで今回は、そんなお悩みを解決する

Excel関数でシフト表の曜日祝日つきカレンダーを瞬時に作成する方法をご紹介します!



シフト表カレンダーの完成イメージ


       ▼
      月を5月から6月に変えると…
       ▼

カレンダーの完成イメージは、画像の通り、

・日付、曜日、祝日を横列に設定
・ヘッダ部の年、月を変更すると日付、曜日、祝日、背景色が変わる

とします。

 

カレンダー表以下の仕様は、

・名前欄にシフト表対象者を入力して管理
・出勤日数を自動カウントする
・勤務形態は4種類を設定
・下図で勤務形態毎に日別、月間で集計を行う

となっています。

こちらについては別記事にて説明する予定で、

今回は、スケジュール表のみ説明をします。



カレンダーの作成手順

①年、月から日付を取得する関数を指定する

まずは下準備として、シフト表のタイトルを作成します。

年、月を別々のセルに分け、A1セルに年C1セルに月となるよう指定します。
4行目以下の名前、出勤日数は今回使いませんが、ダミーで入れておいてください。
その横に、カレンダーの項目となる日付、曜日、祝日を入力します。

文字サイズや色は好みのものに変えて頂いて結構です。

続いて、タイトルの年、月の値から日付を取得する関数の指定をおこないます。

D4セルに以下関数を指定します。

=DATE($A$1,$C$1,1)

DATE関数を使ってのセルの数値を参照し、日付が表示されるようにします。

=DATE(年,月,日)

今回は日に1を指定していますので、月初1日の日付を取得するということになります。

②日付から曜日を取得する関数を指定する

D5セルに以下関数を指定します。

=TEXT(WEEKDAY(D4),”aaa”)

TEXT関数は日付から曜日を取得する関数です。

書式の記号 説明
“aaaa” 曜日の表示。例:月曜日
“aaa” 曜日を短縮形で表示。例:月
“dddd” 曜日を英語で表示。例:Monday
“ddd” 曜日を英語の短縮形で表示。例:Mon

表のように書式の記号を指定することにより取得したい曜日の表示形式を指定することができます。

今回は、”aaa“で月曜日でしたら「月」という形になる指定をしました。

次にWEEKDAY関数で、シリアル値を取得しています。

シリアル値とは、日付を数字に直したときの値です。

戻り値は以下の通りです。

種類の数値 戻り値
1/省略 1(日)~7(土)
2 1(月)~7(日)
3 0(月)~6(日)

WEEKDAY(D4)とした場合、D4の日付から該当する曜日の1から7番までを返します。

③祝日リストシートを設定する

今回のキモとなるのは、祝日です。

自動計算し祝日を割り出す方法がありますが、複雑なので、

すでに公開されている祝日リストから数年分取得し、設定することにしました。

サンプルは以下からお借り致しました。
参考:http://office-qa.com/Excel/ex176.htm

空のシートを作成し、

シート名は「祝日」とします。

そのシートに、サイトに公開されている祝日リストをコピペします。

④祝日リストからVLOOKUPで値参照する

作成した祝日リストの日付をキーにして表示されている日付と合致した場合には、
曜日下のセルに祝日を表示させるようにします。

以下の関数を設定します。

=IFERROR(VLOOKUP(D4,祝日!$A:$B,2,FALSE),” “)

VLOOKUP関数で祝日リストを参照し、日付と合致していたら祝日リストの2番目を表示させています。

もし該当がなければ “ ” 半角スペースを代入するようにしています。

⑤日付2日目以降を作成する

これまでは1日目のみを設定しましたが、次は2日目から月末まで関数を設定したいと思います。

まず日付です。

以下の数式をE4セルへ入力します。

=D4+1

これで1日プラスとなりました。

次にE4セルを選択し、AH4列までドラッグして数式をコピーしていきます。

同じ要領で曜日セルもコピーしていきます。

曜日はD5セルからコピーしてください。

祝日セルはD6セルからコピーしてください。

はい、こちらで関数の設定は完了となります。

お疲れさまでした。



カレンダーの書式「土曜は青、日曜は赤、祝日は緑」を設定する

土日、祝日になると背景に色がつくように書式設定したいと思います。

①土曜日の書式を設定する

D4セルを選択し、リボンの[ホーム]タブ>[条件付き書式]>[新しいルール]をクリックします。

[新しい書式ルール]ダイアログボックスが表示されます。

[指定の値を含むセルだけ書式設定]を選択し、

[次のセルのみの書式設定]を

[特定の文字列] [次の値を含む] “

と入力したら[OK]をクリックします。

②日曜日の書式を設定する

同じように[新しいルール]をクリックし、日曜版を作成します。

[次のセルのみの書式設定]を

[特定の文字列] [次の値を含む] “

と入力したら[OK]をクリックします。

②祝日の書式を設定する

最後に祝日の設定です。

祝日は6行目に出た値があれば書式を設定したいので、

なにか値があれば緑背景、半角スペースのみであれば書式なしという条件にします。

同じように[新しいルール]をクリックし、祝日版を作成します。

[数式を使用して、書式設定するセルを決定]を選択します。

[次の数式を満たす場合に値を書式設定]に

=COUNTIF(D6,”<>” & ” “)

と入力します。

半角スペース以外のもであれば「1」を返す数式となります。

④条件付き書式の設定範囲を指定する

リボンの[ホーム]タブ>[条件付き書式]>[ルールの管理]をクリックします。

条件付き書式ルールの管理から土日祝のルールが表示されたら、

[適用先]に以下範囲を指定します。

=$D$5:$AH$5

31日月までのセルを指定していますが、月を跨いだカレンダーであれば、この範囲を拡張させてください。

⑤書式の表示順を設定する

書式の表示順を設定します。

祝日ルールを選択し、下矢印のアイコンをクリックし、一番下にきたら、「OK」をクリックします。

今回は祝日の書式を効かせる順位を最下位にしています。



完成!

さっそく実行してみましょう。
サンプルでは2021年5月を指定していますので、同年の6月を指定してみます。

はい、変わりましたね。

年次も変えてみましょう。

2021年を2022年にします。

はい!こちらも問題ありませんね!



さいごに

いかがでしょうか。

今回は、

Excel関数でシフト表の曜日祝日つきカレンダーを瞬時に作成する方法

をご紹介しました。

年月を変えるだけで瞬時にできましたね。

また、外部で公開している祝日リストを使う方法も学びました。

サクッとカレンダーが変わるのはとても楽になりますので、

今回の方法でぜひ効率化してみてください!

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です