有網友問到:在 Excel 的資料表中,某些儲存格含有一些以逗號隔開的數字,要如何計算每個儲存格中的數字總和及每個數字出現的次數?(參考下圖)
這個題目要用到陣列的觀念,不過使用 SUMPRODUCT 函數很容易就可以解決了。本例以每個數字的大小為 0 ~ 9 之範圍來說明,每個儲存格固定有 9 個數字。
(1) 計算儲存格中所有數字的總和
儲存格B3:=SUMPRODUCT(VALUE(MID(A3,ROW($1:$9)*2-1,1)))
ROW($1:$9)*2-1:產生 1, 3, 5, … ,15, 17 的數字陣列。
MID(A3,ROW($1:$9)*2-1,1)):在儲存格A3中,每間隔 2 (1, 3, 5, … ,15, 17),取出 1 個字。
VALUE(MID(A3,ROW($1:$9)*2-1,1)):將上式取出的文字轉換為數字。
再透過 SUMPRODUCT 函數,計算陣列數字的總和。
(2) 計算儲存格中每個數字出現的次數
儲存格C3:=SUMPRODUCT((VALUE(MID($A3,ROW($1:$9)*2-1,1))=C$2)*1)
複製儲存格C3,貼至儲存格C3:K12。
原理同 (1),再加入一個條件:
VALUE(MID($A3,ROW($1:$9)*2-1,1))=C$2:判斷取得的數字和第 2 列中的數字是否一樣。
(VALUE(MID($A3,ROW($1:$9)*2-1,1))=C$2)*1:將上式的判斷結果(True/Fasle 陣列),轉換為數字的陣列(1/0)。
再透過 SUMPRODUCT 函數,計算陣列數字的總和。
【補充資料】
相關之詳細函數說明,請參閱微軟網站:
SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。 |
語法:SUMPRODUCT(array1, [array2], [array3], ...) array1:要求對應元素乘積和的第一個陣列引數。 array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |