Skip to content

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 只當第一道防線

高風險流程不要因為設了驗證就假設資料乾淨;下游仍要有 QA(資料準備、資料品質)。