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

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

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

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

・毎回時間帯のスケジュール表をつくることは面倒

・参加する時間帯に色をつけたいがやり方がわからない

・ガントチャート形式のシフト表がほしいけどよいテンプレートがない

・30分や15分単位で表示できるガントチャート形式のシフト表がほしいけどやり方がわからない

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

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

・時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法
・ガントチャート付きシフト表へ休憩時間を追加する方法
・ガントチャート付きシフト表の表示単位を変更する方法

についてまとめます!

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

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

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

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

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

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

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

①時間帯列を作成する

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

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

時間帯は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」となっているセルが赤色に変更されましたね!

ガントチャート付きシフト表へ休憩時間を追加する

シフト表へ出退勤の時間のほかに休憩時間を追加します。

こちらが完成イメージです。

タカヒロ
タカヒロ
利用者様より休憩時間の追加に関するの質問がありましたのでこちらにその方法を追記します。

①休憩時間を入力する列を作成する

休憩時間の「開始時間」と「終了時間」、そして「休憩時間」とタグの「Pos」の列を作成します。

②休憩時間の計算式を挿入する

休憩時間の計算式を挿入します。
H3セルの場合は以下の計算式となります。

=G3-F3

最下部までドラッグします。

③タグを挿入する

休憩であるとわかるようにタグを挿入します。

サンプルでは「休憩」とし、最下部まで入力します。

④ガントチャート部の計算式を変更する

ガントチャート部へ挿入されている既存の計算式を変更します。

J3列を選択し以下の計算式を入力します。

=IF(AND(J$2>=$F3,K$2<=$G3,$I3="休憩"),IF(AND(J$2>=$F3,K$2<=$G3),$I3,""),IF(AND(J$2>=$B3,K$2<=$C3),$E3,""))

次に最下位の行までドラッグし、最終列までドラッグします。

④「休憩」がガントチャートへ表示されるか確認する

「休憩」がガントチャートへ表示されるか確認しましょう。

はい、表示されますね。

タカヒロ
タカヒロ
セルの背景色や文字の色などは適宜変更願います。

ガントチャート付きシフト表の表示単位を変更する

ガントチャート付きシフト表の表示単位はこれまで1時間単位で設定してきましたが、
30分単位や15分分単位で設定したい場合がありますよね。

現在の仕様ですと、時間の間隔が1時間未満の場合、ガンチャートへ表示されませんので、
こちらを30分や15分間隔でも表示できるように設定を変更していきたいと思います。

こちらが完成イメージです。

■30分単位

■15分単位

ガントチャート付きシフト表の表示単位を30分単位に設定する

ガントチャート付きシフト表の表示単位を30分単位に変更してみましょう。

まずは、時間列を修正します。

開始時間の次のセルに30分追加した時間を入力します。

終了時間のセルまでドラッグしコピーします。

続いて、数式を終了時間の列までドラッグしてコピーします。

数式は1時間間隔版と同じです。

J3列を選択し以下の計算式を入力します。

=IF(AND(J$2>=$F3,K$2<=$G3,$I3="休憩"),IF(AND(J$2>=$F3,K$2<=$G3),$I3,""),IF(AND(J$2>=$B3,K$2<=$C3),$E3,""))

30分間隔のガントチャート付きシフト表の完成です。

タカヒロ
タカヒロ
出退勤、休憩時間を30分間隔にしてテストしてみてください。

ガントチャート付きシフト表の表示単位を15分単位に設定する

次はガントチャート付きシフト表の表示単位を15分単位に変更してみましょう。

時間列を修正します。

開始時間の次のセルに15分追加した時間を入力します。

終了時間のセルまでドラッグしコピーします。

続いて、数式を終了時間の列までドラッグしてコピーします。

こちらの数式は1時間間隔版と若干異なり、元のシリアル値に0.0001を加減しています。
15分単位の場合、シリアル値に変換すると割り切れないケースがあり、誤差が生じるためです。

タカヒロ
タカヒロ
例えば13:15の場合、シリアル値は「0.552083333333333」と循環小数になります。
けれど書式が異なるセルに13:15と入力すると、シリアル値は「0.552083333333334」になったりします。
おそらくExcelの仕様によるものと思いますが、イコールの条件にならない場合があり注意したいところですね。

J3列を選択し以下の計算式を入力します。

=IF(AND(J$2>=$F3-0.0001,K$2<=$G3+0.0001,$I3="休憩"),IF(AND(J$2>=$F3-0.0001,K$2<=$G3+0.0001),$I3,""),IF(AND(J$2>=$B3-0.0001,K$2<=$C3+0.0001),$E3,""))

15分間隔のガントチャート付きシフト表の完成です。

 

