如何快速計算Excel中的加班時間和報酬？

Author: Sun Last Modified: 2025-08-23

在許多工作場所，追蹤員工的工作時數，特別是加班時數，對於準確的薪資計算和遵守法規至關重要。假設您有一個表格記錄了某員工的打卡上班、午休和打卡下班時間。您希望快速計算每天的加班時數及相應的報酬，如下方截圖所示。有效的計算不僅能節省時間，還能減少手動錯誤的風險，這對於匯總多名員工或多個薪資週期的數據尤為重要。
calculate the overtime and payment

計算加班和報酬

用於批量計算加班/報酬的VBA宏

使用樞紐分析表進行摘要分析

計算加班和報酬

太棒了！在 Excel 中像 Chrome、Edge、Firefox 和 Safari 一樣使用 Efficient Tabs！
每天為您節省 50% 的時間，並減少數以千計的滑鼠點擊！

您可以使用內建公式有效地確定Excel中的加班時數和相應的報酬。此方法適用於單一員工記錄或需要簡單計算的小型數據集。以下是逐步指南：

1. 首先，計算每天的正常工作時數。點擊F2單元格並輸入以下公式：

=IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)

按下Enter鍵，然後拖動自動填充柄向下複製公式到其他行。這將在F列中顯示每天的正常工作時數。
Apply a formula to display the regular working hour

2. 接下來，計算加班時數。在G2單元格中輸入以下公式：

=IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)

按下Enter鍵後，拖動公式填充加班列的所有行。每天的加班時數將在G列中計算出。
Apply a formula to calculate the overtime

在這些公式中：

B2：開始工作（打卡上班時間）
C2：午休開始
D2：午休結束
E2：結束工作（打卡下班時間）
該計算假設標準工作日為8小時；您可以根據政策需求調整公式中的「8」和時間參照。

提示：確保Excel中的時間值格式正確（例如hh:mm）。

3. 為了彙總每週的總正常工作時數和加班時數，選擇F8單元格並輸入：

=SUM(F2:F7)

然後，將此公式拖動到G8單元格以獲得總加班時數。
apply a formula to get total regular hours and overtime hours

4. 在指定單元格中計算正常工作時數和加班的報酬。例如，在F9單元格中計算正常工資，輸入：

=F8*I2

同樣地，在G9單元格中計算加班工資，輸入：

=G8*J2

這裡，I2和J2應該包含正常工作和加班的每小時費率。
use formulas to calculate the payment for regular hours and overtime

為了獲得正常工作和加班的總報酬，在H9單元格中使用簡單的求和公式：

=F9+G9

這個最終結果代表審查期間的總薪酬，結合了正常工資和額外的加班工資。
apply a formula to calculate the total payments

這種基於公式的計算方法對於日常或每週計算來說非常直觀且快速，並且如果工作安排或加班標準改變，也很容易適應。然而，對於大量員工或高級報告需求，其他Excel功能或自動化可能更高效。

優勢：簡單，不需要編程知識，易於維護小數據集。
局限性：每個工人/表格需要手動設置，如果表格結構改變則需要維護公式，不適合非常大的數據集。

如果您的數據集增長或需要為許多工人或不同時期計算加班/報酬，請考慮自動化此過程或使用Excel的內建分析工具。請參閱以下選項：

用於批量計算加班/報酬的VBA宏

當處理涉及多個工人、工作表或時期的大數據集時——手動填寫公式效率低下——您可以使用VBA宏來自動化整個計算過程。這種方法簡化了重複處理，特別是在處理複雜數據結構或頻繁數據導入時尤為有效。

情景：您有一個表格，其中包含員工、工作開始、午餐開始、午餐結束、工作結束等欄位，您希望批量計算正常工作時數、加班和報酬。

注意：在運行之前，保存您的工作簿並確保已啟用宏。備份以防測試或初次運行時意外數據丟失。

