Excel 中的回归分析

回归分析是统计建模的一部分，用于估计两个或多个变量之间的关系。在 MS Excel 中，您可以执行多项统计分析，包括回归分析。这是一个不错的选择，因为几乎所有计算机用户都可以访问 Excel。

Excel 提供了内置方法来计算回归。在 MS Excel 中，回归功能位于“数据”选项卡末尾。您需要从“加载项”中专门为您的 Excel 添加此“数据分析工具库”。

注意：Excel 用户无需从互联网安装“数据分析工具库”。它可在“加载项”中使用。

在深入研究之前，您必须了解——什么是回归分析？其中的变量类型以及许多其他基本知识。我们将在本章中解释所有这些术语。因此，请阅读本章直至结尾。

什么是回归分析？

回归分析是一种分析，它显示了因变量和自变量之间的关系，并生成一个方程。该方程包含一个系数，该系数表示因变量和自变量之间的关系。

简单线性回归

在简单线性回归中，一个变量的值用于描述另一个变量的值。被描述的变量称为因变量，而用于描述或预测因变量值的变量称为自变量。

注意：自变量和因变量是回归分析中最必不可少的两个术语。

回归中的变量类型

回归分析有两个变量

1. 因变量（预测变量）
2. 自变量（解释变量）

因变量是我们试图理解和预测的因素。因变量的值根据自变量而变化。相比之下，自变量是影响因变量并帮助预测因变量值的因素。

真实场景

让我们以一个场景来理解回归分析的变量——

例如，我们有存储在 Excel 工作表中的 12 个月的销售数据。这些数据是 1 月到 12 月的雨伞销售数据。每个月的销售量因降雨量而异。雨伞在七月卖得最多，在一月卖得最少。

这个 Excel 工作表将包含三列：月份（1 月至 12 月）、降雨百分比和销售雨伞数量（每个月销售的雨伞总数）。

此处，

因变量：雨伞

自变量：降雨百分比

因此，雨伞是因变量，其销量取决于每个月的降雨百分比，这是一个自变量。当降雨量高或低时，雨伞的销量会增加或减少。希望您已理解回归分析中的因变量和自变量。

验证“数据分析工具库”是否已安装

现在，在继续前进之前，请验证“数据分析工具库”是否已启用并在“数据”选项卡中可用。转到“数据”选项卡，然后在功能区的末尾查找“数据分析工具库”。参见下面的截图

如果未启用，请从加载项中将其添加到您的 Excel 中以执行回归分析。

如果“数据分析”选项不可用（如上图所示），请按照下面详细介绍的步骤将其添加到您的 Excel 中。

启用数据分析工具库

请按照以下步骤在“数据”选项卡中启用“数据分析工具库”。

步骤 1：在您当前活动的 Excel 工作表中，转到 Excel 菜单栏中的“文件”。

步骤 2：在左侧边栏的“更多？”中，您会看到“选项”选项。单击它，这将打开一个包含各种设置的面板。

步骤 3：在“Excel 选项”面板中，单击左侧边栏中的“加载项”。

步骤 4：在此，验证“管理”下拉列表框中已选择“Excel 加载项”。如果是，请单击下拉按钮旁边的“转到”。

步骤 5：在“Excel 加载项”对话框中，选中“分析工具库”并单击“确定”。

步骤 6：关闭所有多余的打开的选项卡，然后查看“数据”选项卡中是否已添加“数据分析”工具库。

现在，您的 Excel 已准备好对数据进行回归分析。因此，我们现在将对上述场景执行回归分析。

应用回归分析

现在，您将逐步了解如何在 Excel 数据上执行回归分析。我们在这里有这些数据。

步骤 1：在“数据”选项卡中，单击早期步骤中已添加到 Excel 的“数据分析”选项。

步骤 2：向下滚动，从列表中选择“回归”，然后单击此面板中的“确定”。

步骤 3：现在，在回归对话框中配置以下设置。

• 在“输入 Y 范围”中，提供因变量的单元格引用。在我们的数据集中，雨伞是位于 C 列的因变量。因此，单元格引用将是C2:C13。
• 在“输入 X 范围”中，提供自变量的单元格引用。例如，在我们的数据集中，降雨量是位于 B 列的自变量。因此，单元格引用将是B2:B13。
• 如果 X、Y 范围中包含标题单元格引用，请勾选“标签”复选框。
• 请仔细从此处选择一个输出选项。我们选择了“新建工作表”。
• 最后，勾选“残差”复选框，这将为您提供实际值和预测值之间的差异。

步骤 4：仔细输入所有必需的详细信息，然后单击“确定”。

