2017年5月30日 星期二

Excel-列出含有特字元的清單(SMALL,ROW,COLUMN,陣列公式)

網友想要根據 Excel 工作表中的基本資料(如下圖),列出含有指定字串的資料清單,該如何處理?
例如下圖中,要列出每一項目中含有「BBB」字串者,其中有可能某一項中有一個以上符合。為了方便說明,並且簡化公式,特別使用「輔助欄位」。
Excel-列出含有特字元的清單(SMALL,ROW,COLUMN,陣列公式)

【公式設計與解析】
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 函數將其顯示為空字串。

沒有留言:

張貼留言

好康東東