贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

(網友提問)在 Excel 的工作表中有一組資料清單(參考下圖),如何列出清單中含有指定字元的資料?

例如:要找出資料中含有 T、N、R 的清單。

Excel-根據清單中列出含有指定字元的資料(OFFSET,SUBSUTITUTE)

 

【公式設計與解析】

選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

儲存格C2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(資料,C$1,"")<>
資料,ROW(資料),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格C2,貼至儲存格C2:E18。

(1) SUBSTITUTE(資料,C$1,"")

利用 SUBSTITUTE 函數將資料陣列中每個儲存格都以儲存格C1的內容用空白取代。

(2) IF(第(1)式<>資料,ROW(資料),"")

對資料陣列中的每個儲存格判斷第(1)式的傳回結果,如果取代後的結果和原來的內容不一樣,表示含有儲存格C1內容,則傳該儲存格的列號;否則傳回空字串(空白)。(ROW 函數可傳儲存格的列號)

(3) SMALL(第(2)式,ROW(1:1))

當公式向下各列複製時,將第(2)式傳回的列號利用 SMALL 函數由小至大取出。

(4) OFFSET($A$1,第(3)式-1,0)

將第(3)式傳回的列號代入 OFFSET 函數取得由儲存格A1起始所對應的儲存格內容。

(5) IFERROR(第(4)式,"")

利用 IFERROR 函數將第(3)可能傳回的錯誤訊轉換為空字串(空白)。

文章標籤
創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 訪客
  • 假如資料像欄A一樣亂數排列但有重覆資料
    在沒有指定字元的情況下
    有辦法代出欄A有哪幾種資料嗎?(不重覆)

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