資料驗證進階陷阱：相依下拉、自訂公式的參照與貼上繞過

選了「南區」，門市的下拉選單卻還是列出信義、板橋，連北區的店都在裡面。或是自訂公式在第一列好好的，往下到第四列就開始把正確的資料擋掉。

下拉選單與核取方塊怎麼設，在 資料驗證入門 講過；擋重複編號、擋手機格式在 資料驗證實戰；網址與 Email 的判斷在 ISURL 與 ISEMAIL。這篇直接從會出事的地方開始，一個陷阱一節。

相依下拉：選了區域，門市才跟著變

先看資料。對照表放在 A1:B6，一個區域對應幾間門市：

我想要 D2 選區域，G2 只列出該區的門市。網路上常見的相依下拉教學是用 INDIRECT 搭配具名範圍，但在 Google 試算表的資料驗證裡行不通：把 =INDIRECT("F2:F6") 填進「下拉式選單 (來自某範圍)」的來源欄，按完成會顯示「請輸入有效的範圍」；改用 =INDIRECT(A2) 去讀具名範圍，同樣不能用。來源欄要的是真正的儲存格範圍，不是公式的結果。所以改讓輔助欄先算好門市清單，下拉選單再讀取真正的範圍 F2:F6。

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

圖：門市下拉選單的來源不是對照表，而是中間那個會跟著區域變的輔助欄。

做法分三步：

1. D2 設一個下拉式選單，選項手打北區、南區。
2. F2 輸入輔助欄公式。
3. G2 設「下拉式選單 (來自某範圍)」，範圍填 F2:F6。

以下先使用固定的 A2:B6 範例。若在第 7 列新增門市，輔助欄 FILTER、後面的 COUNTIFS 檢查及下拉選單來源範圍必須同步延伸；固定範圍不會自動包含新增列。

輔助欄的公式是：

• B2:B6：要回傳的門市。
• A2:A6=D2：只留區域等於 D2 的那幾列。
• IFERROR(..., "")：D2 還沒選的時候，FILTER 找不到東西會回 #N/A，這樣包起來就是空白。

D2 選「北區」，F2:F4 會溢出三格：

改選「南區」，F2:F3 變成左營、三民，F4 清空。G2 的下拉選單範圍我多框到 F6，留給之後新增的門市。實際測試時，北區只列出信義、板橋、中和，南區只列出左營、三民；輔助範圍內的空白不會變成選項。

圖：選項來源指向輔助欄，不是對照表。

這個做法有兩個限制，我自己很在意。

第一，一個輔助欄只對應一個「區域」儲存格。做成登記表，每一列都有自己的區域與門市時，沒有辦法每列各配一個輔助欄，這時候我會退一步，讓門市欄的下拉選單列出全部門市，再用下一節的自訂公式檢查區域與門市有沒有對上。

第二，改了區域之後，G2 裡原本選好的門市還留在那裡。可以在旁邊放一格檢查：

D2 是北區、G2 是信義，結果是「符合」。D2 改成南區、G2 還是信義，結果是「門市不屬於此區域」。舊值「信義」仍然保留，選單旁也會顯示無效提示；改選左營後，檢查欄才恢復「符合」。重新載入後，南區／左營與檢查結果保留。檢查欄讓這個狀態可以直接看見。

自訂公式的參照：寫對一列，往下就歪

上面說的登記表情境，用自訂公式擋區域與門市對不上的列。對照表搬到 E1:F6（跟登記表放在同一張工作表，公式最單純）：

登記表在 A1:C5，B 欄是要驗證的門市：

選取 B2:B100，條件選「自訂公式為」，這一條是對的：

規則裡寫的公式是給選取範圍的左上角那一格（這裡是 B2），往下套到其他列時，沒鎖的部分會跟著位移，跟 條件式格式 的自訂公式是同一套規則。

• $E$2:$E$6、$F$2:$F$6：對照表，行列都鎖，每一列都看同一份。
• $A2：區域，鎖住 A 欄，列跟著走。
• B2：門市，欄列都不鎖，B3、B4 往下變。

最常見的錯是什麼都沒鎖：

=COUNTIFS(E2:E6, A2, F2:F6, B2) > 0

