Excel统计分析——多元线性回归分析

本文共计1800字（多图），建议阅读时间2分钟

目录

• 一元线性回归分析
• 建立模型——参数估计
• 图表法
• 函数法
• 工具法
• 回归分析工具
• 线性规划工具
• 分析模型——参数检验
• 应用模型——变量预测
• 多元线性回归分析

多元线性回归分析的三步，都大致跟一元线性回归分析差不多，相似部分就略过。
然后重点放在第二节，也就是多元线性回归模型的优化上。
这是多元线性回归跟一元线性回归需要多做一步的工作。

到这篇文章为止，「Excel统计分析之线性回归」系列就完成了。总共三篇文章。
前两篇是
Excel统计分析——一元线性回归分析（一）
Excel统计分析——一元线性回归分析（二）

现在发送关键词消息【回归分析】到「数据分析师修炼手册」公众号，就可提取本文示例Excel文档了。
未来不定期更新~

建模三步骤

多元线性回归分析案例：
探究公众号文章的推荐占比与文章的质量指标之间的关系，想知道哪几个因素能带来“平台推荐流量”，一定程度上解开“流量密码”。

多元线性回归分析案例数据

建立模型

函数法

多元线性回归模型为：

其中ε随机误差。

多元线性回归方程的矩阵形式：

公式示意

对上面等式求极值，也就是说所有自变量X求偏导=0，得到n元一次方程组。

Excel中的MDETERM函数就是用来求解多元一次方程组的，求解可得到所有X变量的斜率，通过待定系数法，得到截距。

公式就不贴了，大家自己看Excel吧。不明白的还可以私信来问。我看到了会解答的。

另外，通过LINEST函数做多元线性回归，可以更加方便便捷。——这里强烈推荐。
而且不仅可以得到参数，还能顺便把模型的偏回归系数的参数检验也做了。
只不过第一次接触的话，用起来会比较难以理解，因为这个函数的输出是一个数组。

附LINEST用法（来自微软官网的LINEST 函数介绍）：
LINEST 函数可通过使用最小二乘法计算与现有数据最佳拟合的直线，来计算某直线的统计值，然后返回描述此直线的数组。也可以将 LINEST 与其他函数结合使用来计算未知参数中其他类型的线性模型的统计值，包括多项式、对数、指数和幂级数。因为此函数返回数值数组，所以它必须以数组公式的形式输入。请按照本文中的示例使用此函数。
直线的公式为：
y = mx + b
- 或 -
y = m1x1 + m2x2 + ... + b
如果有多个区域的 x 值，其中因变量 y 值是自变量 x 值的函数。m 值是与每个 x 值相对应的系数，b 为常量。注意，y、x 和 m 可以是向量。 LINEST 函数返回的数组为 {mn,mn-1,...,m1,b}。 LINEST 函数还可返回附加回归统计值。
返回的回归统计值数组顺序如下：

LINEST 函数 返回的回归统计值数组

但这也是有点难以看懂的吧。我把输出结果用色块区分了下，应该更好读了。
同一个颜色色块的指标名称和数值是对应的。若是四个数值则与四个自变量一一对应的。

使用LINEST函数的多元线性回归模型

中间有一些阴影框的区域就是使用LINEST函数的区域，只用在最左上角输入函数公式
=LINEST(Y数组,X数组,TRUE,TRUE)
即可，Excel会根据样本数量，自动溢出为N行N列的数组。留出足够空间即可。

回归工具

用法跟前面一篇文章讲一元线性回归分析的一模一样，只不过X值区域选择范围更大了而已。

回归分析工具界面

输出结果如下

回归分析工具 输出结果

规划求解

同样地，还可以通过规划求解的方式获得模型参数。
其实就是用数值计算的方式来解N元一次方程组。

得到的参数值，也都是跟上述所有方法得到的一模一样。不再赘述了。

分析模型——模型显著性检验&优化

在多元线性回归分析中，显著性检验包含两个方面：
对于多个自变量与因变量的整体显著性进行检验，通过F检验来实现；
以及对于每个自变量对因变量影响的显著性（即回归系数显著性）进行检验，通过T检验或F检验来实现。

整体显著性检验

多元线性回归方程的方差分析，列表如下

P值小于0.05，通过总体显著性检验，说明模型整体是有效的，所建立的回归模型是有统计学意义的。

与回归工具输出的第二部分结果也是一模一样。（唯一不同的是，回归工具输出的是Significance F，而不是P值）

回归系数显著性检验

回归工具返回的结果，和LINEST函数所做的检验，都是T检验。

回归分析工具输出的回归系数显著性检验

与前面提到的

使用LINEST函数的多元线性回归模型

两个方法下，各个回归系数的P值结果都一样。

并且我们可以看到，其中X4（阅读关注率）的P值最小，是0.01796，X1（送达阅读率）的P值次之，为0.06948，但已经超过了0.05水平。X3（阅读完成率）P值0.10573，离0.05还有点近。但X2（首次分享率）P值就非常大了。

整体显著，部分回归系数不显著，如何处理？

建议把P值接近0.05的自变量保留剔除其他自变量，再重新建模。
这里我们保留X4（阅读关注率）、X1（送达阅读率）和X3（阅读完成率），剔除X2（首次分享率）。

使用回归工具重新建模如下

回归输出（剔除X2）

可以看到，模型得到了优化，所有回归系数都显著了，X3（阅读完成率）P值0.03057，降到了0.05以下。

综上，可以得出结论，对获取平台推荐流量最有效的指标是阅读关注率、其次是送达阅读率和阅读完成率。

应用模型——因变量预测

见Excel示例文件中的G4-G10单元格。此处略。

---

最后给大家介绍一本书，也是本系列文章的主要参考文献。是一块大部头，对Excel统计分析比较感兴趣的，推荐购买，可作为工具书，随时翻看。