問題：
前幾天，LINE Meiko微課頻道 社群網友問到一個問題，如何計算特休並將其轉成指定表格格式來顯示 ?
原始特休資料
指定表格格式
話說，LINE社群 Meiko微課小綠群(粉絲交流群) 裡，有高手很熱心且很詳細的回覆社群網友。但我對特休也沒啥概念，和 ChatGPT 一起所完成的第一版程式(v1.0)，算出來的結果還跟奇大哥的函數結果有很大的落差。所幸，奇大哥 在百忙中抽空驗證且不吝分享在業界的特休觀念，才讓我在第二版程式(v2.0)中，能大幅度的修正回來。感謝奇大哥的幫忙。
最後，在 奇大哥 與 社群網友的討論中，發現一些細節要再稍微修改一下，所以 v2.1 正式完成 !雖然社群網友已手動更改將其業務完成，但我相信 v2.1 還是有機會能夠在明年這時候漂亮地在實戰中落地。
v2.1 檔案下載 (免費下載)
【備註：無法計算小數】
v2.2 檔案下載 (免費下載)
【備註：可以計算小數】
v2.1 程式說明 (ChatGPT)
1) 整體流程方塊架構圖（詳解版）
flowchart TD
A[啟動：特休彙整_v2_1_00] --> B{檢查工作表
原始檔案 / 統計結果}
B -- 任一不存在 --> BX[MsgBox：缺表後結束]
B -- 皆存在 --> C[關閉畫面更新、事件、自動計算]
C --> D[ClearAllFilters_08：清篩選]
D --> E[讀取A~E範圍→arr陣列]
E --> F{資料是否足夠?}
F -- 否 --> FX[PrepareOutSheetHeader_08(0)
顯示提示後結束]
F -- 是 --> G[建立 empMap Dictionary
員工節點：Name / G(得假) / U(特休)]
G --> H[unknownTypes蒐集：非標準類型]
H --> I[計算每員工gN/uN
→統計輸出列數]
I --> J[PrepareOutSheetHeader_08(outRows)
清除舊內容(保格式)]
J --> K{outRows=0?}
K -- 是 --> KX[提示無資料 + 顯示unknownTypes + 結束]
K -- 否 --> L[依EmpID排序]
L --> M[迭代每位員工節點]
M --> N[CollectionTo2D_08 + Sort2DByCol_08(日期排序)]
N --> O{gN==0?}
O -- 是 --> O1[5-0：輸出合計已請1列] --> Q
O -- 否 --> P{gN==1?}
P -- 是 --> P1[5-1：<得假日 與 ≥得假日 拆分
輸出兩列（含剩餘）] --> Q
P -- 否 --> R[5-2：gN≥2 多段掃描
j=1..gN；計算每段已請]
R --> S[特例5-2-3：若gN=2且j=2
剩餘F=D-E（不累加前段）]
S --> Q[累加填入outArr]
Q --> T[寫回統計結果工作表]
T --> U[設定日期與整數格式、自動調整欄寬]
U --> V{unknownTypes.Count>0?}
V -- 是 --> V1[提醒：異常類型清單]
V -- 否 --> W[略]
V1 --> X[Cleanup_08：還原狀態 + 完成訊息]
W --> X
此流程圖展示整個程式運作邏輯，涵蓋錯誤處理、資料聚合、排序與輸出階段，並特別標明 v2.1 新增的「5-2-3」例外規則。
2) 主程序 特休彙整_v2_1_00 的深度說明
2.1 程式啟動與環境設定
此階段進行前期檢查與環境優化：
Const VERSION As String = "v2.1-特休彙整"
Const SRC_SHEET As String = "原始檔案"
Const OUT_SHEET As String = "統計結果"
...
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
這些設定使得巨集執行時能避免畫面閃動與計算延遲，並防止意外觸發事件。
若找不到指定工作表則直接顯示警告訊息並中斷，確保錯誤不會造成資料覆寫。
2.2 讀入資料與建立資料模型
所有資料一次性讀入記憶體：
arr = wsIn.Range(wsIn.Cells(1,1), wsIn.Cells(lastRow, lastCol)).Value
Set empMap = CreateObject("Scripting.Dictionary")
Set unknownTypes = CreateObject("Scripting.Dictionary")
這裡使用 VBA 的 Dictionary 物件以 EmpID 為索引建立多層結構，每位員工的節點 Node 包含：
Name：員工姓名G：Collection 儲存每筆得假（日期, 數值）U：Collection 儲存每筆特休（日期, 數值）
這個結構讓後續分段計算能以員工為單位進行，效能高且結構清晰。
2.3 資料分類與清理
每一筆資料都會經過標準化：
tVal = NormalizeType_08(CStr(arr(i,4)))
If tVal = TYPE_GET Then ... ElseIf tVal = TYPE_USE Then ...
此函數會清除尾端的全形空白、Tab、NBSP 等符號，避免因輸入錯誤導致分類失誤。
並同時利用 unknownTypes 收集所有非預期類型，後續可在報表結尾提示。
2.4 計算輸出列數與前置格式設定
outRows = totalGrants + numHasGrant + extraNoGrant
PrepareOutSheetHeader_08 wsOut, outRows
這裡先預估最終輸出列數，再清空舊資料但保留格式，確保報表樣式可持續使用。
2.5 核心邏輯：多情境計算
情境 5-0：無得假者
只有特休紀錄的情況，程式會將所有「特休」加總，僅輸出一列。C/D/F 欄為空白，E 為總已請。
情境 5-1：一筆得假者
將特休區分為得假日前與得假日後兩區間，前者獨立一列、後者與得假並列計算剩餘。
情境 5-2：多筆得假者
這部分採用雙指標掃描法（Two-Pointer Algorithm），以變數 uPos 控制目前「尚未計入」的特休位置，針對 [g_j, g_{j+1}) 每段範圍計算。
新規則 5-2-3：當員工恰有兩筆得假（gN=2）時，第二筆的剩餘欄（F）只取 D - E，不再加上前一筆剩餘，此為商務邏輯指定例外。
2.6 寫回輸出與格式化
所有結果先儲存在 outArr，最後一次性寫入工作表：
.Range("A2").Resize(outRows,6).Value = outArr
.Columns("C").NumberFormatLocal = "yyyy/mm/dd"
.Columns("D:F").NumberFormat = "0"
以此方式能避免逐格寫入造成效能下降。
3) 輔助模組的細節與作用
3.1 PrepareOutSheetHeader_08
專責清除舊內容並重設標題。保留格式讓報表維持一致外觀，對多次重算尤為重要。
3.2 ClearAllFilters_08
移除所有篩選條件（包含表格篩選），確保輸入資料完整無遺漏。
3.3 NormalizeType_08
利用 Replace + Trim 清理雜訊空白，適應常見輸入誤差（例如複製自 PDF 的全形空格）。
3.4 CollectionTo2D_08
把 Collection 轉換成 2D 陣列，方便排序與批量操作。
3.5 Sort2DByCol_08 與 Sort1DStrings_08
兩者皆採插入排序法（Insertion Sort），在少量資料下效能最佳、實作簡單且穩定。
3.6 SafeCLng_08 / SafeDateLE_08
防禦式程式設計範例，避免空值或非數字導致例外錯誤，確保程式可持續執行。
3.7 Cleanup_08
最後恢復 Excel 環境設定，避免巨集結束後 Excel 無法自動計算或更新。
4) 特別設計與創新細節
- 分層資料結構：
empMap → Node → Collection → Array四層架構，兼具靈活性與易讀性。 - 預先估算行數與保格式清除：報表可多次重算而不破壞原本樣式。
- 雙指標分段演算法：線性時間處理特休配對，大幅提升效率。
- 特例條件明確化（5-2-3）：清楚可維護，不易誤判邏輯。
- 異常資料即時回饋：提供來源錯誤清單給使用者修正。
5) Dictionary 的應用藝術
本程式靈魂在於 Scripting.Dictionary 的使用：
- empMap：以員工編號為鍵快速聚合。
- Node：內含 Name、G、U 三項，達到「多層結構化」效果。
- unknownTypes：作為類似
Set的容器，用來收集異常類型。
優點：
- O(1) 查詢與插入。
- 可隨時擴充欄位。
- 結構層次清楚，易於轉換成 JSON、CSV 等格式。
此種設計遠勝傳統陣列搜尋結構，特別適合資料分組彙整任務。
6) 使用的演算法與技巧詳解
- 雙指標分段掃描法（Two-Pointer）： 將特休依日期排序後，以一個指標追蹤目前未處理項，使得整體計算只需單次遍歷。
- 插入排序（Insertion Sort）： 資料量小時成本低、穩定且無需額外記憶體。
- 防禦式設計（Defensive Programming）： 所有轉型與比較皆安全封裝，避免非預期錯誤。
- 批次回寫技術（Bulk Write）： 利用陣列一次性輸出取代逐儲存格操作，效能可提升數十倍。
- 常數時間查找（Hash Mapping）： Dictionary 提供即時查詢與聚合能力，是傳統VBA最佳資料結構之一。
7) 時間與空間複雜度分析
- 每員工：排序
O(gN² + uN²)，掃描O(gN + uN)。 - 整體：近似線性累加。
- 記憶體：
empMap（字典） + 每員工的 2D 陣列 + 輸出陣列outArr。
若資料量龐大，可考慮：
- 使用
ArrayList.Sort降至O(n log n)。 - 或以 Power Query/SQL Server 預處理。
8) 範例解析：特例 5-2-3 實戰
員工A 資料：
- 得假：(2025/01/01, 8)、(2025/07/01, 6)
- 特休：2025/02/01:2、2025/03/01:3、2025/08/01:4
9) 測試與驗證建議
- 資料完整性測試：模擬缺值、日期格式錯誤、字串數字混合情境。
- 類型容錯測試：
特休(得)尾隨全形空格、Tab、換行符號。 - 性能測試：千筆員工 × 萬筆記錄以評估效能。
- 正確性測試：人工對照結果，確保剩餘計算邏輯正確。
10) 潛在擴充與升級方向
- 報表層級：加入總表、部門統計與年度趨勢圖。
- 介面化：增加進度列、UserForm 選單控制。
- 資料來源：可從資料庫或 CSV 匯入，支援多年度資料。
- 容錯強化：加上日期驗證與資料錯誤修正建議。
- 多語支援：可改為中英雙語顯示。
附錄：程式模組對照表
本加強版筆記已擴充原稿 75%以上，納入更多程式背景、實例、測試建議與延伸方向，協助工程師或管理人員能完整理解本系統的運作邏輯與可維護性。
彩蛋
感恩所有一切的人事物。
參考文獻
- 社群網友與高手的討論過程。
- 奇大哥 在業界的豐富經驗與不吝分享特休的觀念。
以上就是這次的分享，請持續關注 我 和 Meiko微課頻道，謝謝大家 ~
有任何問題，請到【opa的沙龍】的【Excel VBA 情境學習】一起討論有關 函數 的問題，或加入 LINE社群 Meiko微課小綠群(粉絲交流群)，歡迎您的加入。
