预测与决策实验指导书一

《预测与决策实验指导书一》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、《预测与决策》实验指导书课程编号：B0107041C课程名称：预测与决策实验学时：4适应专业：经济与管理学院各专业目录实验一:一元线性回归在Excel中的实现实验二:移动平均法在Excel+的实现实验三:指数平滑法在Excel的实现实验四:移动平均追势模型在Excel中的实现实验一一、实验目的与基本要求1、熟悉Excel有关命令的使用方法2、利用Excel掌握有关菜单的功能3、掌握一元线性回归在Excel屮实现的基木方法，熟悉Excel的有-关操作二、实验仪器与设备计算机三、实验内容与具体步骤（要详

2、细）1>进入Excel程序。2、一元线性回归的输入形式：ABCD1nx

3、y2111.68.53214.111.14317.113.65419.615.86522.117.67625.620.58733.627.89840.533.510947.839.2绘出散点图:计算y2及xy,分别在“D2、E2、F2”单元格通过相对引用输入计算公式并向下复制。F2▼A二B2*C2ABcDEF1nXy2X2y2111.68.5134.5672.2598.6

4、3214.111.14317.113.65419.615

5、.86522.117.67625.620.58733.627.89840.533.5101947.839.2D2▼A=B2*B2ABCDEF1nXyx22X翌2111.68.5134.5672.2598.63214.111.1198.81123.21156.514317.113.6292.41184.96232.565419.615.8384.16249.64309.686522.117.6488.41309.76388.967625.620.5655.36420.25524.88733.627.8

6、1128.96772.84934.089840.533.51640.251122.251356.7510947.839.22284.841536.641873.76在“All”单元格输入求和公式并向右复制计算工X、工y、工X?、工y?及工xy。Bll▼A二SUM(B2：B10)ABCDEF1nXyx22yxy2111.68.5134.5672.2598.63214.111.1198.81123.21156.514317.113.6292.41184.96232.565419.615.8384.162

7、49.64309.686522.117.6488.41309.76388.967625.620.5655.36420.25524.88733.627.81128.96772.84934.089840.533.51640.251122.251356.7510947.839.22284.841536.641873.7611合计232

8、r令b=Fll▼A二SUM(F2：F10)ABcDEF1nXyx22yxy2111.68.5134.5672.2598.63214.111.1198.81123.21156

9、.51”4317.113.6292.41184.96232.565419.615.8384.16249.64309.686522.117.6488.41309.76388.967625.620.5655.36420.25524.88733.627.81128.96772.84934.089840.533.51640.251122.251356.7510947.839.22284.84]536加41873.7611合计232187.67207.764791.8

10、5875.?l4、一元线性回归系数的计算

11、:a=y-bxA=(A1O*F11-B11*C11)/(A1O*D11-B11*B11)IJKLMb=0.847206〕a=g=C11/A1O-K2*B11/A1O(IJKLb=0.847206a=-0.994641i:按Y=a+bX计算估计值:ABCDEFGH1nXyx22yxyy2111.68.5134.5672.2598.68.8329493214.111.1198.81123.21156.5110.950964317.113.6292.41184.96232.5613.492585419.6

12、15.8384.16249.64309.6815.61066522.117.6488.41309.76388.9617.728617625.620.5655.36420.愛524.820.693838733.627.81128.96772.84934.0827.471489840.533.51640.251122.251356.7533.317210947.839.22284.841536.641873.7639.501811121合计232187.67207.764