Excel回归分析怎么做(excel回归分析怎么做)

Excel实操分析函数，移动平均与线性回归分析，就是那么简单

移动平均预测

移动平均预测方法是一种比较简单的预测方法。这种方法随着时间序列的推移，依次取连续的多项数据求取平均值，每移动一个时间周期就增加一个新近数据，去掉一个远期数据，得到一个新的平均数。由于它逐期向前移动，所以称为移动平均法。由于移动平均可以平滑数据，消除周期变动和不规则变动的影响，使得长期趋势显示出来，因而可以用于预测。

例如，图显示了某企业近10个月的销售数据，要以三个月为计算周期使用移动平均的方法来预测下一个月的销售额，可以这样操作：

在C4单元格中输入下面的公式并向下复制填充至C11单元格：

=ERAGE(B2：B4)

此时C列所得的序列就是这组销售额以三个月为周期的移动平均值，其中最后一个C11单元格的移动平均值就是下一个月的销售额预测值，如图所示。

线性回归预测

图显示了某生产企业近10个月的产量及其能耗数据，通过绘制X/Y散点图可以发现，产量和能耗两组数据基本呈线性关系。假定下一个月的计划产量为 2100，要根据已知的数据对下一个月的能耗进行预测，可以使用下面的公式：

=TREND(C2：C11,B2：B11,2100)

TREND函数通过最小二乘法返回线性拟合的值，其语法为：

