会社の従業員やチームのシフト管理をする際、高額なシステムを導入するほどの規模感でもないためエクセルで簡単にシフト管理表を作成したいということはないでしょうか。
各メンバーのシフトを入力すれば、自動的に月々の勤務時間が計算できれば充分という場合、エクセルでサクッと作れてしまいますので、その手順と私が作成したフォーマットをご紹介したいとおもいます。
- エクセルでのシフト管理表の作成方法
- 無料テンプレートの取得
フォーマット作成の前提条件
今回の記事で紹介するテンプレートの前提条件を箇条書きにしてみました。
- メンバーごとの1カ月の勤務予定を入力するために使用
- 毎月使用していくので、カレンダーは自動更新
- 勤務時間の区分が決められており、シフト表に入力して勤務日時を管理
- 日ごと、月ごとの勤務時間を自動計算
ざっとこのくらいの条件が満たせれば充分でしょうか。
それではさっそく作成していきましょう。
手順１：枠組みの作成
まずはフォーマットの枠を作成していきます。
- 黄色の網掛け：自分で入力するセル
- 灰色の網掛け：関数で自動入力されるセル
今回作成したのは、大きく分けると以下の3つの表です。
- 時間区分表：シフトの時間割を指定
- シフト管理表：誰がいつどの時間区分で勤務するかを指定
- 勤務時間管理表：誰がいつ何時間働いたのかを表示
それでは中身を詳しく見ていきましょう。
1-①：カレンダーの年月を指定する枠を作成
何月のシフト管理表なのかを把握するために加えて、カレンダーを毎月更新できるようにするため、年月を指定するセルを作成しておきます。
また、年月を入れるセル上で右クリックして、「セルの書式設定 > 表示形式 > ユーザー定義」から、数字を入力したら「年」「月」と表示されるようにしておくと便利です。
1-②：シフトの時間区分表を作成
シフト管理表の中に記入する記号と、各シフト区分での勤務時間を記載します。
シフトの開始時間と終了時間、休憩時間を記入し、「=終了時間-開始時間-休憩時間」を計算すれば1日の勤務時間が計算できます。
1-③：メンバーの名前を入力
シフト管理を行うメンバーの名前を入力します。
1-④：シフトの時間区分を入力するプルダウンを作成
シフトの時間区分で決めている「記号」をリスト化し、決められた記号でシフトを埋められるようプルダウンを作成します。
プルダウンの作成は、「データ > データの入力規則」でウィザードを出現させ、
- 入力値の種類：リスト
- 元の値：記号を入力したセルの範囲
と設定して「OK」を押下します。
プルダウンにしておくことで文字の揺らぎがなくなり、後々勤務時間を集計する際に入力ミスによる集計漏れを防ぐことができます。
1-⑤：シフト管理表に入力した名前を勤務時間管理表に転記
シフト管理の名前を変更したら、自動的に勤務時間管理でも名前が変更されるようにします。
C19セルに入力した関数はこちらです。
=IF(C10=””,””,C10)
こうしておけば、メンバーが入れ替わった時などにいちいちすべての名前を変更する必要がなくなります。
1-⑥：シフト管理表に入力した日付と曜日を勤務時間管理表に転記
こちらも同様に、シフト管理表に入力された内容を勤務時間管理表に反映するようにしておきましょう。
関数は手順1-⑤と同じです。
手順２：自動更新できるカレンダーの作成
ここからは自動更新するカレンダーの作成手順を説明します。
年月によって、月の日数や曜日は変動しますので、この日数や曜日の変化を関数で自動的に変更できるようにします。
2-①：月末までの日数を表示し、その月にない日付は表示しない
29日のセル(AF8)に入力すべき関数はこちらです。
=IF(DAY(DATE($年,$月,29))=29,29,””)
年と月は、B5,C5セルで指定していますので、B5,C5セルを絶対参照しておきます。
30日、31日のセルにも同様に関数を入力します。
こうすることで、31日のある年月には31日まで表示され、31日のない年月には31日が表示されないカレンダーを作成することができます。
2-②：年月に対応した曜日を表示させる
次に、あってもなくても良いですがあったほうが便利なので曜日を表示させます。
入力する関数はこちらです。
=DATE($年,$月,$日)
年月はB5,C5セルで指定しているものを絶対参照し、日は手順2-①で作成した日付を参照します。
すると、年月日を表示するようになりましたので、ここからまた「セルの書式設定 > 表示形式」を選択し、ユーザー定義で「aaa」とすると、曜日を表示するようになります。
手順３：勤務時間計算表の作成
ここまででシフト管理表までの作成が完了しました。
最後に、勤務時間管理表でみんなの勤務時間が把握できるようにしておきましょう。
3-①：記号を検索値として勤務時間を計算
シフト管理表に入力した記号から、勤務時間管理表へ勤務時間を計算するようにします。
今回D19セルに入力した関数はこちらです。
=IFERROR(VLOOKUP(D10,$I$3:$M$6,5,FALSE),””)
これをD19セルからAH23セルまでコピーしました。
D10はシフト管理表の記号が入力されたセルであり、この記号を検索値としてシフトの時間区分($I$3:$M$6)から検索してくる指示を与えています。
ここで手順1-④でのプルダウンの作成が活きてくるわけですね。
3-②：24時間以上の時間を表示
次に、24行目に日ごとのメンバーの勤務時間合計、AI列にメンバーごとの1カ月の勤務時間合計を表示するため、SUM関数を入力しました。
しかし、画像を見ていただくと、AI列の表示がおかしいことに気づきましたでしょうか？
通常のままだと、時間の計算は24時間ごとに1周してしまいますので、24時間以上の表示をしないようになっています。
そこで、またしてもセルの書式設定を変更することで24時間以上の時間を表示できるようにします。
時間の合計を計算したセルを範囲選択し、右クリック、「セルの書式設定 > 表示形式」を選択します。
ユーザー定義を選択すると、種類に「h:mm」と表示されているとおもいますので、これを「[h]:mm」と変更します。
時間を表す「h」の両端に「[ ]」を付けることで、24時間以上の時間が表示できるようになります。
手順４．表の見た目を整える
あとは、書体を変えてみたり、枠線を太くしてみたり、日曜日や休みの日だけを赤色にしてみたり、それぞれ好みに合わせて表を飾っていきましょう。
すると、シフトの入力によって自動で勤務時間の計算ができ、何年後でも使えるシフト管理表の完成です。
[無料・登録なし]シフト管理表のテンプレートダウンロード
今回の手順で作成したシフト管理表のテンプレートのダウンロードリンクを貼っておきますので、ご興味のある方は是非ダウンロードしてみてください。
コメント