使用Excel创建线性回归模型

作者|RAM DEWANI编译|VK

来源|Analytics Vidhya

概述

你可以在Excel中执⾏建模，只需⼏个步骤

下⾯是⼀个教程，介绍如何在Excel中构建线性回归模型以及如何解释结果

介绍

Excel真的能构建预测模型？

这通常是我提起这个话题时的第⼀反应。当我演⽰如何利⽤Excel的灵活性为我们的数据科学和分析项⽬构建预测模型时，接下来是⼀个令⼈怀疑的眼神。

让我问你⼀个问题-如果你周围的商店开始收集客户数据，他们是否可以采⽤基于数据的策略来销售他们的商品？他们能预测⾃⼰的销售额或估计可能销售的产品数量吗？

现在你⼀定想知道，他们究竟将如何建⽴⼀个复杂的统计模型来预测这些事情？学习分析或雇佣分析师可能超出了他们的能⼒范围。好消息是，他们不需要。

Microsoft Excel为我们提供了⼀种构建预测模型的能⼒，⽽不必编写复杂的代码。

我们可以很容易地在MS Excel中建⽴⼀个简单的线性回归模型，它可以帮助我们在⼏个简单的步骤中执⾏分析。我们不需要精通Excel或统计学就可以进⾏预测建模！

在这篇⽂章中，我将解释如何在Excel中建⽴⼀个线性回归模型，以及如何对结果进⾏分析，以便你成为⼀名分析师！

⽬录

1. 什么是线性回归？

2. 在Excel中获取所有重要的Add分析⼯具包3. 在Excel中实现线性回归4. 解释我们预测模型的结果5. 我们如何改进模型？6. ⽤Excel做预测！

什么是线性回归？

线性回归是我们⼤多数⼈学习的第⼀种机器学习技术。它也是业界最常⽤的监督学习技术。但什么是线性回归？

它是⼀种线性⽅法，⽤于统计建模因变量（要预测的变量）和⾃变量（⽤于预测的因素）之间的关系。线性回归给出了这样⼀个⽅程：这⾥，Y是因变量，X是⾃变量，C是系数。系数基本上是根据重要性分配给特征的权重。最常⽤的回归⽅法是OLS（普通最⼩⼆乘法）。它的⽬标是减少平⽅和以产⽣这样的最佳拟合线：如果你想了解有关线性回归的更多信息，请参阅以下资源：

Excel加载项获取分析⼯具包

要在Excel中执⾏回归分析，⾸先需要启⽤Excel的分析⼯具包加载项。Excel中的分析⼯具包是⼀个插件程序，为统计和⼯程分析提供数据分析⼯具。

要将其添加到⼯作簿中，请执⾏以下步骤。步骤1–Excel选项

转到⽂件->选项：

步骤2–定位分析⼯具包

转到左侧⾯板上的加载项->管理Excel加载项->转到：

第3步-添加分析⼯具包选择“分析⼯具包”并按“确定”：

你已在Excel中成功添加分析⼯具包！你可以通过转到功能区中的数据栏进⾏检查。让我们开始⽤Excel建⽴我们的预测模型！

在Excel中实现线性回归

到⽬前为⽌，很多东西都是理论上的。现在，让我们深⼊了解Excel并进⾏线性回归分析！下⾯是我们将要处理的问题声明：

Winden镇有⼀家卖鞋的公司。该公司希望通过考虑以下因素来预测每个客户的销售情况：客户收⼊、离家距离、客户每周的跑步频率。

步骤1–选择回归进⼊数据->数据分析：

转到数据⼯具包中的“数据分析”，选择“回归”，然后按“确定”：

步骤2–选择

在这⼀步中，我们将选择⼀些分析所需的选项，例如：

输⼊y范围–独⽴因⼦的范围输⼊x范围-相关因素的范围输出范围–要显⽰结果的单元格范围

其他选项是⾃由选择的，你可以根据你的特定⽬的选择它们。

按OK，我们最终在Excel中⽤两个步骤进⾏了回归分析！很简单！现在我们将在excel中看到回归分析的结果。

⽤Excel分析预测模型的结果

实施线性回归模型是最简单的部分。现在是我们分析的棘⼿⽅⾯——在Excel中解释预测模型的结果。综上所述，我们有三种产出类型，我们将逐⼀介绍：

回归统计表⽅差分析表回归系数表残差表回归统计表

回归统计表告诉我们最佳拟合线如何定义⾃变量和因变量之间的线性关系。两个最重要的度量是R⽅和调整R⽅。R⽅统计量是拟合优度的指标，它告诉我们最佳拟合线解释了多少⽅差。R⽅的范围从0到1。在我们的例⼦中，R平⽅值为0.953，这意味着我们的⾏能够解释95%的⽅差——这是⼀个好的迹象。

但是有⼀个问题-当我们不断增加更多的变量，我们的R平⽅值将继续增加，即使变量可能没有任何影响。调整R平⽅解决了这个问题，是⼀个更可靠的度量。

⽅差分析表

⽅差分析代表⽅差分析。此表将平⽅和分解为其组成部分，以提供模型内变化的详细信息。它包括⼀个⾮常重要的指标，显著性F（或P值），它告诉我们你的模型是否具有统计显著性。简⽽⾔之，这意味着我们的结果可能不是由于随机性，⽽是因为⼀个潜在的原因。

p值最常⽤的阈值是0.05。如果我们得到的值低于这个，就可以了。否则，我们需要选择另⼀组⾃变量。在我们的例⼦中，我们的值远低于0.05的阈值。太棒了，我们现在可以前进了！回归系数表

系数表以系数的形式分解回归线的组成部分。从中我们可以了解很多。

对于Winden鞋业公司来说，似乎每增加⼀个单位的收⼊，销售额就增加0.08，⽽增加⼀个单位的店⾯距离就增加508个销售额！running frequency的增加似乎使销售量减少了24，但我们真的能相信这个特征吗？如果你看上⾯的图⽚，你会发现它的p值⼤于0.5，这意味着它在统计学上不显著。我们将在下⼀节探讨如何处理这种情况。残差表

残差表反映了预测值与实际值之间的差异。它由我们的模型预测的值组成：

如何改进我们的模型？

如前所述，变量running frequency的p值⼤于0.05，因此让我们从分析中移除该变量来检查结果。我们将遵循上述所有步骤，但不包括running frequency列：我们注意到调整后的R平⽅的值从0.920略微提⾼到0.929！

⽤Excel做预测！

我们已经准备好回归分析了，现在该怎么办？让我们看看。

你的⼀位⽼顾客，名叫亚历⼭德，⾛进来，我们想预测他的销售额。我们可以简单地从线性回归模型的数据中插⼊数字，我们很好去！Aleksander有4万的收⼊，住在离商店2公⾥的地⽅。估计销售额是多少？⽅程变成：

在这⾥，我们的模型估计，亚历⼭德先⽣将⽀付4218购买他的新鞋！这就是简单地在Microsoft Excel中进⾏线性回归的能⼒。

结尾

在本⽂中，我们学习了如何在Excel中建⽴线性回归模型以及如何解释结果。我希望这本教程能帮助你成为⼀个更好的分析师或数据科学家。

分析⼯具包包含许多其他Excel分析选项。你可以在⽇常⽣活中尝试很多其他的统计分析！