「勤怠管理表を作ってみたい」「Excelで勤怠管理表を作る方法が知りたい」
こんな疑問を解消します。
勤怠をアナログで管理するのは大変ですよね。
でもネット上にある勤怠管理表だと、カスタマイズが難しく自分に合わないというケースもよくあります。
そこでExcelを使えば、オリジナルの勤怠管理表を作ることができて、手間のかかる勤怠管理も自動化が可能です。
そこで今回は、以下のような勤怠管理表をExcelで作成する方法を解説します。
この勤怠表では「開始時刻」、「終了時刻」、「休憩時間」を入力することで「労働時間」、「残業時間」、「深夜労働時間」、そして「給与」を自動で計算することができます。
本記事では、実際に手を動かしながらExcelでの勤怠管理表の作り方について詳しく説明します。
Excelで勤怠管理表を作る際に特に重要なポイントは以下の3つです。
・時刻と時刻の計算方法
・MAX関数の活用
・時刻データを数字に変換する方法
これらのポイントを押さえることで、Excelでの時刻管理がより簡単かつ正確になります。
自分で勤怠管理表を作ってみたい方は、ぜひ参考にしてみてください。
動画で見たい方はこちら！
目次
勤怠管理表の作り方
Excelで勤怠管理を作る手順は以下の通りです。
・見出しの作成
・値の入力
・労働時間の計算
・残業時間の計算
・深夜時間の計算
・24:00を超えるケースの対応
・テーブル化
・入力されたデータの集計
それぞれ解説していきます。
見出しの作成
まずは勤怠管理表の見出しを作成します。
今回は以下のような見出しにします。
値の入力
見出しができたら、実際にデータを入力していきます。
日付、開始時刻」、「終了時刻」、「休憩時間」の入力
まずは「日付」、「開始時刻」、「終了時刻」、「休憩時間」を入力します。
これらの項目に関しては特に関数等は使用せずに、手入力になります。
労働時間の計算
続いては労働時間を入力します。
労働時間に関しては手入力ではなく、数式による計算で入力します。
計算式としては、
終了時刻 - 開始時刻 - 休憩時間
となります。
上記のように、時刻同士の計算は通常の数値計算と同じようにできます。
残業時間の計算
続いては残業時間を計算します。
残業時間の計算は、
労働時間 - "8:00"
で計算します。
"8:00"の部分は封業規則に応じて変更してください。
ここでの注意点は、「-8」でなく、「-8:00」とすることです。
このような時刻同士の計算では、「8」ではなく時刻の形式である「8:00」を使わないと正しく計算できないので注意してください。
深夜時間の計算
続いては深夜時間の計算します。
深夜時間は22:00以降の労働になります(こちらも就業規則に応じて変更してください)
そのため計算式としては、
終了時刻 - "22:00"
となります。
ただこのまま計算してしまうと、終了時刻が22:00よりも前だった場合に結果がマイナスになってしまいます。
しかし時刻においてマイナスというのは起こり得ないため、そのままエラーが起こります。
なので時刻がマイナスになることが想定される場合には、結果がマイナスになる場合は0にする必要があります。
ここで使用するのがMAX関数です。
MAX関数は引数の中で値が大きい方を採用するため、この関数を使用することで計算結果がマイナスの場合は0にするということができます。
上記踏まえると、深夜時間の計算式は以下のようになります。
=MAX(0,C2-"22:00")
これで終了時刻が22:00よりも前の場合には、マイナスではなくて0が出力されるようになります。
残業時間の計算式の修正
なお「残業時間」に関しても、早退があるケースでは結果がマイナスになる可能性があります。
そのため以下のようにMAX関数を使うように修正しておくと安心です。
=MAX(0,E2-"8:00")
24:00を超えるケースの対応
続いては時刻が24:00を超える際の設定を行います。
実はExcelでは時刻が24:00を超える場合は、そのまま表示することができません。
例えば26:00と入力すると2:00に変換されてしまいます。
ただし終了時刻に関しては26:00などもあり得るため、こちらの対応をする必要があります。
手順は以下になります。
終了時刻のセルを選択
Ctrl+1を押してセルの書式設定を開き、「ユーザー定義」を選択
「種類」の入力欄に[h]:mmと入力
この「ユーザー定義」では、見た目の設定が可能です。
hはhour、mはminutesを表しています。
ただh:mmのままだと、24時間を超える場合にうまく表示ができないので、hに[]をつけます。
このように[]をつけることで24時間を超える場合にそのまま表示することが可能になります。
これで終了時刻が24:00を超える場合でも、正しく表示させることができるようになりました。
この同様の設定を別の項目に対しても実行します。
テーブル化
これで表の1行目の数式が完成しました。
ただし実際の勤怠管理表では、この下に別のデータが続きます。
その際に毎回数式を入力するのは大変なので、自動で数式が適用されるようにします。
ここで使用するのが「テーブル」という機能です。
表をテーブル化することで、1行目に設定した数式が2行目以降にも自動で適用されるようになります。
表をテーブル化する際の手順は以下の通りです。
表の一部を選択
Ctrl+Tを押してテーブル化のダイアログを表示し、「OK」を選択
これで表がテーブルになりました。
試しに下に1行追加すると、1行目で設定した数式が自動で適用されることがわかります。
続いて表のデザインを変更します。
「テーブルデザイン」タブを選択
テーブル化された状態の表を選択後に上部のタブから「テーブルデザイン」タブを選択します。
デザインを選択
続けて好きなデザイン選択します。
これでデザインが適用されました。
入力されたデータの集計
これで値の入力ができたので、ここからは入力されたデータをもとに集計を行います。
出勤数の集計
出勤数はCOUNT関数を使用して計算します。
=COUNT(
と入力した後に、値が入力されている行を選択します。
これで出勤数が計算できました。
総労働時間の集計
総労働時間はSUM関数を使用して計算します。
深夜時間の集計
総労働時間も同様にSUM関数を使用して計算します。
残業時間の集計
続けて残業時間を計算します。
残業時間は、
SUM関数で計算した残業時間の合計 - 深夜時間
になります。
所定労働時間の集計
続いて所定労働時間の計算をします。
所定労働時間の計算は、
総労働時間 - 残業時間 - 深夜時間
となります
給与の計算
最後に給与の計算をしていきます。
給与の計算に関しては、以下の計算式になります。
所定労働時間*24*時給+残業時間*24*残業時給+深夜時間*24*深夜時給
注意点としては、時間に24をかけている点です。
これは時刻を計算できるシリアル値に戻すための処理となっています。
詳しい説明は動画の方でしているので、気になる方はぜひ動画をご覧ください。
最後に出力された結果を「セルの書式設定」から「標準」にします。
これで給与の計算ができました。
