Excel绘制趋势线与线性回归预测

标签：Excel技巧

示例数据如下图1所示，要求绘制冷饮销量随气温变化的趋势线，得出线性回归方程，并预测某气温下的冷饮销量。

图1

首先，绘制散点图并添加趋势线。

插入散点图，对数据坐标轴设置适当的格式，结果如下图2所示。

图2

选择图表数据系列，单击右键，在快捷菜单中选择“添加趋势线”，如下图3所示。

图3

添加趋势线后的图表如下图4所示。右键单击该趋势线，在快捷菜单中选择“设置趋势线格式”，选择“显示公式”，对其设置格式后的效果如下图4所示。

图4

可以看到，Excel给出了趋势线，并添加了相应的线性回归方程。我们可以根据该方程来预测给定温度下的冷饮销量。

也可以使用Excel函数LINEST计算出线性回归方程的系数，从而得出相同的方程。

我们知道，一元一次线性方程表达式为：y=mx+b。我们要根据已知数据，算出m和b值，从而确定这个方程。

如下图5所示，选择单元格区域D3:E3，输入公式：

=LINEST(B2:B9,A2:A9,TRUE,TRUE)

按Ctrl+Shift+Enter键。

图5

此时，就会得到方程的系数，即方程为：

y=7.197x-139

与上图4中的方程基本相同。此时，给出x值（即气温）即可得y值（即冷饮销量），如下图6所示。

图6