Excel 資料驗證防呆：自訂公式禁止重複輸入

身分證字號、發票號、員工編號等唯一識別碼，一旦重複輸入極難排查。Excel「資料驗證」的「自訂」公式功能，能在輸入瞬間攔截重複值並彈出警告，從源頭杜絕錯誤。

設定步驟（Excel 2007/2010/2013+ 通用）

1. 選取需防重複的儲存格區域（如整欄 A:A，或 A2:A10000）。
2. 「資料」索引標籤 → 「資料工具」群組 → 「資料驗證」。
3. 「設定」頁籤：

• 允許： 選「自訂」。
• 公式： 輸入：
  =COUNTIF($A:$A, A1)=1
  （若指定區域 A2:A10000，公式為 `=COUNTIF($A$2:$A$10000, A2)=1`，**必須用絕對參考鎖定比對範圍**，相對參考指向作用儲存格）。

4. 「錯誤警示」頁籤：

• 勾選「輸入無效資料時顯示錯誤警示」。
• 樣式：選「停止」（阻擋輸入）、「警告」或「資訊」（允許覆寫）。
• 標題：如「重複值警告」。
• 錯誤訊息：如「此身分證字號已存在，請確認後重新輸入！」
• 點「確定」完成設定。

運作原理

`COUNTIF(範圍, 當前儲存格)` 計算範圍內等於當前輸入值的筆數。若 =1 表示唯一（含自身），通過驗證；若 >1 表示重複，觸發錯誤警示。

進階應用：跨工作表/活頁簿防重

• 公式引用其他工作表：`=COUNTIF(Sheet2!$A:$A, A1)=1`
• 跨活頁簿：`=COUNTIF('[Master.xlsx]Sheet1'!$A:$A, A1)=1`（來源活頁簿需開啟）。

常見坑點與解法

搭配條件格式雙重保險

「條件格式」→「醒目反白顯示儲存格規則」→「重複值」設紅色填滿，視覺上即時顯示重複，配合資料驗證「事前阻擋 + 事後可見」，雙重防線最安心。

這招「自訂公式防重複」是 Excel 資料品質管理的基石技能，適用於所有需維護唯一性的關鍵欄位。