学校行事やアルバイトなどのシフト管理で時間帯ごとのシフト表を作る機会は多いかと思いますが、
忙しい中で用意しなければならないことが多く、なるべく手間をかけたくないところですよね。
けど、そんな中でやっぱり面倒だなと思うことは、
・毎回時間帯のスケジュール表をつくることは面倒
・参加する時間帯に色をつけたいがやり方がわからない
・ガントチャート形式のシフト表がほしいけどよいテンプレートがない
・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分間隔のガントチャート付きシフト表の完成です。
ガントチャート付きシフト表の表示単位を15分単位に設定する
次はガントチャート付きシフト表の表示単位を15分単位に変更してみましょう。
時間列を修正します。
開始時間の次のセルに15分追加した時間を入力します。
終了時間のセルまでドラッグしコピーします。
続いて、数式を終了時間の列までドラッグしてコピーします。
こちらの数式は1時間間隔版と若干異なり、元のシリアル値に0.0001を加減しています。
15分単位の場合、シリアル値に変換すると割り切れないケースがあり、誤差が生じるためです。
けれど書式が異なるセルに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分間隔のガントチャート付きシフト表の完成です。
以下のように書式とレイアウトを変えてみるとよいですね。
ガントチャート付きシフト表へ2つ目の休憩時間を追加する
読者様のご要望がありましたので、ガントチャート付きシフト表へ2つ目の休憩時間を追加する方法について説明をします。
こちらが完成イメージです。
一つ目の休憩欄の右横に2つ目の休憩欄を設け、シフト表へ反映させていきます。
30分、15分単位に設定したい場合は一つ目の休憩設定の項目を参考に設定をしてください。
①二つ目の休憩時間を入力する列を作成し、計算式と値を設定する
一つ目の休憩欄の右横に2つ目の休憩欄を追加しましょう。
サンプルではJ～M列に追加をしています。
休憩時間の計算式は3行目L列に
=K3-J3を入力し、下位行までドラッグしていきます。
タグであるM列のPos項目へ
「休憩2nd」
と入力します。
仕上げに、休憩時間を一つ目の休憩時間と重ならない時間帯で入力していきましょう。
②ガントチャート部の計算式を変更する
ガントチャート部へ挿入されている既存の計算式を変更します。
Ｎ3列を選択し以下の計算式を入力します。
=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のシフト表。
ガントチャートもマスターしてより使い分けしたいですね！
この記事の関連キーワード
お世話になります。こちらを参考にシフト表を作成しているのですが、
休憩2回を2回取得するようにするためにはどのような計算式になるのでしょうか？
浅学につき、ご教示頂ければ幸いです。
いつもご利用ありがとうございます。
2つ目の休憩時間を追加する方法について記事へ追加いたしましたので
よろしければご参考頂きたくお願いいたします。
https://extan.jp/?p=3011#%E3%82%AC%E3%83%B3%E3%83%88%E3%83%81%E3%83%A3%E3%83%BC%E3%83%88%E4%BB%98%E3%81%8D%E3%82%B7%E3%83%95%E3%83%88%E8%A1%A8%E3%81%B82%E3%81%A4%E7%9B%AE%E3%81%AE%E4%BC%91%E6%86%A9%E6%99%82%E9%96%93%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%99%E3%82%8B
お世話になります。当ページを参考に、30分刻みの表を作成しています。出退勤の計算式を参考に、休憩時間（1時間）を反映させたいと思っています。ところが、特定の時刻だけがうまく表示されません。どのように対処すればいいでしょうか。
・作成しているシフトは9:00～20:00
・ほかの時刻は、1時間分（つまりは2マス）に「休憩」と表示されるのに、16:30、18:00、19:30だけ「休憩」と表示されません。
※16:00～17:00まで休憩だった場合、16:00と16:30のマスに「休憩」と表示される想定でしたが、どうも上記三か所だけは表示されないのです…
・また、時間軸の最後まで計算式をドラッグすると、どのメンバーも20:00のセルに必ず「休憩」が入ってしまいます。
ご教示ください。
いつもご利用ありがとうございます。
一部の「休憩」が反映されない件ですが、
30分刻みの表となりますのでこちらをご参考の上、お手数ですが数式を張りなおしてご確認頂けますでしょうか。
https://extan.jp/?p=3011#%E3%82%AC%E3%83%B3%E3%83%88%E3%83%81%E3%83%A3%E3%83%BC%E3%83%88%E4%BB%98%E3%81%8D%E3%82%B7%E3%83%95%E3%83%88%E8%A1%A8%E3%81%AE%E8%A1%A8%E7%A4%BA%E5%8D%98%E4%BD%8D%E3%82%9230%E5%88%86%E5%8D%98%E4%BD%8D%E3%81%AB%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B
最後の列に「休憩」と表示されてしまう件につきましては、恐縮ではございますが仕様となります。
タイムラインを長めにとっていただき、最後の列は非表示にするなどでご対応いただければと存じます。
15分単位のシフト表が欲しかったので、時間軸の数式を「+TIME(0,15,0)」に変えて作成しましたが、上手く表示される箇所もあれば、休憩時間が反映されなかったり退勤じかんが15分ズレてしまったりしますが原因が分からないので対処出来ません…
分刻みだと難しいですか？
いつもご利用ありがとうございます。
15分単位のシフト表を実現する方法につきまして、以下へ追記いたしました。
https://extan.jp/?p=3011#%E3%82%AC%E3%83%B3%E3%83%88%E3%83%81%E3%83%A3%E3%83%BC%E3%83%88%E4%BB%98%E3%81%8D%E3%82%B7%E3%83%95%E3%83%88%E8%A1%A8%E3%81%AE%E8%A1%A8%E7%A4%BA%E5%8D%98%E4%BD%8D%E3%82%9215%E5%88%86%E5%8D%98%E4%BD%8D%E3%81%AB%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B
また、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( )+１）を使おうとおもいます
そこで問題は
+INDEX(シフト!C10:シフト!C150,MATCH(‘シート１’!A1,シフト!A11:A150,0)+1)
まあ、問題なく取得できましたが、これではC10:C150の列しか対象にならずです
対象としたいのはAからAG列までです
VLOOKUPで
IFERROR(VLOOKUP($A$１,シフト!$A:$AG,$C$1,0),””)このように書いているのですが A１ の値でシフト表のAからAG列の三列目の値を表示させてます
三列目の値を取得するのは C1 の値で３が入れてあります C1の値を４にすると
４列目の値を取得するということにしているのですが
このように対象行の３番目、４番目・・ と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
よろしくお願いいたします。