網友想要根據 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 函數將其顯示為空字串。

文章標籤
創作者介紹

學不完.教不停.用不盡

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