【Excel】数据分析工具库

一、工具库简介

1、作用

便于复杂数据统计分析，可以完成描述统计、直方图、相关系数、移动平均、指数平滑、回归等19中统计分析方法。但是与专业的统计分析软件相比，数据量和复杂程度有限。

2、安装

步骤：【文件】-【选项】-【加载项】-【Excel加载项】-转到-勾选【分析工具库】【分析工具库-vba】
位置：【数据】-【数据分析】

3、统计方法归纳

二、描述性统计分析

1、介绍

描述统计分析的常用指标有平均数、中位数、中枢、标准差、方差等，提供分析对象数据的集中程度和离散程度等信息。

2、操作

1. 位置
   【数据】-【数据分析】-【描述统计】
2. 参数
   1）输入参数

• 输入区域 - 要进行描述统计的数据区域
• 分组方式 - 选择区域是按列还是按行
• 标志位于第一行：勾选后表示第一行是字段名
  2）输入参数
• 输出区域/新工作表组/新工作表 - 描述统计值输出的位置
• 汇总统计 - 平均、标准误差、中位数、众数、标准差、方差、峰度、偏度、区域、最小值、最大值、求和、观测数
• 平均数置信度 - 指总体参数值落在样本统计值某一区间的概率，常用95%或90%
• 第k大（小）值：第k位的最大（小）值
  
  注意：与正态分布相比（峰度=0，偏度=0）
  峰度>0，呈尖峭峰分布；峰度<0，成平阔峰分布
  偏度>0，呈高峰偏左，长尾向右延，正偏态分布；偏度<0，呈高峰偏右，长尾向左延，负偏态分布
  
  如图，该组数据呈平阔峰负偏态分布。

三、直方图

1、介绍

直方图用于展示数据分布，用矩形的宽度和高度表示频数分布，横轴表示数据分组，纵轴表示频数或频率。

2、操作

1. 定义组距
2. 位置：【数据】-【数据分析】-【直方图】
   
3. 参数

• 接收区域：组距数据区域
• 标志：输入区域和接收区域中包含标题则勾选
• 柏拉图：组距按照频数从大到小排列
• 累积百分率：频数累计百分率
• 图表输出：输出图表

四、抽样分析

1、介绍

rand函数可以实现随机抽取数据，但无法实现有规律抽取。“抽样”分析工具可以解决这个问题。

2、操作

1. 位置：【数据】-【数据分析】-【抽样】
   
2. 参数

• 抽样方法-周期：输入周期间隔，抽取间隔位置上的数
• 抽样方法-随机：有放回的方式，任何数值有可能被多次抽取。

五、相关分析

1、介绍

相关关系现象之间存在的非严格的、不确定的依存关系。
相关分析是研究两个或两个以上随机变量之间相互依存的方向和密切程度的方法，直线相关用相关系数表示，曲线相关用相关指数表示，多重相关用复相关系数表示。
相关系数就是反应变量之间先行相关强度的一个度量指标，用r表示，r范围[-1,1]。

2、操作

1. 位置：【数据】-【数据分析】-【抽样】
   
2. 参数

• 分组方式：根据输入区域，选择的是两列数据，因此选择逐列。

3. 解读
   矩阵的行列交叉处就是两个变量之间的相关系数。

六、回归分析

1、介绍

回归函数关系是指现象之间存在的依存关系中，对于某一变量的每一个数值，都有另一个变量值与之相对应，并且可以用一个数学表达式表示出来。
回归分析是研究自变量和因变量之间的关系形式的分析方法，主要通过建立因变量y与自变量Xi(i=1,2,3,…)之间的回归模型，来预测因变量Y的发展趋势。
回归分析与相关分析的联系：均为研究及测量两个及两个以上变量之间关系的方法，实际工作中，一般先进行相关分析，极端相关系数，然后你和回归模型，进行显著性检验，最后用回归模型推算或预测。
回归分析与相关分析的区别：相关分析不区分自变量和因变量，回归分析不仅可以揭示两个变量之间的影响大小，还可以由回归模型进行预测。

