「給料計算ってマジめんどくさい...ExcelのVBAで自動化できないのかな?」
今回は、その疑問に答えていこう。

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

まずは、今回のために用意したExcelファイルをダウンロードしてこよう。

プログラムの流れ

今回は、勤務時間・定時・残業・給料の自動計算をしていこう。

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

ちなみに、全ての項目時間を15分単位で時間を丸めていく

下記からは標準モジュール、GitHubからダウンロードしたら標準モジュール内の『workPayCalculation』に書いていこう。

【重要】もっと簡単にVBAで計算できるように対応
もっと簡単に定時・残業の給料計算を使えるようにしたから、【便利】VBA『クラスモジュール』で定時/残業の給料計算を自動化をチェック

その他の項目について

  • その他、出勤日は『出勤』の項目を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

変数の強制宣言

まずは最初の一行、下記で変数の宣言を強制

Option Explicit

最後にまとめて実行するコード

下記の、PayCalculationに小さいSubをCallしてまとめて実行するPublic Sub

Public Sub PayCalculation()

    Call WorkTimeAdjustment
    Call WorkTimeDistinction
    Call TotalPay

End Sub
Subの種類内容
Public SubExcel開発→マクロの項目でマクロが選択できる
Private Sub単体で使用しない(モジュール内でしか使わない)

15分単位で丸める独自関数をつくろう

次に、下記コードで時間を15分単位で丸める独自関数(Function)を作ってみよう

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
  • TruncationMinで時間を切り捨て
  • RoundUpMinで時間を切り上げ
  • 1/96という数値は15分で丸めるため、24時間×60分÷15分=96の計算
 3行目、9行目『1/96』の数値を変えれば1分単位でも、30分単位でも計算可能

仕事の時間を15分単位で調整する

さっき作った、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
  1. 出勤、休憩開始、休憩終了、退勤の項目を15分単位で丸める
  2. 時間を足し、5行目~35行目までの『勤務時間』の項目を計算
 『出勤』『休憩終了』は切り上げ、『休憩開始』『退勤』は切り捨てで計算

「For文が分からないんだけど」って場合は、下記記事を見てね。

定時、残業の時間を計算する

次に、どの時間以上になったら定時時間と残業の時間を分けるのか処理していこう。

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
  1. fixed_timeで定時時間が何時間(設定値は8:00)かを設定
  2. 『勤務時間』の項目から、定時時間内なのか8時間を超えて残業時間になるのか計算
  3. 『定時』『残業』の項目を計算
  4. もし『勤務時間』が空白の場合は、そのまま空白にするようIf文で分岐

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. 時間を調整してから金額を計算
 premium_rateを応用すれば、深夜時間、夜勤なども対応可能

人気記事【必読】"見やすく修正しやすいコード"につながる本『リーダブルコード』

関連キーワード