【VBA】Excelで勤怠管理表を自動化する方法（コピペOK）

この記事でできること

• VBAでボタンを押すだけで出勤・退勤の時刻を自動記録できる
• 勤務時間と残業時間をDateDiffで自動計算できる
• 月の日付リストを自動生成し、月末に合計勤務時間を一括集計できる

対象: Excel 2016以降 / Microsoft 365、Windows 10/11

完成イメージ（Before / After）

Before（手作業）:

1. 毎朝、出勤時刻をセルに手入力（「9:05」「9:12」…）
2. 退勤時にも手入力
3. 月末に1行ずつ「退勤 – 出勤 – 休憩」を計算
4. 合計を電卓で出す
5. 入力忘れや計算ミスが発生

After（自動化）:

1. 「出勤」ボタンを押す → 現在時刻が出勤セルに自動入力
2. 「退勤」ボタンを押す → 現在時刻が退勤セルに自動入力、勤務時間が自動計算
3. 月末に「集計」ボタンを押す → 合計勤務時間・残業時間が自動集計

5人ほどのチームの勤怠をExcelで管理していた。毎朝出勤時刻を手入力し、月末に1行ずつ勤務時間を計算。たった5人分でも30分かかり、入力忘れや計算ミスが起きていた。ボタン1つで打刻できるようにしてからは、入力ミスがなくなり、月末の集計も一瞬で終わるようになった。

勤怠管理の手入力は、VBAに任せればボタン1つで終わる。

実行前の準備

バックアップを取る

マクロ実行前に、Excelファイルのコピーを別フォルダに保存しておく。

Excelをマクロ有効ブック（.xlsm）で保存する

拡張子が .xlsx のままだとマクロが保存できない。

1. 「ファイル」→「名前を付けて保存」
2. ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
3. 保存

シートの準備

勤怠管理表のシート（シート名：「勤怠」）に以下のヘッダーを1行目に入力する。

実務版コードの GenerateDateList を使えば、ヘッダーも含めて自動生成されるため、手動入力は不要。

手順（コピペ → 実行まで約5分）

VBE（コードを書く画面）を開く

1. Excelで Alt + F11 を押す
2. VBE（Visual Basic Editor）が開く

標準モジュールを挿入する

1. VBEのメニュー →「挿入」→「標準モジュール」
2. 白い画面（コードウィンドウ）が表示される

コードを貼り付けて実行する

1. コードウィンドウに、下のコードをそのままコピペする
2. Alt + F8 → マクロ名を選んで「実行」

ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。出勤・退勤・集計の3つのボタンを並べて配置すると使いやすい。

コード（最小版）– 出勤・退勤を記録して勤務時間を計算

まずは最小構成で動きを確認する。A2に日付、C2に出勤時刻、D2に退勤時刻を記録し、F2に勤務時間を表示する。

'============================================================
' ■ 出勤・退勤の記録と勤務時間の計算（最小版）
'   → 今日の行に出勤/退勤時刻を記録し、勤務時間を自動計算
'============================================================

'--- 出勤を記録する ---
Sub ClockIn()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("勤怠")

    '--- 今日の日付をA2に、現在時刻をC2に記録 ---
    ws.Range("A2").Value = Date
    ws.Range("A2").NumberFormat = "yyyy/mm/dd"
    ws.Range("B2").Value = WeekdayName(Weekday(Date))

    '--- 既に出勤が記録されている場合は上書き確認 ---
    If ws.Range("C2").Value <> "" Then
        Dim ans As VbMsgBoxResult
        ans = MsgBox("出勤時刻が既に記録されています。上書きしますか？", _
                     vbYesNo + vbQuestion)
        If ans = vbNo Then Exit Sub
    End If

    ws.Range("C2").Value = Now
    ws.Range("C2").NumberFormat = "hh:mm"

    MsgBox "出勤を記録しました: " & Format(Now, "hh:mm"), vbInformation

End Sub

