2017年5月4日 星期四

Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)

網友問到:在 Excel 的工作表中一個數值清單,其中每個儲存格內有3個以『,』隔開的數字,如何計算每個儲存格內數字的總和?
參考下圖左,A欄中每個儲存格有 3 個數字,並且以 2 個『,』隔開。如果以「資料剖析」工具,手動來將一個儲存格內容,調整為 3 個儲存格,再予以加總。這也是一個不錯的做法。但是,如果想要以公式來進行運算,該如何處理?如果是一個儲存格中有 4 個數字,又該如何處理?
Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)
【公式設計與解析】
1. 儲存格內有 3 個數字
儲存格B2:
{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW($1:$3)-1)*20+1,20))}
這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格B2,貼至儲存格B2:B18。
(1) SUBSTITUTE(A2,",",REPT(" ",20))
假設每個數字都在數個位數以內,參數 20 是隨意的一個很大的數。
利用 SUBSTITUE 函數,將儲存格A2內容中的『,』全都置換為 20 個空格(REPT(" ",20)),結果如下:
image
(2) MID(第(1)式,(ROW($1:$3)-1)*20+1,20)
在陣列公式中,ROW($1:$3)={1,2,3},透過 MID 函數,藉以取出儲存格中,第 1, 21, 41 個字開始的20個字元。(本例可得:18__________________、17__________________、14__________________,其中『_』表示一個空格)
(3) 1*MID(第(1)式,(ROW($1:$3)-1)*20+1,20)
將第(2)式乘以 1,結果可以將取得的 3 個 20 字元的文字轉換為數字(本例可得:18、17、14)。
(4) SUM(1*第(2)式)
在陣列公式中,透過 SUM 函數將 3 個數字(=18、17、14)予以加總(=49)。

2. 儲存格內有 4 個數字
若儲存格內改為有 4 個數字,儲存格E2:
{=SUM(1*MID(SUBSTITUTE(D2,",",REPT(" ",20)),(ROW($1:$4)-1)*20+1,20))}
這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:E18。
公式原理同『1. 儲存格內有 3 個數字』,只是將公式中 ROW($1:$3) 改成 ROW($1:$4) 即可。

3. 儲存格內有 n 個數字
如果你不想管儲存格內有幾個數字,而修改儲存格公式,該如何處理?
Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)
儲存格B2:{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW(INDIRECT
("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*20+1,20))}
這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格B2,貼至儲存格B2:B18。
將原公式 ROW($1:$3) 改為:
ROW(INDIRECT("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1
如果儲存格有4個『,』,則會產生 ROW($1:$5);如果儲存格有6個『,』,則會產生 ROW($1:$7);...。

沒有留言:

張貼留言

好康東東