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

先前的文章:Excel-利用FILTER函數模糊篩選,提到了模糊篩選,本篇要繼續探討各種做法。因為在 FILTER 函數無法使用萬用字元「*」,因此,要以模擬方式來設計,以達到篩選的效果。

 

【篩選:楊梅國中】

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

選取儲存格A3:F120,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、姓名、生日、性別、國中、地址。

儲存格I4:=FILTER(姓名,國中="楊梅國中")

在「國中」欄位中篩選合於「條件:楊梅國中」者。

 

【篩選:仁*國中】

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

儲存格I4:=FILTER(國中,(LEFT(國中,1)="仁")*(RIGHT(國中,2)="國中"))

因為要模擬「*」萬用字元的概念,在公式中以雙條件來設計:

條件一:LEFT(國中,1)="仁"

條件二:RIGHT(國中,2)="國中"

即表示在國中欄位中要篩選合於「仁」開頭,而「國中」結尾者。

 

【篩選:新屋區】

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

儲存格I4:=FILTER(地址,SUBSTITUTE(地址,"新屋區","")<>地址)

在 SUBSTITUTE 函數中,將地址裡的「新屋區」置換為空字串,若和原地址不相同者,表示地址中含有「新屋區」。

或是

儲存格I4:=FILTER(地址,IFERROR(FIND("新屋區",地址),0)>0)

本例以 FIND 函數來判斷在「地址」欄位中是否含有「新屋區」,若有,則傳回一個數字(位置),若無,則傳回錯誤訊息。所以,再以 IFERROR 函數將錯誤訊息轉為「0」。

因此,只要判斷傳回值是大於 0 者,表示地址中含有「新屋區」。

 

【篩選:八月出生】

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

儲存格I4=FILTER(姓名,MID(生日,4,2)="08")

利用 MID 函數取出「姓名」欄位中第 4 碼起始的 2 碼文字,判斷是否為「08」。

以此做為篩選的條件。

 

【篩選:八月出生的女生】

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

儲存格I4:=FILTER(姓名,(MID(生日,4,2)="08")*(性別="女"))

在此以雙條件來判斷是否為 8 月出生的女生。

條件一:MID(生日,4,2)="08",判斷生日的月份是否為「08」。

條件二:性別="女",判斷性別是否為「女」。

以這二個條件執行邏輯AND運算,做為篩選的條件。

 

【篩選:93年出生住中壢區】

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

儲存格I4:=FILTER(姓名,(MID(生日,1,2)="93")*(SUBSTITUTE(地址,"中壢區","")<>地址))

條件一:MID(生日,1,2)="93",以 MID 函數判斷第1,2碼是否為「93」。

條件二:SUBSTITUTE(地址,"中壢區","")<>地址,置換地址中的「中壢區」,如果和原地址不相同,則表示地址中含有「中壢區」。

以這二個條件執行邏輯AND運算,做為篩選的條件。

或是

儲存格I4:=FILTER(姓名,(MID(生日,1,2)="93")*(IFERROR(FIND("中壢區",地址),0)))

條件一:MID(生日,1,2)="93",以 MID 函數判斷第1,2碼是否為「93」。

條件二:IFERROR(FIND("中壢區",地址),0),以 FIND 函數判斷「地址」是否包含「中壢區」。

以這二個條件執行邏輯AND運算,做為篩選的條件。

 

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

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

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

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

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

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

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

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

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

 Excel-利用FILTER函數模糊篩選

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

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

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

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

【參考資料】

FILTER 函數參考微軟提供的說明網頁:FILTER 函數
SUBSTITUTE 函數參考微軟提供的說明網頁:SUBSTITUTE 函數
FIND,FINDB 函數參考微軟提供的說明網頁:FIND,FINDB 函數
LEFT,LEFTB 函數參考微軟提供的說明網頁:LEFT,LEFTB 函數
RIGHT,RIGHTB 函數參考微軟提供的說明網頁:RIGHT,RIGHTB 函數
MID,MIDB 函數參考微軟提供的說明網頁:MID,MIDB 函數
 

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

arrow
arrow

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