有網友問到:如果想要在一個資料表中,查詢含有某些特定字串的資料搜集在一個清單中,該如何處理?(原始資料參考下圖左,篩選後清單參考下圖右。)

如果你使用「篩選」工具,在文字篩選中使用「包含」條件:

在條件中選擇「包含」+「電子」:

即可篩選出含有特定字串的資料。

如果你想使用公式來篩選,參考以下的作法:

【準備工作】

選取儲存格A1:B26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、內容。

【輸入公式】

(1) 先求內容(含有:電子)

儲存格E2:{=OFFSET($B$1,SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1))-1,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

FIND("電子",內容)>=1:判斷內容各個儲存格中是否含有「電子」,如果有,會傳回一個數字(位置),如果沒有,則會傳為 #VALUE!(錯誤訊息)。

IF(FIND("電子",內容)>=1,ROW(內容),):如果 FIND 有傳回值(含有「電子」),則顯示該儲存格所有列號,否則傳回空的內容。

IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999):使用 IFERROR 函數處理如果傳回值是 #VALUE! 錯誤訊息時,顯示 9999。這只是一個較大的數即可,必須要大於所有資料的列數。

SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1)):當向下複製時會傳回第 1 小值、第 2 小值、第 3 小值、…。該值為一個列號。

最後透過 OFFSET 函數,代入上式所傳回的列號,即可產生一個動態的儲存格位址,顯示的內容即為所求。

(2) 再求日期(含有:電子)

儲存格D2:{=OFFSET($A$1,SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1))-1,)}

公式之原理與上式完全相同,只須改變 OFFSET 的起始參考值。

圖中有些顯示 1900/01/00 和 0 的資料,是因為公式已查不到其他對應的資料了。

你也可以試試練習含有「電腦」的篩選清單。

 

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

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

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

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

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

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

圖片1 Excel-利用FILTER函數模糊篩選

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

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

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

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

arrow
arrow
    全站熱搜

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