回归分析五个步骤：

2、操作

1）简单线性回归

1. 公式
   Y=a+bX+ε

• Y——因变量
• X——自变量
• a——常数项，回归直线在纵坐标上的截距
• b——回归系数，回归直线的斜率
• ε——随机误差，随机因素对因变量所产生的影响

2. 绘制散点图
   一般做线性回归之前，需要先用散点图查看数据之间是否具有先行分布特征。
3. 添加趋势线，通过绘制趋势线建立回归模型
   
4. 检验
   位置：【数据】-【数据分析】-【移动平均】
   

参数

• 残差：观测值和预测值之间的差
• 标准残差：(残差-残差的均值)/残差的标准差
• 残差图：绘制散点图，横坐标不变，纵坐标是残差值。如果点都分布在以0为横轴的直线上下散布，则表示拟合合理。
• 线性拟合图：横轴不变，以因变量和预测值为纵坐标绘制散点图。
• 正态概率图：用于检查一组数据是否服从正态分布，以因变量的百分位排名为横坐标，以因变量为纵坐标绘制散点图。

解读

• 回归统计表
  衡量相关程度，回归模型的拟合效果。
  
• 方差分析表
  判断回归模型的回归效果，即线性关系是否显著，线性模型描述得是否恰当。
  
• 回归系数表
  用于回归模型的描述和回归系数的显著性检验。
  

2）多重线性回归

多重线性回归模型是指一个因变量和多个自动变量的回归模型，与包含两个或两个以上因变量的多元回归模型区分。

• 公式
  Y=a+b₁X₁+b₂X₂+…+b_nX_n+ε

• Y——因变量

• X_n——第n个自变量

• a——常数项，回归直线在纵坐标上的截距

• b_n——第n个回归系数

• ε——随机误差，随机因素对因变量所产生的影响

• 参数
  

• 解读
  

七、移动平均

1、介绍

除了相关分析和回归分析这种找变量之间关系的预测方法，还有根据时间发展进行预测，即时间序列预测。特征如下：

• 假设事物发展趋势会延伸到未来；
• 预测所依据的数据具有不规则性；
• 不考虑事物发展之间的因果关系。
  时间序列预测主要包括移动平均法、指数平滑法、趋势外推法、季节变动法等。
  移动平均法根据时间序列逐期推移，依次计算包含一定奇书的平均值，形成平均值时间序列。

2、操作

1. 公式
   Y_t=(X_t-1 + X_t-2 + X_t-3 +…+ X_t-n)/n

• Y_t——对下一期的预测值
• n——移动平均的时期个数
• a——常数项，回归直线在纵坐标上的截距
• X_t-n——前n期实际值

2. 位置
   【数据】-【数据分析】-【移动平均】
3. 参数
   

• 间隔：指定n组数据来计算平均值
• 图表输出：由实际数据和移动平均值形成的直线图输出。（注意这个图表述可能不准确）
• 标准误差：实际数据与预测数据的标准差，越小表明数据越准确。

4. 结果
   

八、指数平滑

1、介绍

改良的加权平均法，在不舍弃历史数据的前提下，对离预测期较近的历史数据基于较大的权属，权数由近到远按指数规律递减。

2、操作

1. 公式
   Y_t=αX_t-1 + (1-α)Y_t-1 = (1-β)X_t-1 + βY_t-1

• Y_t——时间t的平滑值
• X_t-1——时间t-1的实际值
• Y_t-1——时间t-1的平滑值
• α——平滑系数
• β——阻尼系数
  注意：β=1-α，阻尼系数越大，近期实际值对预测结果的影响越大。实际应用中，阻尼系数是根据时间序列的变化特性来选取的。如果时间序列数据波动不大，比较平稳，则阻尼系数应取小一些，如0.1-0.3；反之，时间序列数据具有迅速且明显的变动倾向，则阻尼系数，如0.6~0.9.

2. 位置
   【数据】-【数据分析】-【指数平滑】
3. 结果
   
   多个阻尼系数对比