线性回归的目标是根据观察到的数据创建一个模型，该模型捕捉自变量和因变量之间的关系，并使用该模型根据自变量的值预测因变量的值(尤其是对于最初没有观察到的自变量的值)。即使我们可以做出这样的预测，这并不意味着我们可以声称自变量和因变量之间存在任何因果关系。

本质上，我们在寻找一条最符合观测数据的直线。

回想一下，直线的方程式是，其中:

=直线的斜率

= y 截距，即直线与 y 轴相交的值

使用一种叫做普通最小二乘的技术，结果是:

Excel 提供以下函数，其中 R1 是包含数据值的范围，R2 是包含数据值的范围:

斜率(R2 R1)=如前所述的回归线的斜率

截距(R2 R1)=如前所述回归线的 y 截距

根据我们之前的观察:

=斜率(R2 R1)=协方差。美国(R1、R2) /风险值。S(R2)

= INTERCEPT(R1、R2)= AVERAGE(R1)–* AVERAGE(R2)

例:求图 66 中数据的回归线(图 75 左侧重复)。基于这个模型，一天抽 10 支、20 支或 30 支烟的人的预期寿命是多少？

结果显示在图 75 的右侧:

图 75:回归线和预测

因此，回归线是:

y = -0.6282x + 85.7204

对于 x 的任何值(= #吸烟数量)，回归方程将生成 y 的值，y 是吸烟者预期寿命的预测值。

例如，如果一个人每天抽 30 支烟，模型预测该人将活 66.87 岁(图 75 中的单元格 E10)。

Excel 提供以下功能来帮助执行这些预测，其中 R1 是包含数据值的范围，R2 是包含数据值的范围:

预测 (x，R1，R2) =计算给定值 x 的预测值

趋势 (R1、R2、R3) =预测与 R3 中的值相对应的值的数组函数

我们展示了如何在图 75 的单元格 E9 中使用 FORECAST，以及在图 75 的单元格 E8 中使用 TREND。

实际上，TREND 是一个数组函数，因此它可以用来进行多次预测。其实如果我们突出显示范围 E8:E10，输入公式=TREND(B4:B18，A4:A18，D8；D10)，然后按 Ctrl+Shift+Enter ，我们会得到如图 75 所示的相同结果。

如果范围 R3 被省略，则趋势将为 R1 的各种值生成预测值。我们可以用这个函数来看看回归模型对我们观察到的值的预测有多好。这将帮助我们确定回归模型到底有多好。结果如图 76 所示:

图 76:残差

这里，我们使用数组公式计算 D4:D18 范围内的预测值:

=TREND(B4:B18，A4:A18)

范围 E4:E18 中显示的残差是实际值和预测值之间的差值。这些是错误术语。它们可以通过在单元格 E4 中插入公式=B4-D4，突出显示范围 E4:E18 并按下 Ctrl+D 来计算。

尽管预测不是 100%准确，但预测值的平均值(单元格 D19)与观察值的平均值(单元格 B19)相同，误差项的平均值(单元格 E19)为零。

观测值(寿命)的样本方差可以通过公式=VAR 计算。S(B4:B18)，值为 120.27。出于我们的目的，我们将这个方差称为总均方(缩写为)。正如我们在第 3 章中看到的，样本方差可以通过以下公式计算:

分子称为平方和总和(缩写为)，分母为总自由度(缩写为)。唯一新的是术语。

事实证明，我们可以将的值(代表观察到的值的总可变性)分成由模型解释的可变性部分(平方的回归和 )和未由模型解释的部分(平方的残差和 )以及类似的自由度。因此，我们有:

每个术语的值是:

其中为的观测值，为模型预测的值，k =模型中自变量的个数(目前 k = 1)。我们还可以引入均方值:

很明显，如果比小，模型很好地拟合了观测数据。既然，这就相当于说相对接近 1。原来，这个分数等于决定系数，也就是。用大写的代替小写的是很常见的，所以我们有:

所有这些统计数据都可以在 Excel 中计算出来，如图 77 所示:

图 77:Excel 中回归参数的计算

使用的公式如表 13 所示(基于数据图 76):

细胞	实体	公式
H4		=DEVSQ(B4:B18)
H5		=DEVSQ(D4:D18)
H6		=DEVSQ(E4:E18)
I4		=COUNT(B4:B18)-1
I5		=I5
I6		=I4-I5
J4		=H4/I4
J5		=H5/I5
J6		=H6/I6
M4		=H5/H4
M5		=SQRT(M4)

表 13:回归公式

残差均方项是模型未捕捉到的误差的一种方差。估计值的标准误差只是的平方根。对于当前示例，这是 63.596 的平方根(图 77 的单元格 J6)，即 7.975。Excel 提供以下函数来计算估计值的标准误差:

STEYX(R2 R1)=回归模型估计值的标准误差，其中 R1 是包含观察到的数据值的范围，R2 是包含观察到的数据值的范围

我们现在将之前引入的带有一个自变量的线性回归模型扩展到有一个或多个自变量的情况:

如果是因变量而是自变量，那么通用多元回归模型从以下形式的提供了对的预测:

其中为模型的回归系数，为随机误差。

我们进一步假设，对于任何给定的值，随机误差正态独立分布，平均值为零。本质上，这些条件中的最后一个意味着即使我们消除了随机误差项，模型也非常适合数据。

示例:珠宝商根据质量(数值从 0 到 8，8 为无瑕疵，0 包含大量瑕疵)和颜色(数值从 1 到 10，10 为纯白，1 为黄色)为钻石定价。根据 11 颗钻石的每克拉价格，每颗重量在 1.0 到 1.5 克拉之间，如图 78 所示，建立一个回归模型，该模型捕捉质量、颜色和价格之间的关系。

根据这个模型，估计一颗颜色为 4、质量为 6 的钻石的价格:

图 78:样本数据

我们通过按下数据>分析|数据分析并从显示的菜单中选择回归来使用 Excel 的回归分析工具。出现图 79 所示的对话框:

图 79:回归数据分析对话框

如图所示填写字段并选择确定后，将显示如图 80 所示的输出。

图 80 中数值的计算是本章前面描述的方法的扩展，适用于只有一个独立变量的情况(特别参见表 13)。调整后的 R 平方(细胞 B6)是一种尝试，以创造一个更好的估计人口决定系数的真实值，并通过以下公式计算:

=1-(1-B5)*(B8-1)/(B8-B12-1)

回归系数(B17:B19)和相应标准误差(C17:C19)的计算使用最小二乘法，但超出了本书的范围。这些系数的 p 值和置信区间的计算如第 7 章所述。

图 80 中分析的关键结果之一是回归模型由公式给出:

价格= 1.7514 + 4.8953 ∙颜色+ 3.7584 ∙品质

图 80:回归数据分析

由于 p 值(单元格 F12) = 0.00497 < .05，我们拒绝零假设。我们得出结论，回归模型非常适合数据。

注意颜色和质量系数是显著的(p 值< .05), that is, they are significantly different from zero. Meanwhile, the intercept coefficient is not significantly different from zero (p-value = .8077 > .05)。

R 平方= .8507 表明模型捕捉到了大量的价格可变性，这支持了回归模型非常适合数据的情况。

如果钻石的颜色为 4，质量为 6，那么我们可以使用该模型来估算价格，如下所示:

价格= 1.75 + 4.90 ∙颜色+ 3.76 ∙品质= 1.75 + 4.90 ∙ 4 + 3.76 ∙ 6 = 43.88305

我们使用如图 81 所示的 TREND 函数得到相同的结果:

图 81:使用回归模型的预测