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

2019-08-16 2022-03-09

「勤務時間の給与計算は、ExcelVBAで自動化可能」

15分単位での時間計算と、正確な金額がでるようにVBAでプログラムをつくっていこう。

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

さて、給与計算プログラムの流れはつぎのようになる。

古いデータを削除して空白にする
15分単位で計算した勤務時間を計算する
定時時間と残業時間をわける
日当たりの金額を計算

ちなみに、今回のためにExcelテンプレートをつくった。ここからダウンロードしてコードはSheet1に書いていこう。

給与自動計算VBAプログラムコードと解説

まず、全文を見て流れを把握しよう。ざっくり把握できたらOK。このあと9ステップで説明していく。

Option Explicit


Const START_WORK_COLUMN As Long = 3 ' 出勤の列番号
Const END_WORK_COLUMN As Long = 4 ' 退勤の列番号
Const TOTAL_TIME_COLUMN As Long = 5 ' 勤務時間の列番号
Const REGULAR_TIME_COLUMN As Long = 6 ' 定時の列番号
Const OVERTIME_COLUMN As Long = 7 ' 残業の列番号
Const DAILY_PAY_COLUMN As Long = 8 ' 日当たり金額の列番号

Const CALC_START_ROW As Long = 5 ' 時間と金額を計算する最初の行
Const FIXED_TIME As Date = #8:00:00 AM# ' 定時のトータル時間
Const TIME_ADJUST As Long = 24 ' 24で固定
Const PREMIUM_RATE As Double = 1.25 ' 残業時間のときの金額を増やす比率(いまは25%で1.25)


Private Function truncationMin(ByVal time As Date) As Date

    ' 時間を15分単位で丸め
    truncationMin = WorksheetFunction.Floor_Math(time, 1 / 96)

End Function


Private Function roundUpMin(ByVal time As Date) As Date

    ' 時間を15分単位で丸め
    roundUpMin = WorksheetFunction.Ceiling_Math(time, 1 / 96)

End Function


Private Function lastRow(Optional ByVal search_column As Long = 1) As Long
    
    ' シートの最終行の取得
    lastRow = Cells(Rows.Count, search_column).End(xlUp).Row

End Function


Public Sub payCalculation()
    
    Call clearCalcData
    Call calcWorkTimeAdjustment
    Call calcWorkTimeDistinction
    Call calcTotalDailyPay

End Sub


Private Sub clearCalcData()

    Range(Cells(CALC_START_ROW, TOTAL_TIME_COLUMN), _
        Cells(Rows.Count, Columns.Count)).ClearContents

End Sub


Private Sub calcWorkTimeAdjustment()
    
    Dim break_time As Date: break_time = Range("D2")
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        Dim start_work As Date: start_work = roundUpMin(Cells(i, START_WORK_COLUMN))
        Dim end_work As Date: end_work = truncationMin(Cells(i, END_WORK_COLUMN))
        
        Cells(i, TOTAL_TIME_COLUMN) = end_work - start_work - break_time
    Next i

End Sub


Private Sub calcWorkTimeDistinction()
    
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        Dim total_time As Date: total_time = Cells(i, TOTAL_TIME_COLUMN)
        
        If total_time > FIXED_TIME Then
            Dim overtime As Date: overtime = total_time - FIXED_TIME
            Dim regular_time As Date: regular_time = total_time - overtime
            
            Cells(i, REGULAR_TIME_COLUMN) = regular_time
            Cells(i, OVERTIME_COLUMN) = overtime
        Else
            Cells(i, REGULAR_TIME_COLUMN) = total_time
        End If
        
    Next i

End Sub


