外觀
Productivity › 試算表 › 資料驗證
資料驗證 (Data Validation)
資料驗證是輸入階段的品質控管:在資料進表的當下擋掉不合法的值,讓下游少做清理。驗證 (validation) 是預防,資料清理 是修正;前面擋得越多,後面修得越少。
驗證不會讓資料變聰明,而是讓輸入變得受控。對表單、報價單、營運台帳、手動維護的對照表特別有用。
設計流程
text
業務規則(policy)→ 轉成可檢查的規則 → 在 Excel 設定驗證 → 故意輸入錯值測試 → 補輸入提示與錯誤訊息很多驗證失敗不是 Excel 功能不夠,而是一開始沒把規則定清楚。先想使用者會輸入什麼錯,再決定怎麼擋;優先保護關鍵欄位,不必追求全表覆蓋。
規則類型
設定位置:資料 → 資料驗證 (Data → Data Validation)。
| 類型 | 例子 | 設定 |
|---|---|---|
| 數值範圍 | 年份 2000–2025、利率不超出公司政策、期數在合理範圍 | 允許:整數 / 小數,介於 |
| 固定長度文字 | 車身號碼 VIN 必須 17 字元、固定長度的 ID | 允許:文字長度,等於 17 |
| 清單(下拉選單) | 廠牌、顏色、狀態、地區 | 允許:清單,來源指向已命名範圍(如 =MakerList) |
| 日期 / 時間 | 不可早於今天、不可晚於截止日 | 允許:日期,或自訂公式 |
| 自訂公式 | 跨欄或依情境的規則 | 允許:自訂,公式回傳 TRUE 才接受 |
常用自訂公式(以 A2 為作用儲存格,套用到整欄時用相對參照):
| 規則 | 公式 |
|---|---|
| 日期不可早於今天 | =A2>=TODAY() |
| 還款起始日晚於簽約日 | =B2>A2 |
| 只有在 C 欄有值時才能填 | =C2<>"" |
| 依貸款類型限制利率上限 | =B2<=XLOOKUP(A2, CapType, CapMax)(CapType、CapMax 為已命名範圍) |
| 不可重複(同欄唯一) | =COUNTIF($A:$A, A2)=1 |
驗證公式不能直接寫 Table 的結構化參照(
Table1[Maker])。把選項放在「選項」工作表的 Table 裡,再定義一個指向該欄的已命名範圍(如MakerList);之後新增選項時 Table 自動延伸,清單也跟著更新。
輸入提示與錯誤訊息
| 分頁 | 用途 | 寫法 |
|---|---|---|
| 提示訊息 (Input Message) | 選取儲存格時顯示要填什麼 | 「請輸入 17 碼 VIN,不含 I、O、Q」 |
| 錯誤提醒 (Error Alert) | 輸入錯誤時的訊息與強度 | 說清楚哪裡錯、規則是什麼、怎麼改 |
| 錯誤提醒樣式 | 行為 |
|---|---|
| 停止 (Stop) | 不允許輸入(預設) |
| 警告 (Warning) | 可選擇仍要輸入 |
| 資訊 (Information) | 只提示 |
限制
| 限制 | 對策 |
|---|---|
| 複製貼上會覆蓋驗證規則與內容 | 保護工作表;或之後用「資料 → 資料驗證 → 圈選錯誤資料」檢查 |
| 設定前已存在的錯值不會被擋 | 同上,「圈選錯誤資料 (Circle Invalid Data)」 |
| 沒有內建的「不可重複」選項 | 用上面的 COUNTIF 自訂公式,搭配 條件式格式 標出重複 |
| 複雜、跨表的邏輯難以維護 | 改用表單工具(Microsoft Forms)或資料庫 + 前端,Excel 只當第一道防線 |