photo credit: Time for a swim? via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Googleスプレッドシートを使った勤怠管理集計システムを作っておりますが、今回は第４回目。だいぶ佳境に差し掛かって参りました。
前回はこちらの記事。
VLOOKUP関数で複数条件による検索をする方法についてお伝えしました。
この方法を使って勤怠管理システムとしては、打刻データから出社時間、退社時間などを引っ張ってくることができるようになりました。
次に、それらをのデータをもとに、開始時刻、終了時刻、勤務時間（通常、残業、深夜）などを計算していく必要があります。
これが…なかなか大変なんですね。ただ、一回正しい計算式を作ってしまえば、あとはスプレッドシートが勝手に計算をしてくれるようになります。
今回は、CEILING関数、FLOOR関数そしてTIME関数を活用して時刻を指定の分単位で丸める方法です。
どうぞよろしくお願いいたします！
開始時刻と終了時刻を求める
勤務管理のシートについては、前回までで以下の図のところまで作成が進みました。
次は、開始時刻、終了時刻を求めていきたいと思います。
実際の打刻と給与計算の根拠の時刻が異なる場合がある
ここで、「出社と退社の打刻が既にあるのに、なぜそれとは別に開始時刻、終了時刻の項目があるのか？」と思われるかも知れません。
しかし、多くの企業の場合、実際の打刻の時刻と、給与計算の根拠となる時刻は別の場合が多いのです。
というのも、実際の打刻に対して一定の切り上げ、切り捨てをしたものを給与計算の根拠としている場合が多いからです。
1分単位で給与計算をしてくれればいいんですけどね…10分や15分といった単位に揃えていることもありますよね。
今回は、実際の打刻と給与計算の根拠の時刻が異なるケースであることを前提に進めていきたいと思います。
切り上げ、切り捨ての単位を別シートで設定
どの単位で切り上げ、切り捨てを行うかという情報について、以下のように「設定項目」というシートを設けて設定しておきたいと思います。
このように別シートで設定をしてそれを参照する形にしておけば、万が一変更があったときにすぐに対応が可能です。
今回は15分単位で切り上げ、切り捨てを行っていると仮定して進めたいと思います。
8:55分に出社の打刻をした場合は開始時刻は9:00となり、18:20に退社の打刻をした場合は18:15が終了時刻となります。
指定の分単位で時刻を丸める
CEILING関数、FLOOR関数で値を丸める
スプレッドシートで切り上げをするときはCEILING関数、切り捨てをする場合にはFLOOR関数を使います。
書き方はそれぞれ
FLOOR(値, 基準値)
と書きます。
例えば
- CEILING(95,10)→100
- FLOOR(95,10)→90
となります。
今回の場合、例えばI3,J3のそれぞれの値を求める場合は
=FLOOR(H3,基準値)
とすればOKですね。
基準値ですが、整数などであればそのまま指定をすればよいのですが、今回は単位が「分」ですから、一工夫が必要です。
TIME関数で「分」から時刻を生成
分を指定してそれを時刻として表現したいわけですよね。
その場合、時、分、秒を指定して時刻に変換するTIME関数を使います。
今回の場合は、「設定項目シート」のB6セルに「分」が指定されておりますから、基準値は
という形で指定することができます。
今回は分単位ですから、「時」と「秒」はゼロでOKですね。
したがいますと、I3,J3のセルは
=FLOOR(H3,TIME(0,’設定項目’!$B$6,0))
とすれば良いということになります。
以上の数式を31日（33行目）までコピー＆ペーストすれば開始時刻、終了時刻に関しての部分は完成となります。
まとめ
スプレッドシートで出社、退社の打刻の時刻から分単位で切り上げや切り捨てをして給与計算の根拠となる開始時刻、終了時刻を求める方法についてお伝えしてきました。
関数としては、基準値で値を切り上げ、切り捨てするCEILING関数とFLOOR関数、「時」「分」「秒」から時刻を生成するTIME関数を使いました。
これらはいずれもエクセルでも使えますので、ぜひ合わせて覚えて下さいね。
さて、次回はいよいよ最大の山場となります。
今回求めた開始時刻と終了時刻から、就業時間（通常、残業、深夜）をそれぞれ求めていきたいと思います。
どうぞお楽しみに！
