讓公式不容易壞：IFERROR 與 IFNA 的取捨、開放式範圍、檢查格

公式在我手上跑得好好的，換別人加一列資料、少選一格範圍、手打一個數字蓋掉公式，就壞了。

這篇講怎麼讓公式沒那麼容易壞，更重要的是壞的時候你看得到。錯誤碼長什麼樣、怎麼查，在 公式出錯怎麼查 講過，這邊不重複。

IFERROR 會把該看到的錯一起吞掉

IFERROR 的用法很簡單：

=IFERROR(要算的公式, 有錯時顯示的值)

只要要算的公式出任何一種錯，就改顯示後面那個值。一整片紅色錯誤碼的報表很難看，包上 IFERROR 立刻乾淨，這也是它容易被濫用的原因。

我手上有一份菜單放在 A1:B6，E2 放要查的品名：

E2 填「青茶」，單價範圍不小心只選到 B5，少了一列：

=XLOOKUP(E2, A2:A6, B2:B5)

結果是 #N/A，這時候至少你知道有問題。如果先包一層：

=IFERROR(XLOOKUP(E2, A2:A6, B2:B5), "查無")

青茶明明在菜單裡，這格卻顯示「查無」。看起來是正常的查詢結果，沒人會懷疑公式，下游的報表就一直用這個「查無」。

所以我的做法是，公式剛寫好的時候先不包，確認結果對了，才決定要不要加。能在計算之前判斷的，我也比較喜歡先用 IF 擋，這個等一下用除法舉例。

IFNA 只處理 #N/A

IFNA 跟 IFERROR 寫法一樣，差別是它只管 #N/A，其他錯誤照樣顯示：

=IFNA(要算的公式, 找不到時顯示的值)

查詢類的函式找不到東西時，本來就是回 #N/A，這是正常情況，用 IFNA 剛好。E2 換成菜單上沒有的「珍珠奶茶」：

得到「沒有這個品項」。

兩者差在哪，用一個會回 #NUM! 的例子最清楚。INDEX 要第 9 列，範圍只有 5 列：

位置超出範圍是公式寫歪了，不是查不到。IFNA 讓這個錯誤留在畫面上，IFERROR 把它變成「查無」。

要小心的是，IFNA 也不是萬能。前面範圍大小不一樣的例子，回的就是 #N/A，IFNA 照樣會吞掉。FILTER 也是同樣的狀況，細節在 FILTER 常見錯誤。XLOOKUP 自己就有「找不到時的值」這個參數，用法在 XLOOKUP 入門，單純查價格的話不用再包一層。

讓錯誤訊息留線索

顯示「查無」的時候，我會把查的是什麼一起帶出來：

E2 是「珍珠奶茶」，就會顯示「查無：珍珠奶茶」。& 把文字跟儲存格的內容接起來。一整欄都是「查無」的時候，看不出是哪一筆，帶了品名就知道是誰。

另外，我不太用 IFERROR(…, "") 讓錯誤變空白。空白看起來像是這裡本來就沒資料，幾個月後沒人記得這格其實出過錯。

能先判斷就先判斷

訂單表在 A1:D3，D 欄是小計，E 欄想算每杯均價。第 3 列的杯數還沒填：

E3 寫 =D3/C3，分母是空白格，會是 #DIV/0!。包上 IFERROR(D3/C3, 0) 的話，E3 顯示 0，看起來像是均價是 0 元，那就錯了。

這種情況我寧可先看杯數有沒有填，沒填就留白，填了才算，這樣其他原因造成的錯誤還是看得到。練習題我留給你寫。

開放式範圍

資料會一直新增的表，範圍寫死會漏算。D2:D6 是前 5 筆的小計（資料跟最後一節的訂單表一樣），F1 算總和：

=SUM(D2:D6)

這時候新增第 7 列，蘋果紅茶 45 元賣 20 杯，D7 是 900。F1 還是 4887，沒有把新的一筆算進去，而且沒有任何錯誤，只是少算。

改成開放式範圍，只寫起點、不寫終點：

現在新增第 7 列之後，F1 變成 5787。標題列的文字會被 SUM 忽略，所以起點從標題下面那一列開始，或連標題一起都可以。要記得總和格不能放在 D 欄自己底下，否則公式會算到自己。

如果你用的是 Excel，要注意 D2:D 這種只寫起點的開放式範圍只有 Google 試算表能用，Excel 的範圍一定要有終點，要寫成明確的範圍，或整欄 D:D，或改用表格的結構化參照。

插入列如果落在範圍中間，範圍本來就會跟著長，不夠的是接在最後面的新增。搭配 FILTER、XLOOKUP 這類要求範圍同大小的函式，開放式範圍也要兩邊都寫到底，例如 A2:A 配 B2:B，才不會又掉進大小不一致的錯，這個寫法在 FILTER 實戰 有範例。

在輸入端擋掉

公式再小心，輸入的資料不對也沒用，像是杯數欄被填進「八」這樣的文字。我會在「資料」>「資料驗證」設定杯數欄只能填數字，輸入錯的時候直接拒絕。設定方式跟「拒絕輸入」和「顯示警告」的取捨，在 資料驗證實戰 有完整步驟。

放一格檢查格

即使前面都做了，我還是會在表的角落放兩格專門檢查的公式，一眼確認這張表現在沒問題。用一份訂單表舉例，A1:D6，D 欄是 =B2*C2 往下填，總和放在 F1：

第一格數有幾個儲存格出錯，ISERROR 對每一格回傳 TRUE 或 FALSE，前面加 -- 變成 1 或 0，再用 SUMPRODUCT 加起來：

目前是 0。如果有人把 C4 的杯數改成文字「八」，D4 的 B4*C4 會出錯，這格就變成 1。

第二格是對帳，用另一個算法把總金額再算一次，看兩邊一不一致：

現在是 TRUE。如果有人手癢把 D3 的公式蓋成手打的 2000，小計加總變成 4852，對帳就是 FALSE，馬上知道有一格被改了。找出是哪一格，可以用 公式出錯怎麼查 提過的 ISFORMULA。

檢查格不是 0 或 FALSE 的時候，我會再搭配條件式格式把它標紅，做法在 任務表自己變色。

練習

試試看把自己表裡的 IFERROR 先拿掉一次，說不定會發現原來一直有個錯被蓋著。