
勤務時間の給与計算は、ExcelVBAで自動化可能
この記事を見れば...
- 15分単位の給与計算が可能
- ボタン1つ押すだけ。定時、残業時間を自動化
- テンプレートあり。あなたは1から作らなくていい
- あなたの作業は、ポチっと押すだけでOK
そんなExcelファイルをVBAで作っていきます。
給与自動計算VBAプログラムの準備と処理の流れ
今回のためにExcelのテンプレートを作ったのでExcel給与計算テンプレートをどうぞ。
給与計算プログラムの流れ
- 時給を決め『C1』セルに数字を入れる(一回のみ)
- 手動で出勤 / 休憩開始 / 休憩終了 / 退勤を24時間表記で入力
- H2セルにあるボタンを押します
- 勤務時間、定時、残業、給料の項目を自動計算
こんな流れで給与を自動計算していきます。
時間は15分単位で丸めましょう
今回は、全ての項目時間を15分単位で時間を丸めましょう。
応用すれば次のようなアレンジも可能。
- 1分単位で計算
- 30分単位で計算
下記からはテンプレートの標準モジュール内「workPayCalculation」に書いていきます。
その他の項目について
- その他、出勤日は「出勤」の項目をCOUNT関数で計算
- 総定時時間、総残業時間はSUM関数で定時 / 残業項目を計算
- 総勤務時間は総定時時間 / 総残業時間をSUM関数
- 日付(dete)についてはDATE関数を使用
- A1 / A2セルの数値を変えると年、月が変わる
給与自動計算VBAプログラムコードと解説
まず、全文を見て流れを把握しよう。
Option Explicit Public Sub PayCalculation() Call WorkTimeAdjustment Call WorkTimeDistinction Call TotalPay End Sub Private Function TruncationMin(ByVal time As Date) '時間を15分単位で丸め TruncationMin = WorksheetFunction.Floor_Math(time, 1 / 96) End Function Private Function RoundUpMin(ByVal time As Date) '時間を15分単位で丸め RoundUpMin = WorksheetFunction.Ceiling_Math(time, 1 / 96) End Function Private Sub WorkTimeAdjustment() Dim start_work As Date Dim start_break As Date Dim end_break As Date Dim end_work As Date Dim i As Long For i = 5 To 35 start_work = RoundUpMin(Cells(i, 2)) start_break = TruncationMin(Cells(i, 3)) end_break = RoundUpMin(Cells(i, 4)) end_work = TruncationMin(Cells(i, 5)) If start_work = 0 Then Cells(i, 6) = "" Else Cells(i, 6) = (start_break - start_work) + (end_work - end_break) End If Next i End Sub Private Sub WorkTimeDistinction() Dim fixed_time As Date Dim i As Long fixed_time = "8:00" For i = 5 To 35 Dim over_time As Date Dim regular_time As Date Dim total_work_time As Date total_work_time = Cells(i, 6) If total_work_time > CDate(fixed_time) Then over_time = total_work_time - fixed_time regular_time = total_work_time - over_time Cells(i, 7) = regular_time Cells(i, 8) = over_time ElseIf total_work_time = 0 Then Cells(i, 7) = "" Else Cells(i, 7) = total_work_time End If Next i End Sub Private Sub TotalPay() Dim hour_pay As Long Dim time_ajust As Long Dim premium_rate As Double hour_pay = Range("C1") time_ajust = 24 premium_rate = 1.25 Dim time_pay As Long Dim over_pay_ajust As Double Dim over_time_pay As Long '時間を調整しないと計算が正しくできないのでtime_ajustで調整している over_pay_ajust = hour_pay * premium_rate time_pay = Range("G1") * time_ajust * hour_pay over_time_pay = Range("G2") * time_ajust * over_pay_ajust Dim total_pay As Long total_pay = time_pay + over_time_pay Range("C2") = total_pay End Sub
Step1: 変数の強制宣言
Option Explicit
まずは最初の一行、上記のコードで変数の宣言を強制。
VBAのオプションで変数の宣言を強制
- Alt+F11キーでVBAエディタを開く
- 右上の「ツール」選択
- オプションへ
- 編集の変数の宣言を強制するにチェック
Step2: 最後にまとめて実行するコードを書いていく
Public Sub PayCalculation() Call WorkTimeAdjustment Call WorkTimeDistinction Call TotalPay End Sub
PayCalculationに小さいSubをCallしてまとめて実行するPublic Sub。
PayCalculationで今から書いていくプログラムの管理をする。
Public SubとPrivate Sub
- Public Sub...Excel開発→マクロの項目でマクロが選択できる
- Private Sub...単体で使用しない(モジュール内でしか使えない)
Subってなに?
Sub ステートメント (VBA) | Microsoft Docsをどうぞ。
Step3: 15分単位で丸める独自関数をつくろう
Private Function TruncationMin(ByVal time As Date) ' 時間を15分単位で丸め TruncationMin = WorksheetFunction.Floor_Math(time, 1 / 96) End Function Private Function RoundUpMin(ByVal time As Date) '時間を15分単位で丸め RoundUpMin = WorksheetFunction.Ceiling_Math(time, 1 / 96) End Function
次に、上記のコードで時間を15分単位で丸める独自関数(Function)を作ってみよう。
- TruncationMinで時間を切り捨て
- RoundUpMinで時間を切り上げ
- 1/96という数値は15分で丸めるため、24時間×60分÷15分=96の計算
- 3、9行目「1/96」の数値を変えれば1分、30分単位でも計算可能
Step4: 仕事の時間を15分単位で調整する
Private Sub WorkTimeAdjustment() Dim start_work As Date Dim start_break As Date Dim end_break As Date Dim end_work As Date Dim i As Long For i = 5 To 35 start_work = RoundUpMin(Cells(i, 2)) start_break = TruncationMin(Cells(i, 3)) end_break = RoundUpMin(Cells(i, 4)) end_work = TruncationMin(Cells(i, 5)) If start_work = 0 Then Cells(i, 6) = "" Else Cells(i, 6) = (start_break - start_work) + (end_work - end_break) End If Next i End Sub
さっき作った、15分単位の関数をコードに入れていこう。
- 出勤 / 休憩開始 / 休憩終了 / 退勤の項目を15分単位で丸める
- 時間を足し、5~35行目までの「勤務時間」項目を計算
- 出勤 / 休憩終了は切り上げで計算
- 休憩開始 / 退勤は切り捨てで計算
For文が分からない?
【超入門】VBAはIf文For文を覚えれば『破壊的』に使える理由で解説してるのでどうぞ。Step5: 定時、残業の時間を計算する
Private Sub WorkTimeDistinction() Dim fixed_time As Date Dim i As Long fixed_time = "8:00" For i = 5 To 35 Dim over_time As Date Dim regular_time As Date Dim total_work_time As Date total_work_time = Cells(i, 6) If total_work_time > CDate(fixed_time) Then over_time = total_work_time - fixed_time regular_time = total_work_time - over_time Cells(i, 7) = regular_time Cells(i, 8) = over_time ElseIf total_work_time = 0 Then Cells(i, 7) = "" Else Cells(i, 7) = total_work_time End If Next i End Sub
次に、どの時間以上になったら定時時間と残業の時間を分けるのか処理していこう。
- fixed_time...定時時間が何時間(設定値は8:00)かを設定
- 「勤務時間」の項目から、定時時間内か8時間を超えて残業時間か計算
- 定時 / 残業項目を計算
- もし「勤務時間」が空白の場合、そのまま空白にするようIf文で分岐
Final Step: Totalの給料を計算する
Private Sub TotalPay() Dim hour_pay As Long Dim time_ajust As Long Dim premium_rate As Double hour_pay = Range("C1") time_ajust = 24 premium_rate = 1.25 Dim time_pay As Long Dim over_pay_ajust As Double Dim over_time_pay As Long '時間を調整しないと計算が正しくできないのでtime_ajustで調整している over_pay_ajust = hour_pay * premium_rate time_pay = Range("G1") * time_ajust * hour_pay over_time_pay = Range("G2") * time_ajust * over_pay_ajust Dim total_pay As Long total_pay = time_pay + over_time_pay Range("C2") = total_pay End Sub
- 総定時時間(G1セル)総残業時間(G2セル)の項目から金額を計算
- 残業時間は時給の1.25倍で計算
- 時間を調整してから金額を計算
- premium_rateを応用すれば深夜時間 / 夜勤なども対応可能
人気記事コードをきれいに書きたい人向け「3日できれいに書ける」たった1冊本とは?
・もっとVBA学びたい人向け記事↓