2013年1月9日 星期三

Excel-分組小計(SUMPRODUCT)

在 Excel 中有一個數列,常會踫到要依不同分組來計算總和。參考下圖的二種分組方式來計算總和,該如何處理?

1. 項次每間隔 5 個為一組

2.項次連續 5 個為一組

選取儲存格B1:B26,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。

1. 項次每間隔 5 個數值為一組

儲存格E2:=SUMPRODUCT(數值*(MOD(ROW(數值)-2,5)=ROW(1:1)-1))

MOD(ROW(數值)-2,5):利用 MOD 函數以求餘數方式取得「0,1,2,3,4,0,1,2,3,4, ...」的陣列。

MOD(ROW(數值)-2,5)=ROW(1:1)-1:取得餘數為 0 者的 True/Fasle 陣列。再透過 SUMPRODUCT 函數計算總和。

複製儲存格E2,貼至儲存格E2:E6。

 

2.項次連續 5 個數值為一組

儲存格E9:=SUMPRODUCT(數值*(INT((ROW(數值)-2)/5+1)=ROW(1:1)))

INT((ROW(數值)-2)/5+1):利用 INT 函數以求商數方式取得「1,1,1,1,1,2,2,2,2,2, ...」的陣列。

INT((ROW(數值)-2)/5+1)=ROW(1:1):取得商數為 1 者的True/Fasle 陣列。再透過 SUMPRODUCT 函數計算總和。

複製儲存格E9,貼至儲存格E9:E13。

 

【補充資料】

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

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

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

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

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

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

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

沒有留言:

張貼留言

檢視其他文章

好康東東