Markdown Reference

2026.06 出勤工時計算純 Excel 解法

2026.06 出勤工時計算純 Excel 解法

以下做法只用 Excel 公式，不用 VBA、Power Query、Python。

限制先說明：目前這個資料夾內沒有原始活頁簿 2026.06月份.xlsx，因此這份解法提供的是可直接套進工作簿的公式設計，而不是已代填完成的 .xlsx 檔。

做法概述

  1. 保留原始工作表 員工出勤明細表 不動。
  2. 新增一張工作表 計算明細，逐列引用原始資料並做清洗。
  3. 在 算工作數 彙總每位員工的 工作天數 與 工作總時數。

假設的原始欄位

員工出勤明細表 欄位如下：

欄內容
A日期，或員工區段標題
B星期
C班次代號
D班次名稱
E上班
F下班
G備註

這份解法的關鍵假設只有兩個：

  1. 員工區段標題列放在 A 欄，而且不是日期數值。
  2. 上班、下班欄位若含文字註記，最後 5 個字元是 hh:mm 時間。

如果你的實際欄位位置不同，只要把公式中的欄位參照改掉即可。

一、建立 計算明細 工作表

在新工作表 計算明細 建立以下欄位：

欄標題
A來源列
B員工
C日期
D星期
E上班原始
F下班原始
G上班時間
H下班時間
I計算下班
J工作時數
K工作日鍵
L每日計數
M每月
N員工(馬賽克)

A 欄：來源列

A2 先手動輸入原始資料第一列的列號，通常是 2。

A3 輸入：

=A2+1

然後往下填滿，直到超過原始資料最後一列即可。

B 欄：帶出員工名稱並向下延續

B2：

=LET(r,$A2,v,INDEX('員工出勤明細表'!$A:$A,r),IF(AND(v<>"",NOT(ISNUMBER(v))),v,IF(ROW()=2,"",B1)))

說明：

  1. 若原始 A 欄該列是文字，就視為員工標題。
  2. 若原始 A 欄該列是日期，則沿用上一列員工名稱。

C 欄：只保留真正的日期列

C2：

=LET(r,$A2,v,INDEX('員工出勤明細表'!$A:$A,r),IF(ISNUMBER(v),v,""))

把儲存格格式設成日期。

D 到 F 欄：帶出原始欄位

D2：

=IF($C2="","",INDEX('員工出勤明細表'!$B:$B,$A2))

E2：

=IF($C2="","",INDEX('員工出勤明細表'!$E:$E,$A2))

F2：

=IF($C2="","",INDEX('員工出勤明細表'!$F:$F,$A2))

G、H 欄：解析最後出現的時間

G2：

=LET(t,TRIM(E2&""),IF(t="","",IF(ISNUMBER(E2),MOD(E2,1),TIMEVALUE(RIGHT(t,5)))))

H2：

=LET(t,TRIM(F2&""),IF(t="","",IF(ISNUMBER(F2),MOD(F2,1),TIMEVALUE(RIGHT(t,5)))))

把 G:H 設成時間格式 hh:mm。

這兩格會把以下內容正確轉成時間：

  1. 07:53
  2. 遲到08:25
  3. 早退15:39
  4. 病假12:23
  5. 特休假09:21

I 欄：下班時間上限 19:30

I2：

=IF(H2="","",MIN(H2,TIME(19,30,0)))

J 欄：計算每筆有效工時

J2：

=IF(OR(C2="",D2="六",D2="日",G2="",H2=""),"",MAX(0,I2-G2))

規則已包含：

  1. 六日不計。
  2. 上班或下班空白不計。
  3. 下班晚於 19:30 時，自動截到 19:30。
  4. 若異常造成下班早於上班，工時至少為 0。

把 J 欄格式設成 [h]:mm。

K 欄：同人同日唯一鍵

K2：

=IF(J2="","",B2&"|"&TEXT(C2,"yyyy-mm-dd"))

L 欄：每日只計 1 個工作天

L2：

=IF(K2="","",--(COUNTIF($K$2:K2,K2)=1))

說明：

  1. 同一位員工同一天第一筆有效紀錄記為 1。
  2. 同一天後續有效紀錄記為 0。
  3. 這樣工作天數不會重複，但工時仍可在 J 欄加總。

