2022年Excel数据分析工具进行多元回归分析

1、使用 excel数据分析工具进行多元回归分析使用 excel数据分析工具进行多元回归分析与简单的回归估算分析方法基本相同。但是由于有些电脑在安装办公软件时并未加载数据分析工具，所以从加载开始说起（以excel2010版为例，其余版本都可以在相应界面找到）。点击 “文件 ” ，如下图：在弹出的菜单中选择“选项 ” ，如下图所示：在弹出的 “选项 ” 菜单中选择 “加载项 ” ，在 “加载项 ”多行文本框中使用滚动条找到并选中“ 分析工具库 ” ，然后点击最下方的“转到 ” ，如下图所示：精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 1 页，共 12

2、页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 1 页，共 12 页 - - - - - - - - -在弹出的 “加载宏 ”菜单中选择 “ 分析工具库 ” ，然后点击“确定 ” ，如下图所示：加载完毕，在 “数据 ”工具栏中就出现“数据分析 ”工具库，如下图所示：精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 2 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 2 页，共

3、12 页 - - - - - - - - -给出原始数据，自变量的值在a2：i21 单元格区间中，因变量的值在j2：j21中，如下图所示：假设回归估算表达式为：试使用 excel数据分析工具库中的回归分析工具对其回归系数进行估算并进行回归分析：点击 “数据 ”工具栏中中的“数据分析 ”工具库，如下图所示：在弹出的 “数据分析 ”-“分析工具 ”多行文本框中选择“回归 ” ，然后点击“确定 ” ，如下图所示：精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 3 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - -

4、 - - - - - - - - - - - 第 3 页，共 12 页 - - - - - - - - -弹出 “回归 ”对话框并作如下图的选择：上述选择的具体方法是：在“y值输入区域 ” ，点击右侧折叠按钮，选取函数y数据所在单元格区域j2： j21，选完后再单击折叠按钮返回；这过程也可以直接在“y值输入区域 ”文本框中输入j2：j21；在“x值输入区域 ” ，点击右侧折叠按钮，选取自变量数据所在单元格区域a2：i21，选完后再单击折叠按钮返回；这过程也可以直接在“x值输入区域 ”文本框中输入a2：i21；置信度可选默认的95%。在“ 输出区域 ”如选 “新工作表 ” ，就将统计分析结果输出

5、到在新表内。为了比较对照，我选本表内的空白区域，左上角起始单元格为k10.点击确定后，输出结果如下：精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 4 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 4 页，共 12 页 - - - - - - - - -第一张表是 “回归统计表 ”(k12：l17):其中：multiple r：（复相关系数r）r2的平方根，又称相关系数，用来衡量自变量x 与 y 之间的相关程度的大小。本例r=0.9134 表明它们之间的

6、关系为高度正相关。（ multiple ：复合、多种）r square ：复测定系数，上述复相关系数r的平方。用来说明自变量解释因变量y 变差的程度，以测定因变量y 的拟合效果。此案例中的复测定系数为0.8343，表明用用自变量可解释因变量变差的83.43%adjusted r square：调整后的复测定系数r2，该值为0.6852，说明自变量能说明因变量y的 68.52%，因变量y 的 31.48%要由其他因素来解释。 （ adjusted：调整后的）标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其它统计量，此值越小，说明拟合程度越好观察值：用于估计回归方程的数据的观察值个数。第

7、二张表是 “方差分析表 ”：主要作用是通过f 检验来判定回归模型的回归效果。该案例中的significance f（f显著性统计量）的p值为 0.00636，小于显著性水平0.05，所以说该回归方程回归效果显著，方程中至少有一个回归系数显著不为0.（significance：显著）第三张表是 “回归参数表 ”：k26：k35 为常数项和b1 b9 的排序默认标示。l26：l35为常数项和b1b9 的值，据此可得出估算的回归方程为：该表中重要的是o 列,该列的 o26：o35 中的 p-value 为回归系数t 统计量的p值。值得注意的是：其中b1、b7 的 t 统计量的p值为 0.0156 和

8、 0.0175，远小于显著性水平0.05，因此该两项的自变量与y 相关。而其他各项的t 统计量的p值远大于b1、b7 的 t 统计量的 p值，但如此大的p值说明这些项的自变量与因变量不存在相关性，因此这些项的回归系数不显著。精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 5 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 5 页，共 12 页 - - - - - - - - -回归分析是一种应用很广的数量分析方法，用于分析事物间的统计关系，侧重数量关系变化

9、。回归分析在数据分析中占有比较重要的位置。一元线性回归模型：指只有一个解释变量的线性回归模型，用来揭示被解释变量与另一个解释变量的线性关系。多元线性回归模型：指含有多个揭示变量的线性回归模型，用来揭示被解释变量与多个解释变量的线性关系。此篇文章主要讲述多元线性回归分析。方法 /步骤线性回归分析的内容比较多，比如回归方程的拟合优度检验、回归方程的显著性检验、回归系数的显著性检验、残差分析、变量的筛选问题、变量的多重共线性问题。操作见图。回归分析通常需要多次试验操作才可以得出较好的模型。“方法 ”中选择 “进入 ”，表示所有的自变量都进入模型，目前还没有考虑到变量的多重共线问题，要先观察初步的结果

10、分析，才会考虑发哦变量的多重共线问题。精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 6 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 6 页，共 12 页 - - - - - - - - -1.3通过观察调整后的判定系数0.924，拟合优度较高，不被解释的变量较少。由回归方程显著性检验的概率为0，小于显著性水平0.05，则认为系数不同时为0，被解释变量与解释变量全体的线性关系是显著的，可建立线性方程。由系数表知，观察回归系数显著性检验中的概率值，如果显

11、著性水平为0.05，除去 “投入人年数 ”外，其他变量均大于显著性水平，这些变量保留在方程中是不正确的。所以该模型不可用，应重新建模。精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 7 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 7 页，共 12 页 - - - - - - - - -2.4重新建模操作见图片，采用的是“ 向后筛选 ”方法，依次剔除的变量是专著数、投入高级职称的人年数、投入科研事业费、获奖数、论文数。最后的模型结果是“立项课题数 =-9

12、4.524+0.492x 投入人年数 ” 。精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 8 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 8 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 9 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 9 页，共 12 页

13、 - - - - - - - - -3.5残差分析：又 p-p图可知，原始数据与正态分布的不存在显著的差异，残差满足线性模型的前提要求。由库克距离（ 0.041 小于 1）和杠杆指变量的值知，没有显著的差异。残差点在 0 线周围随机分布。精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 10 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 10 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 11 页，共 12 页 - - - - - - - - -精品学习资料 可选择p d f - - - - - - - - - - - - - - 第 11 页，共 12 页 - - - -