2013年3月19日 星期二

Excel-個數不固定之加總

最近讓學生練習在 Excel 中對於不固定個數之加總,即給予一個指定數,計算由第1個開始的累加結果。

(1) 使用 OFFSET 函數

儲存格F2:=SUM(OFFSET(B2,,,F1))

以相對位址概念,使用 OFFSET 函數找出一段儲存格範圍。

(2) 使用 INDIRECT 函數

儲存格F2:=SUM(INDIRECT("B2:B"&(F1+1)))

使用 INDIRECT 函數轉換文字位址為真實位址(因為資料由第 2 列開始,所以要再加 1)。

(3) 使用 SUMPRODUCT 函數

儲存格F2:=SUMPRODUCT((B2:B16)*(ROW(B2:B16)<=F1+1))

找出資料所在列號小於指定統計個數(因為資料由第 2 列開始,所以要再加 1)形成的陣列加總。

 

【補充資料】

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

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來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東