タカヒロ
タカヒロ
15分刻みですと時間列の数が多くなりますので、
以下のように書式とレイアウトを変えてみるとよいですね。


ガントチャート付きシフト表へ2つ目の休憩時間を追加する

読者様のご要望がありましたので、ガントチャート付きシフト表へ2つ目の休憩時間を追加する方法について説明をします。

こちらが完成イメージです。

一つ目の休憩欄の右横に2つ目の休憩欄を設け、シフト表へ反映させていきます。

タカヒロ
タカヒロ
時間帯の単位は1時間となります。
30分、15分単位に設定したい場合は一つ目の休憩設定の項目を参考に設定をしてください。

①二つ目の休憩時間を入力する列を作成し、計算式と値を設定する

一つ目の休憩欄の右横に2つ目の休憩欄を追加しましょう。

サンプルではJ~M列に追加をしています。

休憩時間の計算式は3行目L列に

=K3-J3

を入力し、下位行までドラッグしていきます。

タグであるM列のPos項目へ
「休憩2nd」
と入力します。

タカヒロ
タカヒロ
2つ目の休憩がなければ空欄としていますが、タグ名があったとしても時間帯がなければタグは表示されません。

仕上げに、休憩時間を一つ目の休憩時間と重ならない時間帯で入力していきましょう。

②ガントチャート部の計算式を変更する

ガントチャート部へ挿入されている既存の計算式を変更します。

N3列を選択し以下の計算式を入力します。

=IF(AND(N$2>=$F3,O$2<=$G3,$I3="休憩"),IF(AND(N$2>=$F3,O$2<=$G3),$I3,""),IF(AND(N$2>=$J3,O$2<=$K3,$M3="休憩2nd"),$M3,IF(AND(N$2>=$B3,O$2<=$C3),$E3,"")))

次に最下位の行までドラッグし、最終列までドラッグします。

③「休憩2nd」がガントチャートへ表示されるか確認する

「休憩2nd」がガントチャートへ表示されるか確認しましょう。

はい、表示されますね。

3つ目、4つ目の休憩をガントチャートへ追加する

3つ目、4つ目の休憩をガントチャートへ追加する方法について説明をします。

追加する要領は2つ目と同様、表へ3つ目以降の休憩欄を追加し、関数を変更していきます。

2つ目休憩欄以降に3つ目と4つ目の休憩欄を追加します。

休憩開始 休憩終了 休憩時間 Pos 休憩開始 休憩終了 休憩時間 Pos 休憩開始 休憩終了 休憩時間 Pos
15:00 16:00 1:00 休憩2nd 18:00 19:00 1:00 休憩3rd 20:00 21:00 1:00 休憩4th

V3列を選択し以下の計算式を入力します。

=IF(AND(V$2>=$F3,W$2<=$G3,$I3="休憩"),IF(AND(V$2>=$F3,W$2<=$G3),$I3,""),IF(AND(V$2>=$J3,W$2<=$K3,$M3="休憩2nd"),$M3,IF(AND(V$2>=$N3,W$2<=$O3,$Q3="休憩3rd"),$Q3,IF(AND(V$2>=$R3,W$2<=$S3,$U3="休憩4th"),$U3,IF(AND(V$2>=$B3,W$2<=$C3),$E3,"")))))

仕上げに最下位の行までドラッグし、続いて最終列までドラッグしていきます。

さいごに

いかがでしょうか?

今回は、

・時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法
・ガントチャート付きシフト表へ休憩時間を追加する方法
・ガントチャート付きシフト表の表示単位を変更する方法

についてまとめました!

よく利用するExcelのシフト表。

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








