跳至主要内容

在 Excel 中创建预测图表

Author: Siluvia Last Modified: 2025-05-07

默认情况下,Excel 提供了一个内置功能,可帮助创建一个新的工作表以预测数据趋势。除了创建预测工作表外,本文还将向您展示如何直接基于工作表中的数据创建预测图表。

 a forecast chart in Excel

创建一个简单的预测图表
根据阈值创建预测图表
使用一款出色的工具轻松创建预测图表
下载示例文件
视频:在 Excel 中创建预测图表


创建一个简单的预测图表

假设您有一个销售表,其中包含从 1 月到 9 月的实际销售数据(如下方截图所示),并且您希望在一个图表中预测 10 月、11 月和 12 月的销售趋势,您可以按照以下步骤操作完成。

steps of creating a simple forecast chart in Excel

1. 首先,您需要创建一列包含 10 月、11 月和 12 月预测数据的列(如下方截图所示)。

注意:在最后一个实际数据单元格旁边,输入相同的数据。

steps of creating a simple forecast chart in Excel

2. 选择整个表格数据,点击 插入 > 插入折线图或面积图 > 带标记的折线图

steps of creating a simple forecast chart in Excel

3. 右键单击蓝色线条(实际系列),然后从右键菜单中选择 设置数据系列格式

steps of creating a simple forecast chart in Excel

4. 在打开的 设置数据系列格式 窗格中,请按以下步骤进行配置。

4.1) 点击 填充与线条 图标;
4.2) 点击 标记;
4.3) 展开 标记选项,选择 内置 选项,保持在 类型 下拉列表中选择圆点符号,并将原始 大小 替换为 10

steps of creating a simple forecast chart in Excel

5. 现在您需要配置预测系列。选择橙色线条(预测系列),并在 设置数据系列格式 窗格中,执行以下操作。

5.1) 点击 填充与线条 图标;
5.2) 点击 标记;
5.3) 在 标记选项 部分,选择 内置 选项,保持选择圆点符号,然后在 大小 框中将尺寸更改为 10;
5.4) 在 填充 部分,在 颜色 下拉列表中选择 白色

steps of creating a simple forecast chart in Excel

5.5) 在 边框 部分,将边框宽度更改为 1.5pt
5.6) 在 虚线类型 下拉列表中,选择 方块

steps of creating a simple forecast chart in Excel

5.7) 转到 线条 选项卡,然后从 虚线类型 下拉列表中选择 方块点

steps of creating a simple forecast chart in Excel

6. 由于 9 月的数据是实际数据,我们需要将此标记配置为显示为实际系列。

双击此标记以仅选择它,并在 设置数据点格式 窗格中,分别选择与实际系列相同的 填充颜色 和 边框颜色,然后从 虚线类型 下拉列表中选择 实线

steps of creating a simple forecast chart in Excel

之后,您可以看到 9 月的标记已更改如下。

steps of creating a simple forecast chart in Excel

7. 根据需要更改图表标题。然后,一个简单的预测图表就完成了。

steps of creating a simple forecast chart in Excel


基于阈值创建预测图表

假设您想根据阈值创建预测图表(如下方截图所示),本节中的方法将为您提供帮助。

steps of creating a forecast chart based on a threshold value

1. 在原始 数据区域 添加两列新列,分别包含预测数据和阈值。请参见截图:

注意:在最后一个实际数据单元格旁边,输入相同的值。

steps of creating a forecast chart based on a threshold value

2. 您需要根据原始数据范围创建一个中间数据表。该表将用于创建预测图表。

2.1) 表中有五列:第一列与原始表具有相同的系列值,第二、第三和第四列与原始表具有相同的标题,第五列数据将用于显示阈值线下方的数据。

steps of creating a forecast chart based on a threshold value

2.2) 选择第二列(实际列)的第一个空白单元格,在其中输入以下公式并按 Enter 键。然后向下拖动结果单元格的 自动填充柄,直到到达表格底部。
=IF(B2,B2,NA())

steps of creating a forecast chart based on a threshold value

2.3) 选择预测列的第一个空白单元格,在其中输入以下公式并按 Enter 键。然后向下拖动结果单元格的 自动填充柄,直到到达表格底部。
=IF(B2,IF(B3,NA(),B2),C2)

steps of creating a forecast chart based on a threshold value

2.4) 选择第四列的第一个空白单元格,在其中输入以下公式并按 Enter 键。然后向下拖动结果单元格的 自动填充柄,直到表格末尾。
=$D$2

steps of creating a forecast chart based on a threshold value

2.5) 在第五列中,将以下公式输入第一个空白单元格,然后按 Enter 键。通过拖动其自动填充柄将其应用到其他单元格,直至表格底部。
=IF(B18>D18,NA(),B18)

steps of creating a forecast chart based on a threshold value

注意:在上述公式中,所有引用单元格均来自原始数据范围。

3. 选择整个中间数据表,点击 插入 > 插入折线图或面积图 > 带标记的折线图

steps of creating a forecast chart based on a threshold value

然后在当前工作表中插入一个图表(如下方截图所示)。

steps of creating a forecast chart based on a threshold value

4. 现在您需要格式化实际系列和预测系列,直到图表显示如下。点击了解如何操作

steps of creating a forecast chart based on a threshold value

5. 对于阈值线下方的数据系列,您需要放大标记,更改填充颜色并隐藏线条。请选择该系列(图表中的黄色线),并在 设置数据系列格式 窗格中,按以下步骤进行配置。

5.1) 点击 填充与线条 图标;
5.2) 点击 标记 选项卡;
5.3) 展开 标记选项 部分,选择 内置 选项,保持在 类型 下拉列表中选择圆点,并将 尺寸 更改为 10
5.4) 在 填充 部分,为标记指定一种新颜色。这里我选择红色以突出显示阈值线下方的数据;
5.5) 在 边框 部分,指定与填充颜色相同的颜色;

steps of creating a forecast chart based on a threshold value

5.6) 点击 线条 选项卡,然后在线条部分选择 无线条 选项。

steps of creating a forecast chart based on a threshold value

现在,图表显示如下方截图所示。

steps of creating a sforecast chart based on a threshold value

6. 选择图表中的阈值线,并在 设置数据系列格式 窗格中,按以下步骤隐藏标记并更改线条颜色。

6.1) 点击 填充与线条 图标;
6.2) 点击 标记 选项卡;
6.3) 在 标记选项 部分,选择 选项;
6.4) 点击 线条 选项卡,然后为线条选择一种新颜色。请参见截图:

steps of creating a forecast chart based on a threshold value

7. 根据需要更改图表标题。

然后,基于阈值的预测图表完成如下方截图所示。

steps of creating a forecast chart based on a threshold value


在 Excel 中轻松创建预测图表

Kutools for Excel 预测图表 工具可以帮助您快速创建一个简单的预测图表或基于阈值的预测图表,只需几次点击即可完成(如下方演示所示)。立即下载并试用! 30-天免费试用


下载示例文件

sample of creating a simple forecast chart in Excel


视频:在 Excel 中创建预测图表