Excel 資料驗證實戰：身分證長度、編號前綴、數值區間、關鍵欄位非空、日期順序、重複值標紅

善用「資料 → 資料驗證」的「自訂」公式，能在輸入瞬間攔截錯誤，避免事後排查。以下六大實戰場景可直接套用。

1. 身分證字號長度驗證（15 或 18 碼）

假設 B 欄為身分證，B2 起始：
選取 B 欄 → 資料驗證 → 允許「自訂」 → 公式：
=AND(COUNTIF(B:B,B2)=1, OR(LEN(B2)=15, LEN(B2)=18))
• COUNTIF 防重複；LEN 限長度。

固定長度（如 8 碼）只需改為 =AND(COUNTIF(B:B,B2)=1, LEN(B2)=8)。

2. 編號前綴白名單（如 012/017/019 開頭）

F1 起始：
公式：=OR(LEFT(F1,3)="012", LEFT(F1,3)="017", LEFT(F1,3)="019")
前綴擴充只需在 OR 內增添 LEFT(F1,3)="xxx"。

3. 數值區間 + 位數限制（如 1~100 之整數）

D2 起始：
公式：=AND(D2<=100, OR(LEN(D2)=1, LEN(D2)=2, LEN(D2)=3))
同時檢查上限與 1~3 位數。

4. 關鍵欄位非空才允許輸入後續欄位

B 欄為工號、C 欄姓名。C 欄設驗證：
公式：=COUNTA(B2)=1
B2 為空時 C2 拒絕輸入。

5. 強制日期遞增/遞減輸入順序

D 欄輸入日期，要求由小到大：
允許「日期」→ 資料「大於或等於」→ 開始日期：=MAX($D$1:D1)

若要求由大到小：資料「小於或等於」→ =MIN($D$1:D1)。

6. 重複值自動標紅（條件格式）

C 欄檢測重複：
格式 → 條件格式 → 條件 1 選「公式」：=COUNTIF(C:C,C2)<>1 → 格式設紅字。

這些規則皆為「輸入端攔截」，配合條件格式的「事後視覺提醒」，能構建雙層防線，大幅降低髒資料產生機率。