在 Excel 的工作表中有一組數據清單(參考下圖),如何列出列表中含有指定字符的數據?
例如:要找出數據中含有 T、N、R 的清單。
【公式設計與解析】
選取單元格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)可能傳回的錯誤訊轉換為空字符串(空白)。
閱讀更多 老徐漫談 的文章