宣告：本內容由Perplexity在Comet瀏覽器中以助手功能自動完成。
Excel FILTER函數完全攻略：從基礎到進階應用
在資料處理的世界中，篩選功能一直是Excel用戶最常使用的功能之一。隨著Microsoft 365和Office 2021版本的推出，Excel引入了革命性的FILTER函數，讓資料篩選變得更加靈活且強大。本文將帶您深入了解FILTER函數的各種應用方式，從基本語法到進階技巧，讓您能夠輕鬆駕馭這個動態陣列時代的必學函數。
FILTER函數簡介
FILTER函數是Excel動態陣列功能的核心函數之一，它能夠根據指定的條件動態篩選資料範圍，並返回符合條件的所有資料。與傳統的篩選功能不同，FILTER函數會自動調整輸出結果的大小，當源資料發生變化時，篩選結果也會即時更新，這就是所謂的「動態陣列」特性。
FILTER函數的優勢
- 動態更新：當源資料變更時，篩選結果會自動更新
- 多條件篩選：支援複雜的篩選條件組合
- 保持原始格式：篩選後的資料保持原有的格式設定
- 靈活應用：可與其他函數結合使用，創造更複雜的應用
FILTER函數語法結構
FILTER函數的基本語法如下：
=FILTER(array, include, [if_empty])
參數說明
| 參數名稱 | 類型 | 說明 |
|---|---|---|
| array | 必要 | 要篩選的陣列或範圍 |
| include | 必要 | 篩選條件（布林陣列，TRUE/FALSE） |
| if_empty | 選用 | 當沒有符合條件的資料時顯示的值 |
參數詳細解析
array（篩選範圍）
這個參數定義了要篩選的資料範圍。可以是單一欄位、多個欄位，甚至是整個表格。範圍的選擇決定了最終輸出結果包含哪些欄位。
include（篩選條件）
這是FILTER函數的核心部分，用來設定篩選條件。條件必須返回TRUE或FALSE的布林值陣列。只有對應TRUE值的資料行會被包含在結果中。
if_empty（空值處理）
當篩選條件沒有找到任何符合的資料時，可以設定要顯示的替代內容，避免出現錯誤訊息。
基礎應用範例
讓我們透過實際範例來理解FILTER函數的運作方式。假設我們有一份員工資料表，包含姓名、部門、薪資和入職日期等欄位。
範例1：單一條件篩選
假設我們要篩選出所有屬於「業務部」的員工資料：
=FILTER(A:D, B:B="業務部", "查無業務部員工")
這個公式的含意是：
- 篩選A到D欄的資料
- 條件是B欄（部門欄）等於「業務部」
- 如果沒有找到符合條件的資料，則顯示「查無業務部員工」
範例2：數值條件篩選
如果要篩選薪資超過50000的員工：
=FILTER(A:D, C:C>50000, "無符合薪資條件的員工")
範例3：日期條件篩選
篩選2023年後入職的員工：
=FILTER(A:D, D:D>DATE(2023,1,1), "無2023年後入職員工")
進階應用技巧
多條件篩選（AND邏輯）
當需要同時滿足多個條件時，可以使用乘法運算符（*）來組合條件：
=FILTER(A:D, (B:B="業務部")*(C:C>40000), "無符合條件的員工")
這個公式會篩選出既屬於業務部，薪資又超過40000的員工。
多條件篩選（OR邏輯）
當需要滿足任一條件時，可以使用加法運算符（+）：
=FILTER(A:D, (B:B="業務部")+(B:B="行銷部"), "無符合部門條件的員工")
文字包含條件篩選
使用SEARCH或FIND函數來篩選包含特定文字的資料：
=FILTER(A:D, ISNUMBER(SEARCH("經理",A:A)), "無經理職位")
結合其他函數的進階應用
與SUM函數結合
=SUM(FILTER(C:C, B:B="業務部", 0))
計算業務部的總薪資。
與UNIQUE函數結合
=UNIQUE(FILTER(B:B, C:C>50000, "無高薪員工"))
列出薪資超過50000的部門（去除重複）。
常見錯誤及解決方案
錯誤1：#CALC!錯誤
這通常發生在篩選條件的範圍與資料範圍不匹配時。
解決方案：確保條件範圍的行數與資料範圍相同。
錯誤2：#VALUE!錯誤
這可能是由於參數類型不正確造成的。
解決方案：檢查所有參數是否為正確的資料類型。
錯誤3：#SPILL!錯誤
這表示輸出範圍被其他資料阻擋。
解決方案：清理輸出範圍周圍的儲存格，或將公式移到空白區域。
效能優化建議
- 避免使用整欄引用：在大型資料集中，使用具體範圍而非整欄引用（如A:A）
- 預先排序資料：對經常篩選的欄位進行排序可提升效能
- 使用表格結構：將資料轉換為表格格式，便於管理和引用
- 合理使用if_empty參數：避免複雜的嵌狀函數作為空值處理
實際應用場景
銷售資料分析
=FILTER(銷售資料, (月份="1月")*(地區="北區"), "該月該地區無銷售資料")
庫存管理
=FILTER(商品清單, 庫存數量<安全庫存, "庫存充足")
學生成績統計
=FILTER(學生資料, (科目="數學")*(分數>=80), "無優秀數學成績")
與傳統篩選方式的比較
| 功能 | FILTER函數 | 傳統篩選 |
|---|---|---|
| 動態更新 | ✓ | ✗ |
| 複製結果 | ✓ | 需手動 |
| 多重篩選 | ✓ | 複雜設定 |
| 公式整合 | ✓ | 困難 |
| 學習曲線 | 中等 | 簡單 |
結論
FILTER函數代表了Excel資料處理能力的重大躍進，它不僅簡化了複雜的篩選操作，更為資料分析帶來了前所未有的靈活性。掌握FILTER函數的各種應用技巧，將大幅提升您的工作效率，並為更深入的資料分析奠定堅實基礎。
無論您是Excel新手還是進階用戶，FILTER函數都值得您深入學習和實踐。隨著動態陣列功能的普及，這類函數將成為未來資料處理的標準工具。建議您從基本應用開始，逐步嘗試更複雜的組合應用，最終將這個強大的工具完全融入您的日常工作流程中。
本文內容將持續更新，敬請關注最新的Excel功能發展。
留言列表