Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

在 Excel 2021 以上版本加入了 FILTER 函數,對於資料篩選的處理幫助很大。

在 Excel 裡,以往「篩選」可以使用一般篩選和進階選等工具來處理,由於使用工具的方式,無法因為原始資料改變而隨之改變篩選結果。因此,使用者也會想要使用公式運算來處理。

現在,有了 FILTER 函數,以前複雜的公式加上陣列公式運算的情形已大為改善了。對於學習 Excel 來處理工作的人,實有如魚得水、如虎添翼的好工具。

FILTER 函數可以參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

 

1. 以單一條件篩選(指定篩選欄位)

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

(1) 定義儲存格範圍的名稱

選取儲存格A3:E27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、店名、產品、代碼、銷售額。

(2) 篩選店名

儲存格G4:=FILTER(店名,產品="果汁機")

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

只要在儲存格G4輸入公式,按下 Enter 鍵後,公式會自動溢出至其他儲存格陣列範圍。

(3) 篩選代碼

儲存格H4:=FILTER(代碼,產品="果汁機")

(4) 篩選銷售額

儲存格I4:=FILTER(銷售額,產品="果汁機")

 

2. 以單一條件篩選(未指定篩選欄位)

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

儲存格G4:=FILTER(A4:E27,產品="果汁機")

因為沒有指定顯示的欄位,所以只要在第一個儲存格輸入公式,公式會自動溢出至其他儲存格。

 

3. 以多個條件 AND 邏輯運算篩選(指定篩選欄位)

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

(1) 篩選店名

儲存格G4:=FILTER(店名,(產品="果汁機")*(代碼="FM15"))

公式中,兩個條件中的「*」,相當於執行邏輯 AND 運算。如果想要更多的條件,只要依此方式,以括弧含括條件,再以「*」連結運算即可。

(2) 篩選代碼

儲存格H4:=FILTER(代碼,(產品="果汁機")*(代碼="FM15"))

(3) 篩選銷售額

儲存格I4:=FILTER(銷售額,(產品="果汁機")*(代碼="FM15"))

 

4. 以多個條件 OR 邏輯運算篩選(指定篩選欄位)

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

(1) 篩選店名

儲存格G4:=FILTER(店名,(產品="果汁機")+(代碼="FM15"))

公式中,兩個條件中的「+」,相當於執行邏輯 OR 運算。如果想要更多的條件,只要依此方式,以括弧含括條件,再以「+」連結運算即可。

(2) 篩選代碼

儲存格H4:=FILTER(代碼,(產品="果汁機")+(代碼="FM15"))

(3) 篩選銷售額

儲存格I4:=FILTER(銷售額,(產品="果汁機")+(代碼="FM15"))

 

5. 以多個條件 AND邏輯 和 OR 邏輯運算篩選(指定篩選欄位)

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

(1) 篩選店名

儲存格G4:=FILTER(店名,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

公式中,如果同時有「*」和「+」,因為邏輯 AND 運算優先於邏輯 OR 運算。所以會先執行「*」運算,再執行「+」運算。

(2) 篩選代碼

儲存格H4:=FILTER(代碼,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

(3) 篩選銷售額

儲存格I4:=FILTER(銷售額,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

 

6. 手動進階篩選

如果你是以手動的進階篩選來執行,結果如下圖:

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

手動進階篩選的操作和以FILTER函數來操作,其差別在於:

(1) 原始資料變動,FILTER函數結果會隨之變動。

(2) 原始資料變動,手動進階篩選必須重新操作才能隨之變動。

當你想要執行含有 AND 邏輯和 OR 邏輯運算時,在進階篩選裡必須將 AND 邏輯運算者放在同一列,要將 OR 邏輯運算者放在不同列。

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

 

【延伸閱讀:FILTER函數篩選應用】

Excel-以FILTER函數進行模糊篩選

Excel-下拉式清單選取月份列出該月日期

 Excel-根據單條件和雙條件篩選資料(FILTER)

 Excel-列出指定星期幾的日期

 Excel-單一欄位篩選與跨欄位篩選

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-由資料清單中篩選一組

 Excel-利用FILTER函數模糊篩選

 Excel-從日期清單中區別平日和假日計算總和

 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

 

【延伸閱讀:手動進階篩選應用】

 Excel-進階篩選

 Excel-在進階篩選中使用公式運算

 Excel-進階篩選解析(AND,OR運算)

 Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

 Excel-研習練習範例(篩選)

 Excel-資料篩選-6

 Excel-使用進階篩選功能來移除重覆的資料

 

學不完.教不停.用不盡文章列表

arrow
arrow

    vincent 發表在 痞客邦 留言(0) 人氣()