エクセルで勤怠表のテンプレートを作成。残業時間や深夜時間も計算してます

以前フリーランスのSE時代に、ある企業に入り込んで作業してました。その時に使ってたエクセルの勤怠表をテンプレートとして公開します。よかったら使ってください。
なお、この記事でテンプレートの説明を行っている箇所がありますが、私のパソコンにエクセルはインストールされておらず、KingsoftのSpreadsheets2010がインストールされているので、コチラのソフトを使っての説明になります。

エクセルで勤怠表のテンプレートを作ってみました。

先ずはココからダウンロードしてください。

EXCEL勤怠表

1 ファイル 5870byte

kintaihyo

このexcelには特にロック等をかけていません。なのでレイアウト変更などはご自由にどうぞ。

ダウンロードしたZIPファイルを解凍して開いてみるとわかりますが、中身はとても簡単なものです。入力するのは薄緑色のセルのみ。

では、簡単な説明を以下に書きまのでEXCELファイルを開いた状態で見てください。

日付

【A3】セルにその月の勤怠開始日を入力してください。すると、【A33】まで日付が展開されます。

A4～A33 までのセル内には　【一つ上のセル＋１】の数式が入っています。

例）【A4】セル　　【=A3+1】

曜日

B列は曜日を表示していますが、数式はA列をそのまま参照してるだけです。
なので【B3】のセルには　【=A3】と書かれています

ではなぜA列は日付（月日）で表示され、B列は曜日を表示しているのでしょう？

これはセルの【書式設定】で表示方法を指定しているからなんです。

まず対象のセルをすべて選択後、右クリックし、【セルの書式設定】を選択します。するとセルの書式設定の小さなウィンドが開くので、【表示形式】タブの【分類】で【ユーザー定義】を選択し、その右側の【種類】のところに【aaa】と入力します。

これだけでそのセルの日付データは曜日表示に変わります。

余談ですが【(aaa)】というように括弧をつけると、表示内容は【(月)】のようにカッコつきで表示されます。

あと、日曜日を赤色で表示させるには、【条件付書式】で設定します。
設定方法は【書式】-【条件付書式】で

【数式が】【=WEEKDAY(B3)=1】のときに文字の色を赤にするよう【書式】を設定しているだけです。

この　WEEKDAY　関数は
1 ＝　日
2 ＝　月
3 ＝　火
4 ＝　水
5 ＝　木
6 ＝　金
7 ＝　土
となってますので、土曜日の色も変えたければ【追加】ボタンを押して条件を追加してあげれば良いです。

有休、欠勤

【有休休暇】と【欠勤】の列は単純に日数を入れるだけです。そして合計欄に一ヶ月の合計を出しているだけですので大して難しいことはしていません。

【C34】や【D34】の合計欄も【=SUM(C3:C33】っていう感じで簡単な式になってます。。

出勤、退勤、休憩

ここには出退勤時間、休憩時間を60進法で記入します。

例）9時ちょうどの出勤の場合　【9:00】

例）17時30分に退勤の場合　【17:30】

例）お昼休憩が　45分の場合　【0:45】

実働

この【実働】列を簡単に説明しますと

退勤時間　－　出勤時間　－　休憩時間　で計算され、10進数で表示します。

J3セルの数式を見てみましょう。

=IF(E3=””,””,HOUR(H3)+MINUTE(H3)/60)
①————— ②——————————–

①もしE3セルが空白なら空白を
②　　　空白じゃなければH3セルの【時 + 分/60】をセット

■IF文

=IF(セルA=値１,値２,値３)
IF文は条件文　（ここでは　セルA=値１）を書いてカンマの後に条件を満たした時にセットする値、そしてもう一つのカンマの後に条件を満たしていないときにセットする値を書きます。実働時間【J3】のセルの中の式に
=IF(セルA=”星”,”★”,”●”)
と書かれていた場合、セルAの中身をみて、【星】という文字が書かれていればJ3には【★】を表示して、【星】意外の文字（空白も含む）であればJ3には【●】と表示することになります。

文章だけだとほんと難しいですね。

ここでの H3(H列) は実働時間を計算するためのセルにしています。なので普段は非表示にしています。
そしてこのH列は退勤時間－出勤時間－休憩時間で計算してあり、60進で表示しています。

J列の実働時間はH列の60進の実働時間を上記の式により、10進に変換しているのです。

例をあげてみましょう

出勤 9:00
退勤 18:00
休憩 45分
の場合、実働時間（60進）は 8:15（8時間15分）です。

これを上記の式　【HOUR(H3)+MINUTE(H3)/60) 】　に当てはめると

８　＋　１５　÷　６０　＝　８.２５

となって　8時間15分　は　8.25時間　となるわけです。

なんだか文字ばかりで判りにくいですね。すみません。

エクセル勤怠で残業時間や深夜時間の計算式サンプル

時間外

時間外とは1日の規定時間を越えて働く場合に時間外手当てとして支払われる所謂【残業時間】のことです。
一般的には8時間超過分がそれにあたりますが、契約時間を任意に設定できるように【H1】に登録してある時間を規定時間としてみなすように扱っています。なのでとりあえずH1には【8】を設定しています。（必要に応じて変更してください）

時間外の計算はシンプルです。

=IF(J3=””,””,IF(J3<=$H$1,””,J3-$H$1))
①————— ②—————– ③———-

①もしJ3セルが空白なら空白を
②　　　空白じゃなく、H1の値以下なら空白を
③　　　そうじゃなければ【実働時間 – 規定時間】の値をセット

深夜

深夜は２２時以降の作業時間を計算します。注意してほしいのは、時間外にも深夜が含まれることがあるということです。
時間外は８時間超の勤務時間を計算しており、深夜は２２時以降の勤務時間です。

そしてこの【深夜】も【I1】セルに登録してある時間を基準にしており、それ以降の時間を計算しているので深夜時間の開始時間を任意に変更することが可能です。

深夜の計算もシンプルです。

まず、I列に22時（I1で指定した時間）以降の勤務時間を60進で計算し、その後10進に変換しています。

=IF(I3=””,””,HOUR(I3)+MINUTE(I3)/60)
①————— ②—————————–

①もしI3セルが空白なら空白を
②　　　そうじゃなければ I3セルの【時 + 分/60】をセット