勤怠管理システムをエクセルで自作するには?手順と運用のポイントまとめ

勤怠管理システムを自作する前に

　自作する勤怠管理システムの担う機能ですが、次に挙げる3つのうち、「集計」を行ってもらいます。

勤怠管理3つの機能

1. 記録
   出勤時刻、退勤時刻、早退や遅刻があればそういった情報を従業員毎に日々記録します
2. 集計
   記録された勤怠情報を従業員毎、期間毎などに集計します
3. 出力
   給与計算に必要な労働時間の情報や、従業員毎の出勤簿を出力します

• エクセルで勤怠管理を効率化できる方法2つと注意点　無料ツールも紹介

なぜ「集計」だけなのか

　記録から出力全てシステム化したいと思われた方もいらっしゃると思います。今回「集計」部分だけをエクセルで自作することとしたのは、集計が最もエクセルに適した作業のためです。

「記録」の課題　タイムカードなどで取得を

　会社は従業員の労働時間を管理する義務がありますが、この労働時間管理に必要な始業時刻・終業時刻の記録は使用者の現認または客観的な記録方法であることが求められます。

　共有エクセルに個々が出勤時刻を自己申告で入力する、というのは客観性に欠けているので、記録の機能は自作エクセルに盛り込まず、タイムカードや入室記録などで取得した記録を利用するようにしましょう。ちなみに“パソコンの使用時間の記録等”は記録方法として問題ありません。

完成形エクセルのイメージと時間外労働の前提知識

• 勤怠管理の集計フォーマット【会員限定で無料ダウンロード】

　黄色いセルが入力部分で、勤怠種類番号を入力すると"出勤","遅刻","早退","休日出勤","欠勤","有給休暇"がE列に表示され、F～I列に時刻を入力するとJ～N列が計算されます。上段・下段には月の合計時間が集計されています。

　エクセルの作成の前に、割増賃金の対象となる労働時間の解説をします。

• 割増賃金の計算方法を紹介　時間外・休日・深夜の割増率が重なる場合も

残業はどこから

　よく残業と言われる労働時間を正しくは法定外労働時間といい、法定労働時間を超えて労働した時間に対し割増賃金、いわゆる残業手当が支給されます。

　法定労働時間は1日8時間とされているので、1日10時間労働した場合は2時間分について残業手当が支給されます。残業手当の割増率は通常25％です。改正労働基準法により、2023年4月から月60時間を超える時間外労働の割増賃金率が、中小企業でも「50％」に引き上げられます。

深夜と休日の割増賃金について

　22:00から翌日5:00を深夜として、この時間帯に労働した場合は深夜手当が支給されます。深夜手当も割増率は通常25％とされており、残業との重複もありますので残業が深夜まで及んだ場合は割増率が合計50%ということになります。

　休日労働の割増率は35%で、法定休日に労働した時間に対し加算されます。法定休日は週に1回または4週に４日あれば良いとされています。

作成の準備とエクセルの数式入力まで

　今回作成するエクセルではマクロを使用するので、上部メニューのファイル→オプション→リボンのユーザー設定で、右側の窓の□開発にチェックをつけておき上部メニューに開発を表示させておきましょう。

　また、ファイルの拡張子は.xlsxではなく.xlsm（Excel2007以降のマクロ付きExcelファイル）となります。

D・E列：勤怠項目の表示

　E列にはD列に入力された数字によって勤怠項目を選択する数式を入れます。色々と方法はありますが、今回はIF関数とCHOOSE関数を使用しました。

E10セルの数式
　=IF(D10="","",CHOOSE(D10,"出勤","遅刻","早退","休日出勤","欠勤","有給休暇"))

　CHOOSE関数は入力された数値をインデックス値として左から順に該当するものを表示します。

　この例ですと1~6以外の数値が入るとエラーが出ますのでD列にはデータツールのデータの入力規則で、1から6の整数のみが入力出来るようにしておくと良いでしょう。

F～I列：時刻の入力と表示形式について

　エクセルは入力された値によって自動でセルの書式設定が変更されます。

　例えば、セルに9:00と入力し、そのセルを選択するとホーム→数値の部分が「ユーザー定義」になっており、数式バーの部分には9:00:00と表示されていると思います。このまま時間の計算が出来ますが、値がマイナスとなる時刻は表示されません。

　減算と同様に、時間の加算計算も可能です。例えば9:00+18:00とすると9:00から18時間経過した3:00が表示されます。今回ここの列らには秒は要らず、0:00~24:00を表示させたいので、ホーム→数値→ユーザー定義から表示形式を [h]:mmを選択しておきましょう。

F～I列：勤怠項目による色の塗り分け

　入力するセルには色がついているといったルールがある方が転記ミスを減らせますので、勤怠項目が欠勤・有給休暇となった場合はF~I列を白塗りするようにします。

