今回は、個人がエクセルを使ったシフト表管理をもっと楽にするために、もう少し凝ったシフト表テンプレートの作成方法についてご案内します。
簡単な計算の実施から表の作成まで幅広く活用することが出来る、エクセルなどの表計算ソフト。シフト表の作成にもエクセルを活用している人は多いと思います。エクセルに少し慣れてくると、思った以上にレイアウトなどが自由に表現できることに気づき、紙で作っていた頃に比べるとカレンダー形式のシフト表の作成が格段に楽になったように感じられたのではないかと思います。
しかし何回かエクセルでのシフト表づくりを続けると少し欲が出てきて、毎回レイアウトを組むことが煩わしくなったり、組んだシフトのチェックに結構時間がかかったりといった悩みが出てはいないでしょうか？
エクセルのテンプレートは自分で１から作ることも出来ます。その場合であれば、作成したテンプレートを使い回すことができますので、手書きからエクセルに変更するメリットの１つと言えます。ただし一方で、エクセルをあまり作り込み過ぎてしまうと属人化に繋がり易いという危うさもあります。
まずは基本となる記号を使ったシフト表の作り方を解説し、さらに応用編として、多様な勤務時間に対応するための「タイムスケジュール表」の考え方についてもご紹介します。
【今回のテンプレート化によって実現すること】
- カレンダー部分の更新を最小限度の手順で更新できるようにする。
- 日別の勤務記号ごとの必要人数をチェックしやすくする。
- スタッフ別の勤務記号ごとの割当回数をチェックしやすくする。
今回ご紹介するシフト表テンプレートのサンプルは下記ボタンよりダウンロードしてご利用いただくことができます。
下図は、テンプレートファイルの「シフト表テンプレート」というシートです。罫線の枠内で背景色があるセルは、毎月入力し直す必要のない固定値か関数が埋め込まれている部分です。それ以外の白いセルは、スタッフ名や勤務記号などの値を入力する部分となっています。
では、このサンプルの内容を元に解説していきます。
- カレンダー部分の更新はひと手間で済ませよう
- １日ごとの必要人数をチェックできるようにしよう
- １ヶ月の勤務回数をチェックできるようにしよう
- 【応用編】勤務時間が多様なら「タイムスケジュール表」も検討
- テンプレートは２種類のシートで構成
- テンプレートの使い方
- テンプレートに使用した機能や関数について
- 最後に
カレンダー部分の更新はひと手間で済ませよう
一見、カレンダー部分の毎月の更新は大した手間ではないように感じられて、その実、月末日の違いや土日の色の変更など、多少ですが煩わしく感じられます。
しかし、やることは決まっているので、少し作り込んでおけば最低限の手順でこうした更新が自動化できます。
▼あわせて読みたい記事
【全30選】シフトに困っている人に勧めたいシフト作成システム・アプリ
【必見】AIで勤務表の自動作成を特長とするシフト管理ツール10選｜導入する前に知っておくべきこと
【おすすめ】シフト作成に特化したシフト管理システム比較17選｜クラウドのメリットとは
＃月の末日は関数で制御
今回、カレンダー部分の更新で目指すのは、１箇所（１セル）だけ値を変えれば月末日や土日の色の変更まで済んでしまうようにすることです。
変更する値はシフト表の当該月を表す「月度」という項目１箇所で、ここではその入力するセルをB1セルとします。このセルには当該月の１日日付を「2020/4/1」のように西暦日付で入力します。ここの表記は入力したとおりのものでも構わないのですが、項目名を「月度」としたように、ここではセルの書式設定を「yyyy"年"mm"月"」とし、「2020年04月」と表記させます。
次に日付の表記部分です。この例では毎月の起点となる１日をD2セルとします。（ちなみに日付部分のセルの書式設定は「d」と日付のみ表示するようにしています。）
ここでは「月度」の値をそのまま参照するので、関数としてはそのまま「=$B$1」と入力するだけでも構いませんが、ここはもう一工夫して「月度」が空欄の場合は何も表示しないように「=IF($B$1<>"",$B$1,"")」とIF文を使ってみましょう。なお、B1セルは列も行も絶対参照なので、列を表す「B」と行を表す「1」、それぞれの前に絶対参照を示す「$」を入れます。
さて、日付の2日以降について、前回記事では隣のセルの値に＋１するという関数を紹介しました。今回も同じ考え方でD2セルの隣のE2セルに「=D2+1」として31日までのセルにコピーすれば済むのですが、テンプレート化する上で１つ問題点が残ります。
それは月末日が月によって違うため、31日に満たない月の場合、末尾の何日かに翌月の日付が入ってしまうという点です。（以下の図の例では「月度」を「2019/2/1」とした場合、３日も余計な日付が表示されてしまうことが分かります。）
そこでまたIF文を使ってこの問題の回避策を取り入れましょう。
使う関数は「MONTH関数」です。これは参照したセルが何月かを調べる関数です。そこでまず以下のように考えます。
「月度の月と前日日付＋１日の月が同じ場合はそのまま前日日付＋１日にしよう、しかし、同じでない場合は空欄にしよう。」
これを以下のような関数にしてE2セルに入力しようと思います。
「=IF(MONTH($B$1)=MONTH(D2+1),D2+1,"")」
しかし、実はこれだけだと前日日付が空欄の場合に結果がエラー表記になってしまうので、
「=IF(D2<>"",IF(MONTH($B$1)=MONTH(D2+1),D2+1,""),"")」
として、更にIF文で囲んで前日日付が空欄でない場合に先ほどのIF文が実行されるようにします。これを３日から31日（F2～AH2セル）まで数式だけのコピーをしましょう。
ここまでやってみたら「月度」のセルを色々な月に変更してみてください。２月でも11月でも、その月の末日までしか日付が入らず、余った枠は空欄になります。
＃土日の文字色は条件付き書式で制御
今度は土曜日を青文字に、日曜日を赤文字に自動で書式を変える方法です。
まず曜日を示すセルに値を入れます。前回記事では日付セル（D2）の下のセル（D3）に「WEEKDAY関数」を埋め込んだ上で、セルの書式設定を「aaa」にして「土」「日」などの曜日を表示するようにしましたが、セルの書式設定をしていれば関数は「=D2」のように日付セルを単純に参照するだけで曜日を表示することもできるので、今回はそのようにしています。
次に文字色ですが、日付と曜日の範囲それぞれの色を曜日によって変更します。日付と曜日の範囲（D2～AH3セル）を囲んでからエクセルメニューの「条件付き書式」→「新しいルール」で「新しい書式ルール」のウィンドウが開きます。
「ルールの種類を選択してください」欄から一番下の「数式を使用して、書式設定するセルを決定」を選び、ウィンドウ下部の「ルールの内容を編集してください」をここでは土曜日の設定として下図のようにします。
・「次の数式を満たす場合に値を書式設定」→「=WEEKDAY(D2)=7」
・「書式」ボタンから文字色を青系の色で選択
同じように日曜日も以下のように条件付き書式に設定すると、日曜日の日付が赤文字になります。
・「次の数式を満たす場合に値を書式設定」→「=WEEKDAY(D2)=1」
・「書式」ボタンから文字色を赤系の色で選択
これで土曜日は青色に、日曜日は赤色に文字色が自動で変更されました。月の末日の時と同様、「月度」を変更してみてください。「月度」に連動して自動的に土日の日付の色が変わるようになりました。
これで今回のテンプレートのカレンダー部分は「月度」のB1セル１箇所だけを修正すれば、日付や曜日、文字色まで自動更新できるようになりました。
ところで「祝日の場合にも自動で赤くできないの？」という疑問もあると思います。「祝日の一覧を用意して該当日がその一覧に含まれる場合は赤くする」という方法で実現は可能ですが、今度は祝日の管理が必要となってしまいます。今回は「お手軽に管理できるテンプレート」を目指しているため、敢えてそれ以上の機能はつけないでおきます。
１日ごとの必要人数をチェックできるようにしよう
勤務調整をしていて頭を悩ますことの一つに、日ごとの出勤者の人数調整があります。
「今日は日勤者がやけに多いな？」
「あれ？夜勤の人数が足りないぞ！」
というようなことはよく起きがちです。
そこでこのテンプレートでは、１日１日の勤務記号ごとの必要人数をチェックできる欄を用意して、人数の過不足が一目で分かるようにしてみましょう。
ここからはサンプルの「シフト表_202004」シートを用いて説明を進めます。このシートには既にスタッフ名や４月のシフト予定などが入ったサンプルになっています。（※「休」「有」の背景色が条件付き書式で変わるようになっていますが、ここでは説明を割愛します。前回記事など参考にしてください。）
このシートの26行目からは「必要人数」欄とし、シフト表で用いられている勤務記号（「早」「遅」など）ごとに２行ずつ枠を用意します。 勤務記号を入力するB列は上下２行分のセルを結合します。（例：B27セルとB28セル、結合後は「B27」として参照します。）
この欄の「早」の勤務記号（27、28行目）を例に内容を紹介します。まずチェックしたい勤務記号である「早」をB27セルに入力します。２行あるうちの１行目は「必要人数」行とし、「早」のスタッフの日付ごとに必要な人数を任意の数値で入力します。
２行目は「日計」行とし、その日割り当てられた「早」の人数を集計します。ここで使われている関数は、前回も登場した「COUNTIF関数」です。１日の集計セルであるD28セルには次のように入力します。
「=COUNTIF(D$4:D$24,$B27)」
これは、１日のシフト表部分の範囲（D4～D24セル）に「早」（B27セル）と同じ文字が何個あるかを集計する内容になります。（スタッフ行の4～24行目、勤務記号のある列「B」の前に絶対参照を示す「$」があることも注意！）
また、この行の範囲（D28～AH28セル）には以下のような内容で２つの条件付き書式を設定します。
これは「必要人数」に入力した値と「日計」で集計された値とを比較し、必要人数より日計が小さければ日計の文字を赤く、大きければ青くするものです。これにより、勤務記号ごとに毎日必要とする人数が、シフト表の中できちんと満たされているか一目で分かるようになります。
このチェック欄を勤務記号の数に応じて増やしたい場合は、この２行を選択して必要人数欄の末尾行の下にコピーすれば、関数や条件付き書式もそのままコピーできます。必要な分だけコピーしてください。
１ヶ月の勤務回数をチェックできるようにしよう
日ごとの必要人数のを終えると、月全体での勤務のバランスも問題になってきます。
「Ａさんの休みが少な過ぎた！」
「Ｂさんの勤務に遅番ばかり割り当たってしまった！」
１日１日の調整にばかり気を取られるとこんな状況に陥って、また１日１日見直しなんてことも・・・。
この問題はどちらが先ということでもなく、また一朝一夕で片付くことでもありませんが、少しでも効率よく進めるために、このテンプレートでは１ヶ月の勤務記号ごとの勤務回数もチェックできるようにしてみましょう。
引き続き「シフト表_202004」シートを用いて説明します。このシートのAI～AN列は「勤務回数」欄とし、スタッフごとの１ケ月の休みなど、勤務記号ごとの回数を集計します。これらの列の２行目には勤務記号を、３行目には当該月にその勤務記号を割り当てたい回数を入力できるようにします。４行目以降のスタッフごとの行には、そのスタッフの勤務記号ごとの割当回数を集計する関数を設定します。「休」の勤務記号を設定したAM列を例に関数の説明をします。
AM2セルには勤務記号の「休」を、AM３セルにはその月の休みの割当回数（ここでは「９」回）を、スタッフごとの行である４～23行目については、４行目の「佐藤」さんを例に「休」の回数を集計する以下の関数をAM4セルに入力します。
「=COUNTIF($D4:$AH4,AM$2)」
これは、１日～31日(D4～AH4セル)の範囲に「休」（AM2セル）と同じ文字が何個あるかを集計しています。（ここでも列の「D」と「AH」、行の「2」の前に「$」があることに注意！）
そして「休」を設定したAM列の範囲（AM4～AM23セル）には、必要人数と同様、条件付き書式で「休」の割当回数（AM3セル）と比較して小さければ赤文字で、大きければ青文字で表示するように設定します。
これにより「休みの回数を４月は平等に９回割り当てたい」という時に色が赤か青に変わっていれば、休みの回数が９回でない、見直しが必要なスタッフがいるということが分かります。
なお、AO列は２行目に「計」、３行目に「30」とサンプルには入っています。これはAO3セルに「=IF($B$1<>"",DAY(EOMONTH($B$1,0)),"")」という関数を入力しており、「月度」に入力した日付に対し、「EMONTH関数」でその月の末日を求めた結果から「DAY関数」で日付部分のみ抜き出して表示するようにしています。
この列の４行目以降には、勤務記号ごとの集計列（AI～AN列）の結果を集計する「SUM関数」が入力されており、AO3列の値と比較して少なければ赤文字に、多ければ青文字にする条件付き書式を設定しています。これによりその月の日数分のシフトが埋まっているかチェックできる欄になっています。
【応用編】勤務時間が多様なら「タイムスケジュール表」も検討
ここまで解説した「記号式」のシフト表は非常に便利ですが、近年はスタッフの働き方も多様化し、「9:30-15:00」や「17:00-21:30」など、勤務時間が細分化されている職場も増えています。
このような場合、「早番」「遅番」といった記号だけでは管理しきれなくなってきます。
タイムスケジュール表（ワースケ）とは？
そんな時に有効なのが、時間軸を設けてスタッフの勤務時間をグラフィカルに可視化する「タイムスケジュール表」（ワークスケジュール、ワースケとも呼ばれる）です。
時間帯ごとに誰が勤務しているかが一目でわかるため、「14時台のホールが手薄になるな」といった、より詳細な人員配置の把握と調整が可能になります。
Excelでの作成は非常に困難
ただし、このタイムスケジュール表をExcelで作成するのは、実は非常に困難です。
時間軸を15分単位にするだけで1日に96列もの膨大なセルが必要になり、数式も「月間の入力シートと、31日分のタイムスケジュールシートを連携させる」といった、シートをまたいだ複雑な関数（INDEX関数、串刺し計算など）を組む必要があります。
下手に作り込むと、かえってメンテナンスが誰にもできない「属人化の塊」になってしまう危険性も高いため、Excelでこの形式を実現しようとする場合は、相応のスキルと覚悟が求められます。
テンプレートは２種類のシートで構成
「タイムスケジュール表」は通常１日１枚で作成するものです。それを毎日１ファイルとするのは煩わしいですし、各スタッフの勤務時間を１日１日入力したとしてもある程度の期間（１週間、１ヶ月・・）の出勤状況をまとめて把握したいという場合には１日ごとに分かれたままでは非効率です。
そこで今回のテンプレートは、期間全体（１ヶ月間）の「シフト表」である「月間」シートと、１日ごとの「タイムスケジュール表」である「日別」シートの２種類のシートを用意し、できる限り入力する手間を省けるようにすることと、全体の把握をしやすくすることを目指して作成します。
＃「月間」シート
本シートは１ヶ月分のいわゆる「シフト表」です。スタッフや担当業務などのマスタ情報の登録と、スタッフの勤務を１日ごとに「開始／終了」時刻で入力をします。これらを登録することで、すべての日付の「日別」シートの「タイムスケジュール表」に反映されます。また、本シートは１ヶ月間の各種集計列も設けています。
これらにより、本シートでは各スタッフの１ヶ月間の総労働時間、勤務日数の確認、及び日ごとの担当業務ごとの出勤人数の確認ができるようになっています。
＃「日別」シート
本シートはシート名が「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」を参照することになります。
実際には更にこれを時間に変換する関数で整えて、最終的に各「日付」シートの「タイムスケジュール表」に反映しています。
********************
まだまだ紹介しきれませんが、今回のテンプレートでは他にも様々な関数を用いています。また、書式設定などを工夫することで時刻入力を簡素化するような仕組みも取り入れています。
ここでご紹介しなかったようなことに関しては、テンプレートを分析してみてください。また、使ってみてここはこうしたいなとか、ここは不便だなというところがあれば、アレンジして皆様の使いやすいテンプレート作成にもチャレンジしてみてください。
最後に
今回ご紹介したシフト表のテンプレートづくり、そして応用編としての「タイムスケジュール表」はいかがでしたでしょうか？テンプレートで使用したエクセル関数で使い方が分かりづらいものは少なかったと思います。
・カレンダー部分：「MONTH関数」「WEEKDAY関数」
・必要人数欄：「COUNTIF関数」
・勤務回数欄：「COUNTIF関数」「EMONTH関数」「DAY関数」
これに条件付き書式を織り交ぜただけで、だいぶ機能性もアップしたシフト表になったかと思います。今回ご紹介しなかった手法もたくさんありますので、アレンジにも挑戦してみてください。
ただし、繰り返しになりますが、やり過ぎると属人化が進んでしまうのでご注意ください。このような問題があるのがテンプレートの怖いところですので「シンプルかつ必要機能を揃える」ということを意識してみてください。。
今回ご紹介のテンプレートには操作方法を記載した説明シートもご用意していますので、是非ご覧になってください。
なお、当テンプレートに限らずエクセルで作成したシフト表の欠点として「勤務回数と必要人数のバランスを考慮出来ない」ということがございます。先ほどのテンプレートにおいて、勤務回数・必要人数それぞれが「どのくらい足りない・余っている」ということを確認することは出来るのですが、「どのようにすれば両方の条件を満たせるシフト表になるか」については人の力で行っていかなければなりません。
こちらの問題は実はよくある問題で、勤務回数・必要人数およびその他の条件のバランス調整に1週間掛かってしまうということもよくあります。このような場合は、バランス調整も含めて自動作成するシフト管理サービスがおススメです。
ＪＲシステムが提供する『勤務シフト作成お助けマンDay』では、ここまでご紹介したエクセルで実現できる自動レイアウトやチェック機能について、似たような機能を最初から備えているうえで、最適なシフトを自動で作成する機能を有しています。
「お助けマン」では、本利用と同じ機能を2か月間無料でトライアルできますので、「エクセルもいろいろできるのは分かったけど、シフト表の調整自体が大変だから、システムが自動で最適なシフトを作成出来るようになりたい」とお考えでしたら、是非チャレンジしてみてはいかがでしょうか？
