【サンプル無料提供】エクセルを使って自作!シフト表テンプレート化のコツ

3234362_l

過去の記事「エクセルで効率的にシフト作成・管理!失敗しない3つのポイント」では、レイアウトやプリントアウト後のイメージに焦点を当てたシフト表づくりをご紹介しました。エクセルに少し慣れてくると、思った以上にレイアウトなどが自由に表現できることに気づき、紙で作っていた頃に比べると格段にシフト表づくりが楽になったように感じられたのではないかと思います。
しかし何回かエクセルでのシフト表づくりを続けると少し欲が出てきて、毎回レイアウトを組むことが煩わしくなったり、組んだシフトのチェックに結構時間がかかったりといった悩みが出てはいないでしょうか?

前回記事ではテンプレートのダウンロードをあまりお勧めしていませんでしたが、自分で1から作ったテンプレートならばできる限り使い回すことができるようにするのがデジタル化をするメリットの1つです。ただ一方でこれも前回記事で指摘したとおり、あまり作り込み過ぎてしまうと属人化に繋がり易いという危うさもあります。
そこで今回の記事では、以下の部分に目的を絞り、テンプレートとして使えるシフト表を敢えて作ってみます。

【今回のテンプレート化によって実現すること】
  1. カレンダー部分の更新を最小限度の手順で更新できるようにする。
  2. 日別の勤務記号ごとの必要人数をチェックしやすくする。
  3. スタッフ別の勤務記号ごとの割当回数をチェックしやすくする。

今回ご紹介するシフト表テンプレートのサンプルはダウンロードしてご利用いただくことができます。
ご興味いただけましたら、お読みいただいた後でも結構ですので是非ダウンロードしてご利用ください。
(※以下のボタンをクリックするとダウンロード用のフォームページが開きます。)

テンプレートサンプルダウンロード

下図は、テンプレートファイルの「シフト表テンプレート」というシートです。罫線の枠内で背景色があるセルは、毎月入力し直す必要のない固定値か関数が埋め込まれている部分です。それ以外の白いセルは、スタッフ名や勤務記号などの値を入力する部分となっています。

01_シフト表テンプレートシート

では、このサンプルの内容を元に解説していきます。

カレンダー部分の更新はひと手間で済ませよう

一見、カレンダー部分の毎月の更新は大した手間ではないように感じられて、その実、月末日の違いや土日の色の変更など、多少ですが煩わしく感じられます。
しかし、やることは決まっているので、少し作り込んでおけば最低限の手順でこうした更新が自動化できます。

#月の末日は関数で制御

今回、カレンダー部分の更新で目指すのは、1箇所(1セル)だけ値を変えれば月末日や土日の色の変更まで済んでしまうようにすることです。

変更する値はシフト表の当該月を表す「月度」という項目1箇所で、ここではその入力するセルをB1セルとします。このセルには当該月の1日日付を「2020/4/1」のように西暦日付で入力します。ここの表記は入力したとおりのものでも構わないのですが、項目名を「月度」としたように、ここではセルの書式設定を「yyyy""mm""」とし、「202004月」と表記させます。

02_月度入力

次に日付の表記部分です。
この例では毎月の起点となる1日をD2セルとします。(ちなみに日付部分のセルの書式設定は「d」と日付のみ表示するようにしています。)
ここでは「月度」の値をそのまま参照するので、関数としてはそのまま「=$B$1」と入力するだけでも構いませんが、ここはもう一工夫して「月度」が空欄の場合は何も表示しないように「=IF($B$1<>"",$B$1,"")」とIF文を使ってみましょう。なお、B1セルは列も行も絶対参照なので、列を表す「B」と行を表す「1」、それぞれの前に絶対参照を示す「$」を入れます。

さて、日付の2日以降について、前回記事では隣のセルの値に+1するという関数を紹介しました。今回も同じ考え方でD2セルの隣のE2セルに「=D2+1」として31日までのセルにコピーすれば済むのですが、テンプレート化する上で1つ問題点が残ります。
それは月末日が月によって違うため、31日に満たない月の場合、末尾の何日かに翌月の日付が入ってしまうという点です。
(以下の図の例では「月度」を「2019/2/1」とした場合、3日も余計な日付が表示されてしまうことが分かります。)

03_月末日NG

そこでまたIF文を使ってこの問題の回避策を取り入れましょう。
使う関数は「MONTH関数」です。これは参照したセルが何月かを調べる関数です。そこでまず以下のように考えます。
 「月度の月前日日付+1日の月が同じ場合はそのまま前日日付1日にしよう、
  しかし、同じでない場合は空欄にしよう。」

これを以下のような関数にしてE2セルに入力しようと思います。
 「=IF(MONTH($B$1)=MONTH(D2+1),D2+1,"")」
しかし、実はこれだけだと前日日付が空欄の場合に結果がエラー表記になってしまうので、
 「=IF(D2<>"",IF(MONTH($B$1)=MONTH(D2+1),D2+1,""),"")」
