2013年5月19日 星期日

Excel-計算多組項目中指定內容的個數和總和(SUMPRODUCT)

在 Excel 中有一個資料表(如下圖),其中的項目由多類內容組合而成(例如:A、B、C、D等),其中以「,」分隔,現在要求取各個內容的個數和總和,該如何處理?

【準備工作】

選取儲存格B1:C22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、數值。

【輸入公式】

(1) 計算含有某內容的個數

儲存格F2:=SUMPRODUCT((NOT((SUBSTITUTE(項目,E2,"")=項目)))*1)

SUBSTITUTE(項目,E2,""):將項目欄位中的含有儲存格E2內容置換為空白("")。

SUBSTITUTE(項目,E2,"")=項目:判斷置換後的內容是否和原內容相同(表示儲存格中不含有該內容),若是則傳回 True,若不是則傳回 False,得到一組 True/False 的陣列。

NOT((SUBSTITUTE(項目,E2,"")=項目)):以 NOT 函數,將上式中的 True/False 的陣列,轉換為 Fasle/True 的陣列。

(NOT((SUBSTITUTE(項目,E2,"")=項目)))*1:將上式的 False/True 陣列,轉換為 0/1 陣列。

透過 SUMPRODUCT 函數,將上式的 1 和 0 加總,即為所求。

 

(2) 計算含有某內容的總和

儲存格G2:=SUMPRODUCT(NOT((SUBSTITUTE(項目,E2,"")=項目))*數值)

原理同(1),只要將(1)中的「*1」改成「數值」,即為所求。

 

【補充資料】

相關函數說明,請參考微軟網站:

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

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

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

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

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

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

 

沒有留言:

張貼留言

檢視其他文章

好康東東