'--- 退勤を記録して勤務時間を計算する ---
Sub ClockOut()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("勤怠")

    '--- 出勤が未記録なら警告 ---
    If ws.Range("C2").Value = "" Then
        MsgBox "出勤時刻が記録されていません。先に出勤を記録してください。", vbExclamation
        Exit Sub
    End If

    '--- 退勤時刻を記録 ---
    ws.Range("D2").Value = Now
    ws.Range("D2").NumberFormat = "hh:mm"

    '--- 勤務時間を計算（分単位で取得し、時:分に変換）---
    Dim startTime As Date
    Dim endTime As Date
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value

    Dim workMinutes As Long
    workMinutes = DateDiff("n", startTime, endTime)

    '--- 休憩60分を引く ---
    Dim breakMinutes As Long
    breakMinutes = 60
    ws.Range("E2").Value = breakMinutes
    workMinutes = workMinutes - breakMinutes
    If workMinutes < 0 Then workMinutes = 0

    '--- 勤務時間をセルに記録 ---
    ws.Range("F2").Value = Int(workMinutes / 60) & ":" & _
                           Format(workMinutes Mod 60, "00")

    MsgBox "退勤を記録しました: " & Format(Now, "hh:mm") & vbCrLf & _
           "勤務時間: " & ws.Range("F2").Value, vbInformation

End Sub

書き換えポイント

コードの流れ

1. ClockIn: Now で現在時刻を取得し、出勤セル（C2）に記録。既に記録がある場合は上書き確認
2. ClockOut: 退勤セル（D2）に現在時刻を記録し、DateDiff("n", 出勤, 退勤) で勤務時間を分単位で計算
3. 休憩時間を引いて、時:分の形式でF2に出力

重要: セルの書式が「標準」のままだと時刻がシリアル値（小数）で表示される。コード内で NumberFormat = "hh:mm" を設定しているが、手動で書式変更する場合は セルの書式をVBAで変更する方法 を参照。

コード（実務版）– ボタン1つで打刻＆月末に勤務時間を自動集計

実務では1ヶ月分の日付リストが必要。日付の自動生成、土日のグレーアウト、打刻、勤務時間・残業時間の計算、月末集計をまとめて実装する。

日付リストの自動生成では DateSerial を使う。日付の差分計算の詳しい仕組みは 2つの日付の差分・加算・比較を計算する方法 で解説している。

'============================================================
' ■ 勤怠管理表の自動化（実務版）
'   → 日付リスト生成 / 出勤・退勤打刻 / 勤務時間・残業時間計算 / 月末集計
'   → 土日はグレーアウト（祝日は対象外）
'============================================================

'--- 定数 ---
Const SHEET_NAME As String = "勤怠"
Const HEADER_ROW As Long = 1
Const DATA_START_ROW As Long = 2
Const BREAK_MINUTES As Long = 60          ' 休憩時間（分）
Const STANDARD_MINUTES As Long = 480      ' 所定労働時間（分）= 8時間

'============================================================
' 日付リストを自動生成する（月初〜月末）
'============================================================
Sub GenerateDateList()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)

    '--- 対象年月を取得（InputBoxで指定） ---
    Dim targetYM As String
    targetYM = InputBox("勤怠表を作成する年月を入力してください（例: 2026/03）", _
                        "年月の指定", Format(Date, "yyyy/mm"))
    If targetYM = "" Then Exit Sub

    '--- 年と月を分割 ---
    Dim targetYear As Integer
    Dim targetMonth As Integer
    targetYear = CInt(Left(targetYM, 4))
    targetMonth = CInt(Right(targetYM, 2))

    '--- 月末日を取得（翌月の0日 = 今月の末日） ---
    '    ※ 12月でも Month+1=13 → DateSerial が自動で繰り上げるため正常動作
    Dim lastDay As Date
    lastDay = DateSerial(targetYear, targetMonth + 1, 0)
    Dim totalDays As Integer
    totalDays = Day(lastDay)

    '--- ヘッダーを設定 ---
    ws.Range("A1:H1").Value = Array("日付", "曜日", "出勤", "退勤", _
                                     "休憩(分)", "勤務時間", "残業時間", "備考")
    ws.Range("A1:H1").Font.Bold = True

    '--- 既存データをクリア ---
    If ws.Cells(DATA_START_ROW, 1).Value <> "" Then
        Dim ans As VbMsgBoxResult
        ans = MsgBox("既存のデータをクリアして新しい勤怠表を作成しますか？", _
                     vbYesNo + vbQuestion)
        If ans = vbNo Then Exit Sub
    End If
    ws.Range("A" & DATA_START_ROW & ":H" & DATA_START_ROW + 40).ClearContents
    ws.Range("A" & DATA_START_ROW & ":H" & DATA_START_ROW + 40).Interior.ColorIndex = xlNone

    '--- 日付リストを生成 ---
    Dim i As Long
    Dim targetDate As Date
    Dim wDay As Integer

    For i = 1 To totalDays
        targetDate = DateSerial(targetYear, targetMonth, i)
        Dim r As Long
        r = DATA_START_ROW + i - 1

        ws.Cells(r, 1).Value = targetDate
        ws.Cells(r, 1).NumberFormat = "yyyy/mm/dd"
        ws.Cells(r, 2).Value = WeekdayName(Weekday(targetDate))

        '--- 休憩時間のデフォルト値 ---
        ws.Cells(r, 5).Value = BREAK_MINUTES

        '--- 土日判定でグレーアウト ---
        wDay = Weekday(targetDate, vbMonday)
        If wDay >= 6 Then
            ws.Range("A" & r & ":H" & r).Interior.Color = RGB(220, 220, 220)
            ws.Cells(r, 8).Value = IIf(wDay = 6, "土曜", "日曜")
        End If
    Next i

    '--- 集計行を追加 ---
    Dim summaryRow As Long
    summaryRow = DATA_START_ROW + totalDays + 1
    ws.Cells(summaryRow, 1).Value = "合計"
    ws.Cells(summaryRow, 1).Font.Bold = True

    '--- 列幅を自動調整 ---
    ws.Columns("A:H").AutoFit

    MsgBox targetYear & "年" & targetMonth & "月の勤怠表を作成しました。" & vbCrLf & _
           "日数: " & totalDays & "日", vbInformation

