毎月の勤怠管理で、タイムカードの出勤時間と退勤時間を見ながら勤務時間を手計算していませんか。
人数が少ないうちは対応できても、従業員数が増えると集計作業だけで多くの時間がかかってしまいます。
Excelを活用すれば、出勤時間と退勤時間を入力するだけで勤務時間や残業時間を自動計算できます。
さらに月間集計まで自動化できるため、給与計算や勤怠確認の効率化にもつながります。
この記事では、Excelでタイムカードを自動集計する方法を基本から実務向けの活用方法まで詳しく解説します。
✅ Excelでタイムカードを自動集計する基本構成
タイムカード集計を始める前に、どのような表を作るべきかを理解しておくことが重要です。
先に数式だけ作成してしまうと、後から休憩時間や残業時間を追加したくなった際に修正が大変になります。
実務では勤務時間だけでなく、休憩時間や残業時間も管理するケースがほとんどです。
最初から拡張しやすい構成で作成しておくと後々の運用が楽になります。
まずは基本的な表の構成から確認しましょう。
・タイムカード集計表の例
| 日付 | 出勤 | 退勤 | 休憩 | 勤務時間 |
|---|---|---|---|---|
| 4/1 | 9:00 | 18:00 | 1:00 | 8:00 |
| 4/2 | 9:15 | 18:30 | 1:00 | 8:15 |
このような構成にしておくと、
- 勤務時間
- 残業時間
- 月間合計時間
を計算しやすくなります。
・時間形式を設定する
入力前に対象セルの表示形式を設定します。
- 時刻を入力するセルを選択
- 右クリック
- 「セルの書式設定」を選択
- 「時刻」を指定
時間として認識させることで計算が正しく行われます。
✅ タイムカードの勤務時間を自動計算する方法
勤務時間計算はタイムカード集計の基本となる部分です。
ただし単純に退勤時間から出勤時間を引くだけでは、休憩時間を考慮できません。
また日をまたぐ勤務にも対応できなくなる場合があります。
最初に正しい計算式を覚えておくことで、後のトラブルを防げます。
まずは基本的な勤務時間計算を見ていきましょう。
・勤務時間を計算する数式
出勤時間がB列、退勤時間がC列、休憩時間がD列の場合
=C2-B2-D2
・設定手順
- 勤務時間セルを選択
- 数式を入力
- Enterキーを押す
- オートフィルで下へコピー
これだけで勤務時間を自動計算できます。
・表示形式を設定する
勤務時間セルは次の表示形式がおすすめです。
[h]:mm
24時間を超える集計にも対応できます。
✅ 月間勤務時間を自動集計する方法
日ごとの勤務時間を計算したら、次は月間合計を求めます。
ここで表示形式を間違えると、実際には160時間働いていても「16:00」などと表示されることがあります。
時間データ特有の落とし穴なので注意が必要です。
月間集計を正しく行う方法を確認しましょう。
・SUM関数を使用する
勤務時間がE列の場合
=SUM(E2:E32)
・集計手順
- 月合計セルを選択
- SUM関数を入力
- Enterキーを押す
これで月間勤務時間が集計されます。
・24時間超の表示にする
セルの書式設定で
[h]:mm
を設定してください。
例えば、
- 160時間30分
- 180時間15分
のように正しく表示できます。
月間勤務時間を集計すると、「合計時間が正しく表示されない」「SUM関数なのに結果がおかしい」といったトラブルが発生することがあります。時間データ特有の集計ミスについては、「【Excel】時間データを自動集計する方法|SUM関数で合計時間がズレる原因も解説」で詳しく解説しています。
✅ タイムカードから残業時間を自動計算する方法
勤務時間だけでなく、残業時間も自動計算できると勤怠管理がさらに便利になります。
特に給与計算では残業時間の集計が重要です。
毎回手計算するとミスが発生しやすくなります。
Excelなら定時時間を超えた分だけを自動計算できます。
・定時を8時間とする場合
勤務時間がE列の場合
=MAX(E2-TIME(8,0,0),0)
・なぜMAX関数を使うのか
例えば7時間勤務の日は、
=E2-TIME(8,0,0)
だけだとマイナスになります。
MAX関数を利用すると、
- 8時間以下 → 0
- 8時間超 → 超過分
のみ表示できます。
・残業時間の月間集計
残業時間列をSUM関数で集計します。
=SUM(F2:F32)
給与計算資料としても活用できます。
今回は定時を超えた時間を簡易的に集計する方法を紹介しましたが、法定労働時間や残業管理まで考慮する場合は別の設計が必要になります。残業時間の詳しい計算方法については、「【Excel】残業時間を自動計算する方法|法定時間を考慮した管理表作成のコツ」を参考にしてください。
✅ 深夜勤務や日をまたぐ勤務に対応する方法
夜勤や交代勤務では、退勤時間が翌日になることがあります。
このケースを考慮せずに数式を作ると、マイナス時間が表示されてしまいます。
実務では非常によく発生するため、事前に対策しておきましょう。
・日をまたぐ勤務の数式
例
- 出勤:22:00
- 退勤:翌5:00
の場合
=IF(C2<B2,C2+1-B2-D2,C2-B2-D2)
・数式の考え方
退勤時間が出勤時間より小さい場合
C2+1
として翌日扱いにしています。
・夜勤勤務でも利用可能
この方法なら
- 深夜勤務
- シフト勤務
- 24時間体制の業務
にも対応できます。
夜勤や24時間体制の勤務では、日付変更をまたぐ時間計算が頻繁に発生します。より詳しい計算方法や実務上の注意点については、「【Excel】日をまたぐ時間計算を正しく行う方法|深夜勤務や夜勤管理に対応」で解説しています。
シフト制勤務では勤務時間が日によって変わるため、さらに柔軟な集計方法が必要になります。シフト表を使った時間計算については、「【Excel】シフト勤務の時間計算を自動化する方法|日をまたぐ勤務にも対応」をご覧ください。
✅ タイムカード集計でよくあるトラブル
タイムカード集計では数式よりも入力データが原因でエラーになるケースが多くあります。
計算式ばかり確認していると原因が見つからないこともあります。
実務でよくあるミスを知っておくことで、集計トラブルを防げます。
・時間が文字列になっている
以下のような入力は文字列扱いになる場合があります。
9:00
半角入力を推奨します。
・勤務時間がマイナスになる
日をまたぐ勤務の対応ができていない可能性があります。
IF関数を利用した計算式を確認してください。
・合計時間が正しく表示されない
表示形式が
h:mm
になっている可能性があります。
月間集計は
[h]:mm
を使用してください。
・#VALUE!エラーが表示される
時間データではなく文字列が混在していることがあります。
入力データを確認しましょう。
時間計算で発生するエラーの多くは、セルの入力形式やデータ型の違いが原因です。#VALUE!エラーの詳しい原因と対処方法については、「【Excel】数式で出る「#VALUE!」エラーの原因と対処法を徹底解説」を参考にしてください。
✅ Excel VBAでタイムカード集計をさらに効率化する方法
従業員数が多い場合、数式だけでは管理が大変になることがあります。
例えば、
- 複数シート集計
- 部署別集計
- 月次レポート作成
などはVBAが活躍します。
ボタン一つで勤怠データを集計できるようになるため、大幅な業務効率化が可能です。
まずは関数で仕組みを作り、その後必要に応じてVBA化するのがおすすめです。
✅ まとめ:Excelでタイムカードを自動集計して勤怠管理を効率化しよう
Excelを活用すれば、タイムカードの勤務時間や残業時間を自動集計できます。
今回のポイントをまとめます。
- 出勤時間と退勤時間から勤務時間を計算できる
- 休憩時間を差し引いて実働時間を求められる
- SUM関数で月間勤務時間を集計できる
- MAX関数で残業時間を自動計算できる
- 日をまたぐ勤務はIF関数で対応できる
- VBAを活用すればさらに自動化できる
毎月の勤怠集計作業を効率化したい方は、まずExcelの関数による自動集計から導入してみましょう。