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

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

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

ですね。

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

をご紹介します！

タカヒロ

業務ごとにカレンダーテンプレートを用意しておくとすぐ使えますのでオススメです。

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

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

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

とします。

タカヒロ

カレンダー表以下の内容については、別記事にまとめましたので、
よろしければご参照ください。

Excelで日別シフト表を自動作成する方法！土日祝日定休日カレンダー付き！

カレンダーの作成手順

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

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

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

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

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

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

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

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

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

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

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

タカヒロ

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

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

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

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

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

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

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

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

タカヒロ

自動計算し祝日を割り出す方法がありますが、複雑なので、
すでに公開されている祝日リストから数年分取得し、設定することにしました。

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

空のシートを作成し、

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

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

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

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

タカヒロ

祝日以外に定休日や創立記念日など業務に応じた設定ができます。

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

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

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

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

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

まず日付です。

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

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

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

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

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

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

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

お疲れさまでした。

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

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

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

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

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

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

［次のセルのみの書式設定］を

と入力します。

「プレビュー」横の「書式」をクリックし、青系の背景色を指定し、

プレビューで配色を確認したら［OK］をクリックします。

タカヒロ

色はお好きな色で構いません。

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

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

［次のセルのみの書式設定］を

と入力します。

「プレビュー」横の「書式」をクリックし、今度は赤系の背景色を指定し、

プレビューで配色を確認したら［OK］をクリックします。

②祝日の書式を設定する

最後に祝日の設定です。

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

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

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

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

［次の数式を満たす場合に値を書式設定］に

と入力します。

タカヒロ

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

「プレビュー」横の「書式」をクリックし、緑系の背景色を指定し、

プレビューで配色を確認したら［OK］をクリックします。

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

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

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

［適用先］に以下範囲を指定します。

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

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

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

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

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

完成！

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

はい、変わりましたね。

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

2021年を2022年にします。

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

さいごに

いかがでしょうか。

今回は、

をご紹介しました。

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

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

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

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

タカヒロ

時間帯別のシフト表もご用意しました。とてもカンタンに作れますね。

Excelで時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法！休憩時間つき！