End Sub

'============================================================
' 出勤を打刻する
'============================================================
Sub StampClockIn()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)

    '--- 今日の行を探す ---
    Dim targetRow As Long
    targetRow = FindTodayRow(ws)

    If targetRow = 0 Then
        MsgBox "今日の日付が勤怠表に見つかりません。" & vbCrLf & _
               "先に「日付リスト生成」を実行してください。", vbExclamation
        Exit Sub
    End If

    '--- 土日チェック ---
    Dim wDay As Integer
    wDay = Weekday(Date, vbMonday)
    If wDay >= 6 Then
        Dim confirmHoliday As VbMsgBoxResult
        confirmHoliday = MsgBox("今日は" & WeekdayName(Weekday(Date)) & "です。" & vbCrLf & _
                     "休日出勤として記録しますか？", vbYesNo + vbQuestion)
        If confirmHoliday = vbNo Then Exit Sub
    End If

    '--- 上書き確認 ---
    If ws.Cells(targetRow, 3).Value <> "" Then
        Dim confirmOverwrite As VbMsgBoxResult
        confirmOverwrite = MsgBox("出勤時刻が既に記録されています（" & _
                      Format(ws.Cells(targetRow, 3).Value, "hh:mm") & "）。" & vbCrLf & _
                      "上書きしますか？", vbYesNo + vbQuestion)
        If confirmOverwrite = vbNo Then Exit Sub
    End If

    '--- 出勤時刻を記録 ---
    ws.Cells(targetRow, 3).Value = Now
    ws.Cells(targetRow, 3).NumberFormat = "hh:mm"

    MsgBox "出勤を記録しました: " & Format(Now, "hh:mm"), vbInformation

End Sub

