有網友問到:在 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 函數,計算陣列數字的總和。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()