資料驗證實戰：把訂單登記表的輸入錯誤擋在門口

店員每天手動填的訂單登記表，放一個月大概就會長成這樣：

我想知道信義店有幾筆訂單、賣了幾杯，公式很直接：

手算的話，信義店有 3 筆，12 + 7 + 3 = 22 杯。但公式給的是 2 筆、12 杯。

公式沒有錯，資料在進來的時候就歪了：「信義店」是另一個字串，「三」是文字，SUMIF 不會加。事後清理的做法在 文字清理函式 講過，這篇講怎麼讓它根本進不來。

先決定每一欄擋什麼

登記表之後會長到六欄（上面是日期、門市、品項、杯數，後面再加訂單編號與手機），我一欄一欄想「這格的答案有幾種可能」。答案是固定清單，就用下拉選單；是數字或日期，就設範圍；規則比較特別，就寫自訂公式。

← 左右滑動看完整的圖，點圖可放大

圖：每一欄一條規則，最下面是不符合時的處理方式，先看顏色就知道哪一欄比較寬鬆。

下面照這張圖由上往下做。每一條規則都在同一個地方設定，先看一次入口。

1. 選取要設規則的範圍，例如門市欄的 B2:B200。
2. 點「資料」>「資料驗證」，右側會開出規則面板。
3. 點「新增規則」，在「條件」選規則種類。
4. 點「進階選項」，決定資料無效時要拒絕還是警告，最後按「完成」。

圖：所有規則都從這個面板開始。

下拉選單：門市和品項

門市和品項的答案就那幾種，我直接做成下拉選單。選項放在另一張「設定」工作表，A 欄是門市（信義、板橋、中和），B 欄是品項（蘋果紅茶、蘋果綠茶、烏龍奶茶、青茶）。

選取 B2:B200，新增規則，條件選「下拉式選單 (來自某範圍)」，範圍填 設定!A2:A10。我多框幾格留給新門市，之後開新店，只要在設定工作表 A 欄往下加一格，所有登記表的選單同時多一個選項。

圖：選項住在設定工作表，登記表只負責選。

選項也可以改成手打：條件選「下拉式選單」，一個選項一列。選項只有兩三個、不會變的時候這樣比較快，但我個人還是習慣放在設定工作表，因為門市名稱改了只要改一處。選項如果要跟著資料自動長出來，先在設定工作表的某一欄用 UNIQUE 算出清單，下拉選單的來源再指向那一欄（來源欄只能填範圍，不能直接寫公式），作法在 FILTER 實戰 有寫。

選單的外觀可以選晶片，每個選項有自己的顏色，我給三間門市三種顏色，一整欄看過去就知道哪一列是哪間店。

圖：選了之後，儲存格裡就只會有選單裡的那個字。

品項欄照做一次，範圍改成 設定!B2:B10。

數字與日期範圍

杯數欄選 D2:D200，條件選「數字」>「介於」，填 1 到 50。「三」是文字、-3 低於 1，兩個都會被判無效。上限我抓 50，因為目前資料裡最大一筆是 33，留點空間給大單。

日期欄選 A2:A200，條件選「日期」>「是有效日期」。打「十月五日」這種文字會被判無效，打 10/5 這種 Sheets 認得的日期則可以通過。

這兩條要注意，「介於」只管範圍，不管是不是整數，12.5 杯也過得去。真的要整數，就要用下面的自訂公式。

自訂公式：擋重複的訂單編號

訂單編號是 T1001、T1002 這樣一筆一個，不該重複。內建條件沒有「不重複」，這就是自訂公式派上場的地方。

選取 E2:E200，條件選「自訂公式為」，填：

• $E$2:$E$200：整欄編號的範圍，鎖住不動。
• E2：公式是寫給範圍左上角那一格，其他格會跟著往下變 E3、E4⋯⋯，跟 條件式格式 的自訂公式是同一個規則。
• =1：打進去的那一格自己也算一次，所以出現 1 次才代表沒有別人用過。

假設 E2:E4 已經是 T1001、T1002、T1003，店員在 E5 又打了 T1002，這四格各自算出來的結果是：

E5 因為不符合而被擋下。E3 也會是 FALSE，因為現在整欄有兩個 T1002，這是正常的，等 E5 被擋掉、改回正確編號就恢復了。

自訂公式：擋格式不對的手機

手機號碼是 09 開頭、後面 8 碼數字，共 10 碼。格式不是固定清單，也不是數字範圍，用 REGEXMATCH（正規表示式比對）最直接。選取 F2:F200，條件一樣選「自訂公式為」：

• F2&""：把儲存格內容轉成文字再比對。
• ^09：開頭必須是 09。
• [0-9]{8}：後面接剛好 8 個數字。
• $：到這裡就結束，後面不能再有東西。

四種輸入的結果：

這條只管手機。市話有區碼長短之分，不要硬套同一條規則，另開一欄放比較好。

拒絕輸入，還是只顯示警告

每條規則的「進階選項」裡，有兩種處理方式：

← 左右滑動看完整的圖，點圖可放大

圖：兩種設定的差別只在不符合之後，資料有沒有進得了儲存格。

• 拒絕輸入：不寫入，跳出訊息，店員只能改。
• 顯示警告：照樣寫入，儲存格角落標一個小記號，滑鼠移上去看得到提示。

我的分法是看答案有沒有「只有一種對」。門市、品項、訂單編號、手機都只有一種對，就用拒絕。杯數不一樣，一次 60 杯可能是真的大單，擋掉會讓店員去亂改數字，所以我讓它警告，店員確認過可以留下，我之後看到記號也知道要看一眼。

← 左右滑動看完整的圖，點圖可放大

圖：警告不擋人，但會留下記號。

「進階選項」裡還有「顯示驗證說明文字」，被擋下來時會看到這段字。我會寫成「請從選單選擇門市」這種可以直接照做的句子，不要留預設的「輸入無效」。

規則擋不到的地方

規則只管之後輸入的格子，下面三件事要自己顧：

• 已經在表裡的舊資料不會被改，不符合規則的舊值會被標上記號，要回頭用 文字清理函式 整理。
• 從別處複製貼上的內容不一定會被擋下，貼完之後掃一眼儲存格角落有沒有記號。
• 保護設定工作表、選項來源清單與公式欄，可以降低誤改；日常輸入格則要保留給店員編輯。把附有資料驗證的輸入範圍限制為只有管理員可改，也會一起禁止店員輸入值，並不是「允許輸入、只鎖規則」。警告式保護也不會阻止編輯。Google 官方保護說明列出這些權衡；實際的角色權限請用第二個帳號測試一次再上線。

練習

規則設好再交給店員，之後會填錯的是他們，不用你一個一個回頭修。登記表要做成會自己換內容的報表，接 FILTER 實戰 的作法就可以了。我是喜特先生，Mr. Sheet，我們下個教學見！