用Excel做线性回归分析

以 601015 陕西黑猫2016年3月14日至3月25日录入数据。录入结果见下图。

日期 成交量x（股） 收盘价y（元） 1 3月14日 695万 8. 2 3月15日 447万 8.76 3 3月16日 336万 8.68 4 3月17日 2万 8.91 5 3月18日 784万 9.24 6 3月21日 827万 9.45 7 3月22日 706万 9.31 8 3月23日 568万 9.38 9 3月24日 513万 9.16 10 3月25日 327万 9.27

由上图录入的数据制作散点图

1

回归

从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下：

⑴ 首先，打开“工具”下拉菜单，可见数据分析选项（见图5）(2007为”数据”右端的”数据分析”)：

图5

用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：

图6

⑵ 然后，选择“回归”，确定，弹出如下选项表：

2

图7

进行如下选择：X、Y值的输入区域（B1:B11，C1:C11），标志，置信度（95%），新工作表组，残差，线性拟合图。

或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%）， 新工作表组，残差，线性拟合图。

注意：选中数据“标志”和不选“标志”，X、Y值的输入区域是不一样的：前者包括数据标志：

最大积雪深度x(米) 灌溉面积y(千亩)

后者不包括。这一点务请注意。

图8-1 包括数据“标志”

3

图8-2 不包括数据“标志”

⑶ 再后，确定，取得回归结果（图9）。

图9 线性回归结果

⑷ 最后，读取回归结果如下：

4

截距：a2.356；斜率：b1.813；相关系数：R0.9；测定系数：R20.979；F值：F371.945。

⑸ 建立回归模型，并对结果进行检验

ˆ2.3561.813x 模型为：y至于检验，R、R2和F值可以直接从回归结果中读出。实际上，R0.94160.632R0.05,8，检验通过。有了R值，F值和t值均可计算出来。F值的计算公式和结果为：

FR21(1R2)nk10.941621(10.94162)1011371.9455.32F0.05,8

显然与表中的结果一样。t值的计算公式和结果为：

tR1Rnk120.97941610.9794161011n10i119.2862.306t0.05,8

回归结果中给出了残差（图10），据此可以计算标准离差。首先求残差的平方

ˆi)，然后求残差平方和Si(yiy离差为

222i1.7240.17416.107，于是标准

s于是

n1116.1072ˆ(yy)S1.419 iink1i1v8s1.4190.038810~15%0.1~0.15 y36.53图10 y的预测值及其相应的残差等

进而，可以计算DW值（参见图11），计算公式及结果为

5

DW(i2nini1)22ii1(1.9111.313)2(0.4170.833)20.751 222(1.313)(1.911)0.417取0.05，k1，n10（显然v10118），查表得dl0.94，du1.29。显然，DW=0.751dl0.94，可见有序列正相关，预测的结果令人怀疑。

图11 利用残差计算DW值

最后给出利用Excel快速估计模型的方法：

6