TREND(known_y's,known_x's,new_x's,const)

其中第一参数是已知的目标值序列，第二参数是已知的变量值序列，第三参数是需要预测的目标值所对应的变量值。将数据表中的数据代入就可以通过线性拟合运算得到相应的预测值。

除了TREND函数外，FORECAST函数也可以进行线性回归的预测，其公式为：

=FORECAST(2100,C2：C11,B2：B11)

以下是FORECAST函数的语法，与TREND函数在参数的排列位置上稍有区别：

FORECAST(x,known_y's,known_x's)

上述公式的预测结果均为875.23，即表示采用线性回归模型进行预测的情况下，产量达到2100时其能耗将达到875.23。

除此以外，还可以通过函数公式计算线性拟合方程y=kx+b中的斜率k和截距b的参数取值。

计算斜率可以使用函数SLOPE，其语法为：

SLOPE(known_y's,known_x's)

因此计算此例中线性拟合方程的斜率可以使用下述公式：

=SLOPE(C2：C11,B2：B11)

计算截距可以使用函数INTERCEPT，其语法为：

INTERCEPT(known_y's,known_x's)

因此计算此例中线性拟合方程的截距可以使用下述公式：

=INTERCEPT(C2：C11,B2：B11)

结合上述两个函数的计算结果，产量达到2100时所需能耗的预测公式也可以变化为：

=SLOPE(C2：C11,B2：B11)*2100+INTERCEPT(C2：C11,B2：B11)

别再乱套模型了，我用Excel发现了数据分析的本质：回归分析

最近很多人都问我，为什么感觉数据分析越学越乱，经常是学了一大堆名词，真正遇到问题的时候却更多是直接套用模型，很难将这些理论联系起来。

这其实就回归到了一个至关重要的问题：数据分析的本质是什么？

事物都是万变不离其宗的，一切外在的方法都是为了事物本质而服务的，数据分析自然也不例外，今天我们就来探讨一下数据分析的本质。

数据分析的本质

其实绝大多数的数据分析问题，都可以归纳为一个问题：相关性问题。

相关性分析是数据统计学中的基础思想，主要就是为了探究数据之间是否具有关联性，简单说就是X与Y或者X与Y、Z等之间的变化是否有关联。

比如，常年吸烟者的数量是否与肺癌患者的数量成正相关、健身者与感冒患者之间是否成负相关等等，这些例子都是简单的相关性判断

在数据分析中，更常见的则是更为复杂的相关性分析，也就是为了找到变量之间的相关系数，简单说就是为了找到Y=A+B*X之中的B。

比如，用户点击率与网站访问量之间是否有关系、广告曝光量与投入成本的关系等等，这个方程的求取过程也就是所谓的“回归分析”。

回归分析在统计学中包含了很多类别，比如一元回归、多远回归、方差回归、线性回归、非线性回归等，但我们不必涉及这么深，只需要了解其本质即可。

我们就拿广告曝光量与投入成本这个例子来解剖一下数据分析中的回归：

回归分析

首先我们假设一个数据分析中常见的场景：

小李是公司里负责市场广告的，某一次公司要举办大型活动，要求小李在线上广告上达到50w次的曝光量，于是小李写了一份方案提出要加大投入费用。而老板则觉得广告的投入费用太大，没有必要拿那么多钱，而小李则觉得多投入才有效果。

那么，对于究竟应该投入多少广告成本呢？

1、分析目的

于是我们就找到了本次数据分析的目的，就是要找到广告曝光量与费用成本之间的因果关系，也就是投入多少钱，广告曝光就能对应提高多少吗？

但是普通的统计方法是没办法得到严格的因果关系的，因此我们只能退而求其次地用回归分析来研究其相关关系和影响因子，用相关性代替因果关系。

2、确定变量

然后，我们要确定X、Y各是什么。

在这里Y自然就是广告曝光，也就是因变量，在数据分析中是指业务指标或者核心需求，比如销售额这种我们关心的能够随着其他因素的变化而变化的指标。

X自然就是投入成本，也就是自变量，在数据分析中是指用来解释业务指标的因子。

回归分析的任务就是，通过研究X和Y的相关关系，尝试去解释Y的形成机制，进而达到通过X去预测Y的目的。那么，X到底会长成什么样呢？

通常情况下X不是一个变量，而是多个变量，比如影响广告曝光的不止是投入成本，还可能是网站SEO等，在实际情况中我们需要将X一个个都找出来，最终的回归方程就变成了：

这里我们再添加一些其他的影响因素，比如费用投入X2、人力投入X3、投放时间X4、广告点击率X5、对象人群量X6、定价X7、投入广告位数量X8和定向设置量X9。

3、建立回归模型

为了找到X与Y之间的变量关系，我们可以通过建立回归模式来实现，这里就用Excel的回归功能简单实操一下。

将数据直接导入excel中，在“选项”菜单中选择“加载项”，在“加载项”多行文本框中使用滚动条找到并选中“分析工具库”，然后点击最下方的“转到”：

打开“加载宏”的选项中选择“数据分析库”，然后点击“确定”，就可以将数据分析库加载到Excel中。

然后点击“数据”菜单栏中的“数据分析”，在跳出的对话框中点击“回归”，然后下拉选取对应的X、Y区域，选择“正态分布”，点击“确定”即可。

这样就可以得到最终的结果

4、回归方程检验

有了回归方程，我们还需要检验一下拟合情况如何。我们主要看的指标有4个：最上面的回归统计表中的Multiple R以及R Square，中间表格中的Significance F，以及下方格中的P-value。

Multiple R：也就是R值，大于等于0.8即代表正相关，这里我们的R值是0.91，表明广告曝光与投入是正相关。R Square：R的平方值是指拟合系数，这个数值越大则代表回归拟合的越好，这里为0.83，说明拟合效果很好。Significance F：是指显著性检验度，这个值越小就代表因变量和自变量之间的关联性越显著，这里数值等于0.006，说明成本投入是影响广告曝光最显著的因素。P-value：是指系数的显著性检验度，一般大于0.05就不具有统计学意义了，小于0.05说明具有统计学意义。这里数值基本都小于0.05，说明这个结果极具统计学意义。

5、回归方程

最终按照图中得到值就可以得到回归方程，这样小李再向老板申请广告投入费用的时候，就不怕被老板说了。

最后简单总结一下。什么是回归分析？回归分析一句话：就是研究XY相关性的分析。

我们碰到的绝大多数数据分析问题，仔细想一下，都可以被规范成一个回归分析的问题。而对于真实的工作而言，能否把这个问题，成功的规范成为一个回归分析问题，是实际项目成功的关键。而规范是否成功的一个具体表现就是：第一，因变量Y是否定义清晰；第二，解释性变量X是否精准有力。