Excelなどの表計算ソフトでシフトを作るためのテンプレートを探していたのですが、なかなか希望通りのものに巡り会えなかったので自作してみました。
作りたかったものは、4桁で時間を入力すると、勤務時間を自動計算してくれるシフト表です。
普通の時間計算だと、時間として認識させるためにはコロン「：」を入れる必要があります。
ExcelではVBAなどのマクロを使うと簡単にできるようなのですが、VBAの知識は無かったので、自分のわかる機能で実装する方法を探した結果がこれでした。
テンプレートのダウンロード
まずは出来上がったテンプレートを公開していますので、こちらからどうぞ。
時間を4桁の数字で入力しても、何時間のシフトなのか計算できるようにしました。
年月日、曜日は1箇所に入力すれば自動計算されるように設定しました。
休日・総労働時間を自動で計算できるようにしました。
使い方
- 年月の太枠内に作りたいシフトの年月を入力する
- シフトの開始日が自動計算される。
- シフト期間の月、日、曜日が自動計算される。
- 土曜日・日曜日は赤背景が挿入される。
- 個人のシフトの入力は、出勤時間・退勤時間・休憩時間をコロン（：）を抜いた時間表記で入力する。
- ９時に出勤なら、「出」の欄に「900」と入力。
- 休憩時間が1時間なら「休憩」の欄に「100」と入力。
- 休憩時間が分単位なら、時間に直して入力する。
例）90分なら「130」 45分なら「45」
- 1日の労働時間、週間の労働時間、月間の労働時間、休日は自動で計算されるので、出勤・退勤・休憩の欄を入力するだけでOK。
時間の入力を4桁の数字で行う方法
Excelの入力では、4桁の数字をそのまま時間として表示したり、計算したりすることはできません。
今回のテンプレートで実装した方法は、入力された値を表現を変更して計算するという方法です。
このためにTEXT()を使います。
TEXT(データ値, "表示フォーマット")
の形で使います。
実際のデータではこんな感じになります。
| A | B | C | D | |
| 1 | 出勤 | 退勤 | 休憩 | 勤務時間 |
| 2 | 900 | 1800 | 100 | 8:00 |
| 3 | 1100 | 2000 | 100 | 8:00 |
勤務時間を自動で計算させるためにはD2・D3のセルにはTEXT()を使った計算式を入力します。
D2の場合はこのようにします。
= TEXT(B2,"00:00") - TEXT(A2,"00:00") - TEXT(C2, "00:00")
すると、書式がコロンありの時間に書き換えられて、時間としての計算がされるようになります。
実はこれだけでは不十分で、時間を表示させる書式設定をする必要が有ります。
セルの書式としてExcel上のセルの書式設定で設定すれば、時間の表示をすることができます。
合計した時間を表示するときは、「[h]:mm」と記載すると期間の意味で時間を表示することができます。
この場合は時刻のところにはフォーマットがないので、ユーザー設定として定義します。
年月の入力のみで自動で日付や曜日まで計算する方法
このテンプレートで次に力を入れたのが、日付の自動補完機能です。
何年の何月のシフトを作るのか？を入力すると、自動で日付と曜日まで入力してくれる機能です。
この機能の実装の流れはこんな感じです。
- 年月を手入力する
- 年月からシフト開始日を設定する
- シフト開始日の通し番号（シリアル値）を取得する。
- 1日ごとにシリアル値が1増えるように計算式を組む。
- シリアル値から月日・曜日を入力する。
はじめに入力値からシフトの開始日を作成します。
シフトの開始日は会社ごとに異なるでしょうから、それぞれの状況に合わせて再設定した方がいいと思います。
開始日を作成するにはCONCATENATE()で文字列として作成します。
CONCATENATE("文字列1", データ値, "文字列2", ･･･)
この関数ではセルのデータや文字列をつなげて、文字列として出力します。
上のスクリーンショットのA3では、年と月の入力値からシフト開始日を文字列として一旦作成しています。
次に、文字列になった年月日をシリアル値という通し番号に変換します。
上のスクリーンショットのA5がこれに該当します。
シリアル値は整数が日付、小数点以下が時刻を表します。
つまりシリアル値に1を足すと基準の日付の1日後ということになります。
これを利用して、すべての日付を自動計算しています。
シリアル値を求めるにはDATEVALUE()を使います。
DATEVALUE("日付の文字列")
A5はシフト開始日のシリアル値を求めているので、こう書いています。
=DATEVALUE(A5)
これで「2016年5月1日」のシリアル値が「42491」とわかるので、1日ずらすたびに1を足していけばいいことになります。
これをその下側（A6以下）に適応しています。
ここからシフト表の日付・曜日を自動取得していきます。
まず、日付を取得するにはDAY()を使います。
DAY(シリアル値)
そのシリアル値が示している年月日の日だけを表示します。
曜日を取得するにはWEEKDAY()を使用します。
WEEKDAY(シリアル値)
シリアル値が示している日付の曜日を数字で表示します。
デフォルトでは「１～７」が「日～土」にそれぞれ対応します。
その上で、曜日を表示させるセルの書式設定を変更します。
ユーザー設定で「aaa」とすると、曜日を表す１～７の数値が日～土に変換されて表示されます。
解説が長くなりましたが、今回のシフト表で使った機能はこんなところです。
日付・時刻の計算はなれるまでは少し大変ですが、なれてしまえばそれほど難しい物ではないので、この機会にオリジナルのシフト表を作成してみてはどうでしょうか？
