Excelでシフト表の時間計算するなら関数が便利！コツや注意点を解説

Excelでシフト表を作成し社員やアルバイトの労働時間の管理を効率よく行いたいという担当者の方も多いのではないでしょうか。この記事では、エクセルでのシフト作成の方法や、作成時のポイントや注意点、また、初心者でもわかりやすいよう関数を用いたシフト表の作り方を紹介します。

▼更にシフト作成について詳しく知るには？
シフト表作成に役立つツールとは？メリットやおすすめは？

▼面倒なシフト作成をAIで自動化
シフト自動作成AIクラウドHRBEST紹介ページ

Excelで作れる主なシフト表の種類

エクセルに詳しい人ならまっさらな状態から関数を使ってシフト表を作れますが、エクセルに慣れていない方や今すぐ作成したいという場合は、まずテンプレートの種類を知り活用豊方法を知りましょう。

月間シフト表

月間シフトとはカレンダーのように、月の流れに合わせて使えるシフト表です。このシフト表は職場で共有するシフト表というよりは、個人で管理するシフト表として使うのが一般的です。月間のカレンダーのようなイメージになるため、１日に使えるマスの大きさは小さく、勤務時間の詳細まで情報を表示できません。どの従業員が出勤しているかを管理する場合に向いています。勤務開始時間と勤務終了時間が固定のワークスタイルだと使いやすいでしょう。

週間シフト表

週間シフトは１週間のシフトが記載されたシフト表です。曜日で管理できるため従業員の出勤状況や勤務時間の管理がしやすいのがメリット。急な欠員が発生しても、１週間の中で調整可能です。詳細を記入できる枠を作成することで細かな調整・対応もしやすいといった特徴もあります。

タイムシフト表

タイムシフトとは、名前の通り、その日の営業時間に必要な人員が記載されているシフト表です。一般的には横軸に時間が記載されており、縦軸には従業員の名前を入力します。そして該当する勤務時間分の横棒グラフを書き、見た目も分かりやすく表現します。その日の勤務状況がわかりやすい反面、工数が多くなり作成に手間がかかるといった特徴があります。

シフト表はExcel関数で自動化すれば効率的

シフト表は、従業員の名前や担当業務、勤務時間をわかりやすく、かつ管理しやすいように作る必要があります。従業員数が多い職場や、早番や遅番など勤務時間のパターンが多様な職場において、関数は非常に便利な機能です。シフト作成で使用する代表的な関数には、次のような種類があります。

DATE関数で日付を自動入力

シフト表には必ず日付を入力する際に、「DATE関数」を使用します。月間シフト表の場合、シフト表には「◯年◯月」とわかりやすい位置に表示するでしょう。まずはエクセルのフォーマット上に「年」と「月」を入力する場所を作ります。例えば、エクセルのA1に「2020」、B1に「年」、C1に「4」、D1に「月」と入力します。セルの幅によっては少し文字間にすき間があるかもしれませんが、見た目は「2020年4月」と表示されます。前準備を終えた段階で、日付を表示したいセルに「=DATE(A1,C1,1)」と入力すると、「2020/4/1」と表示されます。日付は表示された箇所から縦軸にも横軸にも伸ばすことができます。

WEEKDAY関数で曜日を自動入力

シフト表には日付とセットで曜日も入力していく際に、「WEEKDAY関数」で簡単に曜日を表示することができます。先程の例であれば「B1」には「2020/4/1」と表示されているはずです。B2に「=WEEKDAY(A2)」と入力すると「4」と表示されます。なお、「1」は日曜日を意味するので、「4」は水曜日です。

次に、B3からAF3までをドラッグして選択し、右クリックで「セルの書式設定」を行いましょう。「ユーザー定義」の種類に「aaa」と入力すると、「4」が「水」と変換されて表示されます。

COUNTA関数で出勤日数を自動計算

従業員が月に何日出勤するかを表示するためには「COUNTA関数」を使います。このCOUNTA関数では、文字や数字などが入力されているセルの数をカウントします。例えば、上図の田中さんの出勤日数をカウントするには、「=COUNTA(B2:D2)」を出勤日数を表示させたいセルに入力すると、月間の出勤日数が自動計算されます。

