IT

【自動化】VBA/勤務時間と給与を自動計算する方法【勤務表テンプレあり】

Pcを見ている女性
勤務時間の給与計算は、ExcelVBAで自動化可能

この記事を見れば...

  • 15分単位の給与計算が可能
  • ボタン1つ押すだけ。定時、残業時間を自動化
  • テンプレートあり。あなたは1から作らなくていい
  • あなたの作業は、ポチっと押すだけでOK

そんなExcelファイルをVBAで作っていきます。

給与自動計算VBAプログラムの準備と処理の流れ

今回のためにExcelのテンプレートを作ったのでExcel給与計算テンプレートをどうぞ。

給与計算プログラムの流れ

  1. 時給を決め『C1』セルに数字を入れる(一回のみ)
  2. 手動で出勤 / 休憩開始 / 休憩終了 / 退勤を24時間表記で入力
  3. H2セルにあるボタンを押します
  4. 勤務時間、定時、残業、給料の項目を自動計算

こんな流れで給与を自動計算していきます。

時間は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のオプションで変数の宣言を強制

  1. Alt+F11キーでVBAエディタを開く
  2. 右上の「ツール」選択
  3. オプションへ
  4. 編集の変数の宣言を強制するにチェック

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
  1. 総定時時間(G1セル)総残業時間(G2セル)の項目から金額を計算
  2. 残業時間は時給の1.25倍で計算
  3. 時間を調整してから金額を計算
  4. premium_rateを応用すれば深夜時間 / 夜勤なども対応可能

あなた向け今まさに「きれいなコードを書かなくちゃ...」と考えているあなたへ。

・もっとVBA学びたい人向け記事↓

関連キーワード