更新日：2022/11/04
簡単な計算の実施から表の作成まで幅広く活用することが出来る、エクセルなどの表計算ソフト。シフト表の作成にもエクセルを活用している人は多いと思います。
その場合の１日ごとの勤務の表現方法は、「早番」「夜勤」などの勤務記号を用いている場合が多いと思います。その記号を見ればどのスタッフが何時から何時まで勤務しているのか、従事しているスタッフの方々には一目瞭然でしょう。
しかし今は働き方も多様です。勤務時間はスタッフごとに細分化され、記号で表すにはもはや数が増えすぎてどの記号がいつ働いている勤務なのか、理解が追い付かなくなっている場合もあるでしょう。すると最終的には勤務記号ではなく、勤務する時刻そのものをシフト表に書き込むことになります。
これはこれで柔軟なシフト表と言えますが、今度は何時にどのスタッフが働いているのか、シフト表上で１日の中の時間帯ごとの人数把握をするのが難しくなってきます。
こんな時に便利なのが「タイムスケジュール表」や「ワークスケジュール表（ワースケ）」などと呼ばれる形式です。「タイムスケジュール表」は時間軸を設け、スタッフごとに勤務している時間帯にマークを付けたりバーを描いたりとグラフィカルに表現し、時間帯ごとにどのスタッフが従事しているかが把握しやすい表現になっています。
そこで今回は「タイムスケジュール表」のエクセルでのテンプレート化にチャレンジしたいと思います。
今回ご紹介するシフト表テンプレートは下記ボタンよりダウンロードしてご利用いただくことができます。テンプレートは、原本用としてデータの入っていないものと、参考用にサンプルデータが入っているものとの２種類を用意しています。データの有り無しのみで、構造に違いはありません。
▼ シフト記号で作成するシフト表のエクセルテンプレートについては、以下で解説しています
【無料ダウンロード】自分で作ろう！エクセルで作るシフト表のテンプレート化のコツ
さて、今回はエクセルで作る「タイムスケジュール表」テンプレートということですが、これをエクセルで作るのは実はかなり面倒です。というのも、24時間という幅をどのように分割して作成するかで手間が大分変ってくるからです。最小単位を１時間としても24列必要です。１分単位までは流石に少ないと思いますが、30分、15分、10分、5分くらいまで考えている職場は意外に多いと思います。
そこまで細分化されてくると、エクセルでその分の列を作るのも大変ですし、関数などの計算式も相応に考えることが増えてきます。いくらセルのコピー機能を活用して作成できても、構成するセルが増えればどこかの計算式に粗が出てしまうのもなかなか防ぎにくいです。
しかも、そうしてようやく作った「タイムスケジュール表」はあくまで１日分です。テンプレート化しても、１日ごとにテンプレートのファイルをコピーしてシフトの時間を入れてとするのは大変ですし、１ヶ月分の勤務時間を把握したいとなったら31日分の「タイムスケジュール表」からどうやって集計するのか、最初は悩ましく感じると思います。
それらを踏まえて、今回のエクセルでのテンプレートについて解説していきます
テンプレートは２種類のシートで構成
前述した通り、「タイムスケジュール表」は通常１日１枚で作成するものです。それを毎日１ファイルとするのは煩わしいですし、各スタッフの勤務時間を１日１日入力したとしてもある程度の期間（１週間、１ヶ月・・）の出勤状況をまとめて把握したいという場合には１日ごとに分かれたままでは非効率です。
そこで今回のテンプレートは、期間全体（１ヶ月間）の「シフト表」である「月間」シートと、１日ごとの「タイムスケジュール表」である「日別」シートの２種類のシートを用意し、できる限り入力する手間を省けるようにすることと、全体の把握をしやすくすることを目指して作成します。
＃「月間」シート
本シートは１ヶ月分のいわゆる「シフト表」です。スタッフや担当業務などのマスタ情報の登録と、スタッフの勤務を１日ごとに「開始／終了」時刻で入力をします。これらを登録することで、すべての日付の「日別」シートの「タイムスケジュール表」に反映されます。また、本シートは１ヶ月間の各種集計列も設けています。
これらにより、本シートでは各スタッフの１ヶ月間の総労働時間、勤務日数の確認、及び日ごとの担当業務ごとの出勤人数の確認ができるようになっています。
＃「日別」シート
本シートはシート名が「1」～「31」と付けられたシートで、こちらが「タイムスケジュール表」となります。シート名の数字は日付を表します。本シートは15分刻みで列が分かれており、「月間」シートで「開始／終了時間」を登録することでスタッフが勤務している時間を「■」で示します。またページ下段では担当業務ごとの人数も集計されているため、どの時間帯にどのくらいの人数が従事しているかも把握できます。
このように本シート上では何も入力しなくても「タイムスケジュール表」としての利用はできますが、例外として[休憩時間指定]欄を入力用の列として用意しています。
これは「休憩時間」の時間帯を日ごとに細かく指定したいという場合に、例えば[休憩時間指定]欄に「12:00～13:00」と登録することで、「タイムスケジュール表」上でその時間帯にそのスタッフが不在であることを「□」で示します。また、「□」は担当業務の人数からも除外して集計するので、より実態に即した時間帯別の人数把握ができるようになります。
テンプレートの使い方
過去のエクセルテンプレートの記事では、エクセルの書式設定や関数などを細かく説明しましたが、今回は関数の作りなどかなり複雑になっています。複数のシートを使うことで、シート間の参照や複数シートの串刺し算なども使用しています。ある程度エクセルについて熟知してないと難しいと感じられる点があるので、この点はポイントを絞ってまとめて後述します。
以上のようなことから、今回はテンプレートの使い方から説明します。使い方についてはテンプレートファイルの「説明」シートにも記載していますので、合わせてご参照ください。
＃1.「月間」シート：マスタ情報の登録
「月間」シート内に「マスタ情報」という言葉はありませんが、毎月共通して使うであろう情報をここでは「マスタ情報」とします。まずはこれらの準備から次の順番で登録してください。
①担当業務を登録する
本シート下段にある「【担当業務】」の[担当名]欄を登録します。サンプルでは「スーパーマーケット」でよく見られる業務名を登録しています。ここに登録することで、次に説明するスタッフごとに割り当てる[担当]欄プルダウンの構成要素になります。また、本シートや「日別」シート上での担当業務ごとの人数集計にも用いられます。
②スタッフ情報を登録する
次に、本シート上段の「【月間シフト表】」の[氏名]欄にスタッフの名前を登録し、[担当]欄に①で設定した[担当名]をプルダウンから選択します。
少し特殊なのが[標準休憩時間]です。こちらは各スタッフの最も標準的な休憩時間を登録する欄です。例えば、標準的な実働時間が8時間のスタッフの場合、勤務時間中の休憩は通常1時間を取得することが多いと思います。その場合は「100」と入力（「01:00」と変換されます）します。
実働時間が4時間程度と短い場合、通常は休憩時間を取得しないスタッフもいると思います。その場合は「0」と入力（「00:00」と変換されます）します。
ここで入力した休憩時間がどのように機能するのかは後述します。
ここまで登録したら一旦エクセルファイルを保存します。ここまでの内容はスタッフの異動がない場合には毎月変更する必要がない項目です。以降はこれを原本として毎月のシフト表作成作業を進めましょう。
＃2.「月間」シート：月の勤務予定の登録
ここからは毎月必ず行う手順です。引き続き「月間」シートでの手順になります。
①月度を入力する
まずシフト表を作成する「月度」を入力します。サンプルでは「2021年9月」分を作成しているので、[月度]欄に「2021/9/1」と入力しています。入力することで「2021年09月度」と表示形式を自動で変換します。（※この日付は、必ず1日の日付にしてください。）
[月度]欄を入力すると、カレンダー部分が1日からその月の末日まで自動で表示されます。土曜日は青文字、日曜日は赤文字で表示されます。（※この辺りのエクセルの書式設定は過去記事を参考にしてください。）
②スタッフの日ごとの勤務時間（開始／終了）を入力する
次はスタッフの日ごとの勤務時間を「開始」時刻、「終了」時刻で入力します。入力は、例えば「9時～18時の勤務」という場合は、[開始]欄に「900」と入力（「09:00」と変換されます）し、[終了]欄に「1800」と入力（「18:00」と変換されます）します。休日の場合は[開始][終了]欄は空白のままで結構です。
③総労働時間を入力する
次はスタッフごとの対象月の「総労働時間」を設定します。本シートを右の方にスクロールすると、図のように各種集計欄が現れます。ここまでで既に勤務時間を入力しているので、[実働時間]、[勤務日数]、[非勤務日数(休み)]が計算されています。
[総労働時間設定]欄にスタッフごとの対象月に想定される労働時間の上限を入力してください。例えば１日８時間勤務が標準でその月は22日勤務となれば「176時間」となりますので、「17600」と入力（「176:00」と変換されます）します。入力すると、[総労働時間差分]欄に[実働時間]欄との差分を計算します。青文字であれば設定値を超過、赤文字であれば設定値に対する不足を示します。
《補足》
ここで、時刻入力について詳しく説明していなかったので改めて補足します。本テンプレートの時間を入力するセルについては、全て「:」（コロン）を入力しなくても「09:00」のような時刻表記に変換するよう「セルの書式設定」の「表示形式」で定義をしています。
例：「09:00」と入力したい → 「900」と入力
「12:45」と入力したい → 「1245」と入力
「176:00」と入力したい → 「17600」と入力
ここまでの手順でシフト表としての最低限の準備はできました。何れかの「日別」シートを開いてみてください。「タイムスケジュール表」でスタッフごとの勤務が「■」で示されていることが分かります。
＃3.「日別」シート：休憩時間の登録
ここまでである程度の準備はできましたが、折角「タイムスケジュール表」まで用意したので、今回はもうひと手間加えてスタッフが休憩をしている時間帯を担当業務の人数カウントから外すようにする設定をしてみます。
何れかの日付の「日別」シートを開き、[休憩判定]欄をご覧ください。本欄は「月間」シートの該当スタッフの[標準休憩時間]欄が入力された状態で該当日付の[開始／終了]欄が入っていると、「標準」と表示されます。この場合、タイムスケジュール表の該当の勤務時間には「■」のみが表示され、実働時間としては[標準休憩時間]の時間を差引いて計算しますが、どの時間帯に休憩をするかは表現していません。
例：[開始／終了]＝09:00～18:00、[標準休憩時間]＝01:00
→ [実働時間]＝08:00 となるが、どの時間に休憩を取るかを明示していない状態
そこでどの時間帯に休憩を取得するかを明示するために、[休憩時刻指定]の[開始／終了]欄に休憩時間を登録すると、「タイムスケジュール表」の該当時間が「□」と休憩時間を示す表示に変わり、「担当業務」の集計カウントからも除外されるようになります。
このような状態で休憩を設定した場合には、[休憩判定]欄が「指定」と表示されます。なお、ここで設定する時間は[標準休憩時間]で設定した時間とイコールでなくても結構です。例えばその日は通常1時間休憩のところ、1時間30分休憩となる場合には、[休憩時刻指定]を「13:00～14:30」指定することで、こちらの値を優先して[実働時間]の集計を行います。
他の表示も含めて[休憩判定]については以下のようになります。「勤務外」と「未指定」は表示としてはエラーの状態なので見直してください。
| 表示 | 設定の状態 |
| 標準 | 「月間」シートの[標準休憩時間]が休憩として設定されている。 |
| 指定 | 「日別」シートの[休憩時刻指定]が休憩として設定されている。 |
| 休 | 「月間」シートの該当日付の[開始／終了]が空白。（休日扱い） |
| 勤務外 | 「日別」シートの[休憩時刻指定]が勤務時間外に設定されている。 |
| 未指定 | 「月間」シートの[標準休憩時間]が空白。 |
これで休憩時間の登録もでき、より細かな時間ごとの業務配置ができるようになりました。
＃4.チェックと修正
ここまでで一通りの設定方法については説明しました。ここからはチェックと修正作業になります。チェックで大事なポイントは以下となります。
・時間帯ごとの担当人数が適切か
・総労働時間、勤務／休み回数が適切か
「日別」シートの下段にある「担当人数：時間帯別」を確認し、人数が不足している時間帯がある場合は休憩時間や出勤するスタッフの調整をします。ある日付の勤務を調整すると、別の日の勤務や総労働時間なども影響しますので、「月間」シートの各種集計列を見ながら調整を進めてください。
ここまで完了したらその月のシフト表作成は完了です。実際の業務でご活用ください。
テンプレートに使用した機能や関数について
ここからは今回使用した関数の説明です。少し難しい内容も含みますので、取り敢えず使ってみたいという方は、本章を読み飛ばしていただいて構いません。
今回のエクセルテンプレートは、複数のシートが相関関係をもって構成されていることから、お互いの値を参照したり計算したりするのに複雑な関数も用いています。
例えば、上図②スタッフの「氏名」などのように、単純に特定のセルの情報を参照するだけであれば、「日別」シートの特定のセルに、「=月間!$B5」と「=[シート名]![セル番号]」のような書式で記載すればそのセルを参照することができます。（※参照したいセルが他のシートにある場合、参照したいセル番号の前に「[シート名]＋「!」」と付けることで参照できます。）
しかし、上図①のように複数のシートの値をまとめて集計する場合はどうすればいいのでしょう？また、上図③のように自分の日付の[開始][終了]時刻のセルを参照するにはどうすればいいのでしょう？
各「日別」シートから「月間」シートの該当する[開始][終了]のセルを②と同じ方法で参照すれば簡単ですが、それでは31シート分それぞれで参照し直さなくてはなりません。今回の「日別」シートの作り方として、異なる日付のシートで異なる関数を用いるようなことはしていません。１つ元となる「日別」シートを作成したら、31枚分コピーしてシート名を変えただけです。
今回の関数説明は、細かく全ての説明をせず、この辺りの要点のみを説明します。
＃1.シートの串刺し計算
同じ書式のシートが複数ある場合、それぞれのシートの同じセルの値をまとめて合計する方法を「串刺し計算」という言い方をします。「月間」シートの[実働時間]（BQ列）では、スタッフごとの全「日別」シートの[実働時間]（DF列）を「SUM関数」で集計しています。
「=SUM('1:31'!$DF5)」
「!」の前は参照するシート名を示すことは先ほども触れましたが、「'」（シングルクォーテーション）で囲んだ「1:31」は「1」日～「31」日の「日別」シートが範囲として指定されていることを示します。それら範囲の[実働時間]（DF列）を「SUM関数」で集計することで、すべての日付の[実働時間]を集計することができます。
実際に埋め込まれている関数には、更に「[氏名]欄（B列）が空欄でない場合に集計を実行する、そうでない場合は空欄にする」という意味で、
「=IF($B5<>"",SUM('1:31'!$DF5),"")
という関数にして「月間」シートの「BQ」列に設定しています。
＃2.シート名を読み込んでから参照する開始／終了セルを特定する
各「日別」シートでは、自分のシート名が何日のものであっても「月間」シートから自分のシート名と同じ日付の[開始][終了]時刻を参照できるようにしなくてはなりません。そのためにはまず、自分のシート名を読み込む関数を用意します。
各「日別」シートには、非表示にしているところがいくつかあります。3行目がその1つで、非表示を解くと「G3」セルにそのシート名を読み込む関数が埋め込まれています。
「=RIGHT(CELL("filename",G3),LEN(CELL("filename",G3))-FIND("]",CELL("filename",G3)))」
「CELL("filename",G3)」という関数がたくさん出てきますが、これはエクセルファイル（本ファイル）が保存されている場所と[シート名]を「\\[フォルダ名]\[ファイル名][シート名]」とう書式で読み込むための関数です。ここでは「CELL関数」自体の詳しい説明は致しませんが、「"filename"」とした部分がファイルのフルパスを呼び出すためのキーワードになります。このキーワードにより[シート名]まで含んだフルパスを読み込みますが、欲しいのは[シート名]だけです。
そこで、単純に「=CELL("filename",G3)」とした関数で表示される結果の文字列を解析し、「RIGHT関数」と「FIND関数」という文字列の参照や抽出をする関数で[シート名]部分だけを抽出したのが上述したものです。その結果、上図の例では「日別」シートの内の「1」シートで実行したため、「1」と[シート名]だけが抽出されます。
この結果を用いて、[日付]（B1セル）も「2021年09月01日(水)」と表示してます。
次に、[シート名]を特定できたら、「日別」シートから「月間」シートの対応する日付の[開始][終了]を参照しなくてはなりません。「1」シートからでも、「31」シートからでも、「月間」シート対応する日付の列を参照するにはどうしたらいいのでしょうか。
ここで用いるのが「INDEX関数」です。
例えば、上図「月間」シートの5行目、「佐藤」さんの何れかの日付の[開始]欄を参照しようという場合には、以下のような関数になります。
「=INDEX(月間!$E5:$BN5,1,1+($G$3-1)*2)」
「INDEX関数」の中には「,」で区切られた3つの引数があります。
最初の「月間!$E5:$BN5」は、参照したい領域の全体を示します。この場合は、1日の[開始]欄のあるE列から31日の[終了]欄のある「BN」列までを範囲として指定しています。
次の「1」は、選択した範囲の何行目かを指定します。ここでは「佐藤」さんの5行目の1行だけを指定してるので、「1」としています。
最後の「1+($G$3-1)*2」が分かりづらいところで申し訳ありませんが、これは選択した範囲の何列目かを指定しています。しかしこここそが、何日のシートであっても特定できる肝になっています。「$G$3」は「CELL関数」で説明した[シート名]を表示させたセルを示します。日付が1日のシートであれば「1」となり、「1+(1-1)*2」と読み替えられますので、結果は「1」列目であることを示します。
つまり、選択した範囲「月間!$E5:$BN5」の1行目、1列目を指定したことになるので、上図での「E5」セル、つまりは「1日目の[開始]欄」を参照していることになります。「$G$3」が「5」なら「M5」を参照することになります。
実際には更にこれを時間に変換する関数で整えて、最終的に各「日付」シートの「タイムスケジュール表」に反映しています。
********************
まだまだ紹介しきれませんが、今回のテンプレートでは他にも様々な関数を用いています。また、書式設定などを工夫することで時刻入力を簡素化するような仕組みも取り入れています。
ここでご紹介しなかったようなことに関しては、テンプレートを分析してみてください。また、使ってみてここはこうしたいなとか、ここは不便だなというところがあれば、アレンジして皆様の使いやすいテンプレート作成にもチャレンジしてみてください。
最後に
今回ご紹介した「タイムスケジュール表」も含むシフト表のテンプレートはいかがでしたでしょうか？
日付だけで表現するシフト表の場合は１枚のシートで作成できたものを、今回は１日ごとの時間の内訳も表現するシートも必要となるため２種類、32シートで構成する形になりました。
また、シート間を連携させる関数の記述は必須となり、メンテナンス性も考慮するとやや難しい関数も用いる必要が生じました。テンプレートであまり凝り過ぎると属人化に繋がりやすい危うさは認識しつつも、継続して使っていくための工夫も考えると必要だと考えて盛り込んでみました。
このまま使えるものとなれば幸いですが、足りないところがあれば、そこはエクセルですのでアレンジにもチャレンジしてみてください。
なお、エクセルで作るテンプレートにおいては「結果の把握は容易に行えるけど、適切な人材配置を行うためには結局人の力がすべてになる」と欠点がございます。先ほどのテンプレートにおいて、割り当たった時間が表形式に表示されたり、月当たりの勤務回数や時間当たりの人数を見ることは簡単に出来るようになるのですが、勤務回数・勤務時間・必要人数それぞれが「どのようなバランスで割り当てていくと最適なシフトになるのか」については人の力で行っていかなければなりません。
こちらの問題は実はよくある問題で、勤務回数・勤務時間・必要人数およびその他の条件のバランス調整にかなりの時間を要してしまうということもよくあります。このような場合は、バランス調整も含めて自動作成するシフト管理サービスがおススメです。
ＪＲシステムが提供する『勤務シフト作成お助けマン Time』は、必要人数などの設定をしておけば、ここでご紹介したような「タイムスケジュール表」を自動で作成することができます。また、作成期間も１ヶ月だけではなく、１週間、２週間、半月といった期間からも作成することができます。
エクセルでも多くのことができますが、スタッフの勤務希望を反映させたり問題のある箇所をチェックしたりといったことまで考えると、シフト作成者の方の負担も相当なものです。「お助けマン」では、本利用と同じ機能を2か月間無料でトライアルできますので、もしも「エクセルでは限界だ。システム化したい！」とお考えでしたら、是非チャレンジしてみてはいかがでしょうか？
▼あわせて読みたい記事
エクセルで効率的にシフト作成・管理！失敗しない３つのポイント
シフト作成を引き継ぐ際に、気を付けたい5つのポイント
【無料ダウンロード】自分で作ろう！エクセルで作るシフト表のテンプレート化のコツ