COUNTIF関数で時間帯別人数を自動計算

早番、遅番、日勤、夜勤などのシフト体制であれば、それぞれの時間帯ごとに稼働する人数をカウントする際に、COUNTIF関数「=COUNTIF(範囲、検索条件)」を使用します。
まずはシフト表に「早番」「日勤」など従業員ごとにシフトを入力しましょう。上図であれば、田中さんの早番は「=COUNTIF(B2:D2,”早”)」で求めることができます。

Excelでシフト表を作成する際に覚えておきたいコツ

ここまで、シフト表を作成する上で必要となる一部エクセル関数をご紹介いたしましたが、
いくつかエクセルでシフト表を作成する際に覚えておきたいコツをお伝えします。

一定した休憩時間はTIME関数を使う

休憩時間が一定の場合は、時間、分、秒のシリアル値を返す TIME 関数を用います。TIME 関数は、TIME(時間、分、秒)の式で表されます。1時間の休憩なら「=TIME(1,0,0)」です。勤務時間を計算するための表を作成します。出社時間、退社時間および実働時間を入力するための表を作成します。上図では、月曜日の実働時間は「=C2-B2-TIME(1,0,0)」となります。

合計時間が24時間を超える場合の対応

上図で実働時間の合計を求めようとD8に「=SUM(D2:D6)」と入力すると、合計時間が 24 時間を越えてしまい、日にちが繰り上がって正しく表示されません。合計時間を正しく時間表示するには、セルの書式設定の表示形式を変更します。[ユーザー定義] をクリックします。次に、[種類] ボックスに「[h]:mm」と入力し、[OK] をクリックします。

勤務が日付をまたぐ場合の対応

夜勤やシフト勤務などがある場合、始業時刻の値が終業時刻の値よりも大きくなってしまい、単純に「=＜終業時刻セル＞-＜始業時刻セル＞」では計算できません。
このような場合、IF関数を使って、「=IF(＜始業時刻セル＞ > ＜終業時刻セル＞,1 – ＜始業時刻セル＞ + ＜終業時刻セル＞,＜終業時刻セル＞-＜始業時刻セル＞) 」とすればよい。＜始業時刻セル＞が＜終業時刻セル＞よりも大きければ、「1-＜始業時刻セル＞」で24時から＜始業時刻セル＞を引き、そこに＜終業時刻セル＞を足せば、日付をまたいだ勤務時間が計算できます。上図であれば、実働時間を「=IF(B2>C2,1-B2+C2-D2,C2-B2-D2)」と求めることができます。

Excelでシフト表を作成する際の注意点

次に、いくつかエクセルでシフト表を作成するときの注意点を解説していきます。

Excel関数でミスをすると正しく表示されない

エクセルの関数は見やすく、自動設定ができ、効率的にシフト表の作成ができるようになります。しかし、エクセル関数は種類も多く慣れるまでに時間がかかり、間違った数値設定をしてしまうと合計数に差異が出たりなどミスも起きやすくなるのが実態です。

業界ごとのルールに対応しきれない

最初はテンプレートや少しの関数で十分シフト表が作成できるかもしれませんが、
自社ルールや業界ならではのシフト表を作るのは非常に時間がかかりますし、対応しきれません。

Excelに限界を感じたらシフト自動作成ツール「HRBEST」がおすすめ！

これまで、シフト作成に必要な作成方法を説明してきましたが、Excelのシフト表では関数など複雑な処理が必要になり、ミスや自社や業界ルールに対応できない場合があります。そこで、AIでシフトを自動作成できる「HRBEST」をおすすめします。HRBESTは、従業員から希望シフトを提出してもらうだけで、自動的に集計を行い、最適化されたシフトを提案するツールです。無料でのお試しにも対応しているので、低コストでシフト作成の自動化を始められます。

まとめ

シフト表を作成するにはExcelの機能が便利ですが、計算したい項目が増えれば増えるほどテンプレート化が複雑になり、個人での対応が難しくなってしまいます。
一方、シフト作成ツールを使用すれば、簡単な設定で誰でもシフトの作成ができるようになります。AIクラウドの「HRBEST」は、ワンクリックで自動的にシフトを決定できるツールです。シフトの作成にお悩みの方はぜひお問い合わせください。