として、更にIF文で囲んで前日日付が空欄でない場合に先ほどのIF文が実行されるようにします。
これを3日から31日(F2~AH2セル)まで数式だけのコピーをしましょう。

ここまでやってみたら「月度」のセルを色々な月に変更してみてください。2月でも11月でも、その月の末日までしか日付が入らず、余った枠は空欄になります。

04_月末日OK

#土日の文字色は条件付き書式で制御

今度は土曜日を青文字に、日曜日を赤文字に自動で書式を変える方法です。

まず曜日を示すセルに値を入れます。前回記事では日付セル(D2)の下のセル(D3)に「WEEKDAY関数」を埋め込んだ上で、セルの書式設定を「aaa」にして「土」「日」などの曜日を表示するようにしましたが、セルの書式設定をしていれば関数は「=D2」のように日付セルを単純に参照するだけで曜日を表示することもできるので、今回はそのようにしています。

05_曜日表示

次に文字色ですが、日付と曜日の範囲それぞれの色を曜日によって変更します。
日付と曜日の範囲(D2AH3セル)を囲んでからエクセルメニューの「条件付き書式」→「新しいルール」で「新しい書式ルール」のウィンドウが開きます。

06_条件付き書式選択

「ルールの種類を選択してください」欄から一番下の「数式を使用して、書式設定するセルを決定」を選び、ウィンドウ下部の「ルールの内容を編集してください」をここでは土曜日の設定として下図のようにします。
 ・「次の数式を満たす場合に値を書式設定」→「=WEEKDAY(D2)=7
 ・「書式」ボタンから文字色を青系の色で選択

07_書式ルールの編集

同じように日曜日も以下のように条件付き書式に設定すると、日曜日の日付が赤文字になります。
 ・「次の数式を満たす場合に値を書式設定」→「=WEEKDAY(D2)=1
 ・「書式」ボタンから文字色を赤系の色で選択

これで土曜日は青色に、日曜日は赤色に文字色が自動で変更されました。月の末日の時と同様、「月度」を変更してみてください。「月度」に連動して自動的に土日の日付の色が変わるようになりました。

08_曜日色変更

これで今回のテンプレートのカレンダー部分は「月度」のB1セル1箇所だけを修正すれば、日付や曜日、文字色まで自動更新できるようになりました。
ところで「祝日の場合にも自動で赤くできないの?」という疑問もあると思います。
もちろん方法はあります。しかし今回は敢えて踏みとどまってここまでにします。

シフト表自動作成に関する資料をご用意しています

1日ごとの必要人数をチェックできるようにしよう

勤務調整をしていて頭を悩ますことの一つに、日ごとの出勤者の人数調整があります。
 「今日は日勤者がやけに多いな?」
 「あれ?夜勤の人数が足りないぞ!」
というようなことはよく起きがちです。
そこでこのテンプレートでは、1日1日の勤務記号ごとの必要人数をチェックできる欄を用意して、人数の過不足が一目で分かるようにしてみましょう。

ここからはサンプルの「シフト表_202004」シートを用いて説明を進めます。
このシートには既にスタッフ名や4月のシフト予定などが入ったサンプルになっています。
(※「休」「有」の背景色が条件付き書式で変わるようになっていますが、ここでは説明を割愛します。前回記事など参考にしてください。)

09_シフト表202004シート

このシートの26行目からは「必要人数」欄とし、シフト表で用いられている勤務記号(「早」「遅」など)ごとに2行ずつ枠を用意します。 勤務記号を入力するB列は上下2行分のセルを結合します。(例:B27セルとB28セル、結合後は「B27」として参照します。)
この欄の「早」の勤務記号(2728行目)を例に内容を紹介します。

まずチェックしたい勤務記号である「早」をB27セルに入力します。
2行あるうちの1行目は「必要人数」行とし、「早」のスタッフの日付ごとに必要な人数を任意の数値で入力します。
2行目は「日計」行とし、その日割り当てられた「早」の人数を集計します。ここで使われている関数は、前回も登場した「COUNTIF関数」です。1日の集計セルであるD28セルには次のように入力します。
 「=COUNTIF(D$4:D$24,$B27)
これは、1日のシフト表部分の範囲(D4~D24セル)「早」(B27セル)と同じ文字が何個あるかを集計する内容になります。(スタッフ行の424行目、勤務記号のある列「B」の前に絶対参照を示す「$」があることも注意!)
また、この行の範囲(D28AH28セル)には以下のような内容で2つの条件付き書式を設定します。

10_条件付き書式_必要人数_赤

11_条件付き書式_必要人数_青

