EXCEL 回归分析.doc

回归分析本节以某公司电视机销售量数据为例，介绍应用Excel进行回归分析，包括三个方面的内容：散点图、简单线性回归和多元线性回归。例1：某电视机公司想预测该公司生产电视机的年销售量,他们收集了从1997年到2006年共10年的年度销售量、广告支出、人事支出和营业总支出，如图5-1所示。图5-1 某公司电视机的统计数据1、绘制散点图散点图是一种比较直观地描述变量之间相互关系的图形。一般在做线性回归之前，需要用散点图检验数据。下面我们将用上图的数据来说明利用Excel绘制散点图的方法。在菜单中选择【插入】【图表】。在图表类型对话框中选择【XY散点图】，然后点【下一步】。在图表源数据对话框中给定数据区域为“销售量（千台）”和“广告支出”两列，并选择系列产生在“列”，然后点【下一步】。在图表选项对话框中，点击【标题项】，并填入图表标题“销售量与广告支出散点图”，分类（X）轴标题“广告支出（百万）”，数值（Y）轴标题“销售量（千台）”，如图5-2所示。点击【图例】，并把【显示图例】左侧的选项框清空，然后点【下一步】。在图表位置对话框中选择“作为其中的对象插入”，然后点【完成】，就可以看到下图5-3的结果。图5-2 某公司电视机的统计数据图5-3 销售量与广告支出散点图从图5-3中可以较直观地看出广告支出与销售量之间有一定的相关性。运用Excel工具还可以在散点图上插入趋势线，下面以图5-3为例说明趋势线的用法。具体方法如下：单击图中任一数据点以选取数据系列。在“图表”菜单中选择“添加趋势线”，就会出现如图5-4的添加趋势线对话框。图5-4 添加趋势线对话框单击【添加趋势线】对话框中的类型标签，单击线性图。单击【选项】标签，在选项对话框中选择“自动设置：线性”，选择【显示公式】复选框，然后选【确定】。就可以得到有趋势线的散点图（见下图5-5）。图5-5 带有趋势线的销售量与广告支出散点图 这样不仅显示了趋势线，连拟合方程都显示出来了。2、简单线性回归在某些情况下，散点图可以直观地看出数据间的相关性。但这还不足够，我们还需要用一个回归模型来描述因变量与自变量之间的变化关系，再通过控制或给定自变量的数值来估计或预测因变量可能的数值。下面仍以图5-1的数据为例来介绍用Excel进行简单线性回归分析的方法。步骤如下：在菜单中选择【工具】，在下拉菜单中选【数据分析】。在数据分析对话框中选择【回归】。点击【确定】。就会出现如下图5-6的回归对话框。图5-6 回归对话框在Y值输入区域（Y）中填入销售量列，在X值输入区域（X）中填入广告支出列。选中“标志”复选框并在适当的地方选择输出区域，然后点【确定】，就可以看到图5-7的回归结果。在图5-7中，可以找到简单回归分析所需要的所有结果。在回归统计表中可以找到Multiple R，对应正文中的相关系数；R Squanare，对应正文中的判定系数；Adjusted R Square，对应正文中经调整的判定系数；当然还有估计标准误差。在方差分析表中可以得到方差分析的结果。在最后一个表中可以找到简单线性回归方程的显著性检验的结果和区间估计的结果，其中对应Intercept和“广告支出”的系数，即简单线性回归方程中的截距a和斜b。最终我们得到的销售量和广告支出的简单线性回归方程为图5-7 简单线性回归结果3、多元线性回归在Excel中进行多元回归计算和简单回归计算差别不大，下面仍以图5-1的数据为例来介绍用Excel进行多元回归分析的方法。首先在菜单中选择【工具】，在下拉菜单中选【数据分析】。在数据分析对话框中选择【回归】，然后点【确定】。就会出现回归对话框。在Y值输入区域（Y）中填入销售量列。到这里的步骤与简单回归完全相同。在X值输入区域（X）中要填入广告支出和人事支出两列。选中【标志】复选框并在适当的地方选择输出区域，然后点【确定】，就可以看到下图5-8。在图5-8中我们可以看到两个自变量的系数，从而拟合出二元一次线性方程。最终得到的回归方程