时间数列计算方法.docVIP

实验七 相关与回归分析 一、实验目的：掌握相关图的绘制，相关系数的计算，回归分析和抽样估计的方法。 二、实验环境：使用的软件为EXCEL2003。硬件为PC机，每人一台机器。 三、实验内容与实验步骤 实验内容、原理分析及具体实验步骤见讲义。 四、实验结果总结 对实验结果进行分析，完成思考题目，总结实验的心得体会，并提出实验的改进意见。 实验内容及步骤： 一、相关图的绘制 以下面资料建立EXCEL工作表。 广告投入 销售额 12.5 21.2 15.3 23.9 23.2 32.9 26.4 34.1 33.5 42.5 34.4 43.2 39.4 49 45.2 52.8 55.4 59.4 60.9 63.5 制作相关图的步骤如下： 选择区域A1:B11；点击EXCEL图表向导； 在“图表类型”中选择“XY散点图”；在“子图表类型”中选择第一种散点图，并点击“下一步”，选“系列产生在列”，点击“下一步”，X轴“销售额”，Y轴“广告投入”，点击下一步“作为其中对象插入”，“完成”，并对图形进行修饰编辑，最后得到如图所示广告投入与月平均销售额之间的散点图。 图1 散点图的制作 二、相关系数 在EXCEL中，相关系数函数和相关系数宏提供了两种计算相关系数的方法。 1、相关系数函数 在EXCEL中，CORREL函数和PERSON函数提供了计算两个变量之间的相关系数的方法，这两个函数是等价的。与相关系数有关的函数还有RSQ（相关系数的平方，即判定系数r2）。 在这里我们以CORREL函数和上面的资料为例，介绍利用函数计算相关系数的方法。 ◆选放相关系数的空白单元格，点击EXCEL函数图钮“fx”，选择“统计”函数； ◆在统计函数点击“CORREL”，进入函数向导； 在“array1”中输入第一个变量“广告投入”的数据区域A2:A11（也可以点击其右侧红色按纽，用鼠标选中输入数据区域a2:a11）；“array2”中输入第二个变量“月均销售额”的数据区域B2:B11样，即可在当前光标所在单元格显示函数的计算结果（0.994198）。 2、相关系数宏 在EXCEL数据分析宏中，EXCEL专门提供了计算相关系数宏过程。利用此宏过程，可以计算多个变量之间的相关矩阵。 （1）单相关系数的计算 以上面资料为例，利用相关系数宏计算相关系数矩阵的过程如下： ◆点击EXCEL“工具”菜单，选择“数据分析”过程；选择“相关系数”过程。 ◆在“输入区域”中输入两个变量所在区域A2:B11，分组方式“逐列”，输出区域选择在同一工作表中的以D1区域。计算结果如图所示。 （2）复相关系数的计算 下表是某品牌笔记本电脑10中型号产品的价格、重量和CPU主频的有关数据资料： 品牌笔记本电脑10中型号产品的数据资料 型号 价格（万元） 重量（千克） CPU主频 1 0.9 1.2 350 2 0.7 1.5 350 3 1.3 1.1 433 4 1.5 0.8 500 5 1.9 0.7 650 6 2.0 1.4 700 7 2.2 0.8 650 8 2.8 0.5 700 9 1.5 1.2 500 10 3.2 0.6 750 要求：计算价格、重量与CPU主频的二元线性偏回归系数矩阵。 打开excel工作簿，执行“工具/数据分析/相关系数”，在对话框中把价格、重量、主频三个变量值（B3:D12）输入输入区域，选定输出区域，单击“确定”按钮，就能得到价格、重量与CPU主频的二元线性偏回归系数矩阵。 列 1 列 2 列 3 列 1 1 列 2 -0.76044 1 列 3 0.934828 -0.63129 1 三、回归分析 EXCEL虽然提供了9个函数用于建立回归模型和回归预测。这9个函数列于表中。但EXCEL提供的回归分析宏仍然具有更方便的特点。 函数名 定 义 INTERCEPT 一元线性回归模型截距的估计值 SLOPE 一元线性回归模型斜率的估计值 RSQ 一元线性回归模型的判定系数（r2） FORECAST 依照一元线性回归模型的预测值 STEYX 依照一元线性回归模型的预测值的标准误差 TREND 依照多元线性回归模型的预测值 GROWTH 依照多元指数回归模型的预测值 LINEST 估计多元线性回归模型的未知参数 LOGEST 估计多元指数回归模型的未知参数 1、使用LINEST函数计算回归统计值 例：某超市2005.1-10月份销售额资料如图，试用最小平方法求出趋势方程。 选择单元格C2:D6，输入公式“=LINEST(B2:B11,A2:A11,,1)”，按住Ctrl+Shift组合键，再按回车键，则在C2:D6区域中显示计