【人材】勤怠データから月ごとの残業時間を集計し、上限に近い人を知らせる

想定する場面

人材派遣会社や人事部門では、スタッフの残業時間が法令や協定で定めた上限を超えないよう、月の途中で状況を確認する必要があります。勤怠システムから出力した日別データを手で集計していると、月末に上限超過が分かる、といった事態になりかねません。

このマクロは、日別の始業・終業・休憩時間から1日の残業時間を計算し、スタッフごとに月の合計を出して、上限に近い人を色で知らせます。

データの形（例）

「勤怠」シートを次の列構成とします。時刻は Excel の時刻（例：9:00、18:30）で入っているものとします。

• A列：スタッフID
• B列：氏名
• C列：日付
• D列：始業時刻
• E列：終業時刻
• F列：休憩（分）

コード

所定労働時間を8時間として、それを超えた分を残業とします。Excel の時刻は「1日＝1」の小数で表されるので、24×60 を掛けて分に直してから計算しています。

Sub OvertimeSummary()
    Const STD_MIN As Long = 480          ' 所定労働時間 8時間 = 480分
    Const LIMIT_H As Double = 45         ' 月の上限の目安（時間）
    Dim ws As Worksheet, outWs As Worksheet, data As Variant, dic As Object, k As Variant
    Dim i As Long, workMin As Double, ot As Double, r As Long, rec As Variant

    Set ws = ThisWorkbook.Worksheets("勤怠")
    Set outWs = ThisWorkbook.Worksheets("残業集計")
    data = ws.Range("A2:F" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Value
    Set dic = CreateObject("Scripting.Dictionary")

    For i = 1 To UBound(data, 1)
        If IsNumeric(data(i, 4)) And IsNumeric(data(i, 5)) And data(i, 4) <> "" And data(i, 5) <> "" Then
            workMin = (data(i, 5) - data(i, 4)) * 24 * 60
            If workMin < 0 Then workMin = workMin + 24 * 60      ' 日をまたぐ勤務
            workMin = workMin - Val(data(i, 6))
            ot = Application.Max(0, workMin - STD_MIN)
            If dic.Exists(data(i, 1)) Then rec = dic(data(i, 1)) Else rec = Array(data(i, 2), 0#, 0)
            rec(1) = rec(1) + ot
            rec(2) = rec(2) + 1
            dic(data(i, 1)) = rec
        End If
    Next i

    outWs.Cells.Clear
    outWs.Range("A1:D1").Value = Array("スタッフID", "氏名", "出勤日数", "残業時間（h）")
    r = 2
    For Each k In dic.Keys
        rec = dic(k)
        outWs.Cells(r, 1).Value = k
        outWs.Cells(r, 2).Value = rec(0)
        outWs.Cells(r, 3).Value = rec(2)
        outWs.Cells(r, 4).Value = Round(rec(1) / 60, 2)
        If rec(1) / 60 >= LIMIT_H Then
            outWs.Rows(r).Interior.Color = RGB(255, 150, 150)
        ElseIf rec(1) / 60 >= LIMIT_H * 0.8 Then
            outWs.Rows(r).Interior.Color = RGB(255, 230, 150)
        End If
        r = r + 1
    Next k
    MsgBox dic.Count & " 人分を集計しました（赤：上限以上、黄：上限の8割以上）"
End Sub

時刻の計算で間違えやすい点

Excel の時刻は「1日＝1」の小数なので、18:00 − 9:00 は 0.375 になります。これを時間にするには24を、分にするには24×60を掛けます。合計した時間をセルに時刻の形式で表示すると、24時間を超えた分が切り捨てられて見えるため、この記事では数値（時間）として出力しています。

日をまたぐ夜勤（22:00〜翌6:00 など）は、終業から始業を引くとマイナスになるので、1日分（1440分）を足して補正しています。深夜労働や休日労働を分けて集計する必要がある場合は、それぞれの時間帯を別に計算する処理を追加してください。

注意点

このサンプルは「所定8時間を超えた分」を単純に残業としています。実際の残業時間の計算方法（法定内残業の扱い、変形労働時間制、休日出勤の扱いなど）や上限時間は、就業規則や労使協定によって異なります。集計結果は目安として使い、正式な判断は勤怠システムや労務の担当者の確認に基づいて行ってください。

ほかの記事