網友問到:如何在 Excel 的工作表中,將符合條件的多個儲存格內容集合在一個儲存格中?(如下圖)

例如:將項目『甲』的編號集合成『W355, O237, N493』,該如何處理?

Excel-將符合條件的多個內容集合在一個儲存格(MATCH,OFFSET,SUBSTIITUE,COUNTIF)

 

【公式設計與解析】

上圖中的項目和編號是相鄰的兩欄,且相同項目的編號是連續排列在一起。

選取儲存格A1:A25,按 Ctrl+Shfit+F3 鍵,定義名稱:項目。

儲存格E2:=MID(SUBSTITUTE(PHONETIC(OFFSET($A$1,MATCH(D2,項目,0),0,COUNTIF(項目,D2),2)),D2,", "),3,9999)

複製儲存格E2,貼至儲存格E2:E9。

(1) MATCH(D2,項目,0)

利用 MATCH 函數找出儲存格D2在項目儲存格陣列中的第幾個位置。

(2) COUNTIF(項目,D2)

利用 COUNTIF 函數找出儲存格D2在項目儲存格陣列中共有幾個。

(3) OFFSET($A$1,第(1)式,0,第(2)式,2)

將第(1)式和第(2)式代入 OFFSET 函數找出符合條件的儲存格範圍。

Excel-將符合條件的多個內容集合在一個儲存格(MATCH,OFFSET,SUBSTIITUE,COUNTIF)

(4) PHONETIC(第(3)式)

將第(3)傳回的儲存格範圍內的儲存格內容,利用 PHONETIC 函數串接在一起。

本例得到:甲W355甲O237甲N493

(5) SUBSTITUTE(第(4)式,D2,", ")

利用 SUBSTITUTE 函數將上式的結果中的儲存格D2,全部置換『, 』(逗號+空格)

本例得到:, W355, O237, N493

(6) MID(第(5)式,3,9999)

利用 MID 函數將上式中的第一個置換『, 』(逗號+空格)

本例得到:W355, O237, N493

arrow
arrow

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