月排班表驗算報告
月排班表的 140 格公式用另一套試算引擎逐格重算。
✅ 已驗算 2026年10月4日・140 格公式
找到並已修正的問題
這一款在這次逐格重算裡沒有發現需要修正的公式。
怎麼驗的
- 把模板交給 LibreOffice（跟本站線上編輯器不同的另一套試算引擎）真的重算一次，不是拿同一套公式自己算自己。
- 這張表共有 140 格公式，全部重算，沒有任何一格出現錯誤值（#DIV/0!、#NAME?、#VALUE! 這類）。
- 這張表由規則庫產生，另有專屬守門整張比對：範例 4 位員工的「勞基法檢查」必須全部符合，每日各班人數與星期必須正確；並把整週沒休、中班接早班、只休 1 天等違法排法放進去，每一種都必須被判出來。
驗算用的輸入值
重算與獨立比對用的都是模板自己的範例數字，共 33 格。你把這些格子換成自己的數字，公式不變、結果會跟著變。
| 格子 | 這一格是什麼 | 範例值 |
|---|---|---|
| B2 | 年份 | 2,026 |
| D2 | 年份 | 10 |
| B4 | 日期 | 1 |
| C4 | 日期 | 2 |
| D4 | 日期 | 3 |
| E4 | 日期 | 4 |
| F4 | 日期 | 5 |
| G4 | 日期 | 6 |
| H4 | 日期 | 7 |
| I4 | 日期 | 8 |
| J4 | 日期 | 9 |
| K4 | 日期 | 10 |
| L4 | 日期 | 11 |
| M4 | 日期 | 12 |
| N4 | 日期 | 13 |
| O4 | 日期 | 14 |
| P4 | 日期 | 15 |
| Q4 | 日期 | 16 |
| R4 | 日期 | 17 |
| S4 | 日期 | 18 |
| T4 | 日期 | 19 |
| U4 | 日期 | 20 |
| V4 | 日期 | 21 |
| W4 | 日期 | 22 |
| X4 | 日期 | 23 |
| Y4 | 日期 | 24 |
| Z4 | 日期 | 25 |
| AA4 | 日期 | 26 |
| AB4 | 日期 | 27 |
| AC4 | 日期 | 28 |
| AD4 | 日期 | 29 |
| AE4 | 日期 | 30 |
| AF4 | 日期 | 31 |
公式逐格重算結果
2026年10月4日 重算・公式 140 格。「重算結果」是模板範例數字算出來的值。
| 格子 | 這一格是什麼 | 重算結果 | 公式 | 驗法 |
|---|---|---|---|---|
| B5 | 星期 | 四 | =IF(1>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,1)),"日","一","二","三","四","五","六")) | 重算 |
| C5 | 星期 | 五 | =IF(2>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,2)),"日","一","二","三","四","五","六")) | 重算 |
| D5 | 星期 | 六 | =IF(3>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,3)),"日","一","二","三","四","五","六")) | 重算 |
| E5 | 星期 | 日 | =IF(4>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,4)),"日","一","二","三","四","五","六")) | 重算 |
| F5 | 星期 | 一 | =IF(5>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,5)),"日","一","二","三","四","五","六")) | 重算 |
| G5 | 星期 | 二 | =IF(6>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,6)),"日","一","二","三","四","五","六")) | 重算 |
| H5 | 星期 | 三 | =IF(7>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,7)),"日","一","二","三","四","五","六")) | 重算 |
| I5 | 星期 | 四 | =IF(8>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,8)),"日","一","二","三","四","五","六")) | 重算 |
| J5 | 星期 | 五 | =IF(9>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,9)),"日","一","二","三","四","五","六")) | 重算 |
| K5 | 星期 | 六 | =IF(10>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,10)),"日","一","二","三","四","五","六")) | 重算 |
| L5 | 星期 | 日 | =IF(11>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,11)),"日","一","二","三","四","五","六")) | 重算 |
| M5 | 星期 | 一 | =IF(12>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,12)),"日","一","二","三","四","五","六")) | 重算 |
| N5 | 星期 | 二 | =IF(13>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,13)),"日","一","二","三","四","五","六")) | 重算 |
| O5 | 星期 | 三 | =IF(14>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,14)),"日","一","二","三","四","五","六")) | 重算 |
| P5 | 星期 | 四 | =IF(15>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,15)),"日","一","二","三","四","五","六")) | 重算 |
| Q5 | 星期 | 五 | =IF(16>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,16)),"日","一","二","三","四","五","六")) | 重算 |
| R5 | 星期 | 六 | =IF(17>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,17)),"日","一","二","三","四","五","六")) | 重算 |
| S5 | 星期 | 日 | =IF(18>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,18)),"日","一","二","三","四","五","六")) | 重算 |
| T5 | 星期 | 一 | =IF(19>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,19)),"日","一","二","三","四","五","六")) | 重算 |
| U5 | 星期 | 二 | =IF(20>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,20)),"日","一","二","三","四","五","六")) | 重算 |
| V5 | 星期 | 三 | =IF(21>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,21)),"日","一","二","三","四","五","六")) | 重算 |
| W5 | 星期 | 四 | =IF(22>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,22)),"日","一","二","三","四","五","六")) | 重算 |
| X5 | 星期 | 五 | =IF(23>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,23)),"日","一","二","三","四","五","六")) | 重算 |
| Y5 | 星期 | 六 | =IF(24>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,24)),"日","一","二","三","四","五","六")) | 重算 |
| Z5 | 星期 | 日 | =IF(25>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,25)),"日","一","二","三","四","五","六")) | 重算 |
| AA5 | 星期 | 一 | =IF(26>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,26)),"日","一","二","三","四","五","六")) | 重算 |
| AB5 | 星期 | 二 | =IF(27>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,27)),"日","一","二","三","四","五","六")) | 重算 |
| AC5 | 星期 | 三 | =IF(28>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,28)),"日","一","二","三","四","五","六")) | 重算 |
| AD5 | 星期 | 四 | =IF(29>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,29)),"日","一","二","三","四","五","六")) | 重算 |
| AE5 | 星期 | 五 | =IF(30>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,30)),"日","一","二","三","四","五","六")) | 重算 |
| AF5 | 星期 | 六 | =IF(31>DAY(DATE($B$2,$D$2+1,0)),"",CHOOSE(WEEKDAY(DATE($B$2,$D$2,31)),"日","一","二","三","四","五","六")) | 重算 |
| AG6 | 王小明 | 23 | =COUNTIF(B6:AF6,"早")+COUNTIF(B6:AF6,"中")+COUNTIF(B6:AF6,"晚") | 重算 |
| AH6 | 王小明 | 184 | =AG6*8 | 重算 |
| AI6 | 王小明 | 8 | =COUNTIF(B6:AF6,"休") | 重算 |
| AJ6 | 王小明 | ✓ 符合 | =IF(OR(COUNTIF(B6:H6,"休")<1,COUNTIF(I6:O6,"休")<1,COUNTIF(P6:V6,"休")<1,COUNTIF(W6:AC6,"休")<1),"✗ 有一週沒有例假",IF(OR(AND(B6="中",C6="早"),AND(OR(B6="早",B6="中"),C6="晚"),AND(C6="中",D6="早"),AND(OR(C6="早",C6="中"),D6="晚"),AND(D6="中",E6="早"),AND(OR(D6="早",D6="中"),E6="晚"),AND(E6="中",F6="早"),AND(OR(E6="早",E6="中"),F6="晚"),AND(F6="中",G6="早"),AND(OR(F6="早",F6="中"),G6="晚"),AND(G6="中",H6="早"),AND(OR(G6="早",G6="中"),H6="晚"),AND(H6="中",I6="早"),AND(OR(H6="早",H6="中"),I6="晚"),AND(I6="中",J6="早"),AND(OR(I6="早",I6="中"),J6="晚"),AND(J6="中",K6="早"),AND(OR(J6="早",J6="中"),K6="晚"),AND(K6="中",L6="早"),AND(OR(K6="早",K6="中"),L6="晚"),AND(L6="中",M6="早"),AND(OR(L6="早",L6="中"),M6="晚"),AND(M6="中",N6="早"),AND(OR(M6="早",M6="中"),N6="晚"),AND(N6="中",O6="早"),AND(OR(N6="早",N6="中"),O6="晚"),AND(O6="中",P6="早"),AND(OR(O6="早",O6="中"),P6="晚"),AND(P6="中",Q6="早"),AND(OR(P6="早",P6="中"),Q6="晚"),AND(Q6="中",R6="早"),AND(OR(Q6="早",Q6="中"),R6="晚"),AND(R6="中",S6="早"),AND(OR(R6="早",R6="中"),S6="晚"),AND(S6="中",T6="早"),AND(OR(S6="早",S6="中"),T6="晚"),AND(T6="中",U6="早"),AND(OR(T6="早",T6="中"),U6="晚"),AND(U6="中",V6="早"),AND(OR(U6="早",U6="中"),V6="晚"),AND(V6="中",W6="早"),AND(OR(V6="早",V6="中"),W6="晚"),AND(W6="中",X6="早"),AND(OR(W6="早",W6="中"),X6="晚"),AND(X6="中",Y6="早"),AND(OR(X6="早",X6="中"),Y6="晚"),AND(Y6="中",Z6="早"),AND(OR(Y6="早",Y6="中"),Z6="晚"),AND(Z6="中",AA6="早"),AND(OR(Z6="早",Z6="中"),AA6="晚"),AND(AA6="中",AB6="早"),AND(OR(AA6="早",AA6="中"),AB6="晚"),AND(AB6="中",AC6="早"),AND(OR(AB6="早",AB6="中"),AC6="晚"),AND(AC6="中",AD6="早"),AND(OR(AC6="早",AC6="中"),AD6="晚"),AND(AD6="中",AE6="早"),AND(OR(AD6="早",AD6="中"),AE6="晚"),AND(AE6="中",AF6="早"),AND(OR(AE6="早",AE6="中"),AF6="晚")),"✗ 換班間隔不足11小時",IF(OR(COUNTIF(B6:H6,"休")<2,COUNTIF(I6:O6,"休")<2,COUNTIF(P6:V6,"休")<2,COUNTIF(W6:AC6,"休")<2),"⚠ 有一週休不到2天：休息日出勤要付加班費","✓ 符合"))) | 重算 |
| AG7 | 李小華 | 21 | =COUNTIF(B7:AF7,"早")+COUNTIF(B7:AF7,"中")+COUNTIF(B7:AF7,"晚") | 重算 |
| AH7 | 李小華 | 168 | =AG7*8 | 重算 |
| AI7 | 李小華 | 10 | =COUNTIF(B7:AF7,"休") | 重算 |
| AJ7 | 李小華 | ✓ 符合 | =IF(OR(COUNTIF(B7:H7,"休")<1,COUNTIF(I7:O7,"休")<1,COUNTIF(P7:V7,"休")<1,COUNTIF(W7:AC7,"休")<1),"✗ 有一週沒有例假",IF(OR(AND(B7="中",C7="早"),AND(OR(B7="早",B7="中"),C7="晚"),AND(C7="中",D7="早"),AND(OR(C7="早",C7="中"),D7="晚"),AND(D7="中",E7="早"),AND(OR(D7="早",D7="中"),E7="晚"),AND(E7="中",F7="早"),AND(OR(E7="早",E7="中"),F7="晚"),AND(F7="中",G7="早"),AND(OR(F7="早",F7="中"),G7="晚"),AND(G7="中",H7="早"),AND(OR(G7="早",G7="中"),H7="晚"),AND(H7="中",I7="早"),AND(OR(H7="早",H7="中"),I7="晚"),AND(I7="中",J7="早"),AND(OR(I7="早",I7="中"),J7="晚"),AND(J7="中",K7="早"),AND(OR(J7="早",J7="中"),K7="晚"),AND(K7="中",L7="早"),AND(OR(K7="早",K7="中"),L7="晚"),AND(L7="中",M7="早"),AND(OR(L7="早",L7="中"),M7="晚"),AND(M7="中",N7="早"),AND(OR(M7="早",M7="中"),N7="晚"),AND(N7="中",O7="早"),AND(OR(N7="早",N7="中"),O7="晚"),AND(O7="中",P7="早"),AND(OR(O7="早",O7="中"),P7="晚"),AND(P7="中",Q7="早"),AND(OR(P7="早",P7="中"),Q7="晚"),AND(Q7="中",R7="早"),AND(OR(Q7="早",Q7="中"),R7="晚"),AND(R7="中",S7="早"),AND(OR(R7="早",R7="中"),S7="晚"),AND(S7="中",T7="早"),AND(OR(S7="早",S7="中"),T7="晚"),AND(T7="中",U7="早"),AND(OR(T7="早",T7="中"),U7="晚"),AND(U7="中",V7="早"),AND(OR(U7="早",U7="中"),V7="晚"),AND(V7="中",W7="早"),AND(OR(V7="早",V7="中"),W7="晚"),AND(W7="中",X7="早"),AND(OR(W7="早",W7="中"),X7="晚"),AND(X7="中",Y7="早"),AND(OR(X7="早",X7="中"),Y7="晚"),AND(Y7="中",Z7="早"),AND(OR(Y7="早",Y7="中"),Z7="晚"),AND(Z7="中",AA7="早"),AND(OR(Z7="早",Z7="中"),AA7="晚"),AND(AA7="中",AB7="早"),AND(OR(AA7="早",AA7="中"),AB7="晚"),AND(AB7="中",AC7="早"),AND(OR(AB7="早",AB7="中"),AC7="晚"),AND(AC7="中",AD7="早"),AND(OR(AC7="早",AC7="中"),AD7="晚"),AND(AD7="中",AE7="早"),AND(OR(AD7="早",AD7="中"),AE7="晚"),AND(AE7="中",AF7="早"),AND(OR(AE7="早",AE7="中"),AF7="晚")),"✗ 換班間隔不足11小時",IF(OR(COUNTIF(B7:H7,"休")<2,COUNTIF(I7:O7,"休")<2,COUNTIF(P7:V7,"休")<2,COUNTIF(W7:AC7,"休")<2),"⚠ 有一週休不到2天：休息日出勤要付加班費","✓ 符合"))) | 重算 |
| AG8 | 張大偉 | 22 | =COUNTIF(B8:AF8,"早")+COUNTIF(B8:AF8,"中")+COUNTIF(B8:AF8,"晚") | 重算 |
展開其餘 100 格
| 格子 | 這一格是什麼 | 重算結果 | 公式 | 驗法 |
|---|---|---|---|---|
| AH8 | 張大偉 | 176 | =AG8*8 | 重算 |
| AI8 | 張大偉 | 9 | =COUNTIF(B8:AF8,"休") | 重算 |
| AJ8 | 張大偉 | ✓ 符合 | =IF(OR(COUNTIF(B8:H8,"休")<1,COUNTIF(I8:O8,"休")<1,COUNTIF(P8:V8,"休")<1,COUNTIF(W8:AC8,"休")<1),"✗ 有一週沒有例假",IF(OR(AND(B8="中",C8="早"),AND(OR(B8="早",B8="中"),C8="晚"),AND(C8="中",D8="早"),AND(OR(C8="早",C8="中"),D8="晚"),AND(D8="中",E8="早"),AND(OR(D8="早",D8="中"),E8="晚"),AND(E8="中",F8="早"),AND(OR(E8="早",E8="中"),F8="晚"),AND(F8="中",G8="早"),AND(OR(F8="早",F8="中"),G8="晚"),AND(G8="中",H8="早"),AND(OR(G8="早",G8="中"),H8="晚"),AND(H8="中",I8="早"),AND(OR(H8="早",H8="中"),I8="晚"),AND(I8="中",J8="早"),AND(OR(I8="早",I8="中"),J8="晚"),AND(J8="中",K8="早"),AND(OR(J8="早",J8="中"),K8="晚"),AND(K8="中",L8="早"),AND(OR(K8="早",K8="中"),L8="晚"),AND(L8="中",M8="早"),AND(OR(L8="早",L8="中"),M8="晚"),AND(M8="中",N8="早"),AND(OR(M8="早",M8="中"),N8="晚"),AND(N8="中",O8="早"),AND(OR(N8="早",N8="中"),O8="晚"),AND(O8="中",P8="早"),AND(OR(O8="早",O8="中"),P8="晚"),AND(P8="中",Q8="早"),AND(OR(P8="早",P8="中"),Q8="晚"),AND(Q8="中",R8="早"),AND(OR(Q8="早",Q8="中"),R8="晚"),AND(R8="中",S8="早"),AND(OR(R8="早",R8="中"),S8="晚"),AND(S8="中",T8="早"),AND(OR(S8="早",S8="中"),T8="晚"),AND(T8="中",U8="早"),AND(OR(T8="早",T8="中"),U8="晚"),AND(U8="中",V8="早"),AND(OR(U8="早",U8="中"),V8="晚"),AND(V8="中",W8="早"),AND(OR(V8="早",V8="中"),W8="晚"),AND(W8="中",X8="早"),AND(OR(W8="早",W8="中"),X8="晚"),AND(X8="中",Y8="早"),AND(OR(X8="早",X8="中"),Y8="晚"),AND(Y8="中",Z8="早"),AND(OR(Y8="早",Y8="中"),Z8="晚"),AND(Z8="中",AA8="早"),AND(OR(Z8="早",Z8="中"),AA8="晚"),AND(AA8="中",AB8="早"),AND(OR(AA8="早",AA8="中"),AB8="晚"),AND(AB8="中",AC8="早"),AND(OR(AB8="早",AB8="中"),AC8="晚"),AND(AC8="中",AD8="早"),AND(OR(AC8="早",AC8="中"),AD8="晚"),AND(AD8="中",AE8="早"),AND(OR(AD8="早",AD8="中"),AE8="晚"),AND(AE8="中",AF8="早"),AND(OR(AE8="早",AE8="中"),AF8="晚")),"✗ 換班間隔不足11小時",IF(OR(COUNTIF(B8:H8,"休")<2,COUNTIF(I8:O8,"休")<2,COUNTIF(P8:V8,"休")<2,COUNTIF(W8:AC8,"休")<2),"⚠ 有一週休不到2天：休息日出勤要付加班費","✓ 符合"))) | 重算 |
| AG9 | 陳美玲 | 23 | =COUNTIF(B9:AF9,"早")+COUNTIF(B9:AF9,"中")+COUNTIF(B9:AF9,"晚") | 重算 |
| AH9 | 陳美玲 | 184 | =AG9*8 | 重算 |
| AI9 | 陳美玲 | 8 | =COUNTIF(B9:AF9,"休") | 重算 |
| AJ9 | 陳美玲 | ✓ 符合 | =IF(OR(COUNTIF(B9:H9,"休")<1,COUNTIF(I9:O9,"休")<1,COUNTIF(P9:V9,"休")<1,COUNTIF(W9:AC9,"休")<1),"✗ 有一週沒有例假",IF(OR(AND(B9="中",C9="早"),AND(OR(B9="早",B9="中"),C9="晚"),AND(C9="中",D9="早"),AND(OR(C9="早",C9="中"),D9="晚"),AND(D9="中",E9="早"),AND(OR(D9="早",D9="中"),E9="晚"),AND(E9="中",F9="早"),AND(OR(E9="早",E9="中"),F9="晚"),AND(F9="中",G9="早"),AND(OR(F9="早",F9="中"),G9="晚"),AND(G9="中",H9="早"),AND(OR(G9="早",G9="中"),H9="晚"),AND(H9="中",I9="早"),AND(OR(H9="早",H9="中"),I9="晚"),AND(I9="中",J9="早"),AND(OR(I9="早",I9="中"),J9="晚"),AND(J9="中",K9="早"),AND(OR(J9="早",J9="中"),K9="晚"),AND(K9="中",L9="早"),AND(OR(K9="早",K9="中"),L9="晚"),AND(L9="中",M9="早"),AND(OR(L9="早",L9="中"),M9="晚"),AND(M9="中",N9="早"),AND(OR(M9="早",M9="中"),N9="晚"),AND(N9="中",O9="早"),AND(OR(N9="早",N9="中"),O9="晚"),AND(O9="中",P9="早"),AND(OR(O9="早",O9="中"),P9="晚"),AND(P9="中",Q9="早"),AND(OR(P9="早",P9="中"),Q9="晚"),AND(Q9="中",R9="早"),AND(OR(Q9="早",Q9="中"),R9="晚"),AND(R9="中",S9="早"),AND(OR(R9="早",R9="中"),S9="晚"),AND(S9="中",T9="早"),AND(OR(S9="早",S9="中"),T9="晚"),AND(T9="中",U9="早"),AND(OR(T9="早",T9="中"),U9="晚"),AND(U9="中",V9="早"),AND(OR(U9="早",U9="中"),V9="晚"),AND(V9="中",W9="早"),AND(OR(V9="早",V9="中"),W9="晚"),AND(W9="中",X9="早"),AND(OR(W9="早",W9="中"),X9="晚"),AND(X9="中",Y9="早"),AND(OR(X9="早",X9="中"),Y9="晚"),AND(Y9="中",Z9="早"),AND(OR(Y9="早",Y9="中"),Z9="晚"),AND(Z9="中",AA9="早"),AND(OR(Z9="早",Z9="中"),AA9="晚"),AND(AA9="中",AB9="早"),AND(OR(AA9="早",AA9="中"),AB9="晚"),AND(AB9="中",AC9="早"),AND(OR(AB9="早",AB9="中"),AC9="晚"),AND(AC9="中",AD9="早"),AND(OR(AC9="早",AC9="中"),AD9="晚"),AND(AD9="中",AE9="早"),AND(OR(AD9="早",AD9="中"),AE9="晚"),AND(AE9="中",AF9="早"),AND(OR(AE9="早",AE9="中"),AF9="晚")),"✗ 換班間隔不足11小時",IF(OR(COUNTIF(B9:H9,"休")<2,COUNTIF(I9:O9,"休")<2,COUNTIF(P9:V9,"休")<2,COUNTIF(W9:AC9,"休")<2),"⚠ 有一週休不到2天：休息日出勤要付加班費","✓ 符合"))) | 重算 |
| B11 | 早班人數 | 2 | =COUNTIF(B6:B9,"早") | 重算 |
| C11 | 早班人數 | 2 | =COUNTIF(C6:C9,"早") | 重算 |
| D11 | 早班人數 | 2 | =COUNTIF(D6:D9,"早") | 重算 |
| E11 | 早班人數 | 2 | =COUNTIF(E6:E9,"早") | 重算 |
| F11 | 早班人數 | 2 | =COUNTIF(F6:F9,"早") | 重算 |
| G11 | 早班人數 | 0 | =COUNTIF(G6:G9,"早") | 重算 |
| H11 | 早班人數 | 0 | =COUNTIF(H6:H9,"早") | 重算 |
| I11 | 早班人數 | 1 | =COUNTIF(I6:I9,"早") | 重算 |
| J11 | 早班人數 | 1 | =COUNTIF(J6:J9,"早") | 重算 |
| K11 | 早班人數 | 1 | =COUNTIF(K6:K9,"早") | 重算 |
| L11 | 早班人數 | 1 | =COUNTIF(L6:L9,"早") | 重算 |
| M11 | 早班人數 | 1 | =COUNTIF(M6:M9,"早") | 重算 |
| N11 | 早班人數 | 0 | =COUNTIF(N6:N9,"早") | 重算 |
| O11 | 早班人數 | 0 | =COUNTIF(O6:O9,"早") | 重算 |
| P11 | 早班人數 | 1 | =COUNTIF(P6:P9,"早") | 重算 |
| Q11 | 早班人數 | 1 | =COUNTIF(Q6:Q9,"早") | 重算 |
| R11 | 早班人數 | 1 | =COUNTIF(R6:R9,"早") | 重算 |
| S11 | 早班人數 | 1 | =COUNTIF(S6:S9,"早") | 重算 |
| T11 | 早班人數 | 1 | =COUNTIF(T6:T9,"早") | 重算 |
| U11 | 早班人數 | 0 | =COUNTIF(U6:U9,"早") | 重算 |
| V11 | 早班人數 | 0 | =COUNTIF(V6:V9,"早") | 重算 |
| W11 | 早班人數 | 2 | =COUNTIF(W6:W9,"早") | 重算 |
| X11 | 早班人數 | 2 | =COUNTIF(X6:X9,"早") | 重算 |
| Y11 | 早班人數 | 2 | =COUNTIF(Y6:Y9,"早") | 重算 |
| Z11 | 早班人數 | 2 | =COUNTIF(Z6:Z9,"早") | 重算 |
| AA11 | 早班人數 | 2 | =COUNTIF(AA6:AA9,"早") | 重算 |
| AB11 | 早班人數 | 0 | =COUNTIF(AB6:AB9,"早") | 重算 |
| AC11 | 早班人數 | 0 | =COUNTIF(AC6:AC9,"早") | 重算 |
| AD11 | 早班人數 | 1 | =COUNTIF(AD6:AD9,"早") | 重算 |
| AE11 | 早班人數 | 1 | =COUNTIF(AE6:AE9,"早") | 重算 |
| AF11 | 早班人數 | 1 | =COUNTIF(AF6:AF9,"早") | 重算 |
| B12 | 中班人數 | 0 | =COUNTIF(B6:B9,"中") | 重算 |
| C12 | 中班人數 | 0 | =COUNTIF(C6:C9,"中") | 重算 |
| D12 | 中班人數 | 1 | =COUNTIF(D6:D9,"中") | 重算 |
| E12 | 中班人數 | 1 | =COUNTIF(E6:E9,"中") | 重算 |
| F12 | 中班人數 | 1 | =COUNTIF(F6:F9,"中") | 重算 |
| G12 | 中班人數 | 1 | =COUNTIF(G6:G9,"中") | 重算 |
| H12 | 中班人數 | 1 | =COUNTIF(H6:H9,"中") | 重算 |
| I12 | 中班人數 | 1 | =COUNTIF(I6:I9,"中") | 重算 |
| J12 | 中班人數 | 1 | =COUNTIF(J6:J9,"中") | 重算 |
| K12 | 中班人數 | 2 | =COUNTIF(K6:K9,"中") | 重算 |
| L12 | 中班人數 | 2 | =COUNTIF(L6:L9,"中") | 重算 |
| M12 | 中班人數 | 2 | =COUNTIF(M6:M9,"中") | 重算 |
| N12 | 中班人數 | 1 | =COUNTIF(N6:N9,"中") | 重算 |
| O12 | 中班人數 | 1 | =COUNTIF(O6:O9,"中") | 重算 |
| P12 | 中班人數 | 0 | =COUNTIF(P6:P9,"中") | 重算 |
| Q12 | 中班人數 | 0 | =COUNTIF(Q6:Q9,"中") | 重算 |
| R12 | 中班人數 | 1 | =COUNTIF(R6:R9,"中") | 重算 |
| S12 | 中班人數 | 1 | =COUNTIF(S6:S9,"中") | 重算 |
| T12 | 中班人數 | 1 | =COUNTIF(T6:T9,"中") | 重算 |
| U12 | 中班人數 | 1 | =COUNTIF(U6:U9,"中") | 重算 |
| V12 | 中班人數 | 1 | =COUNTIF(V6:V9,"中") | 重算 |
| W12 | 中班人數 | 0 | =COUNTIF(W6:W9,"中") | 重算 |
| X12 | 中班人數 | 0 | =COUNTIF(X6:X9,"中") | 重算 |
| Y12 | 中班人數 | 1 | =COUNTIF(Y6:Y9,"中") | 重算 |
| Z12 | 中班人數 | 1 | =COUNTIF(Z6:Z9,"中") | 重算 |
| AA12 | 中班人數 | 1 | =COUNTIF(AA6:AA9,"中") | 重算 |
| AB12 | 中班人數 | 1 | =COUNTIF(AB6:AB9,"中") | 重算 |
| AC12 | 中班人數 | 1 | =COUNTIF(AC6:AC9,"中") | 重算 |
| AD12 | 中班人數 | 1 | =COUNTIF(AD6:AD9,"中") | 重算 |
| AE12 | 中班人數 | 1 | =COUNTIF(AE6:AE9,"中") | 重算 |
| AF12 | 中班人數 | 2 | =COUNTIF(AF6:AF9,"中") | 重算 |
| B13 | 晚班人數 | 1 | =COUNTIF(B6:B9,"晚") | 重算 |
| C13 | 晚班人數 | 1 | =COUNTIF(C6:C9,"晚") | 重算 |
| D13 | 晚班人數 | 0 | =COUNTIF(D6:D9,"晚") | 重算 |
| E13 | 晚班人數 | 0 | =COUNTIF(E6:E9,"晚") | 重算 |
| F13 | 晚班人數 | 1 | =COUNTIF(F6:F9,"晚") | 重算 |
| G13 | 晚班人數 | 1 | =COUNTIF(G6:G9,"晚") | 重算 |
| H13 | 晚班人數 | 1 | =COUNTIF(H6:H9,"晚") | 重算 |
| I13 | 晚班人數 | 1 | =COUNTIF(I6:I9,"晚") | 重算 |
| J13 | 晚班人數 | 1 | =COUNTIF(J6:J9,"晚") | 重算 |
| K13 | 晚班人數 | 0 | =COUNTIF(K6:K9,"晚") | 重算 |
| L13 | 晚班人數 | 0 | =COUNTIF(L6:L9,"晚") | 重算 |
| M13 | 晚班人數 | 1 | =COUNTIF(M6:M9,"晚") | 重算 |
| N13 | 晚班人數 | 1 | =COUNTIF(N6:N9,"晚") | 重算 |
| O13 | 晚班人數 | 1 | =COUNTIF(O6:O9,"晚") | 重算 |
| P13 | 晚班人數 | 2 | =COUNTIF(P6:P9,"晚") | 重算 |
| Q13 | 晚班人數 | 2 | =COUNTIF(Q6:Q9,"晚") | 重算 |
| R13 | 晚班人數 | 1 | =COUNTIF(R6:R9,"晚") | 重算 |
| S13 | 晚班人數 | 1 | =COUNTIF(S6:S9,"晚") | 重算 |
| T13 | 晚班人數 | 2 | =COUNTIF(T6:T9,"晚") | 重算 |
| U13 | 晚班人數 | 1 | =COUNTIF(U6:U9,"晚") | 重算 |
| V13 | 晚班人數 | 1 | =COUNTIF(V6:V9,"晚") | 重算 |
| W13 | 晚班人數 | 1 | =COUNTIF(W6:W9,"晚") | 重算 |
| X13 | 晚班人數 | 1 | =COUNTIF(X6:X9,"晚") | 重算 |
| Y13 | 晚班人數 | 0 | =COUNTIF(Y6:Y9,"晚") | 重算 |
| Z13 | 晚班人數 | 0 | =COUNTIF(Z6:Z9,"晚") | 重算 |
| AA13 | 晚班人數 | 1 | =COUNTIF(AA6:AA9,"晚") | 重算 |
| AB13 | 晚班人數 | 1 | =COUNTIF(AB6:AB9,"晚") | 重算 |
| AC13 | 晚班人數 | 1 | =COUNTIF(AC6:AC9,"晚") | 重算 |
| AD13 | 晚班人數 | 1 | =COUNTIF(AD6:AD9,"晚") | 重算 |
| AE13 | 晚班人數 | 1 | =COUNTIF(AE6:AE9,"晚") | 重算 |
| AF13 | 晚班人數 | 0 | =COUNTIF(AF6:AF9,"晚") | 重算 |
內容依據
以下規定於 2026-10-04 對照官方原文查證；法規與申請須知可能修正，使用前請以最新公告為準。
- 勞動基準法（修正日期 113-07-31）
第 24 條休息日加班費；第 30 條正常工時、二週與八週變形、出勤紀錄保存 5 年並逐日記載至分鐘；第 30-1 條四週變形；第 32 條加班上限；第 32-1 條補休；第 34 條輪班更換與連續休息時間；第 35 條休息；第 36 條例假與休息日；第 37、39 條國定假日與工資加倍；第 40 條天災事變停止假期。
- 勞動基準法施行細則（修正日期 113-03-27）
第 21 條出勤紀錄的種類，以及因勞動檢查或勞工申請時應以書面提出。
適用範圍與限制
- 驗算用的是模板的範例數字。你改了輸入值之後，公式相同、數字會不同；插入或刪除列之後，請確認加總的範圍有包含到新的一列。
- 驗算證明的是「公式照表上寫的算法算對了」，不代表這個算法適合每一種情況；算法的假設與相關規定，見模板頁下方的說明。
每一欄怎麼填、算法背後的規定與常見錯誤，寫在月排班表模板頁下方的說明。
驗算報告說明的是範本公式與內容是否符合所列法規與官方費率，實際權益仍以勞動契約、主管機關認定與個案事實為準。