以前にも時間給の計算表を作成するというブログを書きましたが、今回はそれに修正を加えて書いてみました。実務上よくあるのですが、このようなデータを作ると数式が入力された箇所に誤って別の入力をしてしまい、数式などが崩れて正しく計算ができなくなるということを見聞きします。
そのようなことも防止するための案も盛り込んでみました。
※出勤簿完成形のイメージ。
日々の勤務時間を入力する場所を作る
まず出勤簿の大まかな形を作りましょう。日付と勤務時間を入力する場所を作ります。日付はその対象月だけ入力してあとは関数を使って自動的に入力されるように設定していきます。実働時間も出勤の時間と退勤の時間を入力すると休憩時間を差し引いた時間が自動で表示されるようにします。
セルB4に対象月の初日を設定する関数は、=DATE(B2,B3,1)です。対象年、対象月の1日を作るという意味です。これをもとにA列に日付を自動で入力するようにしますので、セルA7にはB4を入れます。このとき絶対参照でB4をくくりましょう。=$B$4です。
7行目以降は、=IF(A7=””,””,IF(A7+1>EOMONTH($B$4,0),””,A7+1))として、初日のセルから起算して月末まで表記する関数です。月末が月によって31日や30日など変動しますので、その月の末尾までしか表示しないとしています。
これで日付の設定はできました。日付に曜日も入れたい場合は、書式設定でyyyy/m/d(aaa)とすればその日の曜日まで自動で表示することができます。
出勤時間と退勤時間はそれぞれ日々入力していくので何も入力しません。休憩を1時間とした場合に、実働時間は勤務時間から差し引いて表示するようにします。
セルE7に=IF(OR(B7=””,C7=””),””,C7-B7-D7)を入れます。これで出勤、退勤の時間が入力されたときには休憩を差し引いて実働時間を自動計算します。この関数も月末が表示できるあたりの行までコピーしましょう。
時間の入力とそれを合計する場所を作る
出勤時間と退勤時間の入力については、時間を入力しますが、8時から出勤の場合、”8”だけ入力するのではなく、”8:00”と入力することに注意しましょう。Excelでは時間はこのように入力しなければ”時間”として認識しません。8時ちょうどの場合などは”8:”として00を省略してもかまいません。8:15などは正確に入力する必要があります。
月末まで表示された行より下に1ヶ月分の実働時間を集計するようにします。今回はセルE40に合計を表示します。これで1日ごと、1ヶ月合計の実働時間それぞれの設定が完了しました。
時給計算の方はここに時間単価をかけて給与総額も自動で算定できるようにしましょう。
すぐ下のセルに時間単価を入力して自動計算できるようにすればOKです。
給与計算をするときは、単純に時給をかけるだけだと正確な金額になりませんので、関数の最後に24をかけるようにします。=E40*E41*24です。
崩れないように保護をする
これで出勤簿兼給与計算表は完成しました。例えば、従業員を複数名雇用していて、これらの方の出勤簿を用意しなければならないこともあるでしょう。そして毎月のデータを準備する必要も出てくるでしょう。そのときのためにこれらの書式や設定が崩れないよう「保護」をすることをおススメします。
入力しても良い部分だけ範囲指定します。書式設定を開き保護のタブでロックのチェックマークを外します。写真は初期設定なのでチェックマークが入っていますが、ここをクリックしてチェックマークのない状態にしてOKとします。
画面上部の校閲からシートの保護を選択します。
シートの保護のウィンドウが表示されるので、任意のパスワードを決めて入力します。
もう一度同じパスワードを入力してOKとすれば、入力しても良いと指定した部分以外が保護されます。その部分に入力しようとするとエラーが表示され入力できないのです。
そのつもりはなかったのに入力の際に関数が入ったところを触ってしまい、計算式が崩れてしまった、またはうまく計算してくれなくなったなどの防止のために原本をコピーしておくこともご検討いただければ。
- 直近の出来事
月初ですので自分の経理をしました。まだまだこれからです。