Private Sub calcTotalDailyPay()
    
    Dim hour_pay As Currency: hour_pay = Range("C2")
    Dim regular_pay As Currency
    Dim overtime_pay As Currency
    
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        regular_pay = Cells(i, REGULAR_TIME_COLUMN) * TIME_ADJUST * hour_pay
        overtime_pay = Cells(i, OVERTIME_COLUMN) * TIME_ADJUST * (hour_pay * PREMIUM_RATE)
        
        Cells(i, DAILY_PAY_COLUMN) = _
            WorksheetFunction.RoundDown(regular_pay + overtime_pay, 0)
    Next i

End Sub

Step1: 変数の強制宣言

Option Explicit

まずは最初の一行、上記のコードで変数の宣言を強制。オプションで変数の宣言を強制していないならつぎのように設定しておこう。

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

変数ってなに？って場合は、プログラミングの"変数"とはExcelのセルといっしょを見てくれ。

Step2: 定数をつかって設定していこう

Const START_WORK_COLUMN As Long = 3 ' 出勤の列番号
Const END_WORK_COLUMN As Long = 4 ' 退勤の列番号
Const TOTAL_TIME_COLUMN As Long = 5 ' 勤務時間の列番号
Const REGULAR_TIME_COLUMN As Long = 6 ' 定時の列番号
Const OVERTIME_COLUMN As Long = 7 ' 残業の列番号
Const DAILY_PAY_COLUMN As Long = 8 ' 日当たり金額の列番号

Const CALC_START_ROW As Long = 5 ' 時間と金額を計算する最初の行
Const FIXED_TIME As Date = #8:00:00 AM# ' 定時のトータル時間
Const TIME_ADJUST As Long = 24 ' 24で固定
Const PREMIUM_RATE As Double = 1.25 ' 残業時間のときの金額を増やす比率(いまは25%で1.25)

ここではシートの設定をまとめておこう。上記のようにしておくことでコードの管理がしやすくなる。

管理することで、あとで「この数字なんだったけ？」ってことがなくなる。

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

Private Function truncationMin(ByVal time As Date) As Date

    ' 時間を15分単位で丸め(切り捨て)
    truncationMin = WorksheetFunction.Floor_Math(time, 1 / 96)

End Function


Private Function roundUpMin(ByVal time As Date) As Date

    ' 時間を15分単位で丸め(切り上げ)
    roundUpMin = WorksheetFunction.Ceiling_Math(time, 1 / 96)

End Function

次に、上記のコードで時間を15分単位で丸める独自関数(Function)を作ってみよう。truncationMinで時間を切り捨て、roundUpMinで時間を切り上げ。

1/96という数値は15分で丸めるため、24時間 × 60分 ÷ 15分＝ 96の計算

「1/96」の数値を変えれば1分、30分単位でも計算可能だ。

Step4: シートの最終行を取得する関数をつくる

Private Function lastRow(Optional ByVal search_column As Long = 1) As Long
    
    ' シートの最終行の取得
    lastRow = Cells(Rows.Count, search_column).End(xlUp).Row

End Function

この関数をつかえば、最終行が変わっても自動で、値があるセルの最終行を取得してくれる。いまは1列目の最終行を取得している。

search_columnのデフォルトは1(A列)を設定

他の列の最終行を取得したいなら引数に列番号をいれればOK。引数ってなに？って場合は、プログラミングの"引数"とはクレープといっしょを見てくれ。

Step5: まとめて実行するコードを書いていく

Public Sub payCalculation()
    
    Call clearCalcData
    Call calcWorkTimeAdjustment
    Call calcWorkTimeDistinction
    Call calcTotalDailyPay

End Sub

このpayCalculationを実行すれば、すべての処理が実行される。まあプログラムを管理する場所と覚えておけば大丈夫だ。

ちなみに、こんごPublic SubとPrivate Subがでてくるが、違いはつぎのようになる。

Public Subは「他モジュールでもつかえる」
Private Subは「書いたモジュールでしかつかえない」

Step6: データを初期化する

Private Sub clearCalcData()

    Range(Cells(CALC_START_ROW, TOTAL_TIME_COLUMN), _
        Cells(Rows.Count, Columns.Count)).ClearContents

