13.11：如何使用微软 Excel® 进行回归分析

Last updated
Save as PDF

Page ID: 204951

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

本章的这一部分是为了认识到，我们现在要问的不仅仅是快速计算比率或平方根。事实上，回归分析的使用在上世纪中叶之前几乎不存在，而且直到1960年代末和1970年代初才真正成为一种广泛使用的工具。即便如此，按照当今的标准，即使是最大的IBM机器的计算能力也是可笑的。在早期，程序是由研究人员开发并分享的。没有所谓的 “软件” 市场，当然也没有什么叫做 “应用程序” 的市场，进入市场只有几年的历史。

随着个人计算机的出现和重要软件市场的爆炸式增长，我们有许多回归和统计分析包可供选择。每个人都有自己的优点。我们之所以选择 Microsoft Excel，是因为无论是在大学校园还是在大学后市场，都可以广泛使用。 Stata是一种替代方案，如果你选择走这条路，它的功能对于更高级的计量经济学研究非常重要。还有更高级的软件包，但通常需要分析师进行大量编程才能进行分析。本节的目标是演示如何使用 Excel 运行回归，然后使用需求曲线的简单版本的示例进行回归。

使用 Excel 进行回归的第一步是将程序加载到您的计算机中。如果你有 Excel，你就有 Analysis ToolPak，尽管你可能没有激活它。该程序占用大量空间，因此不会自动加载。

要激活分析工具包，请执行以下步骤：

单击 “文件” > “选项” > “加载项” 以显示插件 “ToolPaks” 的菜单。选择 “Analysis ToolPak”，然后单击窗口底部附近的 “管理：excel 插件” 旁边的 “开始”。这将打开一个新窗口，你可以在其中单击 “Analysis ToolPak”（确保框中有绿色复选标记），然后单击 “确定”。现在数据菜单下应该有一个 “分析” 选项卡。这些步骤显示在以下屏幕截图中。

单击 “数据”，然后单击 “数据分析”，然后单击 “回归” 和 “确定”。恭喜，你已经进入回归窗口了。该窗口要求您输入。单击\(Y\)和\(X\)范围旁边的复选框将允许您使用 Excel 的点击和拖动功能来选择您的输入范围。 Excel 有一个奇怪的怪癖，那就是点击和拖放功能要求自变量（\(X\)变量）全部在一起，这意味着它们形成一个单一矩阵。如果您的数据设置为两列\(Y\)变量之间的变\(X\)量，Excel 将不允许您使用单击和拖动。举个例子，假设列 A 和列 C 是自变量，列 B 是\(Y\)变量，即因变量。 Excel 不允许您单击和删除数据范围。解决方案是将带有\(Y\)变量的列移动到列 A，然后可以单击并拖动。如果你只想用一些\(X\)变量运行回归，同样的问题会再次出现。你需要设置矩阵，这样你想要回归的所有\(X\)变量都在紧凑的矩阵中。这些步骤在以下场景镜头中进行了介绍。

一旦你选择了用于回归分析的数据并告诉 Excel 哪一个是因变量 (\(Y\))，哪些是独立\(X\)的贵重物品，你就可以在参数和输出如何显示方面有多种选择。请参阅 “输入” 部分\(\PageIndex{22}\)下的屏幕截图。如果你选中 “标签” 复选框，程序会将每个变量的第一列中的条目作为其名称放在输出中。您可以在 Excel 电子表格的第一行为每个变量输入实际名称，例如需求分析中的价格或收入，该名称将显示在输出中。

重要性级别也可以由分析师设定。这不会改变计算出的 t 统计量，称为 t 统计量，但会改变计算的 t 统计量的 p 值。它还将改变系数的置信区间的边界。始终显示 95% 的置信区间，但更改此置信区间后，您还将获得区间的其他置信水平。

Excel 还允许你抑制截距。这迫使回归程序在估计线必须穿过原点的条件下最小化残差平方和。在模型中除了零以外的某个值没有任何意义的情况下，可以执行此操作，行首为零。一个例子是经济生产函数，它是输入（比如劳动时数）的单位数与产出之间的关系。零工人的正产出是没有意义的。

输入数据并做出选择后，单击 “确定”，默认情况下，结果将发送到单独的新工作表中。 Excel 的输出以其他回归包程序的典型方式呈现。第一个信息块给出了回归的总体统计数据：倍数\(R\)、\(R\)平方和根据自由度调整后的\(R\)平方，也就是你要报告的平方。您还会得到（估计值）的标准误差和回归中的观测值数。

第二个信息块名为 ANOVA，代表方差分析。我们对本节感兴趣的是标记的列\(F\)。这是原假设的计算\(F\)统计数据，即所有系数都等于零，而至少有一个系数不等于零。这个假设检验是在 13.4 的 “方程有多好？” 下提出的下一列在 “显著性 F” 的标题下给出了本次检验的 p 值。如果 p 值小于 0.05（计算出的\(F\)统计量在尾部），我们可以以 90% 的置信度说我们不能接受所有系数都等于零的原假设。这是一件好事：这意味着至少有一个系数与零有显著差异，因此确实会对的值产生影响\(Y\)。

