《基于Excel 的地理数据分析》多元线性回归分析.docx

基于Excel 的地理数据分析多元线性回归分析多元线性回归分析是一元线性回归分析的推广，或者说一元线性回归分析是多元线性回归分析的特例。掌握了一元线性回归分析，就不能学习多元线性回归分析方法了。利用Excel进行多元线性回归与一元线性回归的过程大体相似，操作上有些细节方面的微妙差别。不过，对于多元线性回归，统计检验的内容相对复杂。下面以一个简单的实例予以说明。【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。通过产值的回归模型，探索影响交通运输业的主要因素。我们想要搞清楚的是，在工业、农业和固定资产投资等方面，究竟是哪些因素直接影响运输业的发展。数据来源于李一智主编的《经济预测技术》。原始数据来源不详。§2.1 多元回归过程2.1.1 常规分析在 Excel 中，多元线性回归大体上可以分为如下几个步骤实现。第一步，录入数据。结果如下图所示（图2-1-1）。第二步，计算过程。比较简单，分为如下若干个步骤。打开回归对话框。沿着主菜单的“工具（T）”→“数据分析（D）…” 路径打开“数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析选项框，选项框的各选项与一元线性回归基本相同（图2-1-2）。具体说明如下。（2）输入选项。首先，将光标置于“Y值输入区域（Y）”中。从图2-1-1所示的F1单元格起，至F19止，选中用作因变量全部数据连同标志，这时“Y值输入区域（Y）”的数据区域中立即出现“$F$1:$F$19”。然后，将光标置于“X值输入区域（X）”中。从图2-1-1所示的C1单元格起，至E19止，选中用作自变量全部数据连同标志，这时“X值输入区域（X）”中立即出现“$C$1:$E$19”——当然，也可以直接在“X值输入区域（X）”中手动输入地址为“$C$1:$E$19”的单元格范围。注意，与一元线性回归的设置一样，这里数据范围包括数据标志“工业产值x1”、“农业产值x2”、“固定资产投资x3”和“运输业产值y”。因此，选项框中一定选中“标志”项（图2-1-3）。如果不设“标志”项，则“X值输入区域（X）”的对话框中应为“$C$2:$E$19”，“Y值输入区域（Y）”的对话框中则是“$F$2:$F$19”。否则，计算结果不会准确。完成上述设置以后，确定，立即给出回归结果。由于这里的“输出选项”选中了“新工作表组（P）”（图2-1-3），输出结果在出现在新建的工作表上（图2-1-4）。第三步，结果解读。这一步与一元线性回归也没有太大差别。（1）读出回归系数，建立模型。从图2-1-4所示的“输出摘要（SUMMARY OUTPUT）”中可以读出截距a，以及三个回归系数b1、b2和b3，对应于三个变量工业产值x1、农业产值x1、固定资产投资x2。数值如下a = ?1.0044 , b1 = 0.053326 , b2 = ?0.00402 , b3 = 0.090694 .与t统计量等价的时P值。P小于0.05，表明回归系数的置信度达到95％以上，相应的t检验在显著性水平为α=0.05时可以通过；P小于0.01，表明回归系数的置信度达到99%以上，相应的t检验在显著性水平为α=0.01时可以通过。其余依此类推。为了简明，可以将P值添加到线性回归模型里面，得到展，交通运输业越是受到负面影响。这在道理上是不通的。按理说，农业增长应该引起交通运输业的进一步发展才对。其二，回归系数b2的t检验不能通过。回归系数的P值高达0.779，置信度只有20%左右，这就有问题了。其三，回归系数b2的绝对值偏小。可以判定，自变量之间可能存在多重共线性问题。2.1.2 偏相关系数的计算和分析在具有多重共线性的线性回归问题中，偏相关系数（partial correlation coefficient）在进行变量取舍判断时具有一定的参考价值。Excel不能直接给出偏相关系数，但借助有关的函数或命令，可以方便地算出偏相关系数。计算公式为有了上述公式，可以借助计算矩阵行列式的函数mdeterm计算逆矩阵，然后计算偏相关系数。最快速的办法是利用矩阵求逆函数minverse。具体工作可以由以下几个步骤完成。第一步，计算相关系数相关系数可以借助命令correl或者pearson逐一计算。为了直观和便捷，不妨给出相关系数矩阵。首先，沿着“工具（T）→数据分析（D）”的路径，从工具箱的“数据分析”对话框中选择“相关系数”（图2-1-5）。然后，根据图2-1-1所示的数据分布的单元格范围，在“相关系数”对话框中进行如下设置（图2-1-6）。注意：“输入区域（I）”中包括自变量和因变量覆盖的数据范围，包括数据标志，并且我们是逐列计算。确定以后，得到相关系数矩阵。由于相关矩阵是对称的，Excel只给出了下三角部分（图2-1-7）。容易根据对称性将上三角部分填补起