更新日：2022/11/04
簡単な計算の実施から表の作成まで幅広く活用することが出来る、エクセルなどの表計算ソフト。シフト表の作成にもエクセルを活用している人は多いと思います。
エクセルに少し慣れてくると、思った以上にレイアウトなどが自由に表現できることに気づき、紙で作っていた頃に比べるとカレンダー形式のシフト表の作成が格段に楽になったように感じられたのではないかと思います。
しかし何回かエクセルでのシフト表づくりを続けると少し欲が出てきて、毎回レイアウトを組むことが煩わしくなったり、組んだシフトのチェックに結構時間がかかったりといった悩みが出てはいないでしょうか？
今回の記事では、個人がエクセルを使ったシフト表管理をもっと楽にするために、もう少し凝ったシフト表テンプレートの作成方法についてご案内したいと思います。
エクセルのテンプレートは自分で１から作ることも出来ます。その場合であれば、作成したテンプレートを使い回すことができますので、手書きからエクセルに変更するメリットの１つと言えます。ただし一方で、エクセルをあまり作り込み過ぎてしまうと属人化に繋がり易いという危うさもあります。
そこで今回の記事では、以下の部分に目的を絞り、テンプレートとして使えるシフト表の作り方を説明していきます。作成したテンプレートを使ってシフト作成をカンタンにしてみましょう。
【今回のテンプレート化によって実現すること】
- カレンダー部分の更新を最小限度の手順で更新できるようにする。
- 日別の勤務記号ごとの必要人数をチェックしやすくする。
- スタッフ別の勤務記号ごとの割当回数をチェックしやすくする。
今回ご紹介するシフト表テンプレートのサンプルは下記ボタンよりダウンロードしてご利用いただくことができます。
▼ 時間で作成するタイムスケジュールのエクセルテンプレートについては、以下で解説しています
【無料ダウンロード】自分で作ろう！エクセルで作るタイムスケジュール表のテンプレート化のコツ
下図は、テンプレートファイルの「シフト表テンプレート」というシートです。罫線の枠内で背景色があるセルは、毎月入力し直す必要のない固定値か関数が埋め込まれている部分です。それ以外の白いセルは、スタッフ名や勤務記号などの値を入力する部分となっています。
では、このサンプルの内容を元に解説していきます。
カレンダー部分の更新はひと手間で済ませよう
一見、カレンダー部分の毎月の更新は大した手間ではないように感じられて、その実、月末日の違いや土日の色の変更など、多少ですが煩わしく感じられます。
しかし、やることは決まっているので、少し作り込んでおけば最低限の手順でこうした更新が自動化できます。
▼あわせて読みたい記事
エクセルで効率的にシフト作成・管理！失敗しない３つのポイント
【テンプレート無料提供】エクセルで作る！タイムスケジュール表テンプレート
シフト作成を引き継ぐ際に、気を付けたい5つのポイント
＃月の末日は関数で制御
今回、カレンダー部分の更新で目指すのは、１箇所（１セル）だけ値を変えれば月末日や土日の色の変更まで済んでしまうようにすることです。
変更する値はシフト表の当該月を表す「月度」という項目１箇所で、ここではその入力するセルを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列の値と比較して少なければ赤文字に、多ければ青文字にする条件付き書式を設定しています。これによりその月の日数分のシフトが埋まっているかチェックできる欄になっています。
最後に
今回ご紹介したシフト表のテンプレートづくりはいかがでしたでしょうか？テンプレートで使用したエクセル関数で使い方が分かりづらいものは少なかったと思います。
・カレンダー部分：「MONTH関数」「WEEKDAY関数」
・必要人数欄：「COUNTIF関数」
・勤務回数欄：「COUNTIF関数」「EMONTH関数」「DAY関数」
これに条件付き書式を織り交ぜただけで、だいぶ機能性もアップしたシフト表になったかと思います。今回ご紹介しなかった手法もたくさんありますので、アレンジにも挑戦してみてください。
ただし、繰り返しになりますが、やり過ぎると属人化が進んでしまうのでご注意ください。このような問題があるのがテンプレートの怖いところですので「シンプルかつ必要機能を揃える」ということを意識してみてください。。
今回ご紹介のテンプレートには操作方法を記載した説明シートもご用意していますので、是非ご覧になってください。
なお、当テンプレートに限らずエクセルで作成したシフト表の欠点として「勤務回数と必要人数のバランスを考慮出来ない」ということがございます。先ほどのテンプレートにおいて、勤務回数・必要人数それぞれが「どのくらい足りない・余っている」ということを確認することは出来るのですが、「どのようにすれば両方の条件を満たせるシフト表になるか」については人の力で行っていかなければなりません。
こちらの問題は実はよくある問題で、勤務回数・必要人数およびその他の条件のバランス調整に1週間掛かってしまうということもよくあります。このような場合は、バランス調整も含めて自動作成するシフト管理サービスがおススメです。
ＪＲシステムが提供する『勤務シフト作成お助けマンDay』では、ここまでご紹介したエクセルで実現できる自動レイアウトやチェック機能について、似たような機能を最初から備えているうえで、最適なシフトを自動で作成する機能を有しています。
「お助けマン」では、本利用と同じ機能を2か月間無料でトライアルできますので、「エクセルもいろいろできるのは分かったけど、シフト表の調整自体が大変だから、システムが自動で最適なシフトを作成出来るようになりたい」とお考えでしたら、是非チャレンジしてみてはいかがでしょうか？
