excel做正态分布分析（excel数据分析案例）

2024年11月29日 20:35:17 • 投稿 • 阅读 38

如果你是公司营销部/市场部的一员，老板希望在推广渠道投入8,000元以达到15,000元的收入，你觉得这些钱够吗？

在这样一个案例中，如能判断这些钱够不够？一个常用的方法是使用数据分析中的回归分析预测。

回归分析是在我们业务历史数据的基础上，根据数据之间的相关关系搭建模型来预测未来发展的趋势。

今天，我们来学习一下如何使用Excel来进行数据分析的回归分析。

excel做正态分布分析（excel数据分析案例）

什么是回归分析

回归分析的本质是研究数据与数据之间的相关关系。

在统计学中，回归分析的种类繁多，但是在实际中，最常用的还是一元/多元线性回归，也就是研究一个或多个因素与另外一个因素的关系。

那些能引起其他因素变化的就是自变量，根据其他因素变化而变化的则是因变量。在上面的案例中，销售额收入随着投入成本的变化而变化，那么销售额就是因变量，成本则是自变量。

在这个基础上建立的回归模型：Y（因变量）=a1X1（自变量1）+a2X2（自变量2）+a3X3（自变量3）+……+an Xn+b（一共有n个自变量，a1、a2、a3…an是未知的系数，b是误差）

了解回归分析的基础概念后，我们下面来说说使用Excel对数据进行回归分析。

回归分析的步骤

1.明确问题与目的

进行任何数据分析之前，第一件事永远是确定我们面临的问题和使用数据分析的目的。

在上面的案例中，我们遇到的问题是投入多少成本，才能达到老板想要的收益。而数据分析的目的，则是预测达到这些收益所需要投入的成本。

明确目的后，就是整理数据。我们要按照时间顺序把过去某段时间所有投入的成本和总收入进行归纳整理，历史数据越多，预测就越准确。

我们根据历史数据整理了如下表格：

excel做正态分布分析（excel数据分析案例）

2.确定自变量和因变量

在一次回归分析中，自变量可以有多个，但因变量只能有一个。

在我们的案例中，这次的因变量是销售额Y，而在只考虑推广渠道成本的情况下，自变量只有一个X，所以这次搭建的模型为：Y=aX+b

3.加载Excel数据分析功能

Excel的数据分析功能需要另外加载。它的具体位置在文件-选项-加载项，在加载页面勾选“分析工具库”后，点击下面的“转到”后再点击确定即可。

excel做正态分布分析（excel数据分析案例）

这时候，我们会发现在Excel数据功能栏下方的右侧多出了一个“数据分析”按钮。

excel做正态分布分析（excel数据分析案例）

4.进行回归分析，建立模型

加载完数据分析功能后，点击按钮后，在弹窗中找到“回归”并选择。

excel做正态分布分析（excel数据分析案例）

这时候，会弹出导入数据的窗口。其中Y值就是因变量，X值是自变量。

点击右侧的“?”后，长按鼠标左键从第一行的数据拖动到最后一行，再按下Enter键即可快速导入。Y值只能选择一列的数据，而X值可以同时选择多列。

这里我们只需要选中“推广渠道成本”一栏的数据即可。

如下图所示。

excel做正态分布分析（excel数据分析案例）

导入数据后，勾选下方“残差”和“正态分布”所有选项，点击确定即可进行回归分析。

excel做正态分布分析（excel数据分析案例）

▲ 回归分析后的页面

可以看到Excel自动生成的模型图表：

excel做正态分布分析（excel数据分析案例）

我们要关注的重点在中间“Coefficients”一栏中。其中，Intercept是指X=0时，Y的值，也就是误差b；而“X Variable”则是指X的系数，也就a。

excel做正态分布分析（excel数据分析案例）

那么这个模型的具体数据就出来了：Y=0.852X+5690.875（可以根据你们的需要取小数点后3-4位数，也可以直接使用这个数据）

5.对回归模型进行检验

模型搭建好了，并不是万无一失的。

既然是预测，肯定会与真实数据有误差。我们也可以观察到在模型的散点图上，并非所有真实数据全部都在这条模型线上，因为它是根据数据“拟合”的，并不是完全符合。

所以我们还需要对模型进行检验。一般来说，检验模型的准确率需要考察这几个数字：

Multiple R（相关系数）

相关系数R的值出于-1~1之间，在-1~0的区间中，说明自变量越大，因变量就越小，而在0~1的区间中，则相反。

excel做正态分布分析（excel数据分析案例）

当相关系数R小于-0.8或大于0.8的时候，说明自变量与因变量有很强的相关性。这里的相关系数高达0.983，说明成本与销售额有非常强的相关性。

R Square（拟合系数）

拟合系数=相关系数的平方，它的数值范围为0~1，数值越大，拟合的效果就越好。一般大于0.7时，可以认为拟合的效果符合预期。

这里的拟合系数为0.96，非常接近1，说明模型的拟合效果很不错。

Significance F（显著性检验）

显著性检验是指检验自变量和因变量的线性关系是否明显，它的数值越小，说明真实数据离这条线越近，也就是模型越准确。

excel做正态分布分析（excel数据分析案例）

这里的1.25E-08的意思是1.25× 10的-8次方（即0.00000001），几乎接近于0，也就是说这个模型是比较准确的。

P-value（P值）

P值是用来检测系数（即aX中的a）的显著程度。P值同样也是越小越好，一般来说，P值小于0.05时，这个模型才有统计学意义。

这里的P值为2.4E-09，也是无限接近于0，说明这个模型的可信度比较高。

6.预测

检验完回归模型后，如果各数值都比较正常，那么就可以根据最终确定的模型进行预测了。

在上面的案例中，最终确定的模型为：Y=0.852X+5690.875（这里取小数点后3位）。

按照老板的收入预期，也就是15,000=0.852X+5690.875，最后可以算出X为8320.569，也就是说，如果要达到老板的预期收入，推广渠道的成本最少应该为8320.569元。

原创文章，作者：admin，如若转载，请注明出处：https://www.qq65hfghe5.com/tg/129414.html

excel做正态分布分析（excel数据分析案例）

什么是回归分析

回归分析的步骤

相关推荐