　これはF10~I10列を選択した状態でホーム→スタイル→条件付き書式→新しいルールの作成→数式を使用して、書式設定するセルを決定、を選択し、数式部分に=$D10>4と入力し、書式で白塗りつぶしを選択しOKです。

J~M列：時刻の計算

　J・M列にはそれぞれ当該行のI-HとG-F-Jを入力します。

J10セルの数式
=IF(I10<>"",I10-H10,"")

M10セルの数式
=IF(G10<>"",G10-F10-J10,"")

　L列では法定労働時間を超えた時間分を計算する数式を入れます。TIME(時,分,秒)関数を利用しています。

L10セルの数式
　=IFS(K10="","",K10-"8:00">TIME(0,1,0),K10-"8:00",TRUE,"")

　時刻の計算は””を付けることで可能です。ただ条件式中の比較は””がついているとエラーになるのでTIME関数で0:01を表しています。この数式では法定労働時間の8時間を引いています。

　もし所定労働時間が7時間で7時間を超えたら割増賃金を払う、というような場合は”8:00”を”7:00”にしてください。

　深夜労働のM列は少々複雑です。数式上は4つのパターン分けをしています。

M10セルの数式 =IFS(K10<>"",IF(AND(F10>TIME(5,0,0),G10<TIME(22,0,0)),"",IF(AND(F10<TIME(5,0,0),G10>TIME(22,0,0)),("5:00"-F10)+(G10-"22:00"),IF(F10<TIME(5,0,0),"5:00"-F10,G10-"22:00"))),TRUE,"")

　勤務時間が0でないことを前提として、下記順にパターンを検証し深夜労働時間を計算しています。

1. 5:00前の労働も22:00後の労働も無し→深夜労働無し
2. 5:00前の労働も22:00後の労働も発生→0:00-5:00労働と22:00以降労働を加算
3. 5:00前の労働のみ発生→0:00-5:00労働計算
4. 22:00後の労働のみ発生→22:00以降労働計算

数式や条件付き書式にしたF10～M10をオートフィル

　ここまで入力したらF10~M10セルを選択し、下段までオートフィルをしてください。下段のK~N列の時間合計はSUM関数で計算します。

　上段の勤怠項目カウントはCOUNTIF関数を利用してD列の番号を条件にすると良いでしょう。

　勤務時間など時間項目は下段のSUM関数が入力されているセルを代入します。

マクロの記述と運用

　最後に、勤怠を有給休暇とした場合の自動入力と、休日出勤の場合の休日労働自動入力のマクロを記述します。上部メニューから開発→Visual Basicをクリックすると、左側にプロジェクト窓とフォルダのアイコンの下にワークシートのアイコンが表示されます。

　これまでに数式を入力してきたシートを選択してください。

D列に変更があったときに働くマクロ

　シートをクリックすると右側に白いウィンドウが出てきたと思うので、そこに次の内容を記述します。

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D10:D41")) Is Nothing Then 'D行に変更があったか
　　Exit Sub
　ElseIf Range("E" & Target.Row) <> "休日出勤" Then '休日出勤以外の処理
　　Range("N" & Target.Row) = "" '以前に自動入力された休日労働数式があれば消す
　ElseIf Range("E" & Target.Row) = "休日出勤" Then '休日出勤の時に次の処理を行います
　　Range("N" & Target.Row) = "=K" & Target.Row '当該行N（休日労働）にK（勤務時間）を数式で代入する
　ElseIf Range("E" & Target.Row) = "有給休暇" Then '有給休暇の時に次の処理を行います
　　Range("F" & Target.Row) = Range("L7") '始業時刻にL7を代入
　　Range("G" & Target.Row) = Range("M7") '終業時刻にM7を代入
　　Range("H" & Target.Row) = Range("N7") '休憩開始にN7を代入
　　Range("I" & Target.Row) = Range("O7") '休憩終了にO7を代入
　Else
　End If

　マクロを記述したウィンドウは右上の×で閉じて大丈夫です。

　マクロは’以降がコメントとなりますので、最初のうちはコメントをこまめにつけておいた方が良いでしょう。後から管理する人もわかりやすくなります。

マクロの注意点

　マクロが実行された後はエクセルブック上にて元に戻す（Ctrl+Z）コマンドが使用出来ません。動作に不安なマクロを作成した場合は実行前に必ずブックを保存するようにしましょう。

　また、開いた際にコンテンツの有効化、マクロの有効化など表示された場合はOKや許可を選択してください。

運用上のアドバイス

　どうしても１人に偏りがちですが、数人で管理しあうことが望ましいです。

　マクロとなると途端にハードルが高くなるように思えますが、実際はコピペだけでも動きますしきちんとブックの保存を行っていればいきなりデータが全て消えたということも無いはずです。

　自作のメリットは、自由に作成できること。まずは今回の記事を参考に作っていただき、その後は自社用にカスタマイズしてください。

• 勤怠管理の集計フォーマット【会員限定で無料ダウンロード】