回答網友提問:
在 Excel 中有一個資料清單(如下圖左),其中每個儲存格有一個以上的數字,並以「、」隔開,如何計算每個數字出現的次數?
參考下圖,本例先利用輔助欄位將每個儲存格內容分離至多個儲存格中。資料清單儲存格範圍為A1:A55。
【公式設計與解析】
1. 將儲存格內容分離至多個儲存格
儲存格B2:=SUBSTITUTE(MID(SUBSTITUTE($A2,"、",REPT(" ",20)),
(COLUMN(A:A)-1)*20+1,20)," ","")
複製儲存格B2,貼至儲存格B2:F55。
(1) SUBSTITUTE($A2,"、",REPT(" ",20))
利用 SUBSTITUE 函數將儲存格A2的內容裡的「、」以20個空格取代。
(REPT(" ",20)可以產生20個空格)
(2) MID(第(1)式,(COLUMN(A:A)-1)*20+1,20)
(COLUMN(A:A)-1)*20+1向右複製時會產生 1, 21, 41, 61, 81。
利用 MID 函數從分別第 1, 21, 41, 61, 81 個位置取20個字元。
(3) SUBSTITUTE(第(2)式," ","")
利用 SUBSTITUE 函數將第(2)式取得的20個字元裡的空格,以空字串取代。。
2. 計算個數
儲存格I2:=SUMPRODUCT(($B$2:$F$55=H2&"")*1)
利用 SUMPRODUCT 函數計算儲存格範圍B2:F55裡和儲存格H2相同者的數量。
小技巧:公式裡的「H2&""」是因為 MID 函數取得的是文字,而在儲存格H2中的是數字,所以「H2&""」可以將數字轉換為文字,如此計算才能正確。
留言列表