學不完．教不停．用不盡

以下的例子來練習陣列的觀念。在 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-分離儲存格中的文字和數字