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

学校行事やアルバイトなどのシフト管理で時間帯ごとのシフト表を作る機会は多いかと思いますが、

忙しい中で用意しなければならないことが多く、なるべく手間をかけたくないところですよね。

けど、そんな中でやっぱり面倒だなと思うことは、

ということはありますよね。

今回は、そんな面倒なことを解消する、

時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法をご紹介します！

ガントチャート付きシフト表の完成イメージ

ガントチャート付きシフト表の完成イメージは以下の通り、出勤時間帯に色が入り、さらに遅番早番などの勤務種別の文字が自動的に入るようになっています。

さらに時間帯列の初めのセルの時間を変えると、自動的に時間帯は変更され、色、勤務種別も再計算されます。
　　　　　　　▼

別のシフト表をつくる場合、タイトルや時間帯の変更だけで済むという訳で、
シフト表作成の手間がずいぶん省けますね！

では、作り方について説明をします。

ガントチャート付きシフト表の作成手順

まずは、以下のようにメンバーと出勤・退勤時間、勤務種の表を用意します。

①時間帯列を作成する

まずは時間帯列を作成しましょう。

今回は、時間帯列のはじめのセルだけを変えたら他のセルも自動的に時間帯が入力されるようにします。

時間帯は1日ですので、最大0:00～23：00となりますが、

利用状況に応じてアレンジしていただければと思います。

今回のサンプルは午前6：00を指定しますので、F2セルに6：00を入力します。

セルの書式設定は、「時刻」＞「13：30」を指定します。

続いて隣のセルの値ですが、自動掲載をさせたいので、

=F2+TIME(1,0,0)

を入力します。

これはF2セルの値に1時間を加算するという意味となります。

次に「7：00」が入力されたセルを時間帯終了列の位置までドラッグします。

はい、これで時間帯列の作成は完了です。

②ガントチャートを作成する

続いて、今回のメインとなるガントチャートが表示されるよう作り込んでいきましょう。

1番目のメンバで、時間帯のはじめとなるF3のセルを選択し、以下を入力します。

=IF(AND(F$2>=$B3,G$2<=$C3),$E3,””)

入力が完了すると、以下のようになります。

変わらない方は出勤時間、退勤時間が正確に入力されているか、セルの指定が間違っていないか確認してください。

次に関数を入力したセルを時間帯最後尾列までドラッグします。

続いて、最後のメンバの行までドラックします。

はい、勤務種の文字だけですが、ガントチャートっぽくなりましたね。

③ガントチャートに色を入れる

続いてガントチャートをより見やすくするためにセルや文字に色を付けていきましょう。

1番目のメンバで、時間帯のはじめとなるF3のセルを選択し、

リボンメニューの「条件付き書式」をクリックし、「新しいルール」をクリックします。

ウインドウが表示されたら、「指定の値を含むセルだけを書式設定」を選択し、

ルールの内容のところに「セルの値」、「次の値に等しい」を選択、「早番」と入力します。

次に、セルの色、文字の色を「書式」から指定し、「OK」をクリックします。

「適用先」の範囲を拡張し、時間帯の最後尾列、メンバの最終行の範囲を指定します。

=$F$3:$X$7

入力が終わったら、「OK」をクリックします。

はい、早番のセルの色が変わりましたね。

次に同じ要領で遅番のセルも色を付けていきます。

1番目のメンバで、時間帯のはじめとなるF3のセルを選択し、

リボンメニューの「条件付き書式」をクリックし、「新しいルール」をクリックします。

ルールの内容のところに「セルの値」、「次の値に等しい」を選択、「遅番」と入力します。

次に、セルの色、文字の色を「書式」から指定し、「OK」をクリックします。

以下を「適用先」へ入力し、ルールの範囲を拡張します。

=$F$3:$X$7

終わったら「OK」をクリックします。

はい！ガントチャートができ上りましたね！

③時間帯別の必要人数を計算する

続いて、時間帯別に必要な人数と出勤人数、過不足人数を算出するように計算式を追加します。

時間帯別の必要人数の行を追加し、任意の人数を入れていきます。

続いて、出勤人数を自動算出するようにします。

勤務種の早番、遅番となっていれば数字をカウントしたいので、

F9のセルに以下を入力し、

=COUNTIF(F3:F7,”早番”) + COUNTIF(F3:F7,”遅番”)

時間帯の最後尾列までドラッグしていきます。

続いて、過不足人数を自動計算させます。

F10セルに以下を入力し、

=F9-F8

時間帯の最後尾列までドラッグしていきます。

この時点で、過不足人数が自動計算されていることがわかるかと思います。

次に、「-1」以下となっている不足しているセルをもっと目立たせるように赤く塗りつぶしていきます。

リボンメニューの「条件付き書式」をクリックし、「新しいルール」をクリックします。

ルールの内容のところに「セルの値」、「次の値より小さい」を選択、「0」と入力します。

次に、赤っぽいセルの色、文字の色を「書式」から指定し、「OK」をクリックします。

以下を「適用先」へ入力しルールの範囲を拡張します。

=$F$10:$K$10

完了したら「OK」をクリックします。

はい！「-1」となっているセルが赤色に変更されましたね！

さいごに

いかがでしょうか？

今回は、
時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法について
まとめました！

よく利用するエクセルのシフト表。

ガントチャートもマスターしてより使い分けしたいですね！

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

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