在 Excel 2021 版(Office 365)有新增了動態陣列公式,這個為不太理解傳統陣列公式者,在使用公式時帶來了方便性,讓公式的設計更簡化。
使用以下的例子:使用傳統陣列和動態陣列公式列出模糊搜尋清單,來看看其不同之處。
只要輸入一個字,要從資料區中找出含有這個字的儲存格清單。
定義名稱:
選取儲存格A3:A26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。
1. 使用動態陣列
設計公式:
儲存格E4:=FILTER(資料,資料<>SUBSTITUTE(資料,C4,""))
操作:在儲存格E4輸入公式後,動態陣列公式會自動溢出至公式指定的範圍。
條件:資料<>SUBSTITUTE(資料,C4,"")
利用 SUBSTITUTE 函數將資料內容以儲存格C4置換成空字串,如果和原資料不相同,表示含有儲存格C4的內容。
2. 使用傳統陣列
設計公式:
儲存格G4:{=IFERROR(INDEX(資料,SMALL(IF(SUBSTITUTE(資料,$C$4,"")<>資料,ROW(資料)),ROW(1:1))-3),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
操作:複製儲存格G4,貼至儲存格G4:G26。
公式說明可參考:Excel-在儲存格清單中進行模糊搜尋
由以上二個公式來比較,很明顯的區隔,利用 FITLER 函數(2021版或Office 365)是可以讓公式簡化且公式更具可讀性。
【延伸學習】
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
【參考資料】
留言列表