使用图表趋势线进行预测分析

使用趋势线进行预测分析

虽然可以使用函数公式进行预测分析，但是当现有的数据规律比较复杂时，使用公式就会比较困难，而使用图表来表现数据图形，并且添加趋势线来显示趋势规律则更加方便简单并且直观。

某工厂对最近数月的产品产量和相应的耗电量进行了统计记录，结果如图1-1所示，要求根据目前的数据规律，预测当产量达到800吨时，所需要的耗电量将达到多少。

图1-1 产量和能耗的数据记录

具体方法如下：

首先，需要判断当前的两组数据之间究竟具有什么样的关系或规律，可通过创建图表的方式进行简单的观察判断。

步骤1 选中数据区域A2:B11，单击菜单“插入”→“图表”，打开“图表向导—4步骤之1—图表类型”对话框，在“标准类型”选项卡中，选中左侧“图表类型”列表框中的“XY 散点图”，然后在右侧出现的“子图表类型”中选择“平滑线散点图”，如图1-2所示：

图1-2 创建XY散点图

步骤2 单击“完成”按钮，生成XY散点图如图1-3所示，其中以产品产量数据作为X 轴，以耗电量数据为Y轴。

图1-3 生成XY散点图

步骤3 为了使图表显示更美观更具可读性，可调整图表格式和坐标轴设置，具体的调整方法可参阅Excel Home其它图书中有关图表方面的内容，在此不详细介绍。调整后的显示如图1-4所示：

图1-4 美化后的图表

通过观察图表可以发现，随着产量的增长，耗电量也随之同步增长，并且两者的增长趋势接近于一条直线，因此可以近似认为，产量和耗电量两组数据之间存在着线性关系。

注意：在很多实际情况中，很少有数据组之间保持严格的理想状态的线性关系，因此通常将图形接近于直线的数据组近似认为存在线性规律。

步骤4 在图表中单击任意一个数据点以选中数据系列，然后单击鼠标右键，在弹出的快捷菜单中选择“添加趋势线”命令，如图1-5所示。也可在选中系列后单击菜单“图表”→“添加趋势线”完成同样操作。

图1-5 在图表上添加趋势线

步骤5 显示“添加趋势线”对话框如图1-6所示，在“类型”选项卡的“趋势预测/回归分析类型”中选择默认的“线性”趋势线类型，然后单击“确定”按钮即可完成趋势线的添加。

图1-6 “添加趋势线”对话框中

添加了线性趋势线的图表如图1-7所示，观察此图后可以发现，新增的趋势线与原有的数据点连线几乎完全重合，此趋势线反映了当前已知数据的趋势规律。

图1-7 图表上显示线性趋势线

要使用趋势线来进行未知数据的预测，例如要预测产量达到800吨时所对应的耗电量，可继续如下步骤：

步骤6 在图表上单击选中趋势线，然后单击鼠标右键，在弹出的快捷菜单中选择“趋势线格式”命令。以上操作也可通过在“图表”工具栏的下拉列表中选中“系列1趋势线1”，然后单击“格式”按钮来达到同样目的。如图1-8所示：

图1-8 通过右键菜单或“图表”工具栏来设置趋势线格式

步骤7 在弹出的“趋势线格式”对话框中选择“选项”选项卡，在“趋势预测”区域中将“前推”右侧的微调框数值调整为76个单位（800-724=76），如图1-9所示：

图1-9 设置趋势预测位置

步骤8 单击“确定”按钮关闭“趋势线格式”对话框，显示的图表结果如图1-10所示。现在的趋势线延长到X轴坐标达到800的位置，此时趋势线右侧端点位置的Y轴坐标即为需要预测的耗电量数值。

图1-10 “前推”趋势线来预测未来数据

如果将Y轴坐标的显示间隔减小，可以更容易地找到预测数据点的取值，例如将Y轴的“主要刻度单位”调整为100，显示结果如图1-11所示，此时可以根据图表显示判断出预测点的耗电量数值大约为2700多。

图1-11 减小坐标轴间隔来进行数值定位

注意：如需获取趋势线上数据点的精确取值，具体方法可继续阅读下文。

获取趋势线的取值

趋势线与图表上的数据系列有所区别，趋势线上的数据点无法显示实际数值，因此，如果要获取通过趋势线所预测得到的精确数值，需要进行进一步的处理。

以前文中制作的趋势线为例，要根据趋势线来预测当产量为800吨时所对应的耗电量具体数值，可以参照以下方法来实现：

步骤1 在图表上单击选中趋势线，然后单击鼠标右键，在弹出的快捷菜单中选择“趋势线格式”命令，打开“趋势线格式”对话框，选择“选项”选项卡，如图2-1所示：

图2-1 “趋势线格式”对话框的“选项”选项卡

步骤2 在此选项卡中勾选“显示公式”复选框，单击“确定”按钮关闭对话框，显示图表结果如图2-2所示：

图2-2 显示趋势线的公式

图表中显示了当前线性趋势线的公式。从数学角度上来说，线性关系的可用一次方程来表示，方程表达式为“y=kx+b”，其中y表示位于Y轴的应变量，x表示位于X轴的自变量，k则表示直线的斜率，b则表示Y轴上的截距。当前图表上显示了目前趋势线的数学关系，其中斜率k 为2.9912，截距b为325.51。根据此趋势线的公式，就可以得出具体的预测值。

步骤3 在A12单元格中输入预测时的产量值800，然后在B12单元格内根据趋势线公式输入如下公式：

=2.9912*A12+325.51

得到结果如图2-3所示：

图2-3 根据趋势线公式得到具体预测值

注意：对于使用趋势线公式来求取具体预测值来说，并不要求所求的数据点必须包含于当前显示的趋势线上，也可以是位于趋势线的延长线上的数据点。例如本例中的预测点（800,2718.47）就超出了趋势线的范围之外，位于其延长线上。

本篇文章节选自《Excel数据处理与分析实战技巧精粹》ISBN：9787115182326人民邮电出版社