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

... 的資料搜集在一個清單中，該如何處理？(原始資料參考下圖左，篩選後清單參考下圖右。

) 如果你使用「篩選」工具，在文字篩選中使用「包含」條件： 學不完．教不停．用不盡 跳到主文 分享個人電腦教學和回答網友提問 (Google,Windows,雲端應用) (Excel,Word,PowerPoint) 部落格全站分類：數位生活 相簿 部落格 留言 名片 贊助廠商 Jan19Sun201417:56 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的資料，是因為公式已查不到其他對應的資料了。

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

全站熱搜 創作者介紹 vincent 學不完．教不停．用不盡 vincent發表在痞客邦留言(6)人氣() 全站分類：數位生活個人分類：講義資料此分類上一篇：Word-將一段文字放在多個物件中 此分類下一篇：Excel-產生間隔重覆數列(MOD,INT) 上一篇：Word-將一段文字放在多個物件中 下一篇：Excel-產生間隔重覆數列(MOD,INT) 歷史上的今天 2020:在Windows10和Android手機中使用Emoji符號 2020:GoogleKeep-讓記事每個月或每一週自動移至最頂端 2019:Google試算表-設計有人填答時以電子郵件通知 2016:使用PowerPoint做為照片轉影片工具 2015:Excel-複製圖表格式免去重做的麻煩 2012:Excel-多條件的查詢(INDEX+SMALL+陣列) ▲top 留言列表 發表留言 到站人數 本日人氣： 累積人氣： 內建文章關鍵字搜尋 熱門文章 回到頁首 回到主文 免費註冊 客服中心 痞客邦首頁 ©2003-2022PIXNET 關閉視窗 PIXNET Facebook Yahoo! Google MSN {{guestName}} (登出) 您尚未登入，將以訪客身份留言。

亦可以上方服務帳號登入留言 請輸入暱稱(最多顯示6個中文字元) 請輸入標題(最多顯示9個中文字元) 請輸入內容(最多140個中文字元) 請輸入左方認證碼： 看不懂,換張圖 請輸入驗證碼 送出留言