设置好以下内容后，它将在 Sheet2 中生成分析摘要。

步骤 5：查看 Excel 回归分析生成的输出并进行观察。

此汇总输出将主要包含 REGRESSION STATISTICS（回归统计）、ANOVA（方差分析）和 RESIDUAL OUTPUT（残差输出）。所有这些详细信息都在同一页面上。

解释回归分析结果

我们已经执行了回归分析，您会注意到回归的执行非常容易。您不必做任何困难的事情，因为所有计算都会自动进行。完整的输出以及陈述都是自动生成的。

计算很容易，但解释却不容易理解。所以，是时候解释它的结果了。您已经看到输出包含四个主要部分：回归统计、方差分析和残差输出。让我们来分析它们

回归统计

回归统计告诉您线性回归方程如何拟合我们的数据。

让我们来理解回归统计表中使用的术语。

• 复相关系数 (Multiple R) 是相关系数，它有助于衡量两个变量之间线性关系的强度。复相关系数的值越高，变量之间的关系越强。
  1：强正相关
  -1：强负相关
  0：完全没有关系。
• R 方 (R Square) 是判定系数，目前其值为 0.9047。它表示拟合优度。四舍五入到小数点后两位，即 90%，这足以拟合我们的回归模型。这意味着 90% 的因变量可以通过自变量来解释。
  一般来说，R 方的值越高越好。
• 调整 R 方 (Adjusted R Square) 是 R 方的改进版，它针对自变量的数量进行了调整。它用于多重分析。
• 标准误差 (Standard Error) 也是一个拟合优度度量。回归方程对于较小的数值会更确定。
• 观测值 (Observation) 是模型中的总观测数。

ANOVA

回归分析的下一部分是 ANOVA（方差分析）。然后是系数部分和 ANOVA。

• 自由度 (Df) 指的是自由度。它与方差的来源相关。
• 平方和 (SS) 指的是平方和。
• 均方 (MS) 是均方。
• F 统计量 (F) 检验模型的整体显著性。
• 最后一个是显著性 F (Significance F)，它是 F 的 P 值。

ANOVA 表之后最重要的部分是系数。它允许用户在 Excel 中创建线性回归方程，即：

对于我们的数据集，月份、降雨量和销售雨伞，公式将是

将表格中的值放入此公式

为任何月份输入 x = 降雨量（毫米）的值。例如，我们为一月份的降雨量输入了 76。所以，

y= 0.327*76-15.417

y = 9.435

这是预测的一月份销售雨伞的数量。同样，您可以通过输入降雨百分比来预测任何月份将销售多少雨伞。

残差输出

最后一部分是残差，它显示了实际值和估计值之间的差异。如果您比较这两个值在每个月销售的雨伞总数上的结果，您会发现这两个数字之间存在细微差别。

如果您比较一月份实际销售的雨伞数量和预测值，您会发现它们之间存在细微差别。

一月份实际销售的雨伞 12

一月份销售雨伞的预测值 9.486

实际值和预测值之间的差异可以在各自的列中的残差中看到。

12 - 9.486 = 2.514

您可以在 RESIDUAL OUTPUT（残差输出）表下找到它。

制作线性回归图

您还可以制作图表并将值绘制在上面，以查看两个变量之间的关系。因此，绘制一个线性回归图。

步骤 1：在同一个 Excel 工作簿中打开您的 Sheet1，并选择自变量和因变量的列以及标题。

步骤 2：导航到“插入”选项卡，您会看到“图表”组。单击它，然后选择“散点图”（列表中的第一个）。为了方便起见，请遵循“插入”>“图表组”>“散点图”。

步骤 3：一个散点图将被插入到您当前活动的 Excel 工作簿中，它看起来会像这样——

步骤 4：现在，在此绘制的图表中绘制一个最小二乘回归线。为此，右键单击图表中的任意点，然后从上下文菜单中选择“添加趋势线”。

步骤 5：在“格式趋势线”面板的右侧选择“线性”趋势线形状。

步骤 6：向下滚动“格式趋势线”面板，并可选地勾选“在图表上显示公式”以获取回归公式。但是，这一项是可选的。

您现在可以看到回归方程已经创建。

步骤 7：现在，转到“填充与线条”选项来自定义您喜欢的线条。您可以在此处更改线条的颜色和类型。例如：使用实线而不是虚线。

• 首先，选择“实线”单选按钮，然后向下滚动。
• 将线条颜色更改为红色或其他您想要的颜色。
• 从“短划线类型”列表中选择“实线”。

查看自定义的线性回归图。

您可以在图表中进行一些改进，例如为图表提供轴标题（水平和垂直）。