'============================================================
' 退勤を打刻して勤務時間を計算する
'============================================================
Sub StampClockOut()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)

    '--- 今日の行を探す ---
    Dim targetRow As Long
    targetRow = FindTodayRow(ws)

    If targetRow = 0 Then
        MsgBox "今日の日付が勤怠表に見つかりません。" & vbCrLf & _
               "先に「日付リスト生成」を実行してください。", vbExclamation
        Exit Sub
    End If

    '--- 出勤未記録チェック ---
    If ws.Cells(targetRow, 3).Value = "" Then
        MsgBox "出勤時刻が記録されていません。先に出勤を記録してください。", vbExclamation
        Exit Sub
    End If

    '--- 退勤時刻を記録 ---
    ws.Cells(targetRow, 4).Value = Now
    ws.Cells(targetRow, 4).NumberFormat = "hh:mm"

    '--- 勤務時間を計算 ---
    Dim startTime As Date
    Dim endTime As Date
    startTime = ws.Cells(targetRow, 3).Value
    endTime = ws.Cells(targetRow, 4).Value

    Dim workMinutes As Long
    workMinutes = DateDiff("n", startTime, endTime)

    '--- 休憩時間を引く ---
    Dim breakMin As Long
    breakMin = ws.Cells(targetRow, 5).Value
    If breakMin = 0 Then breakMin = BREAK_MINUTES
    workMinutes = workMinutes - breakMin
    If workMinutes < 0 Then workMinutes = 0

    '--- 勤務時間をセルに記録 ---
    ws.Cells(targetRow, 6).Value = Int(workMinutes / 60) & ":" & _
                                   Format(workMinutes Mod 60, "00")

    '--- 残業時間を計算 ---
    Dim overtimeMinutes As Long
    overtimeMinutes = workMinutes - STANDARD_MINUTES
    If overtimeMinutes < 0 Then overtimeMinutes = 0

    If overtimeMinutes > 0 Then
        ws.Cells(targetRow, 7).Value = Int(overtimeMinutes / 60) & ":" & _
                                       Format(overtimeMinutes Mod 60, "00")
    Else
        ws.Cells(targetRow, 7).Value = "0:00"
    End If

    MsgBox "退勤を記録しました: " & Format(Now, "hh:mm") & vbCrLf & _
           "勤務時間: " & ws.Cells(targetRow, 6).Value & vbCrLf & _
           "残業時間: " & ws.Cells(targetRow, 7).Value, vbInformation

End Sub

'============================================================
' 月末集計（合計勤務時間・合計残業時間を計算）
'============================================================
Sub MonthlySummary()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)

    '--- データの最終行を取得 ---
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    '--- 合計行を探す ---
    Dim summaryRow As Long
    Dim i As Long
    For i = DATA_START_ROW To lastRow
        If ws.Cells(i, 1).Value = "合計" Then
            summaryRow = i
            Exit For
        End If
    Next i

    If summaryRow = 0 Then
        summaryRow = lastRow + 2
        ws.Cells(summaryRow, 1).Value = "合計"
        ws.Cells(summaryRow, 1).Font.Bold = True
    End If

    '--- 勤務時間・残業時間を分単位で集計 ---
    Dim totalWorkMinutes As Long
    Dim totalOvertimeMinutes As Long
    Dim workDays As Long
    totalWorkMinutes = 0
    totalOvertimeMinutes = 0
    workDays = 0

    For i = DATA_START_ROW To summaryRow - 1
        '--- 勤務時間の文字列（"8:00" 形式）を分に変換 ---
        If ws.Cells(i, 6).Value <> "" And ws.Cells(i, 6).Value <> "0:00" Then
            Dim parts() As String
            parts = Split(CStr(ws.Cells(i, 6).Value), ":")
            If UBound(parts) >= 1 Then
                totalWorkMinutes = totalWorkMinutes + CInt(parts(0)) * 60 + CInt(parts(1))
                workDays = workDays + 1
            End If
        End If

        '--- 残業時間を集計 ---
        If ws.Cells(i, 7).Value <> "" And ws.Cells(i, 7).Value <> "0:00" Then
            Dim oParts() As String
            oParts = Split(CStr(ws.Cells(i, 7).Value), ":")
            If UBound(oParts) >= 1 Then
                totalOvertimeMinutes = totalOvertimeMinutes + CInt(oParts(0)) * 60 + CInt(oParts(1))
            End If
        End If
    Next i

    '--- 合計をセルに記録 ---
    ws.Cells(summaryRow, 6).Value = Int(totalWorkMinutes / 60) & ":" & _
                                    Format(totalWorkMinutes Mod 60, "00")
    ws.Cells(summaryRow, 7).Value = Int(totalOvertimeMinutes / 60) & ":" & _
                                    Format(totalOvertimeMinutes Mod 60, "00")
    ws.Cells(summaryRow, 8).Value = "出勤日数: " & workDays & "日"

    ws.Range("A" & summaryRow & ":H" & summaryRow).Font.Bold = True

    MsgBox "月次集計が完了しました。" & vbCrLf & _
           "合計勤務時間: " & ws.Cells(summaryRow, 6).Value & vbCrLf & _
           "合計残業時間: " & ws.Cells(summaryRow, 7).Value & vbCrLf & _
           "出勤日数: " & workDays & "日", vbInformation

End Sub

