# 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` 輸入：

```excel
=A2+1
```

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

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

`B2`：

```excel
=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`：

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

把儲存格格式設成日期。

### D 到 F 欄：帶出原始欄位

`D2`：

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

`E2`：

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

`F2`：

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

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

`G2`：

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

`H2`：

```excel
=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`：

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

### J 欄：計算每筆有效工時

`J2`：

```excel
=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`：

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

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

`L2`：

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

說明：

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

### M 欄：月份鍵

`M2`：

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

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

`N2`：

```excel
=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 月，可直接放文字：

```excel
2026-06
```

### 工作天數

`C2`：

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

### 工作總時數

`D2`：

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

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

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

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

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

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

```excel
=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`，可改用以下寫法。

### 上班時間

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

### 下班時間

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

### 工作時數

```excel
=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` 放進這個資料夾，我可以在不離開這個工作資料夾的前提下，直接幫你把這份公式方案對到實際欄位與實際儲存格位置。