これは「必要人数」に入力した値と「日計」で集計された値とを比較し、必要人数より日計が小さければ日計の文字を赤く、大きければ青くするものです。これにより、勤務記号ごとに毎日必要とする人数が、シフト表の中できちんと満たされているか一目で分かるようになります。
このチェック欄を勤務記号の数に応じて増やしたい場合は、この2行を選択して必要人数欄の末尾行の下にコピーすれば、関数や条件付き書式もそのままコピーできます。必要な分だけコピーしてください。

勤務シフト作成お助けマン

1ヶ月の勤務回数をチェックできるようにしよう

日ごとの必要人数のを終えると、月全体での勤務のバランスも問題になってきます。
 「Aさんの休みが少な過ぎた!」
 「Bさんの勤務に遅番ばかり割り当たってしまった!」
1日1日の調整にばかり気を取られるとこんな状況に陥って、また1日1日見直しなんてことも・・・。 
この問題はどちらが先ということでもなく、また一朝一夕で片付くことでもありませんが、少しでも効率よく進めるために、このテンプレートでは1ヶ月の勤務記号ごとの勤務回数もチェックできるようにしてみましょう。

引き続き「シフト表_202004」シートを用いて説明します。
このシートのAIAN列は「勤務回数」欄とし、スタッフごとの1ケ月の休みなど、勤務記号ごとの回数を集計します。
これらの列の2行目には勤務記号を、3行目には当該月にその勤務記号を割り当てたい回数を入力できるようにします。
4行目以降のスタッフごとの行には、そのスタッフの勤務記号ごとの割当回数を集計する関数を設定します。「休」の勤務記号を設定したAM列を例に関数の説明をします。

AM2セルには勤務記号の「休」を、AM3セルにはその月の休みの割当回数(ここでは「9」回)を、スタッフごとの行である4~23行目については、4行目の「佐藤」さんを例に「休」の回数を集計する以下の関数をAM4セルに入力します。
 「=COUNTIF($D4:$AH4,AM$2)
これは、1日~31日(D4~AH4セル)の範囲「休」(AM2セル)と同じ文字が何個あるかを集計しています。(ここでも列の「D」と「AH」、行の「2」の前に「$」があることに注意!)
そして「休」を設定したAM列の範囲(AM4AM23セル)には、必要人数と同様、条件付き書式で「休」の割当回数(AM3セル)と比較して小さければ赤文字で、大きければ青文字で表示するように設定します。

12_条件付き書式_勤務回数_赤

13_条件付き書式_勤務回数_青

これにより「休みの回数を4月は平等に9回割り当てたい」という時に色が赤か青に変わっていれば、休みの回数が9回でない、見直しが必要なスタッフがいるということが分かります。

なお、AO列は2行目に「計」、3行目に「30」とサンプルには入っています。これはAO3セルに「=IF($B$1<>"",DAY(EOMONTH($B$1,0)),"")」という関数を入力しており、「月度」に入力した日付に対し、「EMONTH関数」でその月の末日を求めた結果から「DAY関数」で日付部分のみ抜き出して表示するようにしています。
この列の4行目以降には、勤務記号ごとの集計列(AIAN列)の結果を集計する「SUM関数」が入力されており、AO3列の値と比較して少なければ赤文字に、多ければ青文字にする条件付き書式を設定しています。これによりその月の日数分のシフトが埋まっているかチェックできる欄になっています。

あわせて読みたい記事
シフト作成を引き継ぐ際に、気を付けたい5つのポイント
エクセルで効率的にシフト作成・管理!失敗しない3つのポイント

最後に

今回ご紹介したシフト表のテンプレートづくりはいかがでしたでしょうか?
テンプレートで使用したエクセル関数で使い方が分かりづらいものは少なかったと思います。
 ・カレンダー部分:「MONTH関数」「WEEKDAY関数」
 ・必要人数欄:「COUNTIF関数」
 ・勤務回数欄:「COUNTIF関数」「EMONTH関数」「DAY関数」
これに条件付き書式を織り交ぜただけで、だいぶ機能性もアップしたシフト表になったかと思います。
今回敢えてご紹介しなかった、もう一歩踏み込んだ手法もいくつかありますが、それはまたの機会があればご紹介します。繰り返しになりますが、やり過ぎると属人化が進んでしまうのがこうしたテンプレートの怖いところです。
今回ご紹介のテンプレートには操作方法を記載した説明シートもご用意しています。これらを十分にご理解いただいてから、ご自身の目的にあったシフト表へのアレンジにも挑戦してみてください。

テンプレートサンプルダウンロード

なお、JRシステムが提供する勤務シフト作成お助けマンでは、ここまでご紹介したエクセルで実現できる自動レイアウトやチェック機能について、似たような機能を最初から備えています。
「エクセルもいろいろできるのは分かったけど、いっそこうしたサービスでもっと高度なシフト表を、しかも自動で作れるようにしたい」とお考えでしたら、2ヶ月間無料でトライアルが可能な本サービスにもチャレンジしてみてはいかがでしょうか?

 

働き方改革におけるシフト作成ガイドダウンロード