最后一个信息块包含单个系数的假设检验。首先列出估计系数、截距和斜率，然后列出（估计系数的）每个标准误差，然后是 t 统计数据（根据系数等于零的原假设计算得出的学生的 t 统计量）。我们根据自由度比较学生 t 的 t 统计数据和临界值，并确定我们是否有足够的证据来否定该变量没有影响的空值\(Y\)。请记住，我们已经将原假设设定为现状，而我们声称自己知道是什么原因\(Y\)导致了变革是在备择假设中。我们想拒绝现状，取而代之的是我们的世界版本，即另类假设。下一列包含此假设检验的 p 值，然后是我们在开头设置的各种置信水平的估计斜率参数的置信区间的估计上限和下限。

估算对玫瑰的需求

以下是使用 Excel 程序针对特定案例运行回归的示例：估算对玫瑰的需求。我们正在尝试估计需求曲线，从经济学理论来看，我们预计某些变量会影响我们购买多少商品。商品价格与需求数量之间的关系是需求曲线。除此之外，我们还有包括其他相关变量的需求函数：一个人的收入、替代商品的价格，可能还有其他变量，例如当年的季节或赠品的价格。需求量将是我们的\(Y\)变量，玫瑰的价格、康乃馨的价格和收入将是我们的自变量，即\(X\)变量。

对于所有这些变量，理论告诉我们预期的关系。对于所讨论商品的价格，玫瑰，理论上预测了一种反向关系，即需求曲线呈负向倾斜。理论还预测了一种商品（这里是玫瑰）的需求量与替代品（在本例中为康乃馨）的价格之间的关系。理论预测，这应该是一种积极或直接的关系；随着替代品价格的下降，我们从玫瑰代替了更便宜的替代品康乃馨。替代品价格的降低会减少对正在分析的商品（这里是玫瑰）的需求。减少产生减少是一种积极的关系。对于普通商品，理论也预测了积极的关系；随着收入的增加，我们会购买更多的好东西，玫瑰。我们期待这些结果，因为这是一百年的经济理论和研究所预测的。本质上，我们正在检验这些百年前的假设。收集的数据由正在测试的模型确定。应该始终如此。人们不是通过向计算机投掷大量数据然后向机器询问理论来进行推断性统计。理论第一，测试随之而来。

这里的这些数据是全国平均价格，收入是国家的人均个人收入。需求量是全国玫瑰的年销售总量。这些是年度时间序列数据；我们正在追踪1984-2017年美国上涨的市场，共有33次观测结果。

由于 Excel 要求将数据输入回归包的方式很古怪，因此最好将自变量、玫瑰的价格、康乃馨的价格和收入放在电子表格中彼此相邻。将数据输入电子表格后，最好查看数据。检查范围、均值和标准差。使用您对本课程第一部分中描述性统计数据的理解。在大型数据集中，您将无法 “扫描” 数据。使用 Analysis ToolPac 可以轻松获取分布的范围、均值、标准差和其他参数。你也可以快速获得变量之间的相关性。检查异常值。查看历史记录。发生了什么事吗？这是否是劳工罢工，进口费用的变化，使这些观察结果与众不同？不要毫无疑问地获取数据。某处可能有错字，谁不经审核就知道了。

进入回归窗口，输入数据并选择 95% 的置信水平，然后单击 “确定”。如果您在每列的顶部添加了标题，则可以在输入范围内包含标签，但如果您这样做，请务必单击主回归页面上的 “标签” 框。

回归输出应自动显示在新工作表上。

给出的第一个结果是 R-Square，它是衡量\(Y\)和\(X_1\)、之间关联强度的指标\(X_2\)，作为一个组\(X_3\)来衡量。我们这里的R平方为0.699，经自由度调整后，这意味着对玫瑰的需求Y的70％的变化可以用玫瑰价格\(X_1\)、\(X_2\)和玫瑰价格\(X_3\)、康乃馨价格和收入的变化来解释。没有统计检验可以确定某的 “显著性”\(R^2\)。当然，更高的值\(R^2\)是首选，但实际上，系数的重要性将决定所检验理论的价值，如果事实证明它们与零有显著差异，则这些系数将成为任何政策讨论的一部分。

看看输出的第三个面板，我们可以将方程写成：

\[Y=b_{0}+b_{1} X_{1}+b_{2} X_{2}+b_{3} X_{3}+e\nonumber\]

其中\(b_0\)是截距，\(b_1\)是玫瑰价格的估计系数，b ₂ 是康乃馨价格的估计系数，\(b_3\)是收入的估计影响，e 是误差项。方程用罗马字母书写，表示这些是估计值，\(\beta\)而不是总体参数。

我们的估计方程为：

\[\text { Quantity of roses sold }=183,475-1.76 \text { Price of roses }+1.33 \text { Price of carnations }+3.03 \text { Income }\nonumber\]

我们首先观察到系数的符号符合理论上的预期。需求曲线向下倾斜，玫瑰价格出现负面信号。此外，正如经济学理论所预期的那样，康乃馨价格和收入系数的迹象都是积极的。

