網友想要根據 Excel 工作表中的基本資料（如下圖），列出含有指定字串的資料清單，該如何處理？
例如下圖中，要列出每一項目中含有「BBB」字串者，其中有可能某一項中有一個以上符合。為了方便說明，並且簡化公式，特別使用「輔助欄位」。
【公式設計與解析】
1. 輔助欄位
儲存格I2：{=SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格I2，貼至儲存格I2:I13。
(1) IF(FIND($B$16,B2:H2)>1,1,0)
在陣列公式中利用 FIND 函數，找出是否同一列中有一個（含）以上包含指定字串的內容。若是（TRUE），則傳回 1，若否（FALSE），則傳回 0。（注意：FIND 函數若是儲存格中找不到含有指定字串，則會傳回錯誤訊息。）
(2) IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),"")
利用 IFERROR 函數將傳回值是錯誤訊者，轉換為空字串。
(3) SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))
在陣列公式中，以 SUM 函數將傳回的 1／0 予以相加。
2. 列出清單
你可以使用「篩選」功能將含有特定字串的項目列出，以下要以公式方式來產生。
儲存格A17：{=IFERROR(OFFSET($A$1,SMALL(IF($I$2:$I$13>0,
ROW($A$2:$A$13),""),ROW(1:1))-1,COLUMN(A:A)-1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格A17，貼至儲存格A17:H27。
(1) IF($I$2:$I$13>0,ROW($A$2:$A$13),"")
判斷儲存格I2:I13中是否大於0（表示含有指定字串），若是，則傳回其列號；若否，則傳回空字串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數由小至大找出符合者。（ROW(1:1)向下複製時，會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。）
(3) OFFSET($A$1,第(2)式-1,COLUMN(A:A)-1)
將第(2)式的結果代入 OFFSET 函數，找出以儲存格A1起始的對應儲存格內容。
(4) IFERROR(第(3)式,"")
因為 SMALL 函數可能傳回錯誤訊息，所以使用 IFERROR 函數將其顯示為空字串。
【延伸閱讀：FILTER函數篩選應用】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表