為什麼我的 FILTER 明明有資料，卻回 #N/A？有時候公式沒壞，是真的沒有符合的列，有時候是公式寫歪了，而兩種情況在儲存格裡長得一模一樣。這篇整理 FILTER 最常踩的幾個坑，基本語法在 FILTER 入門 講過，這邊不重複。
後面都用同一張訂單，放在 A1:D13：
| 日期 | 品項 | 門市 | 杯數 |
|---|---|---|---|
| 2026/10/01 | 蘋果紅茶 | 信義 | 12 |
| 2026/10/01 | 烏龍奶茶 | 板橋 | 37 |
| 2026/10/02 | 蘋果綠茶 | 信義 | 8 |
| 2026/10/03 | 蘋果紅茶 | 板橋 | 21 |
| 2026/10/04 | 烏龍奶茶 | 信義 | 5 |
| 2026/10/05 | 青茶 | 板橋 | 29 |
| 2026/10/06 | 蘋果綠茶 | 中和 | 9 |
| 2026/10/07 | 蘋果紅茶 | 中和 | 33 |
| 2026/10/08 | 青茶 | 信義 | 17 |
| 2026/10/09 | 烏龍奶茶 | 板橋 | 14 |
| 2026/10/10 | 蘋果紅茶 | 信義 | 26 |
| 2026/10/10 | 青茶 | 中和 | 11 |
同樣是 #N/A，意思卻不同
先看真的沒有資料。訂單裡沒有「新店」門市：
結果是 #N/A，把游標移上去，提示寫的是「系統計算 FILTER 時找不到相符項目」。這種是正常的，只是沒有符合的列。
Sheets 的 FILTER 沒有「沒資料時顯示」參數
Excel 的 FILTER 有一個選填的第三個參數，可以直接指定沒資料時顯示什麼。Google 試算表的 FILTER 沒有，第三個位置起放的都是條件。我們來看看如果照 Excel 的習慣，把文字放在第三個位置會怎樣：
板橋明明有訂單，結果卻是 #N/A。因為「沒有資料」被當成第二個條件，而它只有一格，長度和範圍的 12 列對不上。
反過來，Sheets 公式搬到 Excel 也要小心：Sheets 用逗號接的第二個條件，在 Excel 會被當成第三個參數 if_empty，結果就錯了。Excel 的 FILTER 只有一個 include 條件，多條件要用 *（且）與 +（或）組起來，寫法看 FILTER 實戰。另外 Excel 沒有符合的資料時，顯示的是 #CALC! 而不是 #N/A，而且只有 Excel 365、2024、2021 才有 FILTER。
用 IFNA 包起來
沒資料時要顯示文字，一般都是外面包 IFNA：
得到「沒有符合的訂單」。不過這個寫法有個副作用，IFNA 會把所有 #N/A 一視同仁，下一節的範圍大小不一致也會被它蓋掉，變成一句看起來很合理的「沒有符合的訂單」。
我個人的做法是：公式剛寫好的時候先不包，確認結果對了再加 IFNA。IFERROR 連 #REF!、#VALUE! 都會蓋掉，更容易把問題藏起來，除非真的想吞掉所有錯誤，否則我不用它。
想把「真的沒有資料」和「公式寫錯」分開，可以先用 COUNTIF 確認有沒有符合的列：
沒資料時顯示文字，條件範圍如果寫歪，FILTER 照樣報錯，不會被蓋掉。缺點是條件要寫兩遍，改的時候兩邊要一起改。
條件範圍大小不一致
我資料新增了一列，公式只拉了一邊，結果變成：
結果是 #N/A，提示是「FILTER 的範圍大小不符。應該有 12 列 1 欄，實際卻指定了 11 列 1 欄」。
這也是 #N/A，所以接著包 IFNA 的話：
就會顯示「沒有符合的訂單」，但板橋其實有 4 筆。這就是上一節說的副作用，這種錯很難察覺，報表一直顯示沒資料，會讓人先懷疑是不是真的沒訂單，最後才發現是範圍少一列。
修法是讓範圍和每個條件的列數完全一樣，A2:D13 配 C2:C13。資料會持續新增的話，我會兩邊都寫到底，A2:D 配 C2:C，用法在 FILTER 實戰 有寫。
溢出被擋住變成 #REF!
在 F6 輸入板橋的篩選公式：
結果有 4 列 4 欄，會佔 F6:I9。如果 G8 剛好有一格內容，比如有人隨手打的備註「擋住」，F6 就變成 #REF!。提示寫的是「系統未展開陣列結果，以免覆蓋 G8 中的資料。」
修法是把擋路的格子清掉，或把公式搬到空的地方，清掉以後 F6:I9 會自己展開成：
| F | G | H | I | |
|---|---|---|---|---|
| 6 | 2026/10/01 | 烏龍奶茶 | 板橋 | 37 |
| 7 | 2026/10/03 | 蘋果紅茶 | 板橋 | 21 |
| 8 | 2026/10/05 | 青茶 | 板橋 | 29 |
| 9 | 2026/10/09 | 烏龍奶茶 | 板橋 | 14 |
這個坑最煩的是它會「今天才出現」。昨天結果只有 2 列，沒碰到下面的格子，今天資料多了、結果變長，撞到下面的東西才報錯。有些擋路的東西肉眼看不到，像是只有一個空格的儲存格，或是合併儲存格的一部分。我習慣把 FILTER 放在工作表最上方，下面整塊留空，其他東西擺在左邊或右邊。
部分比對：包含某個字
我想找品項裡有「蘋果」的訂單。用等號是行不通的，等號要整格一模一樣：
沒有哪格剛好等於「蘋果」，結果是 #N/A。
REGEXMATCH
REGEXMATCH 判斷文字有沒有符合某個規則，有就是 TRUE，放進條件的位置剛好。規則寫「蘋果」，就是只要包含這兩個字：
品項包含蘋果
=FILTER(A2:D13, REGEXMATCH(B2:B13, "蘋果"))來看看結果，6 筆：
| 日期 | 品項 | 門市 | 杯數 |
|---|---|---|---|
| 2026/10/01 | 蘋果紅茶 | 信義 | 12 |
| 2026/10/02 | 蘋果綠茶 | 信義 | 8 |
| 2026/10/03 | 蘋果紅茶 | 板橋 | 21 |
| 2026/10/06 | 蘋果綠茶 | 中和 | 9 |
| 2026/10/07 | 蘋果紅茶 | 中和 | 33 |
| 2026/10/10 | 蘋果紅茶 | 信義 | 26 |
規則裡的直線 | 代表「或」，所以包含其中一個詞的寫法很短。找青茶或奶茶：
得到烏龍奶茶 3 筆、青茶 3 筆，共 6 列，順序和原始資料一樣。這比條件之間加 + 好讀，詞很多的時候更明顯。規則的更多寫法，看 正規表示式擷取文字。
SEARCH
不想碰正規表示式的話，SEARCH 找得到就回傳位置數字，找不到是 #VALUE!，所以前面加 ISNUMBER 變成 TRUE 和 FALSE。搜尋文字放在 G1，輸入「奶」：
品項包含 G1 的文字
=FILTER(A2:D13, ISNUMBER(SEARCH(G1, B2:B13)))結果是 3 筆烏龍奶茶，也就是 10/01 板橋的 37 杯、10/04 信義的 5 杯、10/09 板橋的 14 杯。
搜尋框裡打到特殊符號
把搜尋框做成讓別人輸入的格子時，兩種寫法的差別會浮現。G1 輸入左括號 (：
左括號在正規表示式裡有特殊意思，單獨一個不是合法的規則，整條公式會出錯，而且顯示的不是 #REF! 或 #VALUE!，而是 #N/A，和查無資料時一模一樣，更難發現是規則寫壞了。換成 SEARCH，( 只是個普通字元，沒有品項含有它，FILTER 就回 #N/A。但 SEARCH 也有自己的特殊字元：? 代表任意一個字、* 代表任意多個字，要找真的問號或星號，前面要加波浪號 ~。
所以我的取捨是：自己寫死的規則用 REGEXMATCH，可以一次比對多個詞；讓別人輸入的搜尋框用 SEARCH，比較不容易被打錯字元弄壞。搜尋框留空時，SEARCH("", ...) 找到的位置都是 1，所有列都會留下，等於不篩。
資料變多會不會變慢
這邊不給秒數，只算比較的次數，這是可以算出來的。一條 FILTER 要把 1,000 列的條件各比一次，就是 1,000 次比較。
如果在每一列旁邊各放一條 ，比如 1,000 列，每列都各自掃一遍整張表，就是 1,000 × 1,000 = 1,000,000 次比較。比較次數是列數的平方，資料加倍，次數是四倍。
我會這樣處理：
- 只要一個加總，用
SUMIFS，不必先FILTER再SUM，見 SUMIFS 進階陷阱。 - 想要一整排的各組小計，用一條
QUERY分組一次算完，而不是每列各算一次。 SORT包在FILTER外面，先篩再排，排序的列數少。
速度到底有多少差異，我建議複製一份自己的檔案，把公式換掉，再比較打開檔案或改資料後多久算完。整體的效能想法放在 效能基本觀念。
什麼時候不用 FILTER
只要一個數字或各組小計，上一節的 SUMIFS、QUERY、樞紐分析表比較合適。另外 FILTER 的結果是公式算出來的，改不了，要改請回原始資料。
練習
找出錯在哪
同事想看板橋門市、杯數至少 20 杯的訂單，寫了：
畫面顯示「沒有符合的訂單」，但板橋明明有訂單。請說明原因，並改成正確的公式，說明正確的結果有幾列。
下次看到 #N/A，先把外面的 IFNA 拿掉看提示怎麼說，再決定是資料真的沒有，還是範圍寫歪了。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章留言討論
共 0 則留言