AVERAGE函数示例：移动平均公式

概括

要计算移动平均值或滚动平均值，可以使用基于AVERAGE 函数并带有相对参考值的简单公式。在所示示例中，E7 单元格中的公式为：

=AVERAGE(C5:C7)

公式向下复制时，会根据当天和前两天的销售额计算 3 天移动平均值。

下面介绍的是基于OFFSET 函数的更灵活的选项，它可以处理可变周期。

关于移动平均线

移动平均值（也称滚动平均值）是基于给定时间间隔内的数据子集计算的平均值。按特定时间间隔计算平均值可以平滑数据，从而减少随机波动的影响。这使得观察整体趋势更加容易，尤其是在图表中。用于计算移动平均值的时间间隔越大，平滑效果就越好，因为每次计算的平均值包含更多的数据点。

解释

示例中所示的公式均使用 AVERAGE 函数，并为每个特定时间间隔设置了相对参考值。E7 中的 3 天移动平均值是通过向 AVERAGE 函数提供一个包含当前日期和前两天的范围来计算的，如下所示：

=AVERAGE(C5:C7) // 3-day average

5 天平均值和 7 天平均值的计算方法相同。在每种情况下，提供给 AVERAGE 函数的范围都会扩大，以包含所需的天数：

=AVERAGE(C5:C9) // 5-day average
=AVERAGE(C5:C11) // 7-day average

所有公式都使用相对引用来表示传递给 AVERAGE 函数的范围。当公式向下复制到列中时，范围会在每一行发生变化，以包含每个平均值所需的数值。

当这些数值绘制成折线图时，平滑效果就很明显了：

移动平均线图示例

数据不足

如果从表格的第一行开始编写公式，前几个公式将没有足够的数据来计算完整的平均值，因为范围会超出第一行数据：

移动平均范围问题

这是否会造成问题，取决于工作表的结构，以及是否需要所有平均值都基于相同数量的值。AVERAGE 函数会自动忽略文本值和空单元格，因此即使值较少，它也能继续计算平均值。这就是为什么它在 E5 和 E6 单元格中“有效”的原因。

一种清晰表明数据不足的方法是检查当前行号，如果行号小于 n，则中止并返回 #NA。例如，对于 3 天平均值，您可以使用：

=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))

公式的第一部分只是生成一个“规范化”的行号，从 1 开始：

ROW()-ROW($C$5)+1  // relative row number

第 5 行的结果是 1，第 6 行的结果是 2，依此类推。

当当前行号小于 3 时，公式返回 #N/A。否则，公式将像之前一样返回移动平均值。这与“分析工具库”中的移动平均值函数的行为一致，该函数在达到第一个完整周期之前都会输出 #N/A。

移动平均值，数据不足时显示 #n/a

但是，随着周期数的增加，数据上方最终会缺少行数，导致无法在 AVERAGE 函数中输入所需的范围。例如，您无法使用图中所示的工作表设置 7 天移动平均值，因为您无法输入超出 C5 上方 6 行的范围。

可变周期，带偏移量

计算移动平均值的更灵活方法是使用 OFFSET 函数。OFFSET 可以创建动态范围，这意味着我们可以设置一个周期数可变的公式。其一般形式为：

=AVERAGE(OFFSET(A1,0,0,-n,1))

其中 n 是每个平均值中包含的周期数。如上所述，OFFSET 函数返回一个范围，该范围将传递给 AVERAGE 函数。您可以在下方看到此公式的实际应用，其中n 是名为 E2 的范围。从单元格 C5 开始，OFFSET 函数构建一个范围，该范围向前延伸到之前的行。这是通过使用等于负n的高度来实现的。当 E5 更改为另一个数字时，移动平均值将重新计算所有行：

带偏移量的移动平均线

将E5单元格中的公式向下复制出来，得到：

=AVERAGE(OFFSET(C5,0,0,-n,1))

与上面的原始公式一样，带有 OFFSET 的版本也会出现前几行数据不足的问题，具体取决于 E5 中给出的周期数。

在所示示例中，平均值计算成功，因为 AVERAGE 函数会自动忽略文本值和空白单元格，并且 C5 单元格上方没有其他数值。因此，虽然传递给 E5 单元格 AVERAGE 函数的范围是 C1:C5，但只有一个值需要求平均值，即 100。然而，随着周期数的增加，OFFSET 函数会不断创建一个超出数据起始位置的范围，最终到达工作表顶部并返回 #REF 错误。

一种解决方案是将范围的大小限制为可用数据点的数量。这可以通过使用 MIN 函数来限制用于高度的数值来实现，如下所示：

带偏移量和限幅范围的移动平均线

=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))

这看起来很吓人，但实际上很简单。我们使用MIN 函数来限制传递给 OFFSET 的高度：

MIN(ROW()-ROW($C$5)+1,n)

在 MIN 函数内部，第一个值是相对行号，计算方法如下：

ROW()-ROW($C$5)+1 // relative row number..1,2,3, etc.

MIN 函数的第二个参数是周期数 n。当相对行号小于 n 时，MIN 函数返回当前行号作为高度偏移量。当行号大于 n 时，MIN 函数返回 n。换句话说，MIN 函数返回这两个值中较小的那个。

OFFSET 选项的一个优点是 n 值可以轻松更改。如果我们把 n 改为 7 并绘制结果图，就会得到如下所示的图表：

带偏移功能的移动平均线图

注意：上述 OFFSET 公式存在一个问题，即它们在 Google 表格中无法正常工作，因为表格中的 OFFSET 函数不允许高度或宽度为负值。附件中的电子表格提供了适用于 Google 表格的替代公式。

详情页