Excel数据分析和建模

提示：本文是基于excel，对数据进行分析

目录

前言

一、Excel数据处理

1、缺失值和空值的处理

2、去除重复值

 3、异常值的处理

4、数据标准化

二、建模

1、一元线性回归：

2、多元回归

总结

---

前言

excel的数据分析功能很强大，其中就包含了数据据预处理，数据分析，对数据进行建模等功能。

本文只是实现过程，回归的原理可以参考：一元线性回归模型（保姆级）_数据小师弟的博客-CSDN博客https://blog.csdn.net/DL11007/article/details/126982286

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、Excel数据处理

1、缺失值和空值的处理

如图所示，指标缺失了一个数据，这时候，我们可以进行以下操作，对数据进行填补

（1）、我们可以用y指标的均值进行填充、，这种就很简单了。

（2）、使用excel中TREND()函数对缺失值进行线性插值，TREND里面的参数为TREND(已知y的数据，已知x的数据，需要插入的x的值)，这样就可以填充缺失值。下面我是使用缺失之前面的值作为已知的x和y数据。如下图：

 

 （3）、还有更多的数据填充方法可以使用VLOOKUP函数进行填充。

这个函数的参数VLOOKUP(Lookup_value, Table_arry, Col_index_num, Range_lookup)

Lookup_value(必须):匹配条件，是指定的查找条件。

Col_index_num：是查找数据所在的列。

Range_lookup：FLASE是精确匹配，TRUE是近似匹配。

Table_array(必须)：查找区域，是一个至少包含一列数据的列标或者单元格区域，并且该区域的第一列必须包含匹配条件，也就是说，谁是匹配值，就把水宣威该区域的第一列。如下图：

2、去除重复值

（1）、我们可以手动的对重复值进行删除，这种方法一般只适合少量数据的情况下，当数据量特别大的时候，这种方法就很笨拙了，要先找到重复值让后在手动删除。

（2）、直接选中所有的数据，然后点击excel窗口的数据，在数据工具中找到删除重复值，点确定，就可以删除重复值并保留唯一的数据。如下图：

 3、异常值的处理

使用查找中的条件查找，找出异常值，然后删除，接着对数据进行填充，就可以只用上述两种方法。

4、数据标准化

这个步骤是处理量纲的问题，去除量纲对数据误差的影响。 对于没有量刚上的问题，可以在直接对数据进行建模。

二、建模

1、一元线性回归：

在excel窗口选着数据-->分析里面的数据分析会弹出一个窗口如下图：

 这里有很多的数据分析相关的模型和检验方法，这里下拉选着回归，然后会弹出另外一个窗口，如下图：

输入： x和y值的区域选着的时候要注意，我们选着的数据不能带有文字的，要选指标下的数据即可（别问我为什么知道，问就是我知道 [狗头保命]），下面我们可以勾选置信区间，还可以设置置信区间的概率。

输出区域：我们可以新建一个表，也可以选择当前数据表，然后选择对应的单元格，结果就会输出在所选单元格内。

残差：excel会根据预测的结果输出每个点的残差值，线性拟合图是把每点的预测画散点图并把原来的点也画上。

这些功能可以根据需求勾选，例子如下：

左上角：是回归所需的数据，这里我是自己写的数据。

中间：回归统计部分R Square: R方

           方差分析：我们只看F检验的结果即（Significance F），F检验是对整个模型的检验。

           红色圈： ①：是一元回归的系数，Intercept是常数项，x Variable是x 变量的系数；

                          ②：是每个系数的t检验；

                          ③：是每个系数的P值；

                          ④：是每个系数的置信区间；

右上角：原数据的散点图和预测数据散点图；

还有预测值和残差值，excel把这两部分放在了同于个表格里面，如下图：

2、多元回归

多元回归的模型建立和一元回归是同一个过程，只是结果的输出会不同，如下图：

不同的地方是在预测图的输出，因为高维的图是画不了的，excel就每个单独的指标（自变量）和因变量做二维的散点图。

注意：区分x变量的系数一般是按照所选表格里面的顺序来排的。

预测值和残差结果，如下图：

总结

提示：这里对文章进行总结：
本文只针对数据量不大，只需要简单的数学模型，大量的数据就不是很适合了，预测的结果会有很大的误差（虽然我试过几千条数据的预测，几乎没看到什么Excel和代码的区别，可能代码实现可以有更多的功能，进行更多的后续处理），本文的内容就差不多了，喜欢的可以点个关注收藏，嗷嗷~~。