2018年1月6日 星期六

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

(網友提問)在 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)可能傳回的錯誤訊轉換為空字串(空白)。

沒有留言:

張貼留言

檢視其他文章

好康東東