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

文章推薦指數: 80 %
投票人數:10人

如果你使用「篩選」工具,在文字篩選中使用「包含」條件:. 在條件中選擇「包含」+「電子」:. 即可篩選出含有特定 ... 學不完.教不停.用不盡 跳到主文 分享個人電腦教學和回答網友提問解決資料處理與設計問題。

部落格全站分類:數位生活 相簿 部落格 留言 名片 贊助廠商 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個中文字元) 請輸入左方認證碼: 看不懂,換張圖 請輸入驗證碼 送出留言



請為這篇文章評分?