FILTER 函數是 EXCEL 在2021年新增的必學函數之一，新增之後讓「篩選特定條件資料」變得方便許多，而且還會隨著資料改變而變動，可以說是 EXCEL 篩選功能的進階版。
本文將會用 5 分鐘的時間，完整介紹 FILTER 函數的用法、進階用法、常見錯誤等等。
如果你還不熟 FILTER 函數，建議可以仔細看過一次，把 FILTER 的用法徹底搞懂，進而提升工作效率。
那麼我們就開始吧！
FILTER 函數用法
以下是 FILTER 的函數說明：
FILTER ( array , include , [if_empty] )
| 引數名稱 | 解釋 |
|---|---|
| array | 選取欲回傳資料的範圍 |
| include | 設定篩選條件 |
| [if_empty] | 若無符合條件的資料，則顯示…（選填） |
使用範例：A 欄是學生姓名，B 欄是性別，C 欄是分數。如果我們要篩選出性別是「女生」的資料，FILTER 函數可以這樣寫：
=FILTER ( A:C , B:B=”女” , “班上無女生” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| array | 選取欲回傳資料的範圍 | A:C |
| include | 設定篩選條件 | B:B=”女” |
| [if_empty] | 若無符合條件的資料，則顯示…（選填） | “班上無女生” |
如果還是不太懂的話，那我們來各個了解 FILTER 的3個引數的規則與限制。
一、array（選取欲回傳資料的範圍）
首先，我們需要先設定 FILTER 函數回傳資料的範圍有多大。
接續前面的例子，我想要回傳的資料有姓名、性別以及分數。因此，FILTER 回傳的範圍就要選擇 A 欄到 C 欄（A:C）。
二、include（設定篩選條件）
選擇回傳資料的範圍後，我們就要設定篩選條件。
在這個引數中需要輸入運算式，運算式的結果只會是「TRUE」或是「FALSE」，如果是 TRUE 就會回傳，而如果是 FALSE 就會被篩掉。
我們想要性別為「女」的資料，因此篩選條件就要設定 B:B=”女”，讓 FILTER 函數回傳所有為 TRUE 的資料。
三、[if_empty]（若無符合條件的資料，則顯示…）
萬一選定範圍中，沒有任何符合篩選條件的資料，FILTER 函數就會顯示第三個引數所設定的值。
我們在第三個引數中輸入”班上無女生”，代表如果 B 欄找不到任何「性別為女」的資料，則顯示「班上無女生」。
按下 Enter 後，FILTER 函數就會篩選出班上所有女生的資料了。
如果 B 欄沒有性別為「女」的資料，FILTER 函數就會顯示第三個引數的值，也就是「班上無女生」。
FILTER 進階用法 – 多個篩選條件
FILTER 函數的用途其實很靈活，配合 UNIQUE 函數還可以做到自動化「提取所有出現過的值」的效果。
而前面的範例介紹了如何篩選出單一條件的方法，接著，我們來看看設定多個篩選條件的 2 種方法。
- 符合 A 條件「且」B 條件：用「*」連接
- 符合 A 條件「或」B 條件：用「+」連接
符合 A 條件「且」B 條件：用「*」連接
如果我想要篩選出國文及格「且」英文及格的資料，在設定篩選條件時，需要用「*」將 2 個條件連接起來：
=FILTER ( A3:C20 , (B3:B20>=60) * (C3:C20>=60) , “無符合資料” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| array | 選取欲回傳資料的範圍 | A1:C20 |
| include | 設定篩選條件 | (B3:B20>=60)*(C3:C20>=60)，國文及格「且」英文及格 |
| [if_empty] | 若無符合條件的資料，則顯示…（選填） | “無符合資料” |
符合 A 條件「或」B 條件：用「+」連接
如果我想要篩選出國文及格「或」英文及格的資料，在設定篩選條件時，則需要用「+」將 2 個條件連接起來：
=FILTER ( A3:C20 , (B3:B20>=60) + (C3:C20>=60) , “無符合資料” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| array | 選取欲回傳資料的範圍 | A1:C20 |
| include | 設定篩選條件 | (B3:B20>=60)+(C3:C20>=60)，國文及格「或」英文及格 |
| [if_empty] | 若無符合條件的資料，則顯示…（選填） | “無符合資料” |
FILTER 常見錯誤
- #SPILL!：FILTER函數輸出的範圍中有不為空的格子，將FILTER下方或右邊的格子清空即可排除。
- #CALC!：沒有符合篩選條件的資料又沒有設定 [if_empty] 時，就會顯示#CALC!。
- #REF!：FILTER的資料來源消失時顯示。
- #NAME!：由於舊版EXCEL不支援FILTER函數，遇到FILTER函數則會顯示#NAME!。