2014年1月19日 星期日

Excel-查詢後建立含有特定字串的清單(使用公式)

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

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

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

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

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

【準備工作】

選取儲存格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 的資料,是因為公式已查不到其他對應的資料了。

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

沒有留言:

張貼留言

檢視其他文章

好康東東