學不完．教不停．用不盡

網友問到：在 Excel 的工作表中一個數值清單，其中每個儲存格內有3個以『,』隔開的數字，如何計算每個儲存格內數字的總和？

參考下圖左，A欄中每個儲存格有 3 個數字，並且以 2 個『,』隔開。如果以「資料剖析」工具，手動來將一個儲存格內容，調整為 3 個儲存格，再予以加總。這也是一個不錯的做法。但是，如果想要以公式來進行運算，該如何處理？如果是一個儲存格中有 4 個數字，又該如何處理？

【公式設計與解析】

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)），結果如下：

(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 個數字

如果你不想管儲存格內有幾個數字，而修改儲存格公式，該如何處理？

儲存格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)；...。