End Sub

データが間違って入らないように、勤務時間以降の列をすべて削除する。ClearContentsで値だけを削除し、書式設定は削除しないから安心してくれ。

Rows.CountでExcelの最終行まで。Columns.Countで最終列まで削除。

Step7: 出勤、退勤の時間から15分丸めで勤務時間を計算する

Private Sub calcWorkTimeAdjustment()
    
    Dim break_time As Date: break_time = Range("D2")
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        Dim start_work As Date: start_work = roundUpMin(Cells(i, START_WORK_COLUMN))
        Dim end_work As Date: end_work = truncationMin(Cells(i, END_WORK_COLUMN))
        
        Cells(i, TOTAL_TIME_COLUMN) = end_work - start_work - break_time
    Next i

End Sub

7, 8行目で、出勤を切り上げ、退勤を切り捨て15分丸めをして勤務時間を計算する。出勤、退勤列の時間表示は変更されないから注意してくれ。

勤務時間 = 退勤時間 - 出勤時間 - 休憩時間

勤務時間では、定時, 残業時間の区別をしないで合計時間を計算する。つぎで定時と残業時間をわけていく。

いまいちFor文がわからない場合は、【超入門】VBAはIf文For文を覚えれば『破壊的』に使える理由を見てくれ。

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

Private Sub calcWorkTimeDistinction()
    
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        Dim total_time As Date: total_time = Cells(i, TOTAL_TIME_COLUMN)
        
        If total_time > FIXED_TIME Then
            Dim overtime As Date: overtime = total_time - FIXED_TIME
            Dim regular_time As Date: regular_time = total_time - overtime
            
            Cells(i, REGULAR_TIME_COLUMN) = regular_time
            Cells(i, OVERTIME_COLUMN) = overtime
        Else
            Cells(i, REGULAR_TIME_COLUMN) = total_time
        End If
        
    Next i

End Sub

勤務時間から計算し9, 10行目で定時時間と残業時間をわける。定時時間はMAXで8時間。

8時間以上の時間は残業時間。8時間以下なら定時時間のみで計算

たとえば、勤務時間が9:15だったら定時時間が8:00、残業時間が1:15と計算される。勤務時間が6:15なら定時時間が6:15、残業時間が空白のままってこと。

Step9: Totalの給料を計算する

Private Sub calcTotalDailyPay()
    
    Dim hour_pay As Currency: hour_pay = Range("C2")
    Dim regular_pay As Currency
    Dim overtime_pay As Currency
    
    Dim i As Long
    
    For i = CALC_START_ROW To lastRow
        regular_pay = Cells(i, REGULAR_TIME_COLUMN) * TIME_ADJUST * hour_pay
        overtime_pay = Cells(i, OVERTIME_COLUMN) * TIME_ADJUST * (hour_pay * PREMIUM_RATE)
        
        Cells(i, DAILY_PAY_COLUMN) = _
            WorksheetFunction.RoundDown(regular_pay + overtime_pay, 0)
    Next i

End Sub

24で調整するおかげで、時間から正確な給料を計算できる。24は24時間ってこと。コードでいうなら、10行目。10行目が定時の日当たり給料を計算している。

日当たりの給料(定時) = 勤務時間 × 24 × 時給

残業の日当たり給料は11行目。1.25で計算しているが、1.25の値を変えれば深夜残業などにも対応可能だ。

日当たりの給料(残業) = 勤務時間 × 24 × (時給 × 1.25)

まとめ: 3つの計算方法をおぼえておこう

15分で時間を丸めたいなら = 24時間 × 60分 ÷ 15分
日当たりの給料(定時) = 勤務時間 × 24時間 × 時給
日当たりの給料(残業) = 勤務時間 × 24時間 × (時給 × 1.25)

基本はコピペしてくれればOK。ただ、上記の計算方法は最低でもおぼえおくといい。役に立つから。

その他の人気VBA記事↓