解释系数可以告诉我们每个变量的变化对玫瑰需求的影响程度。正是能够做到这一点，才使回归分析成为一种有价值的工具。估计系数告诉我们，玫瑰价格上涨一美元将导致购买的玫瑰数量减少1.76美元。康乃馨的价格似乎在玫瑰需求中起着重要作用，因为我们看到，将康乃馨的价格提高一美元将使对玫瑰的需求增加1.33个单位，因为消费者将取代现在更昂贵的康乃馨。同样，人均收入增加一美元将导致玫瑰花购买量增加3.03个单位。

这些结果与经济学理论对玫瑰需求估计中包含的所有三个变量的预测一致。重要的是首先要有一个理论来预测系数的显著性或至少是方向。如果没有理论可以检验，这个研究工具并不比我们之前学到的相关系数更有用。

但是，我们不能就此止步。我们需要首先检查我们的系数从零开始是否具有统计显著性。我们建立了一个假设：

\[H_{0} : \beta_{1}=0\nonumber\]

\[H_{\mathrm{a}} : \beta_{1} \neq 0\nonumber\]

对于回归中的所有三个系数。回想一下，我们无法明确地说出我们的估计值\(b_1\)是实际的实际人口\(\beta_1\)，而只能在置信\((1-\alpha) \%\)度上说出我们无法否认我们的估计值与零有\(\beta_1\)显著差异的原假设。这位分析师声称，玫瑰花的价格会影响需求量。实际上，所包含的每个变量都会影响所需的玫瑰数量。因此，索赔属于备选假设。要推翻原假设，即现状，需要很大的概率，在本例中为0.95\(\beta = 0\)。在所有回归假设检验中，主张是备选方案，声称理论发现了一个对变量有重大影响的\(Y\)变量。

该假设的检验统计量遵循熟悉的标准化公式\(t\)，该公式计算标准差的数量\(b_1\)，即参数的估计值偏离假设值\(\beta_0\)，在本例中为零：

\[t_{c}=\frac{b_{1}-\beta_{0}}{S_{b_{1}}}\nonumber\]

计算机计算该检验统计量并将其显示为 “t stat”。可以在系数估计值的标准误的右侧找到该值。系数的标准误\(b_1\)在公式\(S_{b_1}\)中。为了得出结论，我们将该检验统计量与学生\(t\)在自由度下的临界值进行比较\(n-3-1 =29\)，alpha = 0.025（双尾检验的显著性水平为 5%）。我们的\(t\)统计数据约\(b_1\)为 5.90，大于 1.96（我们在 t 表中查找的临界值），因此我们不能接受无效的原假设。我们得出结论，价格具有显著影响，因为计算出的 t 值在尾部。我们对 b2 和 b3 进行相同的测试。对于每个变量，我们发现我们不能接受无关系的原假设，因为计算出的每种情况的 t 统计量都在尾部，也就是说，大于临界值。本回归中的所有变量都被确定为对玫瑰的需求有显著影响。

这些测试告诉我们单个系数是否与零有显著差异，但没有解决模型的整体质量。我们已经看到，根据自由度调整后的R平方表明，这个具有这三个变量的模型解释了所需玫瑰数量的70％的变化。我们还可以对整个模型进行第二次测试。这是本章第 13.4 节中介绍的\(F\)测试。因为这是多元回归（多个 X），所以我们使用\(F\)-test 来确定我们的系数是否共同影响\(Y\)。假设是：

\[H_{0} : \beta_{1}=\beta_{2}=\ldots=\beta i=0\nonumber\]

\[H_a: "\text{at least one of the} \beta_i \text{ is not equal to 0}"\nonumber\]

在输出的方差分析部分下，我们可以找到该假设的计算\(F\)统计量。在本示例中，\(F\)统计数据为 21.9。同样，将计算出的\(F\)统计数据与给定我们所需的显著性水平和自由度的临界值进行比较将使我们得出结论。

得出此统计检验结论的最佳方法是使用 p 值比较规则。 p 值是给定计算出的\(F\)统计数据后尾的面积。从本质上讲，计算机是在为我们找到表中的\(F\)值并计算 p 值。在 “显著性 F” 下的摘要输出中是这个概率。在本示例中，计算结果为 2.6\(X\) 10-5 或 2.6，然后将十进制五位向左移动。(.000026) 对于显著性水平来说，这是一个几乎无穷小的概率水平，肯定低于我们的 alpha 水平 0.05。

由于无法接受零假设，我们得出结论，该模型的这种规范是有效的，因为至少有一个估计系数与零有显著差异。由于\(F\)-calculated 大于\(F\)-critical，因此我们不能接受 H0\(X_1\)，这意味着\(X_2\)和\(X_3\)加在一起会产生显著影响\(Y\)。

计算机机器的开发以及可用于学术和商业研究的软件使回答几年前我们甚至无法提出的问题成为可能。数据以电子格式提供，可以以十年前无法想象的方式和速度转移到原地进行分析。今天可用于研究和分析的大量数据集为我们提供了比过去更高的结果质量。即使只有 Excel 电子表格，我们也可以进行非常高级别的研究。本节为您提供了进行一些非常有趣的研究的工具，唯一的限制是您的想象力。