残業代をエクセルで計算する——IF・VLOOKUP・時刻関数を使った完全自動シートの作り方
給与計算担当者でも、自分の残業代を自分で検算したい労働者でも、エクセルは最強のツールです。電卓を叩くより正確で、一度作れば毎月使い回せる。このページでは、実際に使える残業代計算シートの設計図を、セルに入れる数式レベルでお見せします。
エクセル計算シートの基本設計——必要な列と入力項目
| 列 | 項目名 | 入力/計算 | 説明 |
|---|---|---|---|
| A | 日付 | 手入力 | YYYY/MM/DD形式 |
| B | 曜日 | =TEXT(A2,"aaa") | 休日判定に使用 |
| C | 開始時刻 | 手入力 | hh:mm形式（例：9:00） |
| D | 終了時刻 | 手入力 | hh:mm形式（例：22:30） |
| E | 休憩時間 | 手入力 | 時間単位（例：1.0） |
| F | 通常残業時間 | 計算式 | 8h超〜22時までの時間 |
| G | 深夜残業時間 | 計算式 | 22時〜5時の時間 |
| H | 休日フラグ | 手入力/判定 | 法定休日=1, 通常=0 |
| I | 残業代 | 計算式 | 割増率を乗じた金額 |
主要な計算式——コピペで使えるエクセル関数
以下は時給1,500円を基準とした場合の主要な計算式です。セル番地は上記レイアウトに準拠しています。
実労働時間（H列）： =(D2-C2)*24-E2（時刻差に24を掛けて時間数に変換）
通常残業時間（F列）： =MAX(0, MIN((D2-C2)*24, (TIME(22,0,0)-MAX(C2,TIME(8,0,0)))*24 ) -8 )——これは8時間超かつ22時までの残業を抽出する式です。
深夜残業時間（G列）： =IF(D2>TIME(22,0,0), (D2-MAX(C2,TIME(22,0,0)))*24, 0)——22時以降の勤務時間を計算します。
残業代（I列）： =IF(H2=1, (F2+G2)*1500*1.35, F2*1500*1.25 + G2*1500*1.5)——休日フラグによる分岐です。
月60時間超・50%割増への対応——累計計算の実装
月60時間超の50%割増に対応するには、残業時間の累計列を追加する必要があります。
| 列 | 項目 | 計算式 |
|---|---|---|
| J | 残業時間合計（1日） | =F2+G2 |
| K | 残業時間累計（月） | =K1+J2 |
| L | 25%割増対象時間 | =IF(K2<=60, J2, IF(K2-J2>=60, 0, 60-(K2-J2))) |
| M | 50%割増対象時間 | =J2-L2 |
| N | 補正後残業代 | =IF(H2=1, J2*1500*1.35, L2*1500*1.25 + M2*1500*1.5) |
これで月の累計が60時間を超えた時点から、超過分に自動で50%割増が適用されるようになります。
よくあるエクセルの落とし穴——3つの典型ミス
1つめ：時刻計算の罠。エクセルは時刻をシリアル値（1日＝1.0）で管理します。たとえば「22:00」は0.9166...。終了時刻が0:30（翌日）の場合、0.0208...となり開始時刻より小さくなるため、IF(D2<C2, D2+1, D2) のように+1日の補正が必要です。
2つめ：24時間以上の表示。残業時間の合計が24時間を超えると、表示形式が「hh:mm」では0時にリセットされて表示されます。「[h]:mm」形式を使わないと、48時間の残業が「0:00」と表示される事態になります。
3つめ：割増率のハードコーディング。時給や割増率をセル参照ではなく計算式に直接書くと、法改正のたびに全セルを修正する羽目になります。別シートに「パラメータ表」を作り、VLOOKUPで参照する設計を推奨します。
よくある質問
- スマホでエクセルの残業計算はできますか？
- スマホ版Excelでも基本的な計算式は動作します。ただし画面が小さいため、シートの設計はシンプルにし、入力セルを大きく、計算セルは保護（ロック）することをお勧めします。Googleスプレッドシートでも同じ関数が使えます。
- エクセルとGoogleスプレッドシート、どちらがおすすめ？
- 個人の残業記録であればGoogleスプレッドシートが便利です。スマホからも編集でき、クラウドに自動保存されるため、記録の紛失リスクが低いです。ただし会社の給与計算にはエクセルの方が一般的で、マクロ（VBA）が使える点で優位です。
- マクロ（VBA）で残業代計算を自動化できますか？
- 可能です。大量の従業員データを処理する場合はマクロが有効です。VBAを使えば、タイムカードのCSVデータをインポート→割増率を自動判定→給与明細フォーマットに出力、といった一連の処理をボタン1つで実行できます。ただしマクロの作成・メンテナンスにはVBAのスキルが必要です。
- エクセルで作った残業記録は裁判の証拠になりますか？
- なります。エクセルのデータは電子データとして民事訴訟の証拠（書証）になりえます。ただし信憑性を高めるためには、(1)毎日記録する習慣をつける（後日の一括作成は信用性が低い）、(2)ファイルのプロパティに作成日時が残るため改ざんせずに保存する、(3)クラウド（OneDrive等）に保存して更新履歴を残す、といった工夫が有効です。