公式出錯怎麼查：錯誤碼怎麼看，再一步步縮小範圍

公式突然變成 #N/A，要從哪裡查起？

我的習慣是先不要動公式，先看錯誤碼是哪一種，再把游標移到那一格看提示。這兩個線索通常已經把範圍縮到一兩個地方了。這篇整理常見的錯誤碼長什麼樣、怎麼自己重現它，以及看不出原因時我會用的幾個檢查工具。

先看錯誤碼，再看提示

出錯的儲存格右上角會有一個小標記，把游標移上去，會跳出一段說明，有時候直接講出是哪個參數、哪個範圍有問題。錯誤碼只是大方向，這段提示才是重點。

後兩個下面各自有一個小例子。Sheets 沒有 #SPILL!，FILTER 沒有符合的資料也不是 #CALC!，這兩種都是 Excel 才有的說法。

重現幾個常見的錯

後面都用同一張訂單，放在 A1:C11，10 筆資料：

範圍大小不一樣，是 #N/A

我只想篩出板橋的訂單，條件範圍卻少選了一列：

=FILTER(A2:C11, B2:B10="板橋")

結果是 #N/A，提示寫「FILTER 的範圍大小不符。應該有 10 列 1 欄，實際卻指定了 9 列 1 欄。」XLOOKUP 也是一樣，E2 放「青茶」，結果範圍少一列：

=XLOOKUP(E2, A2:A11, C2:C10)

也是 #N/A，提示是「XLOOKUP 的陣列引數大小不同。」

範圍大小不對不一定是 #VALUE!，這兩個函式都回 #N/A。所以看到 #N/A 別急著認定是資料沒有。修法就是把範圍改成同樣的列數。

FILTER 真的沒有符合的資料，也是 #N/A

訂單裡沒有「新店」：

=FILTER(A2:C11, B2:B11="新店")

一樣是 #N/A，不過提示換成「系統計算 FILTER 時找不到相符項目。」兩種情況在儲存格裡長得一模一樣，要靠提示分辨。更多 FILTER 的坑放在 FILTER 常見錯誤，XLOOKUP 的在 XLOOKUP 進階陷阱。

位置超出範圍，是 #NUM!

INDEX 用位置取值，要第 9 列，但範圍只有 5 列：

=INDEX(C2:C6, 9)

結果是 #NUM!，提示是「函式 INDEX 的第 2 參數 參數的值 (9) 超出範圍。」位置超出範圍不是 #REF!，而是 #NUM!。

結果展開被擋住，是 #REF!

在 E2 輸入：

=FILTER(A2:C11, B2:B11="板橋")

板橋有 4 筆，結果會往下往右展開成 E2:G5。如果 G2 剛好已經有內容，比如有人打了「備註」，E2 就變成 #REF!，提示寫的是「系統未展開陣列結果，以免覆蓋 G2 中的資料。」

把 G2 清掉，結果就會自己展開，不用動公式。

SUMIFS 的範圍大小不一樣，是 #VALUE!

SUMIFS 的規則一樣是範圍要同大小，但錯誤碼不同：

=SUMIFS(C2:C11, B2:B10, "板橋")

結果是 #VALUE!。同樣是範圍大小不符，FILTER、XLOOKUP 回 #N/A，SUMIFS 回 #VALUE!，我只能一個一個記。SUMIFS 其他的坑在 SUMIFS 進階陷阱。

運算子寫錯，是 #ERROR!

比較大於等於，正確寫法是 >=。如果順序寫反成 =>：

=IF(C2=>10, "多", "少")

結果是 #ERROR!，代表 Sheets 看不懂這條公式，不是資料的問題。這時候回頭檢查符號、括號跟逗號。

除以零與函式名稱打錯

這兩個放在這裡一起看：

=C2/0

結果是 #DIV/0!，分母是 0 或空白格的時候都會遇到。

=SUMM(C2:C11)

函式名稱少打一個字母，結果是 #NAME?。

看不出原因：先檢查資料型別

公式看起來沒錯，卻怎麼都查不到的時候，我第一個懷疑的是型別。一個是數字 1001、一個是文字 1001，看起來一樣，卻是不同的東西。

