如何在 Excel 中计算移动平均值:2026 年的 4 种简单方法

要在 Excel 中计算移动平均线,请在最初的几个值上输入 =AVERAGE(),例如 =AVERAGE(B2:B4)。然后向下拖动公式填充该列。这样每一行都会计算最近三个值的平均值。Excel 的 Analysis ToolPak 和图表趋势线也可以为您自动添加移动平均线。
本指南涵盖了四种实现方法:AVERAGE 公式、Analysis ToolPak、图表趋势线,以及使用 SUMPRODUCT 计算加权平均值。它还通过一个完整的示例演示了在大型文件中平滑趋势的更快方法。详细信息源自 Microsoft 支持页面,查阅日期为 2026 年 11 月 12 日。
什么是移动平均线
移动平均线(也称为滚动平均线)是一系列数据中最近几个值的平均值。随着每个新周期的到来,最旧的值被剔除,最新的值被加入。这个窗口沿着数据“移动”。
Microsoft 解释了其作用。它指出:“移动平均线提供的趋势信息是所有历史数据的简单平均值所无法体现的。”
全年的简单平均值会将所有数据扁平化为一个数字。而三个月的移动平均线则能显示上个季度是在上升还是下降。它能在不掩盖方向的情况下平滑掉一次性的波动尖峰。
常见用途包括:
- 销售趋势。 平滑每周销售额以观察真实走向。
- 库存规划。 Microsoft 将销售、库存和其他趋势列为其移动平均工具的用途。
- 网站流量。 通过七天平均值消除周内波动。
- 股票价格。 比较短期平均线与长期平均线。
窗口的大小至关重要。短窗口反应快但噪声多。长窗口平滑但显示转折较慢。
在 Excel 中设置数据
移动平均线需要按时间顺序排列且没有间隙的数据。将周期放在一列,数值放在下一列。
这是一个包含八周单位销售额的小示例:
| 周次 | 销售额 |
|---|---|
| 1 | 120 |
| 2 | 135 |
| 3 | 128 |
| 4 | 150 |
| 5 | 162 |
| 6 | 155 |
| 7 | 170 |
| 8 | 181 |
将“周次”放在 A 列,“销售额”放在 B 列,标题放在第 1 行。数值则位于第 2 到 9 行。
在开始之前,请检查三件事:
- 按日期排序。 最旧的在前,最新的在后。
- 填补间隙。 缺失的一周会使之后的所有平均值发生偏移。
- 使用数字。 Microsoft 指出 AVERAGE 会忽略区域中的文本,这可能会悄悄改变您的结果。
方法 1:使用 AVERAGE 公式并向下拖动
这是最简单的方法,适用于所有版本的 Excel。
- 确定一个窗口,例如 3 周。
- 在 C4 单元格中输入
=AVERAGE(B2:B4)。 - 按 Enter 键。
- 向下拖动填充柄至 C9。
Excel 每次会将范围移动一行。C5 变为 =AVERAGE(B3:B5),C6 变为 =AVERAGE(B4:B6),依此类推。
前两行没有平均值,因为还没有凑齐三个数值。请将它们留空,或加上标签以便读者了解原因。
Microsoft 对 AVERAGE 的定义很简单。它“返回参数的平均值(算术平均值)”。为了使窗口起作用,单元格引用必须保持相对引用,不能带有美元符号。
这种方法易于审计。任何人都可以点击单元格,准确查看它计算了哪些值的平均值。
为了让该列自动增长,请使用 Ctrl+T 将数据格式化为 Excel 表格。当您在新行中添加第 9 周时,表格会自动为您向下复制公式。
方法 2:使用 Analysis ToolPak 移动平均工具
Analysis ToolPak 包含一个内置工具。Microsoft 表示,它“根据变量在特定数量的前期内的平均值,预测预测期内的值”。
首先,如果您在“数据”选项卡上没看到“数据分析”,请开启 ToolPak。Microsoft 在 Windows 上的步骤是:选择“文件”,然后选择“选项”,接着选择“加载项”。在“管理”框中,选择“Excel 加载项”,然后选择“转到”。接着勾选“Analysis ToolPak”复选框并选择“确定”。
然后运行该工具:
- 转到“数据”选项卡并选择“数据分析”。
- 选择“移动平均”并点击“确定”。
- 在“输入区域”中,选择销售额数值。
- 如果您包含了标题,请勾选“标志位于第一行”。
- 在“间隔”中,输入窗口大小,例如 3。
- 选择一个“输出区域”,例如 C2。
- 如果您需要图表,请勾选“图表输出”,然后点击“确定”。
Excel 会将平均值写入输出区域。前几行会显示 #N/A,因为窗口尚未填满。
该工具写入的是数值而非公式。如果您的数据发生变化,请重新运行该工具。
该工具适合一次性分析,例如季度回顾。如果您勾选了“图表输出”,它还会添加一个快速图表。对于每周更新的工作表,方法 1 中的公式更容易维护。
Microsoft 的页面还展示了每个值的构建方式。它将 N 描述为“移动平均线中包含的前期数量”。该工具只是简单地为每一行计算这 N 个值的平均值。
方法 3:在图表中添加移动平均趋势线
趋势线直接在图表上添加平滑曲线。这是直观展示趋势最快的方法。
Microsoft 的步骤从创建数据图表开始:
- 选择您的数据并插入折线图。
- 点击图表中的任意位置。
- 在“格式”选项卡的“当前选择”组中,在下拉列表中选择趋势线选项。
- 选择“设置所选内容格式”。
- 在“设置趋势线格式”窗格的“趋势线选项”下,选择“移动平均”。
- 将“周期”设置为您的窗口大小,例如 3。
如果图表还没有趋势线,请先从图表元素菜单中添加一条。
Microsoft 注意到关于这条线的一个特性。趋势线的点数比数据系列少。Microsoft 表示,点数等于“系列中的总点数减去您为周期指定的数量”。
趋势线只绘制线条。它不会将平均值放入单元格中。如果您需要报告中的具体数字,请使用方法 1。
方法 4:使用 SUMPRODUCT 计算加权移动平均线
简单平均线对窗口内的每一周都一视同仁。加权版本则赋予近期周次更多的权重。当近期的变化最为重要时,这很有帮助。
使用 SUMPRODUCT 将每个值乘以其权重,然后除以总权重。Microsoft 表示,SUMPRODUCT 处理“您想要先乘后加的组件”所在的数组。
对于权重为 1、2 和 3 的 3 周窗口:
- 将权重 1、2 和 3 放入 F2 到 F4 单元格。
- 在 D4 单元格中输入
=SUMPRODUCT(B2:B4,$F$2:$F$4)/SUM($F$2:$F$4)。 - 将公式向下拖动到 D9。
美元符号锁定了权重单元格,因此只有销售额范围会移动。每个窗口中最新的一周权重为 3,最旧的一周权重为 1。
Microsoft 增加了一条规则。它指出“数组参数必须具有相同的维数”,否则 SUMPRODUCT 会返回 #VALUE!。请保持窗口和权重的规模一致。
移动平均线示例
这是使用方法 1 计算的八周销售额的 3 周简单平均值。
| 周次 | 销售额 | 3 周平均值 |
|---|---|---|
| 1 | 120 | – |
| 2 | 135 | – |
| 3 | 128 | 127.67 |
| 4 | 150 | 137.67 |
| 5 | 162 | 146.67 |
| 6 | 155 | 155.67 |
| 7 | 170 | 162.33 |
| 8 | 181 | 168.67 |
观察第 6 周。销售额从 162 降至 155,但 3 周平均值仍然从 146.67 上升到 155.67。这次下降只是噪声,趋势依然保持。
第 8 周也展示了加权版本。其 3 周加权平均值为 (155 × 1 + 170 × 2 + 181 × 3) ÷ 6。即 1,038 ÷ 6,等于 173.00。
加权数值 173.00 高于简单平均值 168.67。这是因为最近的一周(181)被计算了三次。
如何选择窗口大小
合适的窗口取决于数据的节奏。一些经验法则会有所帮助:
- 匹配周期。 对于具有周波动的日数据,使用 7 天。对于具有年度季节性的月数据,使用 12 个月。
- 短窗口用于快速决策。 3 周期窗口能快速显示转折,但波动较大。
- 长窗口用于战略。 12 周期窗口显示大局,但反应滞后。
- 比较两个窗口。 当短期平均线向上穿过长期平均线时,趋势可能正在转好。
在确定之前,请在同一张图表上尝试两到三个窗口。选择那个既能消除噪声又不会掩盖您所关心的转折点的窗口。
在每个图表和表格旁边注明窗口大小。“3 周平均值”比“平均值”更清晰。这也能防止两位读者误将 3 周线与 6 周线进行比较。
在报告中展示趋势
数字只完成了一半的工作。大多数读者会通过图表来判断趋势,因此图表需要精心设计。
将原始值和平滑值绘制在一起。使用浅灰色线条表示原始每周销售额,使用粗体彩色线条表示 3 周平均值。读者可以同时看到噪声和方向。
直接在图表上标注平滑线,例如“3 周平均值”,而不是依赖图例。从窗口第一次填满的地方开始绘制平滑线,这样它在开始时就不会掉到零。
添加一个陈述发现的简短标题。“销售额在第 6 周的回落中保持增长”告诉读者该看什么。而“每周销售额”则没有这种效果。
如果您在 PowerPoint 中分享图表,请通过链接到工作簿的方式粘贴它。当下个月的数据到来时,您只需刷新图表而无需重建。我们关于如何从 Excel 更新 PowerPoint 图表的指南涵盖了这些选项。
对于仪表板,请将最新值和最新平均值并排放置。诸如“高于平均水平”或“低于平均水平”的小注释有助于忙碌的读者根据数字采取行动。
使用 AI 在 Excel 中计算移动平均线
当您跟踪许多产品或地区时,为每个产品或地区构建一列平均值会很耗时。AI 工具可以一次性平滑整个数据集并绘制图表。
以下是使用 Powerdrill Bloom 的三个步骤。
步骤 1:上传您的 Excel 文件
上传包含时间序列的工作簿。AI Forecasting 工具会要求您上传“具有任何粒度时间戳的历史指标”。
保持每个周期一行,每个产品或地区一列。清晰的标题有助于 AI 理解每个系列。
步骤 2:使用自然语言请求移动平均线
描述您的需求。例如:“为每个地区添加 3 周移动平均线,并标记销售额低于该平均值的周次。”
Excel Calculations 页面指出您可以“以文本形式输入您想要的计算请求”。然后您可以提出后续问题,例如切换到 4 周窗口。
步骤 3:检查结果并绘制趋势图
对照方法 1 手动检查一个平均值。然后展示趋势。Line Graph Creator 表示它可以“将您的原始数据转换为精美的交互式折线图”。
要更广泛地了解随时间变化的模式,请参阅我们的如何进行趋势分析指南。
应避免的常见错误
这些是 Excel 滚动平均值中最常出现的错误。
- 未排序的日期。 乱序行的平均值毫无意义。请先按从旧到新排序。
- 缺失周期。 跳过的一周会使之后的所有窗口发生偏移。请填补间隙或进行标记。
- 锁定引用。 销售额范围上的美元符号会阻止窗口移动。请保持相对引用。
- 范围内的文本。 AVERAGE 会忽略文本,因此存储为文本的数字会悄悄丢失。
- 缺失数据用零代替。 Microsoft 指出“包含值为零的单元格”。为缺失数据输入的零作为占位符会拉低平均值。
- 将平均值视为预测。 滚动平均值描述的是近期水平。它在设计上就滞后于转折。
- 静态 ToolPak 输出。 移动平均工具写入的是数值,因此当数据变化时请重新运行它。
- 未标注的窗口。 务必说明每个平均值涵盖了多少个周期。
当您准备好平滑整个数据集的趋势时,请使用您过去六个月的每周数据尝试 Powerdrill Bloom。
常见问题解答
Excel 中移动平均线的公式是什么?
在单元格窗口上使用 AVERAGE 并向下拖动。对于从第 2 行开始的 3 周期窗口,在第 4 行输入 =AVERAGE(B2:B4),然后向下填充。每一行都会计算最近三个值的平均值。
如何向 Excel 图表添加移动平均线?
选择图表,打开“设置趋势线格式”窗格,在“趋势线选项”下选择“移动平均”。然后将“周期”设置为您希望在每个平均值中包含的点数。
Excel 中的移动平均工具在哪里?
它位于 Analysis ToolPak 中。开启该加载项,然后转到“数据”>“数据分析”并选择“移动平均”。设置输入区域和间隔,然后选择“确定”。
简单移动平均线和加权移动平均线有什么区别?
简单版本赋予窗口内的每个值相同的权重。加权版本赋予近期值更多权重,通常结合使用 SUMPRODUCT 和一组权重。
移动平均线应该使用多少个周期?
使窗口与数据的周期相匹配。对于具有周波动的日数据使用 7,对于具有年度季节性的月数据使用 12。较短的窗口反应更快但噪声更多。
来源: Microsoft 支持,使用 Analysis ToolPak 执行复杂的数据分析 · Microsoft 支持,向图表添加趋势线或移动平均线 · Microsoft 支持,AVERAGE 函数 · Microsoft 支持,SUMPRODUCT 函数。