2012年11月3日 星期六

Excel-分組小計(INDIRECT, OFFSET, SUMPRODUCT)

在 Excel 中常會有一些數列需要分組小計,以下列舉二種公式可以對固定間隔位置的數列加總小計,例如每5個數為一組。(參考下圖)

image

(1) 使用 INDIRECT

儲存格E1:=SUM(INDIRECT("B" & (ROW(B1)-1)*5+1 & ":B" & (ROW(B1)-1)*5+5))

複製儲存格E1,貼至儲存格E1:E5。

(ROW(B1)-1)*5+1:往下複製時會產生 1、6、11、…。

(ROW(B1)-1)*5+5:往下複製時會產生 5、10、15、…。

再透過 INDIRECT 函數將位址字址轉成可用位址,例如:B1:B5、B6:B10、B11:B15、…。

藉由 SUM 函數做加總。

 

(2) 使用 OFFSET

儲存格E1:=SUM(OFFSET($B$1,(ROW(B1)-1)*5,,5,))

複製儲存格E1,貼至儲存格E1:E5。

(ROW(B1)-1)*5:往下複製時會產生 0、5、10、…。

再透過 OFFSET 函數將位址字址轉成可用位址,例如:B1:B5、B6:B10、B11:B15、…。

藉由 SUM 函數做加總。

 

(3) 使用 SUMPRODUCT

儲存格E1:=SUMPRODUCT((B1:B25)*(INT(ROW(B1:B25)/5.1)=(ROW(B1)-1)))

複製儲存格E1,貼至儲存格E1:E5。

INT(ROW(B1:B25)/5.1):因為每 5 個數為一組,當 ROW(B1:B25) 除以 5 時,會產生的整數商為 0,0,0,0,1,1, …,如果除以 5.1 的整數商,則會產生的整數商為 0,0,0,0,0,1, …。

INT(ROW(B1:B25)/5.1)=(ROW(B1)-1):找出上式為0,1,2,3,4的陣列。

透過 SUMPRODUCT 函數,計算儲存格B1:B25符於條件的陣列和。

 

【補充資料】

詳細函數說明請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

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

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

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

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

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

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

沒有留言:

張貼留言

檢視其他文章

好康東東