回答網友提問:

在 Excel 中有一個資料清單(如下圖左),其中每個儲存格有一個以上的數字,並以「、」隔開,如何計算每個數字出現的次數?

參考下圖,本例先利用輔助欄位將每個儲存格內容分離至多個儲存格中。資料清單儲存格範圍為A1:A55。

Excel-計算中由多個數字組成的儲存格裡每個數字的總數(SUBSTITUTE,MID,REPT,SUMPRODUCT)

 

【公式設計與解析】

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&""」可以將數字轉換為文字,如此計算才能正確。

學不完.教不停.用不盡文章列表

arrow
arrow

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