為了示範，我用 TEXT 做出兩格文字的編號。A1:B3 是一張小表，A2 是 =TEXT(1001, "0")，A3 是 =TEXT(1002, "0")，B 欄放品名：

D2 放數字 1001，拿去查：

=XLOOKUP(D2, A2:A3, B2:B3)

結果是 #N/A，查找值是數字，查找範圍裡是文字，對不上。從系統匯出的編號靠左對齊，通常就是這種情況。

用 ISNUMBER 和 ISTEXT 可以直接問儲存格是哪一種：

確定是型別問題後，把數字也轉成文字就找得到了：

得到「蘋果紅茶」。文字看起來相同，還可能是不同字元：TRIM 清一般半形空格，CLEAN 清 ASCII 控制字元，兩者都不是全半形或 Unicode 正規化工具。TRIM("Ａ１") 和 CLEAN("Ａ１") 的結果仍是 Ａ１，要用 ASC("Ａ１") 才會變成 A1；NBSP 與全形空格也不會被 TRIM 清掉。應先用 LEN／UNICODE 找原因，再依規則替換，細節見 文字清理函式參考。

還有一個容易搞混的是空白。ISBLANK 問的是儲存格是不是真的空的：

看起來都是空白，但公式傳回的空字串不是空儲存格，用 ISBLANK 判斷會漏掉。

看公式本身

彩色框

雙擊公式所在的儲存格進入編輯（或按 F2，Mac 用 Ctrl+U，在 Chrome 上），公式裡每個參照都會在表上框出一種顏色，並對應公式裡同色的文字。我常用它檢查範圍有沒有選歪，比如公式寫 B2:B10，框卻只框到第 10 列，一眼就看出少了一列。

顯示全部公式

按 Ctrl+~（Windows、Mac 都是，在 Chrome 上）會把整張工作表切成顯示公式，再按一次切回來。也可以走選單「查看」>「顯示」>「公式」。整欄公式應該長得一樣，如果其中一格突然不同，就是被人改過了。

FORMULATEXT 與 ISFORMULA

不想切換整張表的話，可以在旁邊放 FORMULATEXT 把公式當文字顯示出來：

如果 D2 是 =SUM(C2:C11)，這格會顯示「=SUM(C2:C11)」這串文字。

ISFORMULA 則是問一格是不是公式，回傳 TRUE 或 FALSE。整欄應該都是公式，卻有一格被手打的數字蓋掉時，它會是 FALSE。

從內到外拆開，再按語意換行

長公式先找最裡面要處理的資料。例如「找分隔符號 → 取後半段 → 整理文字 → 查找 → 處理找不到」，每一步都先在旁邊空白欄確認輸出，確定型態、內容與筆數，再包上外層函式。不要一開始就用 IFERROR 把所有問題都變成空白。

換行也照這個結構安排：

• LET 每個變數與定義放在一起，最後一段留給回傳結果。
• IF 把條件、成立與不成立分支分開。
• SUMIFS 的條件範圍與條件成對排列，方便核對是否選到同樣列數。
• QUERY 文字的引號、拼接與子句分清楚，避免為了斷行改變查詢字串本身。

例如先閱讀這條已在前面說明的條件彙總，再比較下面的分行版本：=SUMIFS(C2:C11,B2:B11,"信義")。

兩個版本使用同一組引數；此處沿用本篇資料的 42 杯，示範結構閱讀，不引入新的計算方法。換行應放在字串外，字串裡的空白與換行可能就是資料。保留原式在另一格，分行後比較結果；如果不同，就先檢查是否動到引號、參照或運算子。

這節改寫自方格子寫長算式的建議。重點是先拆解與核對，再整理可讀性。

公式自己參照到自己的結果（循環參照）也會出錯，結果是 #REF!，提示會提到循環，這種要回頭檢查哪些格子互相依賴。「檔案」>「設定」>「計算」裡有「迭代計算」開關，只有刻意做遞迴時才需要打開，不要為了消除錯誤而打開它。Sheets 也沒有 Excel 那種「錯誤檢查」選單，出錯的儲存格會在右上角顯示紅色小三角。

練習

錯誤碼查出來之後，下一步是想辦法讓同樣的錯不要再發生，這部分放在 讓公式不容易壞。如果連公式怎麼寫都還不熟，可以從 第一次寫公式 開始。