第 2 列寫起來沒問題，但套到第 4 列的時候，對照表的範圍會變成 E4:E8、F4:F8，往下滑掉了兩列。把兩條公式分別在 H、I 欄逐列寫出來，結果如下：

第 4 列的北區、信義明明在對照表的第 2 列，但沒鎖的寫法範圍從第 4 列開始，已經看不到它，所以被誤擋。第 5 列是真的對不上，兩種寫法都判 FALSE，這列是該被擋的。

我個人的習慣是自訂公式寫完，先在旁邊的空白欄把同一條公式向下填一遍，看每一列的 TRUE 與 FALSE 對不對，再貼進規則。

下拉晶片的樣式與顏色

下拉式選單的外觀在「進階選項」的「顯示樣式」，有晶片、箭頭、純文字三種。晶片可以幫每個選項上色，一整欄掃過去，哪一列是哪個狀態一眼就看得出來。

圖：選項顏色在資料驗證規則面板裡設定。

幾件事要注意：

• 顏色是跟著規則存的，不是跟著儲存格。選項本身改名，要回規則面板改。
• 選項的顏色與 條件式格式 的底色是各自獨立的兩套，同一格兩邊都設，看起來會很花。
• 依說明中心，「可複選」只有晶片樣式才能開。開了之後，儲存格裡存的是多個值，後面如果要用 COUNTIF 算，要先想清楚怎麼拆。
• 選項來自範圍時，從範圍裡刪掉一個有顏色的值，規則裡那個顏色還會留著，但變成不能編輯，要換掉來源範圍或其他項目的顏色才會消失。

顏色我自己抓三到四種就夠，再多就會分不清。

貼上與手動輸入，結果要分開看

以下是我在繁體中文桌面版 Sheets 用「紅茶／青茶」下拉選單測出來的結果。每一組貼上都包含兩列不在清單內的文字，交叉比較拒絕／警告、一般貼上／僅貼上值，共 12 種情境。來源儲存格本身沒有驗證規則。這是實際操作的結果，不是官方文件的保證，Google 改版後行為可能改變。

上述兩列批次測試在拒絕與警告模式的結果相同。保留規則的無效值有角落標記；一般內部貼上移除原規則後，不能期待舊規則繼續檢查它。這不代表所有貼上都能繞過驗證：把外部純文字只貼進一格時，拒絕模式的一般貼上和僅貼值都被擋下，警告模式才允許進入。不要用單格測試推論整批，這份結果也不代表所有驗證類型、瀏覽器與手機版都一樣。

因此我的做法是貼完核對規則與資料，並在旁邊保留檢查欄。清單放在 F2:F6（信義、板橋、中和、左營、三民），登記表 B2:B5 分別是信義、信義店、板橋、三：

填到 C2:C5，結果：

要知道有幾筆有問題，再數一次：

結果是 2。這一格我會放在表格最上面，打開檔案一眼就看得到，也可以拿它當 條件式格式 的條件，讓整列變色。

保護設定工作表、選項來源清單與公式欄，可以降低誤改；日常輸入格則要保留給店員編輯。把附有資料驗證的輸入範圍限制為只有管理員可改，也會一起禁止店員輸入值，並不是「允許輸入、只鎖規則」。警告式保護也不會阻止編輯。Google 官方保護說明列出這些權衡；實際的角色權限請用第二個帳號測試一次再上線。

拒絕輸入，還是顯示警告

兩種處理方式的差別在 資料驗證入門 講過了，這邊只補進階的兩點。

相依下拉那種「區域對不上門市」的規則，我選拒絕，因為對不上就是錯。但貼上的情境，拒絕不一定生效，所以「拒絕」不能當成唯一的防線，旁邊的檢查欄才是。

另一點是提示訊息。被擋下來的時候，店員只看得到你寫的那句話，我會寫成「請先選區域，再從選單選門市」這種可以直接照做的句子。

資料驗證和貼後檢查各有用途；不要只靠拒絕模式推定整批貼上都已合法。試著把你手上最常被亂填的那一欄，加一條規則再加一格檢查吧！我是喜特先生，Mr. Sheet，我們下個教學見！

延伸改寫自喜特先生的方格子舊文：資料驗證的相依下拉選單。本文改以目前 Sheets 介面可以重現的輔助欄流程更新。