用Excel做一元线性回归预测分析

Excel是一个功能强大的数据管理与分析软件，我们可以用Excel函数与数据分析进行回归预测分析。回归分析法是根据事物的因果关系对应的变量的预测方法，是定量预测方法的一种。

例如，下表是1-6月每月销量数据，根据这个数据预测7-12月销量数据。

预测方法：

一、运用数据分析工具

操作步骤如下：

step1:根据原始数据制作折线图，如下：

step2:通过相关分析判断变量之间的相关程度，并建立回归模型。点击折线图，右键添加趋势线。

step3:选中趋势线，右键设置趋势线格式，趋势线选项选择线，趋势预测显示公式和显示R平方值打勾，在图表中可以看到回归模型y=kx+b和R平方值。R平方值越接近1，回归模型越优。

step4:根据回归模型计算7-12月的预测值。

二、运用函数

我们可以用forecast函数预测，forecast用途：根据一条线性回归拟合线返回一个预测值。使用此函数可以对未来销量或消费趋势进行预测。

语法：FORECAST(x，known_y's，known_x's)。

参数：X 为需要进行预测的数据点的X 坐标(自变量值)。

Known_y's 是从满足线性拟合直线y=kx+b 的点集合中选出的一组已知的y值，Known_x's 是从满足线性拟合直线y=kx+b的点集合中选出的一组已知的x 值。

D7公式为=INT(FORECAST(A8,B$2:B7,A$2:A7))

在用forecast预测之前可以用correl函数查看月份和销量之间的相关程度。

Correl函数用途：返回单元格区域array1 和array2 之间的相关系数，它可以确定两个不同事物之间的关系。

语法：CORREL(array1，array2)

参数：Array1 第一组数值单元格区域。Array2 第二组数值单元格区域。

相关系数的计算公式为：

其中 x 和 y 是样本平均值 AVERAGE(array1) 和 AVERAGE(array2)。

相关系数是变量之间相关程度的指标。样本相关系数用r表示,总体相关系数用ρ表示,相关系数的取值范围为[-1,1]。|r|值越大，误差Q越小，变量之间的线性相关程度越高；|r|值越接近0，Q越大，变量之间的线性相关程度越低。

相关系数用希腊字母γ表示，γ值的范围在-1和+1之间。

γ>0为正相关，γ<0为负相关。γ=0表示不相关；

γ的绝对值越大，相关程度越高。

两个现象之间的相关程度，一般划分为四级：

如两者呈正相关，r呈正值，r=1时为完全正相关；

如两者呈负相关则r呈负值，而r=-1时为完全负相关。

完全正相关或负相关时，所有图点都在直线回归线上；

点子的分布在直线回归线上下越离散，r的绝对值越小。

当例数相等时，相关系数的绝对值越接近1，相关越密切；

越接近于0，相关越不密切。

当r=0时，说明X和Y两个变量之间无直线关系。

通常|r|大于0.8时，认为两个变量有很强的线性相关性。

公式=CORREL(A2:A7,B2:B7)返回结果是-0.9768，说明月份和销量之间存在很强的相关性，结果为负数表示负相关，意味着月份越大销量越低。

作者简介：2001年-2005年在江西移动负责BOSS系统运营工作，2005年开始就职于腾讯公司MIG运营商业务部，2011年开始兼职腾讯学院Excel课程讲师，2015年4月出版图书《Excel高手捷径：一招鲜，吃遍天》，人民邮电出版社出版，2015年12月在台湾出版繁体字版。