'============================================================
' 今日の日付に該当する行を探す（ヘルパー関数）
'============================================================
Function FindTodayRow(ws As Worksheet) As Long

    Dim i As Long
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = DATA_START_ROW To lastRow
        If IsDate(ws.Cells(i, 1).Value) Then
            If CDate(ws.Cells(i, 1).Value) = Date Then
                FindTodayRow = i
                Exit Function
            End If
        End If
    Next i

    FindTodayRow = 0

End Function

書き換えポイント

コードの流れ

1. GenerateDateList: 指定した年月の日付リストを自動生成。DateSerial(年, 月+1, 0) で月末日を取得し、1日から末日までをループで出力。土日の行をグレーアウトし、休憩時間のデフォルト値を設定
2. StampClockIn: Now で現在時刻を取得し、今日の行の出勤セルに記録。土日なら休日出勤の確認、既存記録があれば上書き確認
3. StampClockOut: 退勤時刻を記録し、DateDiff("n", 出勤, 退勤) で勤務時間を分単位で計算。休憩を引いて勤務時間と残業時間を算出
4. MonthlySummary: 全行の勤務時間・残業時間を分に変換して合計し、集計行に出力。出勤日数もカウント
5. FindTodayRow: 今日の日付に一致する行を検索するヘルパー関数

土日の判定には Weekday(日付, vbMonday) を使っている。日付・曜日の判定の詳しい仕組みは 日付・曜日の判定で月末処理を自動化する方法 を参照。

営業日数のカウントが必要な場合は 営業日を計算する方法 と組み合わせると便利。

よくある落とし穴5選

自分もシリアル値の罠にハマった。Now関数で打刻したつもりが、セルに 0.375 と表示されて何時なのか分からず焦った。書式を「hh:mm」に変えたら 9:00 と正しく表示された。Excelの時刻は内部的にシリアル値（0〜1の小数）で管理されている。書式設定さえすれば問題ない。

FAQ

Q1: 打刻を間違えた場合はどうする？

セルの値を直接修正すれば良い。出勤セル（C列）や退勤セル（D列）に正しい時刻を手入力し、退勤マクロをもう一度実行すれば勤務時間が再計算される。

Q2: 休憩時間を変更したい

コード冒頭の定数 BREAK_MINUTES を変更する。例えば45分休憩なら Const BREAK_MINUTES As Long = 45 にする。日によって休憩時間が異なる場合は、E列の値を直接書き換えれば、退勤打刻時にその値が使われる。

Q3: 所定労働時間を7.5時間にしたい

コード冒頭の定数 STANDARD_MINUTES を変更する。7.5時間 = 450分なので Const STANDARD_MINUTES As Long = 450 にする。

Q4: 翌月の勤怠表を事前に作りたい

GenerateDateList を実行すると年月を入力するダイアログが表示される。翌月の年月（例：2026/04）を入力すれば翌月分の日付リストが生成される。

Q5: この勤怠表を法的な勤怠記録として使える？

この勤怠表はExcelでの簡易管理用。法的な労働時間管理には、客観的な記録方法（ICカード、勤怠管理システムなど）が推奨されている。あくまでチーム内の簡易的な勤務時間の把握に使用すること。

まとめ

• Now で現在時刻を取得し、ボタン1つで出勤・退勤を記録できる
• DateDiff("n", 出勤, 退勤) で勤務時間を分単位で計算できる
• DateSerial と Weekday で月の日付リストを自動生成し、土日を判定できる
• 月末に集計マクロを実行すれば、合計勤務時間と残業時間が一瞬で出る

関連記事

• マクロをボタン1つで実行する方法 — 出勤・退勤・集計をそれぞれボタンに割り当て
• セルの書式をVBAで変更する方法 — 時刻表示の書式設定

次にやりたくなること

• 日付・曜日の判定で月末処理を自動化する方法: 祝日の判定を追加して、祝日もグレーアウトする
• 営業日を計算する方法: 月の営業日数を自動カウントし、稼働率を計算する

もっとカスタマイズしたい場合

「フレックスタイム制に対応したい」「シフト制で複数パターンの勤務時間を管理したい」「複数人の勤怠をまとめて管理したい」「勤怠データをCSV出力して他システムに連携したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できる。

相談時に伝えると話が早い情報:

• Excel のバージョン / OS
• 勤務体系（固定時間 / フレックス / シフト制）
• 管理対象の人数
• 出力したい帳票の形式