最近讓學生練習在 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)形成的陣列加總。
【補充資料】
詳細函數說明請參閱微軟網站：
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
|
INDIRECT：傳回文字串所指定的參照位址。 |
|
語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
OFFSET：http://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 必須是正數。 |
SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
|
SUMPRODUCT：傳回各陣列中所有對應元素乘積的總和。 |
|
語法：SUMPRODUCT(array1, [array2], [array3], ...) array1：要求對應元素乘積和的第一個陣列引數。 array2, array3,...：要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註：各陣列必須有相同的維度(相同的列數，相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
留言列表