2017年11月16日 星期四

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

網友問到:如何在 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

沒有留言:

張貼留言

檢視其他文章

好康東東