M 欄：月份鍵

M2：

=IF(C2="","",TEXT(C2,"yyyy-mm"))

N 欄：員工代號與姓名馬賽克

N2：

=LET(s,TRIM(SUBSTITUTE(B2,"員工代號:","")),id,TEXTBEFORE(s," "),nm,TEXTAFTER(s," "),"員工代號:"&LEFT(id,2)&REPT("*",MAX(1,LEN(id)-4))&RIGHT(id,2)&" "&LEFT(nm,1)&REPT("*",MAX(1,LEN(nm)-2))&RIGHT(nm,1))

說明：

  1. 員工代號會保留前 2 碼與後 2 碼，中間以 * 取代。
  2. 中文姓名會保留首尾字，中間以 * 取代。
  3. 所有成果與檢查明細請使用 N 欄，不直接暴露 B 欄原始員工資訊。

向下填滿

把 B2:N2 全部往下填滿到和 A 欄一樣的最後一列。

二、在 算工作數 做彙總

若 算工作數 已有員工名單，可直接套公式。假設：

  1. A 欄是員工。
  2. B 欄是每月。
  3. C 欄要算工作天數。
  4. D 欄要算工作總時數。

若 B2 要填 2026 年 6 月，可直接放文字：

2026-06

工作天數

C2：

=COUNTIFS('計算明細'!$N:$N,$A2,'計算明細'!$M:$M,$B2,'計算明細'!$L:$L,1)

工作總時數

D2：

=SUMIFS('計算明細'!$J:$J,'計算明細'!$N:$N,$A2,'計算明細'!$M:$M,$B2)

把 D 欄格式設成 [h]:mm。

然後將 B2:D2 往下填滿全部員工。

三、如果 算工作數 沒有員工清單

可先在空白區產生唯一員工名單。

若你用的是 Excel 365，在某個空白欄輸入：

=SORT(UNIQUE(FILTER('計算明細'!$N$2:$N$5000,'計算明細'!$L$2:$L$5000<>"")))

再對這份名單套用上一節的彙總公式即可。

四、驗收方式

完成後可直接檢查以下幾點：

  1. 計算明細!J:J 中，星期六、星期日的列應為空白。
  2. 計算明細!I:I 中，凡晚於 19:30 的下班時間都應顯示為 19:30。
  3. 計算明細!G:H 應能把 遲到08:25、早退15:39 這類欄位轉成時間。
  4. 同一員工同一天多筆紀錄時，L 欄只有第一筆為 1，其他為 0。
  5. 算工作數 的工作總時數應為所有有效 J 欄工時加總。

五、最小可用公式組合

如果你只想先做出結果，最少只需要這 6 個公式欄位：

  1. B2 員工
  2. C2 日期
  3. G2 上班時間
  4. H2 下班時間
  5. J2 工作時數
  6. L2 每日計數

但實務上仍建議保留完整 B:N 欄，因為比較容易人工驗算。

六、舊版 Excel 替代寫法

如果你的 Excel 沒有 LET，可改用以下寫法。

上班時間

=IF(E2="","",IF(ISNUMBER(E2),MOD(E2,1),TIMEVALUE(RIGHT(TRIM(E2&""),5))))

下班時間

=IF(F2="","",IF(ISNUMBER(F2),MOD(F2,1),TIMEVALUE(RIGHT(TRIM(F2&""),5))))

工作時數

=IF(OR(C2="",D2="六",D2="日",G2="",H2=""),"",MAX(0,MIN(H2,TIME(19,30,0))-G2))

結論

這套公式完全符合題目規則：

  1. 六日不計工作天數與工時。
  2. 下班時間最多算到 19:30。
  3. 含文字註記的上下班欄位可抓最後的時間。
  4. 同員工同日只加 1 個工作天。
  5. 同員工同日多筆有效紀錄的工時可累加。
  6. 成果與檢查明細使用馬賽克後的員工代號與姓名。

如果你之後把原始 2026.06月份.xlsx 放進這個資料夾，我可以在不離開這個工作資料夾的前提下，直接幫你把這份公式方案對到實際欄位與實際儲存格位置。