2011年11月1日 星期二

Excel-加總多個文數字儲存格中的數字(SUMPRODUCT+陣列)

以下的例子來練習陣列的觀念。在 Excel 的工作表中要將多個儲存格中的數字分離出來並加總,該如何處理?(參考下圖,每個數字前有7碼的文字)

 

【方法一】

儲存格A8:=SUMPRODUCT(RIGHT(A2:A6,LEN(A2:A6)-7)*1)

因為 SUMPRODUCT 函數即以陣列觀念在處理,檢視下圖可以發現:

RIGHT(A2:A6,LEN(A2:A6)-7):可以取得每個儲存格中數字的陣列({ 120, 9, 56, 111, 27 }),此時的數字在儲存格中被視為「文字」。

透過 SUMPURODUCT 函數,將上述陣列乘以 1,可以將文字轉換為數字後再求總合。

【方法二】

儲存格A8:{=SUM(RIGHT(A2:A6,LEN(A2:A6)-7)*1)}

此為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

此公式利用陣公式做法以 SUM 函數來執行陣列效果。

 

【思考】

試比較這兩個運算公式

(1) =SUMPRODUCT(RIGHT(A2:A6,LEN(A2:A6)-7)*1)

(2) {=SUM(RIGHT(A2:A6,LEN(A2:A6)-7)*1)}

公式是否很相近,這兩種做法都可以不需再透過輔助欄位來先分離文字和數字,再對數字加總。

其結果和公式「=SUM({120,9,56,111,27})」會得到相同結果。(以 { } 含括的部分也是陣列)

應用相同原理,可得下列運算式:

{=SMALL(RIGHT(A2:A6,LEN(A2:A6)-7)*1,2)}:取出多個儲存格中第2小的數字。

{=MAX(RIGHT(A2:A6,LEN(A2:A6)-7)*1)}:取出多個儲存格中最大的數字。

 

【延伸學習】

關於將文字和數字分離的說明,可參考另一篇文章:

火星人的天空之城- Excel-分離儲存格中的文字和數字

沒有留言:

張貼留言

檢視其他文章

好康東東