Excel SUM和OFFSET公式

如果Excel工作表包含基于不断变化的单元格范围的计算，则在SUM OFFSET公式中一起使用SUM和OFFSET函数可以简化使计算保持最新的任务。

使用SUM和OFFSET函数创建动态范围

如果您在一段时间内使用不断变化的计算（例如当月的总销售额），则Excel中的OFFSET功能允许您设置随每天的销售数字添加而变化的动态范围。

SUM函数本身通常可以容纳将新数据单元插入到求和范围内。

将数据插入到函数当前所在的单元格时会发生一个例外。

在上面的示例图中，每天的新销售数据都会添加到列表的底部，从而迫使总计在每次添加新数据时不断向下移动一个单元格。

如果SUM函数本身用于汇总数据，则每次添加新数据时都需要修改用作函数参数的单元格范围。

但是，通过一起使用SUM和OFFSET函数，总计的范围变为动态。换句话说，它会发生变化以适应新的数据单元格。添加新的数据单元不会引起问题，因为随着每个新单元的添加，范围会继续调整。

语法和参数

请参阅上图以跟随本教程。

在此公式中，SUM函数用于总计作为其参数提供的数据范围。此范围的起点是静态的，并且被标识为要通过公式计算的第一个数字的单元格引用。

OFFSET函数嵌套在SUM函数内，用于为公式总计的数据范围创建动态端点。这是通过将范围的端点设置为高于公式位置的一个单元格来实现的。

公式的语法：

= SUM（范围开始：OFFSET（参考，行，列））

• 范围开始：需要SUM函数总计的单元格范围的起点。在示例图像中，这是单元格B2。
• 参考：用于计算范围端点的所需单元格引用位于许多行和列之外。在示例图像中，Reference参数是公式本身的单元格引用，因为您始终希望范围在公式上方结束一个单元格。
• 行：需要在计算偏移量时使用的Reference参数之上或之下的行数。该值可以是正数，负数或设置为零。如果偏移的位置高于 参考 论证，价值是负面的。如果它低于，则Rows参数为正。如果偏移位于同一行，则参数为零。在此示例中，偏移量在Reference参数上方开始一行，因此该参数的值为负一（-1）。
• 列数：需要在计算偏移量时使用的Reference参数左侧或右侧的列数。该值可以是正数，负数或设置为零。如果偏移量的位置位于Reference参数的左侧，则此值为负值。如果在右边，Cols论证是积极的。在此示例中，总计的数据与公式位于同一列中，因此此参数的值为零。

使用SUM OFFSET公式计算总销售数据

此示例使用SUM OFFSET公式返回工作表的B列中列出的每日销售数字的总和。

最初，公式输入到单元格B6中，并将销售数据总计为四天。

下一步是将SUM OFFSET公式向下移动一行，为第五天的销售总额腾出空间。这是通过插入新的行6来实现的，该行将公式向下移动到第7行。

作为移动的结果，Excel自动将Reference参数更新为单元格B7 并添加单元格B6 到公式求和的范围。

输入SUM OFFSET公式

1. 单击单元格B6，这是最初显示公式结果的位置。
2. 点击公式 功能区菜单的选项卡。
3. 选择数学和Trig从功能区打开功能下拉列表。
4. 点击和 在列表中打开功能的对话框。
5. 在对话框中，单击1号线。
6. 单击单元格 B2 在对话框中输入此单元格引用。此位置是公式的静态端点。
7. 在对话框中，单击2号线。
8. 输入以下OFFSET函数： OFFSET（B6，-1,0） 形成公式的动态端点。
9. 点击 好 完成该功能并关闭对话框。

总数出现在单元格B7中。

添加第二天的销售数据

要添加第二天的销售数据：

1. 右键单击第6行的行标题以打开上下文菜单。
2. 在菜单中，单击插入 在工作表中插入一个新行。
3. 结果，SUM OFFSET公式向下移动到单元格B7，行6现在为空。
4. 单击单元格 A6.
5. 输入数字5 表示正在输入第五天的销售总额。
6. 单击单元格B6。
7. 输入号码$1458.25 并按下输入 键盘上的键。

Cell B7更新到新的总数$7137.40.

单击单元格B7时，更新公式= SUM（B2：OFFSET（B7，-1,0）） 出现在公式栏中。

OFFSET函数有两个可选参数：Height和Width , 在这个例子中没有使用。

这些参数可以用来告诉OFFSET函数输出的形状是如此多的行高和如此多的列宽。

通过省略这些参数，默认情况下，该函数使用Reference参数的高度和宽度，在此示例中，它是一行高和一列宽。