51 件のコメント

  • お世話になります。こちらを参考にシフト表を作成しているのですが、
    休憩2回を2回取得するようにするためにはどのような計算式になるのでしょうか?
    浅学につき、ご教示頂ければ幸いです。

  • お世話になります。当ページを参考に、30分刻みの表を作成しています。出退勤の計算式を参考に、休憩時間(1時間)を反映させたいと思っています。ところが、特定の時刻だけがうまく表示されません。どのように対処すればいいでしょうか。

    ・作成しているシフトは9:00~20:00
    ・ほかの時刻は、1時間分(つまりは2マス)に「休憩」と表示されるのに、16:30、18:00、19:30だけ「休憩」と表示されません。
    ※16:00~17:00まで休憩だった場合、16:00と16:30のマスに「休憩」と表示される想定でしたが、どうも上記三か所だけは表示されないのです…
    ・また、時間軸の最後まで計算式をドラッグすると、どのメンバーも20:00のセルに必ず「休憩」が入ってしまいます。

    ご教示ください。

  • 15分単位のシフト表が欲しかったので、時間軸の数式を「+TIME(0,15,0)」に変えて作成しましたが、上手く表示される箇所もあれば、休憩時間が反映されなかったり退勤じかんが15分ズレてしまったりしますが原因が分からないので対処出来ません…
    分刻みだと難しいですか?

  • 先の質問で以下の内容でVBAに記述しました
    Sub test()
    Range(“A6”).Formula = “=+IFERROR(INDEX(シフト!$A$1:$AG$129,MATCH($A11,シフト!$A$1:$A$129,0)-1,MATCH($B$7,シフト!$A$8:$AG$8,0)),””)”
    Range(“A6”) = Range(“A6”).Value
    End Sub
    などでオートメーションエラーがでます 少し区別がわからづ温和くしております
    ほかの計算式だとうまくいくのですが
    例えば以下の計算式
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(“F10:BR10”)) Is Nothing Then
    Range(“BY9”).Formula = “=IF($BY$253=0,0,LARGE($BZ$253:$CJ$253,$BY$253))”
    Range(“BZ9”).Formula = “=MAX($BZ$253:$CJ$253)”
    Range(“By9:Bz9”).Value = Range(“By9:BZ9”).Value
    End If
    End Sub

    最初の計算式ではオートメーションエラーが出ます

    • オートメーションエラーが出る場合は、繰り返し処理が多かったり、参照しているアイテムが多かったり、循環参照エラーを起こしていたりなどの原因が考えられますので、範囲を最小限にして同じエラーがでるかテストいただけますでしょうか。

  • 一つ質問です
    現在時間帯シフトから、人件費、経費などの時間帯損益を出す作業に取り掛かっています
    別シートに経費計算などのシートを作成し それとシフト表をリンクさせてます
    ここで問題が一つです
    該当するセルの値の行を参照してその行の一つ下の値を取得するといことです
    xLOOKU 関数を使いたいところですが バージョンにより使えないとのことで
    INDEX   (MATCH(   )+1)を使おうとおもいます
    そこで問題は
    +INDEX(シフト!C10:シフト!C150,MATCH(‘シート1’!A1,シフト!A11:A150,0)+1)
    まあ、問題なく取得できましたが、これではC10:C150の列しか対象にならずです
    対象としたいのはAからAG列までです
    VLOOKUPで
    IFERROR(VLOOKUP($A$1,シフト!$A:$AG,$C$1,0),””)このように書いているのですが A1 の値でシフト表のAからAG列の三列目の値を表示させてます
    三列目の値を取得するのは C1 の値で3が入れてあります C1の値を4にすると
    4列目の値を取得するということにしているのですが
    このように対象行の3番目、4番目・・ とC1の値が変るごとに
    対象行、対象列のセルの一つ下の値を取得したいのですが
    よろしくお願いいたします

    • いつもご利用ありがとうございます。

      ご質問の「C1の値が変るごとに対象行、対象列のセルの一つ下の値を取得したい」ですが、
      C1の値が4の場合、A4:AG最終行までを、5の場合A5:AG最終行までを範囲とするイメージでよろしいでしょうか。
      この場合、INDIRECT関数を利用すれば可能となります。
      以下サンプルとなります。
      VLOOKUP(A1,INDIRECT(“A”& C1 &”:AG最終行”),$C1,0)

      よろしくお願いいたします。

      • いろいろ検証しておりましたら 返事が遅くなりました
        そのような感じよいような動きをしております
        まだいろいろ検証してみることにいたします
        最近では石米のシートに 関数が多すぎて 全体が重たいといいった
        現象に悩まされています 関数、計算式などなど見直しております

        • 関数の数が増えれば処理件数も増えますので
          動作が重くなるのは仕方がないところですね…

          データ量を減らしたり、
          計算結果を値貼り付けで固定値へ変換したり、
          ある程度手を加える必要があるかもしれません。

          • ありがとうございます、やはりそうなりますね
            そこで一部をBVAで処理するようにしようかなと考えています
            セルの変更でVBA発動と ワークシーイベント?セルイベント?
            Sub test()
            Range.(“A6”).Formula = “=+IFERROR(INDEX(シフト!$A$1:$AG$129,MATCH($A11,シフト!$A$1:$A$129,0)-1,MATCH($B$7,シフト!$A$8:$AG$8,0)),””)”
            End Sub
            とまあここまでは コマンドボタンでできるのですが
            セルA1の変更で セルA6に式をいれて 値だけを残したいのですが
            どのようにしたらよいでしょうか?
            また、この方法は有効でしょうか?

          • セルA1の変更でセルA6に式をいれてA6の値だけ残す件につきまして、
            ご提示いただいたVBAに以下を追記すればよいかと思います。
            Range(“<値を残したいセル>”) = Range(“A6”).Value

            よろしくお願いいたします。

  • コメントを残す

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