使用SUM和OFFSET查找數據動態範圍的總計
如果Excel 工作表包含基於單元格更改範圍的計算,則在SUM OFFSET公式中一起使用SUM和OFFSET函數可簡化保持計算更新的任務。
使用SUM和OFFSET函數創建動態範圍
如果您在一段持續變化的時間段內使用計算 - 例如本月的總銷售額 - OFFSET功能允許您設置一個動態範圍,隨著每天的銷售數據的添加而不斷變化。
SUM函數本身通常可以容納插入到正在求和的範圍中的新數據單元。
將數據插入函數當前所在的單元格時會發生一個異常。
在本文附帶的示例圖片中,每天的新銷售數字都會添加到列表的底部,這會強制總數在每次添加新數據時不斷向下移動一個單元格。
如果SUM函數本身用於總計數據,則每次添加新數據時都需要修改用作函數參數的單元格範圍。
然而,通過一起使用SUM和OFFSET函數,總和的範圍變為動態的。 換句話說,它改變以適應新的數據單元。 添加新的數據單元不會導致問題,因為隨著每個新單元格的添加,範圍會不斷調整。
語法和參數
請參閱本文隨附的圖片以跟隨本教程。
在此公式中,SUM函數用於總計提供的數據范圍作為其參數。 該範圍的起始點是靜態的,並且被識別為單元格參考第一個要由公式計算的數字。
OFFSET函數嵌套在SUM函數中,用於為公式計算的數據范圍創建一個動態端點。 這是通過將範圍的終點設置為公式位置上方的一個單元來完成的。
公式的語法 :
= SUM(範圍開始:OFFSET(參考,行,列))範圍開始 - (必填)SUM函數將合計的單元格範圍的起始點。 在示例圖像中,這是單元格B2。
參考 - (必需)用於計算範圍端點的單元格引用位於多行和多列之外。 在示例圖像中, Reference參數是公式本身的單元格引用,因為我們總是希望範圍在公式上方結束一個單元格。
行 - (必填)用於計算偏移量的參考參數上方或下方的行數。 該值可以是正值,負值或設為零。
如果偏移量的位置高於Reference參數,則此值為負值。 如果低於這個值,那麼Rows的說法是肯定的。 如果偏移量位於同一行中,則該參數為零。 在這個例子中,偏移量在Reference參數上面開始一行,所以這個參數的值是負的(-1)。
Cols - (必填)用於計算偏移量的Reference參數左側或右側的列數。 該值可以是正值,負值或設為零
如果偏移量的位置在Reference參數的左側,則該值為負值。 如果在右邊, Cols的論點是肯定的。 在這個例子中,總計的數據和公式在同一列,所以這個參數的值是零。
使用SUM OFFSET公式來總計銷售數據
此示例使用SUM OFFSET公式來返回工作表B列中列出的日銷售數字的總和。
最初,公式被輸入到單元格B6中,並將銷售數據總計四天。
下一步是將SUM OFFSET公式向下移動以騰出第五天的銷售總額。
這是通過插入一個新的第 6 行 ,將公式向下移動到第7行來完成的。
作為移動的結果,Excel會自動將Reference參數更新到單元格B7 ,並將單元格B6添加到由公式求和的範圍中。
輸入SUM OFFSET公式
- 點擊單元格B6,這是公式結果最初顯示的位置。
- 點擊功能區菜單的公式選項卡。
- 從功能區中選擇Math&Trig打開功能下拉列表。
- 點擊列表中的SUM調出函數的對話框 。
- 在對話框中,單擊Number1行。
- 單擊單元格B2將該單元格引用輸入到對話框中。 該位置是公式的靜態終點;
- 在對話框中,單擊Number2行。
- 輸入以下OFFSET函數: OFFSET(B6,-1,0)以形成公式的動態終點。
- 點擊OK完成該功能並關閉對話框。
總值5679.15美元 出現在單元格B7中。
當您單擊單元格B3時,完整函數= SUM(B2:OFFSET(B6,-1,0))出現在工作表上方的公式欄中 。
添加第二天的銷售數據
要添加第二天的銷售數據:
- 右鍵單擊行6的行標題以打開上下文菜單。
- 在菜單中,單擊插入將新行插入到工作表中。
- 因此,SUM OFFSET公式向下移動到單元格B7,現在第6行是空的。
- 點擊單元格A6 。
- 輸入數字5表示第五天的銷售總額將被輸入。
- 點擊單元格B6。
- 輸入數字$ 1458.25並按下鍵盤上的Enter鍵。
B7單元更新為7137.40美元。
當您單擊單元格B7時,更新的公式= SUM(B2:OFFSET(B7,-1,0))出現在公式欄中。
注意 :OFFSET函數有兩個可選參數: 高度和寬度,在本例中省略。
這些參數可以用來告訴OFFSET函數輸出的形狀,因為它有很多行高和很多列寬。
通過省略這些參數,默認情況下,該函數使用Reference參數的高度和寬度,而在此示例中,該參數是一行高一列寬。