如何在 Excel 中进行回归分析:完整指南

要在 Excel 中进行回归分析,请先启用 Analysis ToolPak,然后转到“数据”>“数据分析”并选择“回归”。选择您的 Y 值区域和 X 值区域,选取输出位置,然后点击“确定”。Excel 会在一个新表格中返回系数、R Square 以及其他统计数据。
本指南涵盖了在 Excel 中运行回归的三种方法:Analysis ToolPak、LINEST 函数和图表趋势线。它还提供了一个完整的示例,解释了如何解读输出结果,并展示了一种在大型文件中测试相关关系的更快方法。详细信息源自 Microsoft 支持页面,查阅日期为 2026 年 11 月 12 日。
回归分析能告诉你什么
回归分析衡量一个数值随另一个数值变化而变化的程度。您想要解释的数值是因变量,即 Y。您认为驱动它的数值是自变量,即 X。
Microsoft 这样描述回归工具:它“通过使用‘最小二乘法’拟合一组观测值的直线,来执行线性回归分析”。
这条直线有一个简单的方程。Microsoft 将其写作 y = mx + b,其中 m 是斜率,b 是截距。当有多个 X 时,方程变为 y = m1x1 + m2x2 + ... + b。
从业务角度来看,回归分析可以回答如下问题:
- 每增加一个月的增长,销售额会增加多少?
- 广告支出与注册量之间的相关性有多大?
- 成本的变化中有多少是由订单量解释的?
Microsoft 自己的例子是关于运动员的。它指出您可以分析“运动员的表现如何受到年龄、身高和体重等因素的影响”。
回归显示的是关联性,而非因果关系的证明。两个数值之间存在强关联直线,仍可能源于您未测量的第三个因素。
在 Excel 中设置数据
回归需要干净、匹配的成对数字。每一行应包含一个观测值,Y 位于一列,每个 X 位于其各自的列中。
以下是来自 Microsoft 简单线性回归示例的数据。它列出了六个月的销售额(以美元计)。
| 月份 | 销售额 |
|---|---|
| 1 | 3,100 |
| 2 | 4,500 |
| 3 | 4,400 |
| 4 | 5,400 |
| 5 | 7,500 |
| 6 | 8,100 |
将“月份”放在 A 列,“销售额”放在 B 列,标题放在第 1 行。下面的示例使用此布局。数据随后位于第 2 到 7 行,这就是为什么下面的公式使用这些区域的原因。
一些规则可以保持结果的可靠性:
- 无空单元格。 每一行都需要同时具备 Y 值和 X 值。
- 仅限数字。 以文本形式存储的数值会导致错误。
- 足够的行数。 六行适用于演示,但实际分析需要更多行。
- 每行一个观测值。 不要在一个区域中混合月度行和周度行。
启用 Analysis ToolPak
回归工具位于 Analysis ToolPak 中,这是一个随 Excel 提供的加载项。您可能需要先启用它。
Microsoft 针对 Windows 版 Excel 的步骤如下:
- 选择“文件”,然后选择“选项”,接着选择“加载项”。
- 在“管理”框中,选择“Excel 加载项”,然后选择“转到”。
- 在“加载项”框中,勾选“Analysis ToolPak”复选框,然后选择“确定”。
在 Mac 上,Microsoft 表示从文件菜单转到“工具”>“Excel 加载项”。如果未列出 ToolPak,请选择“浏览”以查找它。
启用后,您将在“数据”选项卡上看到“数据分析”。Microsoft 指出,如果该命令缺失,“您需要加载并激活 Analysis ToolPak 加载项程序”。
方法 1:使用 Analysis ToolPak 运行回归
这是最完整的方法。它通过一个步骤提供完整的摘要表。
- 转到“数据”选项卡并选择“数据分析”。
- 从列表中选择“回归”并点击“确定”。
- 在“输入 Y 区域”中,选择“销售额”列,包括标题。
- 在“输入 X 区域”中,选择“月份”列,包括标题。
- 勾选“标志”,因为两个区域都包含标题。
- 选择“输出区域”或“新工作表”。
- 点击“确定”。
Excel 会构建一个包含三个板块的摘要:回归统计、ANOVA 表和系数表。下一节将解释如何解读它们。
对于多元回归,请在第 4 步中选择多个相邻的 X 列。Microsoft 指出,该工具可以分析“单个因变量如何受到一个或多个自变量值的影响”。
在底层,该工具使用了一个工作表函数。Microsoft 表示“回归工具使用工作表函数 LINEST”。
方法 2:使用 LINEST 函数
LINEST 以公式形式提供相同的数字。它适合希望结果随数据变化而更新的用户。
Microsoft 的语法为 LINEST(known_y's, [known_x's], [const], [stats])。Known_y's 是“销售额”区域,known_x's 是“月份”区域。
对于销售数据,在空白单元格中输入以下内容:
=LINEST(B2:B7, A2:A7)
该函数返回两个值:斜率,然后是截距。Microsoft 表示 LINEST 返回的数组是 "{mn,mn-1,...,m1,b}",因此截距总是排在最后。
要获取更多统计信息,请将最后一个参数设置为 TRUE:
=LINEST(B2:B7, A2:A7, TRUE, TRUE)
当 stats 设置为 TRUE 时,LINEST 会返回标准误差、R 平方、F 统计量、自由度和平方和。Microsoft 指出,该函数“必须作为数组公式输入”,因为它一次返回多个值。
Microsoft 的页面还展示了一个单单元格预测。下面的公式根据第 1 到 6 个月估算第 9 个月的销售额:
=SUM(LINEST(B2:B7, A2:A7)*{9,1})
Microsoft 对此示例的结果是 11,000。
方法 3:为散点图添加趋势线
趋势线在图表上显示回归线。这是查看直线是否拟合的最快方法。
- 选择两列数据。
- 插入散点图。
- 选择图表,然后从图表元素菜单中添加“趋势线”。
- 打开“设置趋势线格式”窗格并保持“线性”选项。
- 勾选在图表上显示方程和 R 平方值的复选框。
图表随后会在直线旁边打印方程,例如 y = 1000x + 2000。这与销售数据的 LINEST 结果一致。
趋势线还可以进行前瞻预测。Microsoft 表示您可以“在‘前推’和‘后推’字段中设置一个值,以将您的数据预测到未来”。
趋势线适用于快速查看或演示文稿。对于标准误差、p 值或多个 X 变量,请使用方法 1 或方法 2。
应该使用哪种方法
每种方法适用于不同的工作。根据您需要展示的内容以及阅读对象进行选择。
| 方法 | 最适用于 | 提供 p 值 | 随数据更新 |
|---|---|---|---|
| Analysis ToolPak | 单一模型的完整报告 | 是 | 否,需重新运行工具 |
| LINEST | 工作电子表格内部的模型 | 否,但提供计算它们的统计数据 | 是 |
| 散点图趋势线 | 快速视觉检查或演示文稿 | 否 | 是 |
当您需要在会议中为结果辩护时,请使用 ToolPak。它的摘要表包含评审者可能询问的每个数字,包括每个系数的 p 值。
当模型为其他公式(如预测表)提供数据时,请使用 LINEST。因为它是一个公式,斜率和截距会在新行加入时立即更改。
当受众需要看到模式时,请使用趋势线。带有直线及其方程的图表比表格更容易阅读。许多分析师会同时使用这三种方法:用趋势线探索,用 LINEST 构建,用 ToolPak 报告。
回归分析示例
以下是三种方法针对六个月销售额返回的结果。
| 统计量 | 数值 | 含义 |
|---|---|---|
| 斜率 (m) | 1,000 | 销售额每月增长约 1,000 |
| 截距 (b) | 2,000 | 直线在第 0 个月的值 |
| R 平方 | 0.93 | 约 93% 的销售额变化遵循月度趋势 |
| 第 9 个月估算值 | 11,000 | 1,000 × 9 + 2,000 |
您可以手动检查斜率。平均月份为 3.5,平均销售额为 5,500。斜率是离差交叉乘积之和除以月份离差平方和。计算结果为 17,500 除以 17.5,即 1,000。
截距随之得出。它是平均销售额减去斜率乘以平均月份:5,500 减去 3,500,即 2,000。
R 平方告诉您直线的拟合程度。Microsoft 将其描述为一个“取值范围在 0 到 1 之间”的数值。为 1 时,直线拟合每个点。为 0 时,Microsoft 表示“回归方程在预测 y 值方面没有帮助”。
如何解读回归输出
Analysis ToolPak 的输出可能看起来很密集。大多数决策取决于少数几个数字。
- Multiple R。 实际值与预测值之间的相关性。越接近 1 表示拟合越紧密。
- R Square。 Y 的变化中由 X 解释的部分。这里约为 0.93。
- Adjusted R Square。 根据 X 变量数量调整后的 R Square。在比较具有不同输入数量的模型时使用它。
- 标准误差。 实际点与直线之间的典型距离。
- 系数。 截距和每个 X 的斜率。这些构成了您的方程。
- P 值。 如果真实效应为零,看到如此大系数的概率。许多分析师将低于 0.05 的值视为显著。
- F 和 Significance F。 对整个模型的检验。较小的 Significance F 表明模型解释的内容多于偶然因素。
Microsoft 增加了一条关于准确性的说明。它指出“数据越线性,LINEST 模型就越准确”。
以普通单位阅读系数。1,000 的斜率意味着在此期间,平均而言,每增加一个月,销售额就会增加约 1,000。
使用 AI 在 Excel 中进行回归分析
当您有很多列且不确定哪些列重要时,手动测试每一对数据会很耗时。AI 工具可以检查整个数据集中的相关关系并对其进行解释。
以下是使用 Powerdrill Bloom 的三个步骤。
第 1 步:上传您的 Excel 文件
上传包含 Y 列和候选 X 列的工作簿。Excel Calculations 页面会要求您先上传工作簿。它补充道:“确保您的数据格式正确,以实现顺畅的计算。”
保持每行一个观测值并清晰标注列标题。AI 使用标题来理解每一列的含义。
第 2 步:使用自然语言请求分析
描述您想要测试的内容。例如:“对销售额关于月份和广告支出运行线性回归,并解释哪一个更重要。”
同一页面指出您可以“以文本形式输入您想要的计算请求”。您可以提出后续问题,例如当您去掉一个异常月份时,相关关系是否仍然成立。
第 3 步:检查结果并绘制图表
在您自己的工作簿中对照 LINEST 检查斜率。然后直观地展示这种关系。Scatter Graph Creator 旨在“通过交互式且可定制的散点图可视化数据关系”。
对于超出直线的预测,AI Forecasting 工具可以构建带有置信带的预测图表。有关该方法本身的背景信息,请参阅我们关于 回归分析 的解释文章。
要避免的常见错误
这些是人们在 Excel 中运行回归时最常出现的错误。
- 混淆 X 和 Y。 Y 是您想要解释的数值。交换它们会改变斜率。
- 忘记勾选“标志”框。 如果您的区域包含标题,请勾选“标志”,否则 Excel 会将标题视为数据。
- 在网页版 Excel 中运行回归。 Microsoft 表示您“无法在那里创建回归分析”,因为回归工具不可用。请使用桌面应用程序。
- 仅看 R Square。 高 R Square 并不能证明因果关系,而且它可能掩盖了曲线模式。务必查看散点图。
- 使用行数太少。 六个点可能会偶然产生一条整齐的直线。更多的数据会提供更可靠的结果。
- 外推太远。 拟合第 1 到 6 个月的直线可能不适用于第 24 个月。
- 忽略异常值。 一个异常的月份可能会拉动整条直线。检查图表中远离其他点的点。
- 添加许多相关的 X 列。 同步移动的输入会使系数不稳定。Microsoft 指出 LINEST 会“检查共线性”并移除冗余的 X 列。
当您准备好测试整个数据集的相关关系时,请尝试使用去年的月度数据 体验 Powerdrill Bloom。
常见问题解答
如何在 Excel 中进行简单线性回归?
启用 Analysis ToolPak,转到“数据”>“数据分析”,然后选择“回归”。选择您的 Y 和 X 区域,如果包含标题则勾选“标志”,然后点击“确定”。您也可以使用 =LINEST(Y 区域, X 区域)。
我可以在网页版 Excel 中进行回归分析吗?
不能完全实现。Microsoft 表示您可以在网页版 Excel 中查看回归结果,但无法在那里创建回归分析。请在桌面应用程序中打开文件来运行它。
Excel 回归中的 R 平方是什么意思?
R 平方显示直线解释了 Y 变化的多少。它的范围从 0 到 1。0.93 的值意味着约 93% 的变化遵循拟合直线。
如何在 Excel 中进行多元回归?
将每个 X 变量放在其各自的相邻列中。在回归工具中,选择所有 X 列作为“输入 X 区域”。LINEST 也接受多个 X 列。
LINEST 和回归工具之间有什么区别?
它们提供相同的系数。Microsoft 表示回归工具使用的是 LINEST。该工具会编写一个完整的摘要表,而 LINEST 以公式形式返回数字,并随您的数据更新。
来源: Microsoft 支持,使用 Analysis ToolPak 执行复杂的数据分析 · Microsoft 支持,LINEST 函数 · Microsoft 支持,执行回归分析 · Microsoft 支持,向图表添加趋势线或移动平均线。