甘特圖專案追蹤表驗算報告
甘特圖專案追蹤表的 139 格公式用另一套試算引擎逐格重算。
✅ 已驗算 2026年10月4日・139 格公式
找到並已修正的問題
這一款在這次逐格重算裡沒有發現需要修正的公式。
怎麼驗的
- 把模板交給 LibreOffice（跟本站線上編輯器不同的另一套試算引擎）真的重算一次，不是拿同一套公式自己算自己。
- 這張表共有 139 格公式，全部重算，沒有任何一格出現錯誤值（#DIV/0!、#NAME?、#VALUE! 這類）。
- 這張表的日期、狀態、落後天數與甘特條全部跟著「今天」的日期變動，每天的正確答案都不同；這一款只確認每一格都算得出值，沒有逐格獨立比對。
- 這張表有些格子用到今天的日期，下表的重算結果是驗算當天的值，之後每天會不同。
驗算用的輸入值
重算與獨立比對用的都是模板自己的範例數字，共 7 格。你把這些格子換成自己的數字，公式不變、結果會跟著變。
| 格子 | 這一格是什麼 | 範例值 |
|---|---|---|
| G5 | 王小明 | 1 |
| G6 | 李小華 | 1 |
| G7 | 張大偉 | 0.6 |
| G8 | 陳美玲 | 0.45 |
| G9 | 林志豪 | 0 |
| G10 | 王小明 | 0 |
| G11 | 全體 | 0 |
公式逐格重算結果
2026年10月4日 重算・公式 139 格・含今天日期的格子每天不同。「重算結果」是模板範例數字算出來的值。
| 格子 | 這一格是什麼 | 重算結果 | 公式 | 驗法 |
|---|---|---|---|---|
| E2 | 開始日期： | 2026/08/24 | =TODAY()-WEEKDAY(TODAY(),3)-35 | 重算 |
| H2 | 今日日期： | 2026/10/04 | =TODAY() | 重算 |
| K4 | 任務編號 | 2026/08/24 | =$E$2 | 重算 |
| L4 | 任務編號 | 2026/08/31 | =K4+7 | 重算 |
| M4 | 任務編號 | 2026/09/07 | =L4+7 | 重算 |
| N4 | 任務編號 | 2026/09/14 | =M4+7 | 重算 |
| O4 | 任務編號 | 2026/09/21 | =N4+7 | 重算 |
| P4 | 任務編號 | 2026/09/28 | =O4+7 | 重算 |
| Q4 | 任務編號 | 2026/10/05 | =P4+7 | 重算 |
| R4 | 任務編號 | 2026/10/12 | =Q4+7 | 重算 |
| S4 | 任務編號 | 2026/10/19 | =R4+7 | 重算 |
| T4 | 任務編號 | 2026/10/26 | =S4+7 | 重算 |
| U4 | 任務編號 | 2026/11/02 | =T4+7 | 重算 |
| V4 | 任務編號 | 2026/11/09 | =U4+7 | 重算 |
| D5 | 王小明 | 2026/08/24 | =$E$2+0 | 重算 |
| E5 | 王小明 | 2026/08/28 | =D5+4 | 重算 |
| F5 | 王小明 | 5 | =E5-D5+1 | 重算 |
| H5 | 王小明 | 已完成 | =IF(G5>=1,"已完成",IF(G5>0,"進行中","未開始")) | 重算 |
| J5 | - | 0 | =IF(G5>=1,0,MAX(0,TODAY()-E5)) | 重算 |
| K5 | - | █ | =IF(AND($D5<=K$4+6,$E5>=K$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| L5 | - | 0 | =IF(AND($D5<=L$4+6,$E5>=L$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| M5 | - | 0 | =IF(AND($D5<=M$4+6,$E5>=M$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| N5 | - | 0 | =IF(AND($D5<=N$4+6,$E5>=N$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| O5 | - | 0 | =IF(AND($D5<=O$4+6,$E5>=O$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| P5 | - | 0 | =IF(AND($D5<=P$4+6,$E5>=P$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| Q5 | - | 0 | =IF(AND($D5<=Q$4+6,$E5>=Q$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| R5 | - | 0 | =IF(AND($D5<=R$4+6,$E5>=R$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| S5 | - | 0 | =IF(AND($D5<=S$4+6,$E5>=S$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| T5 | - | 0 | =IF(AND($D5<=T$4+6,$E5>=T$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| U5 | - | 0 | =IF(AND($D5<=U$4+6,$E5>=U$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| V5 | - | 0 | =IF(AND($D5<=V$4+6,$E5>=V$4),IF($G5>=1,"█",IF($G5>0,"▓","░")),"") | 重算 |
| D6 | 李小華 | 2026/08/31 | =$E$2+7 | 重算 |
| E6 | 李小華 | 2026/09/04 | =D6+4 | 重算 |
| F6 | 李小華 | 5 | =E6-D6+1 | 重算 |
| H6 | 李小華 | 已完成 | =IF(G6>=1,"已完成",IF(G6>0,"進行中","未開始")) | 重算 |
| J6 | T-001 | 0 | =IF(G6>=1,0,MAX(0,TODAY()-E6)) | 重算 |
| K6 | T-001 | 0 | =IF(AND($D6<=K$4+6,$E6>=K$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| L6 | T-001 | █ | =IF(AND($D6<=L$4+6,$E6>=L$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| M6 | T-001 | 0 | =IF(AND($D6<=M$4+6,$E6>=M$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| N6 | T-001 | 0 | =IF(AND($D6<=N$4+6,$E6>=N$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
展開其餘 99 格
| 格子 | 這一格是什麼 | 重算結果 | 公式 | 驗法 |
|---|---|---|---|---|
| O6 | T-001 | 0 | =IF(AND($D6<=O$4+6,$E6>=O$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| P6 | T-001 | 0 | =IF(AND($D6<=P$4+6,$E6>=P$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| Q6 | T-001 | 0 | =IF(AND($D6<=Q$4+6,$E6>=Q$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| R6 | T-001 | 0 | =IF(AND($D6<=R$4+6,$E6>=R$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| S6 | T-001 | 0 | =IF(AND($D6<=S$4+6,$E6>=S$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| T6 | T-001 | 0 | =IF(AND($D6<=T$4+6,$E6>=T$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| U6 | T-001 | 0 | =IF(AND($D6<=U$4+6,$E6>=U$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| V6 | T-001 | 0 | =IF(AND($D6<=V$4+6,$E6>=V$4),IF($G6>=1,"█",IF($G6>0,"▓","░")),"") | 重算 |
| D7 | 張大偉 | 2026/09/07 | =$E$2+14 | 重算 |
| E7 | 張大偉 | 2026/09/25 | =D7+18 | 重算 |
| F7 | 張大偉 | 19 | =E7-D7+1 | 重算 |
| H7 | 張大偉 | 進行中 | =IF(G7>=1,"已完成",IF(G7>0,"進行中","未開始")) | 重算 |
| J7 | T-002 | 9 | =IF(G7>=1,0,MAX(0,TODAY()-E7)) | 重算 |
| K7 | T-002 | 0 | =IF(AND($D7<=K$4+6,$E7>=K$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| L7 | T-002 | 0 | =IF(AND($D7<=L$4+6,$E7>=L$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| M7 | T-002 | ▓ | =IF(AND($D7<=M$4+6,$E7>=M$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| N7 | T-002 | ▓ | =IF(AND($D7<=N$4+6,$E7>=N$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| O7 | T-002 | ▓ | =IF(AND($D7<=O$4+6,$E7>=O$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| P7 | T-002 | 0 | =IF(AND($D7<=P$4+6,$E7>=P$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| Q7 | T-002 | 0 | =IF(AND($D7<=Q$4+6,$E7>=Q$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| R7 | T-002 | 0 | =IF(AND($D7<=R$4+6,$E7>=R$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| S7 | T-002 | 0 | =IF(AND($D7<=S$4+6,$E7>=S$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| T7 | T-002 | 0 | =IF(AND($D7<=T$4+6,$E7>=T$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| U7 | T-002 | 0 | =IF(AND($D7<=U$4+6,$E7>=U$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| V7 | T-002 | 0 | =IF(AND($D7<=V$4+6,$E7>=V$4),IF($G7>=1,"█",IF($G7>0,"▓","░")),"") | 重算 |
| D8 | 陳美玲 | 2026/09/07 | =$E$2+14 | 重算 |
| E8 | 陳美玲 | 2026/10/02 | =D8+25 | 重算 |
| F8 | 陳美玲 | 26 | =E8-D8+1 | 重算 |
| H8 | 陳美玲 | 進行中 | =IF(G8>=1,"已完成",IF(G8>0,"進行中","未開始")) | 重算 |
| J8 | T-002 | 2 | =IF(G8>=1,0,MAX(0,TODAY()-E8)) | 重算 |
| K8 | T-002 | 0 | =IF(AND($D8<=K$4+6,$E8>=K$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| L8 | T-002 | 0 | =IF(AND($D8<=L$4+6,$E8>=L$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| M8 | T-002 | ▓ | =IF(AND($D8<=M$4+6,$E8>=M$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| N8 | T-002 | ▓ | =IF(AND($D8<=N$4+6,$E8>=N$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| O8 | T-002 | ▓ | =IF(AND($D8<=O$4+6,$E8>=O$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| P8 | T-002 | ▓ | =IF(AND($D8<=P$4+6,$E8>=P$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| Q8 | T-002 | 0 | =IF(AND($D8<=Q$4+6,$E8>=Q$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| R8 | T-002 | 0 | =IF(AND($D8<=R$4+6,$E8>=R$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| S8 | T-002 | 0 | =IF(AND($D8<=S$4+6,$E8>=S$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| T8 | T-002 | 0 | =IF(AND($D8<=T$4+6,$E8>=T$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| U8 | T-002 | 0 | =IF(AND($D8<=U$4+6,$E8>=U$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| V8 | T-002 | 0 | =IF(AND($D8<=V$4+6,$E8>=V$4),IF($G8>=1,"█",IF($G8>0,"▓","░")),"") | 重算 |
| D9 | 林志豪 | 2026/10/05 | =$E$2+42 | 重算 |
| E9 | 林志豪 | 2026/10/09 | =D9+4 | 重算 |
| F9 | 林志豪 | 5 | =E9-D9+1 | 重算 |
| H9 | 林志豪 | 未開始 | =IF(G9>=1,"已完成",IF(G9>0,"進行中","未開始")) | 重算 |
| J9 | T-003,T-004 | 0 | =IF(G9>=1,0,MAX(0,TODAY()-E9)) | 重算 |
| K9 | T-003,T-004 | 0 | =IF(AND($D9<=K$4+6,$E9>=K$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| L9 | T-003,T-004 | 0 | =IF(AND($D9<=L$4+6,$E9>=L$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| M9 | T-003,T-004 | 0 | =IF(AND($D9<=M$4+6,$E9>=M$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| N9 | T-003,T-004 | 0 | =IF(AND($D9<=N$4+6,$E9>=N$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| O9 | T-003,T-004 | 0 | =IF(AND($D9<=O$4+6,$E9>=O$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| P9 | T-003,T-004 | 0 | =IF(AND($D9<=P$4+6,$E9>=P$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| Q9 | T-003,T-004 | ░ | =IF(AND($D9<=Q$4+6,$E9>=Q$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| R9 | T-003,T-004 | 0 | =IF(AND($D9<=R$4+6,$E9>=R$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| S9 | T-003,T-004 | 0 | =IF(AND($D9<=S$4+6,$E9>=S$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| T9 | T-003,T-004 | 0 | =IF(AND($D9<=T$4+6,$E9>=T$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| U9 | T-003,T-004 | 0 | =IF(AND($D9<=U$4+6,$E9>=U$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| V9 | T-003,T-004 | 0 | =IF(AND($D9<=V$4+6,$E9>=V$4),IF($G9>=1,"█",IF($G9>0,"▓","░")),"") | 重算 |
| D10 | 王小明 | 2026/10/12 | =$E$2+49 | 重算 |
| E10 | 王小明 | 2026/10/16 | =D10+4 | 重算 |
| F10 | 王小明 | 5 | =E10-D10+1 | 重算 |
| H10 | 王小明 | 未開始 | =IF(G10>=1,"已完成",IF(G10>0,"進行中","未開始")) | 重算 |
| J10 | T-005 | 0 | =IF(G10>=1,0,MAX(0,TODAY()-E10)) | 重算 |
| K10 | T-005 | 0 | =IF(AND($D10<=K$4+6,$E10>=K$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| L10 | T-005 | 0 | =IF(AND($D10<=L$4+6,$E10>=L$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| M10 | T-005 | 0 | =IF(AND($D10<=M$4+6,$E10>=M$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| N10 | T-005 | 0 | =IF(AND($D10<=N$4+6,$E10>=N$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| O10 | T-005 | 0 | =IF(AND($D10<=O$4+6,$E10>=O$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| P10 | T-005 | 0 | =IF(AND($D10<=P$4+6,$E10>=P$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| Q10 | T-005 | 0 | =IF(AND($D10<=Q$4+6,$E10>=Q$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| R10 | T-005 | ░ | =IF(AND($D10<=R$4+6,$E10>=R$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| S10 | T-005 | 0 | =IF(AND($D10<=S$4+6,$E10>=S$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| T10 | T-005 | 0 | =IF(AND($D10<=T$4+6,$E10>=T$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| U10 | T-005 | 0 | =IF(AND($D10<=U$4+6,$E10>=U$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| V10 | T-005 | 0 | =IF(AND($D10<=V$4+6,$E10>=V$4),IF($G10>=1,"█",IF($G10>0,"▓","░")),"") | 重算 |
| D11 | 全體 | 2026/10/19 | =$E$2+56 | 重算 |
| E11 | 全體 | 2026/10/19 | =D11+0 | 重算 |
| F11 | 全體 | 1 | =E11-D11+1 | 重算 |
| H11 | 全體 | 未開始 | =IF(G11>=1,"已完成",IF(G11>0,"進行中","未開始")) | 重算 |
| J11 | T-006 | 0 | =IF(G11>=1,0,MAX(0,TODAY()-E11)) | 重算 |
| K11 | T-006 | 0 | =IF(AND($D11<=K$4+6,$E11>=K$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| L11 | T-006 | 0 | =IF(AND($D11<=L$4+6,$E11>=L$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| M11 | T-006 | 0 | =IF(AND($D11<=M$4+6,$E11>=M$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| N11 | T-006 | 0 | =IF(AND($D11<=N$4+6,$E11>=N$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| O11 | T-006 | 0 | =IF(AND($D11<=O$4+6,$E11>=O$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| P11 | T-006 | 0 | =IF(AND($D11<=P$4+6,$E11>=P$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| Q11 | T-006 | 0 | =IF(AND($D11<=Q$4+6,$E11>=Q$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| R11 | T-006 | 0 | =IF(AND($D11<=R$4+6,$E11>=R$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| S11 | T-006 | ░ | =IF(AND($D11<=S$4+6,$E11>=S$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| T11 | T-006 | 0 | =IF(AND($D11<=T$4+6,$E11>=T$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| U11 | T-006 | 0 | =IF(AND($D11<=U$4+6,$E11>=U$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| V11 | T-006 | 0 | =IF(AND($D11<=V$4+6,$E11>=V$4),IF($G11>=1,"█",IF($G11>0,"▓","░")),"") | 重算 |
| B14 | 總任務數 | 7 | =COUNTA(A5:A11) | 重算 |
| B15 | 已完成 | 2 | =COUNTIF(H5:H11,"已完成") | 重算 |
| B16 | 進行中 | 2 | =COUNTIF(H5:H11,"進行中") | 重算 |
| B17 | 未開始 | 3 | =COUNTIF(H5:H11,"未開始") | 重算 |
| B18 | 整體進度 | 0.4357 | =AVERAGE(G5:G11) | 重算 |
| B19 | 總工期(天) | 57 | =MAX(E5:E11)-MIN(D5:D11)+1 | 重算 |
適用範圍與限制
- 驗算用的是模板的範例數字。你改了輸入值之後，公式相同、數字會不同；插入或刪除列之後，請確認加總的範圍有包含到新的一列。
- 驗算證明的是「公式照表上寫的算法算對了」，不代表這個算法適合每一種情況；算法的假設與相關規定，見模板頁下方的說明。
每一欄怎麼填、算法背後的規定與常見錯誤，寫在甘特圖專案追蹤表模板頁下方的說明。
驗算報告說明的是範本公式與內容是否符合所列法規與官方費率，實際權益仍以勞動契約、主管機關認定與個案事實為準。