めざせエクセルマスター 〜Microsoft Excel入門〜 勤怠を管理する
Excelで勤怠を管理する
ほとんどの業種において、事務作業に使われるツールがExcel。
単純な表管理から複雑な計算までこなしてくれる便利ツールですが、便利が故に使い方を覚えきれずに使いこなせない方も多いかと思います。
今回はそんなExcelで勤怠を管理する方法についてご紹介します。
時間の基本
Excelで時間を入力する際、例えば8時00分を「8:00」と入力すると、そのまま「8:00」と表示されます。
実はこれは表示上「8:00」になっているだけで、実際は「0時00分」を0、「24時00分」を1とした時の値が記録されています。
実際に「8:00」と入力したセルの書式設定を変更し、表示形式を「標準」や「数値」に変更してみてください。
「0.33333…」と表示されます。
そして、これに24を掛けると入力した時刻が時間「h」に統一されます。
8時00分は8.0、8時30分は8.5のように変換され、その後の計算を楽にすることができるようになります。
時間を丸める（15分刻みで区切る）
時給制の勤怠を管理する際、何分刻みで時給を計算するかは職場によってまちまちです。
30分だったり、15分だったり、場合によっては1分刻みという細かいところもあるかもしれません。
ここでは15分刻みを例に話を進めていきます。
15分刻みの場合、タイムカードを打刻した時刻と勤怠の時間との関係は下表のようになります。
| 【出勤】 | 【退勤】 | ||
| 7:31~7:45 | 7:45 | 16:45~16:59 | 16:45 |
| 7:46~8:00 | 8:00 | 17:00~17:14 | 17:00 |
| 8:01~8:15 | 8:15 | 17:15~17:29 | 17:15 |
これを上手く計算させるのに便利なのがCEILING関数とFLOOR関数です。
切り上げするならCEILING関数
CEILING関数を使用すると、指定した倍数に切り上げが可能です。
= CEILING ( 計算させるセル*24 , 0.25 )
と入力すると、0.25の倍数、すなわち15分刻みに切り上げされます。
計算させた例が下図になります。
上表の【出勤】欄の区分け通りの15分刻みに変換されました。
切り下げするならFLOOR関数
FLOOR関数は、指定した倍数に切り下げとなります。
使い方はCEILING関数と同じです。
= FLOOR ( 計算させるセル*24 , 0.25 )
先ほどのCEILING関数では7:46~8:00が8.0に丸められたのに対して、FLOOR関数では8:00~8:14が8.0に丸められています。
合わせ技で勤怠計算してみよう
CEILING関数とFLOOR関数を使用して、勤怠を集計するシートを作成してみます。
（8:00出勤、17:00退勤で休憩1時間を想定）
出勤時は、7:46~8:00の間にタイムカードを打刻した場合を8:00としたいので、CEILING関数で切り上げ、
退勤時は、17:00~17:14の間に打刻した場合を17:00としたいので、FLOOR関数で切り下げます。
B列に社員のリスト、C列に出勤時刻、D列に退勤時刻、E列に休憩時間を入力し、F列で勤務時間を計算させるようにします。
その際の計算式は、以下のようになります。
= FLOOR ( 退勤時刻のセル*24 , 0.25 ) – CEILING ( 出勤時刻のセル*24 , 0.25 ) – 休憩時間
最初のFLOOR関数で退勤時刻を15分単位で丸め時間に換算、次のCEILING関数で出勤時刻を15分単位で丸め時間に換算。
退勤時間から出勤時間を引き、さらに休憩時間を引けば勤務時間が算出されます。
休憩時間を直接数式に入れてしまうと、休憩時間が変更された場合に計算式そのものを修正する必要があるので、上記例のように休憩時間の列を作成して値を入力し、それを参照するようにした方が応用が利きます。
参考までに、E列とF列が数字の後に「h」の単位がついていますが、これは入力しているわけではなく、書式設定で表示させるようにしています。
そのまま「1.0h」のように入力してしまうと、数値ではなく文字列扱いとなり、計算式に使用できなくなります。
ですので、セルには数値を入力したいけど、単位も表示させたい…という時に、この設定を利用するとよいです。
時間は「時（h）」「分（m）」「秒（s）」という3つの単位が合わさっている数字なので、そのままでは上手に計算してくれません。
「時（h）」に変換してから計算させるのがポイントです。
また、勤怠の場合、時刻の切り上げ、切り下げで給与の額にも影響が出てきますので、丸める際にはCEILING関数とFLOOR関数のどちらを使用するべきか、自社のルールに合わせて選択しましょう。