1. 點擊開發者工具 > Visual Basic。在Microsoft Visual Basic for Applications窗口中，點擊插入 > 模塊，然後將以下代碼複製並粘貼到模塊中：

Sub BatchOvertimeCalculation()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long
    Dim regHourCol As String, overtimeCol As String, payCol As String
    Dim startCol As String, lunchStartCol As String, lunchEndCol As String, endCol As String
    Dim regHourlyRate As Double, overtimeHourlyRate As Double
    
    On Error Resume Next
    
    regHourCol = InputBox("Enter column letter for Regular Hour (output):", "KutoolsforExcel", "F")
    overtimeCol = InputBox("Enter column letter for Overtime (output):", "KutoolsforExcel", "G")
    payCol = InputBox("Enter column letter for Payment (output):", "KutoolsforExcel", "H")
    startCol = InputBox("Enter column letter for Work Start:", "KutoolsforExcel", "B")
    lunchStartCol = InputBox("Enter column letter for Lunch Start:", "KutoolsforExcel", "C")
    lunchEndCol = InputBox("Enter column letter for Lunch End:", "KutoolsforExcel", "D")
    endCol = InputBox("Enter column letter for Work End:", "KutoolsforExcel", "E")
    
    regHourlyRate = Application.InputBox("Enter hourly rate for regular hours:", "KutoolsforExcel", 15, Type:=1)
    overtimeHourlyRate = Application.InputBox("Enter hourly rate for overtime:", "KutoolsforExcel", 22.5, Type:=1)
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
    
    For i = 2 To lastRow
        Dim totalHours As Double, regHours As Double, overtimeHours As Double
        
        totalHours = ((ws.Range(lunchStartCol & i) - ws.Range(startCol & i)) + _
                      (ws.Range(endCol & i) - ws.Range(lunchEndCol & i))) * 24
        
        If totalHours > 8 Then
            regHours = 8
            overtimeHours = totalHours - 8
        Else
            regHours = totalHours
            overtimeHours = 0
        End If
        
        ws.Range(regHourCol & i).Value = regHours
        ws.Range(overtimeCol & i).Value = overtimeHours
        ws.Range(payCol & i).Value = regHours * regHourlyRate + overtimeHours * overtimeHourlyRate
    Next i
    
    MsgBox "Batch calculation complete!", vbInformation, "KutoolsforExcel"
End Sub

2. 輸入代碼後，點擊 VBA工具欄中的按鈕運行宏。在對話框中輸入請求的信息（例如哪些列包含您的時間數據和報酬率）。宏將自動填充每行的正常工作時數、加班和總報酬列。
故障排除：確保所有時間列都具有正確的Excel時間格式。如果任何單元格有無效或空白數據，宏將跳過或可能返回“0”。運行宏後，始終手動檢查幾行以確保準確性。

優勢：對於大型/複雜數據集極其高效，消除了手動複製和公式拖動。
局限性：需要一些VBA熟悉度，啟用宏時會出現安全警告，需小心引用正確的列。

總結建議：對於日常或一次性計算，公式快速且直觀。隨著加班計算任務擴大到更多記錄或報告需求變得更加複雜，使用VBA自動化可以顯著減少手動操作和錯誤。始終雙重檢查準確的時間格式化，並在任何解決方案之後，驗證計算邏輯是否符合您公司的加班政策。如果遇到錯誤（如#VALUE!），重新檢查單元格格式或空白條目。在批量操作之前，考慮保留備份。

輕鬆在 Excel 中為日期添加天、年、月、小時、分鐘和秒

如果單元格中有一個日期，而您需要為其添加天、年、月、小時、分鐘或秒，使用公式可能會非常複雜且難以記住。透過 Kutools for Excel 的 Date & Time Helper 工具，您可以輕鬆地為日期添加時間單位、計算日期差異，甚至可以根據出生日期計算某人的年齡，無需記住複雜的公式。

Kutools for Excel - 超過 300 種必備工具，讓 Excel 功能更強大。永久免費享受 AI 功能！立即獲取

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多