excel统计分析基本方法和优缺点（数据分析干货隐藏在Excel中的统计分析功能）

小易将在这篇文章中为大家揭晓许多数据分析师都不一定掌握的统计分析功能。

经过前面几篇文章的介绍，相信大家都对Excel的数据分析有了一定的了解。下面先给大家简单介绍一下“统计分析功能”以及“什么是统计分析？”

使用Excel的“统计分析功能”需要建立在完成了数据处理操作的前提下，因为数据不“干净”，经过“统计分析”得出的数据结论就没什么价值了。

Excel的“统计分析功能”位于它的分析工具库中，一般需要我们手动加载这一功能。

当然，Excel的分析工具库的功能十分强大，它包含的远不止一个“统计分析功能”，还有“方差分析”和“预测分析”等等，但是小易这次给大家介绍的主要是“统计分析功能”~

加载指南：

单击工具栏的“文件”。

再单击弹出页面左下方的“选项”。

在“Excel选项”页面中找到左侧的“加载项”，并在“加载项”中找到“分析工具库”，此时需要选中下方“管理”中的“Excel加载项”再点击“转到”。

在接下来的窗口中勾选“分析工具库”后，点击“确定”就可以开始使用“统计分析功能”啦~

具体使用入口见下图。

接下来估计会有好奇的小伙伴问“那么什么是统计分析呢？”小易这就告诉大家~

统计分析是以概率论为理论基础，根据试验或观察得到的数据来研究随机现象，对研究对象的客观规律做出种种合理的估计和判断。总的来说就是我们需要运用统计分析的知识来处理搜集到的海量数据，将其转化为便于我们得出结论的数据。

前菜上完了，下面进入正题~

本篇文章主要介绍的是如何应用“统计分析功能”中的“描述统计”和“直方图”工具来描述和观察数据的重要特征，以及“假设检验”中的应用“t-检验”、“F-检验”和“z-检验”工具根据样本数据来判断总体数据的分布特征。

描述统计

描述统计

描述统计的任务是描述随机变量的统计规律性。要完整地描述随机变量的统计特性需要分布函数。

但在实际问题中，求随机变量的分布函数是比较困难的。很多时候也不需要去全面考察随机变量的变化规律，而只需知道随机变量的某些特征。

例如，在研究某一地区居民的消费水平时，在许多场合只需知道该地区的平均消费水平；

又如在分析某个年龄段儿童的生长发育情况时，常常关心的是该年龄段儿童的平均身高、平均体重；

再如检查一批灯泡的质量时，既需要注意灯泡的平均寿命，又需要注意灯泡寿命与平均寿命的偏离程度，平均寿命较长、偏离程度较小，质量就较好。

尽管这些数值不能完整地描述随机变量，但能描述随机变量在某些方面的重要特征。

随机变量的常用统计量有平均值、标准误差、标准偏差、方差、最大值、最小值、中值、峰值、众数、偏斜度等。其中，平均值描述了随机变量的集中程度，而方差描述了随机变量相对于平均值的离散程度，是最常用的两个统计量。

当需要计算一组数据的一些常用统计量时，可使用Excel提供的统计函数来实现，但更便捷的方法是使用Excel提供的描述统计工具，它能同时给出一组数据的许多常用统计量。

示例：利用某个班级3门课程的考试成绩数据来运用描述统计功能。

选中数据后，点击“数据分析”，在弹出的窗口中选中“描述统计”。

按照上述操作点击确定后，弹出“描述统计“窗口。此时确认输入区域为”$B$1:$D$16“，输出区域为”$F$1“。并选中”标志位于第一行“、”汇总统计“、”平均数置信度-95%“、”第K大值-3”、”第K小值-3”。

分组方式：通常情况Excel会根据指定的输入区域自动选择；

标志位于第一行复选框：若输入区域包含标志行，则必须勾选此复选框。否则Excel会自动以列1、列2、列3……作为数据的列标志；

汇总统计：若勾选，则显示描述统计结果，否则不显示；

平均数置信度：勾选此框，并输入要使用的置信度，则输出包含均值的置信度，本例输入95%；

第K大值：根据需要指定要输出数据中的第几个最大值，本例输入3；

第K小值；根据需要指定要输出数据的第几个最小值，本例输入3。

输入完有关参数后点击确定，即得到描述统计结果。

直方图

直方图可以更直观地看出成绩的分布情况。虽然可以使用Excel提供的函数和图表向导完成制作，但分析工具库的直方图更加方便。

制作直方图首先需要定义组距，即一组按升序排列的边界值。Excel将统计在当前边界点和相邻的高值边界点之间的数据个数，并据此绘制直方图。本例中根据成绩的优、良、中、及格和不及格分类，在表中的“B19:B24”单元格区域设置的组距如下图。

“直方图”的入口和“描述统计”的入口一致。都位于“数据-分析-数据分析“。

在弹出的“直方图“窗口中，选中输入区域为”$B$2:$B$16“（因为本例使用线性代数成绩绘制直方图），接收区域为”$B$20:$B$24“，输出区域为”$F$1“，并选择输出方式为”图标方式“，再点击确定即可得到直方图。

标志：本例指定的数据未包含标志，故不勾选；

输出方式：根据需要确定是否选择柏拉图、累积百分率、图表输出复选框。若选定柏拉图，则统计结果按频率从大到小的顺序排序；若选定累积百分率，则统计结果中增加一列频率累积百分比数值，并同时在直方图中添加累积百分比折线；若选定图表输出，则根据统计结果画出直方图。本例只勾选图表输出复选框。

假设检验

假设检验的基本思想可以应用小概率原理来解释，即小概率事件在一次试验中是几乎不可能发生的。如果对于总体的某个假设是真实的，那么不支持这一假设的小概率事件A在一次试验中竟然发生了，人们就有理由怀疑该假设的真实性，从而拒绝假设。

假设检验问题常见于根据样本观测值来判断总体假设是否成立的问题中。

处理假设检验问题的一般步骤：

• 根据实际问题的要求，提出原假设H0及备择假设H1；
• 给定显著性水平α和样本容量n（α的值视具体情况而定，通常取0.1、0.05、0.01及0.005等值）；
• 确定检验统计量和拒绝域的形式；
• 按P{拒绝H0/H1为真}=α求出拒绝域；
• 根据样本观测值所求出的统计量确定是接受还是拒绝原假设H0。

本次讲述的假设检验共分为三类：基于成对数据的“t-检验 成对二样本分析”、双样本假设问题中的“F-检验 双样本方差“和”t-检验 双样本等方差假设“。

t-检验 成对二样本分析

基于成对数据的t-检验常用于逐对比较法中，即有时为了比较两种产品、两种仪器、两种方法的差异，常在相同的条件下进行对比试验，得到一批成对的观察值，然后分析观察数据做出推断。

示例：运用t-检验分析某体育疗法对减肥的作用。

现随机抽取了12位病人进行试验，除参加该体育疗法外，其余一切条件都尽可能地做到相同，先需根据试验前后测得体重数据判断这种新体育疗法对减肥是否具有显著作用。

样本数据如下。

“t-检验 平均值的成对二样本分析”的入口同上，位于“数据-分析-数据分析”。下面就可以对样本数据使用分析方法啦~

根据上述操作后，需要在弹出的“t-检验 平均值的成对二样本分析“窗口中输入相应参数。

变量1的区域：指定试验前的数据所在单元格区域B1:B13；

变量2的区域：指定试验后的数据所在单元格区域C1:C13；

假设平均差：根据实际问题输入假设成对观测样本d的均值μd。本例中假设样本x与样本y的平均值相等，所以输入0；

标志：指定的数据区域包含标志行则勾选；

α值：根据需要指定显著性水平，本例为0.05；

输出区域：选中将结果输入到输出区域。

输入完参数后点击确定，即得到下列分析结果。

根据结果可看出，样本的t统计量为4.745664，大于t双尾临界值2.200985，所以拒绝原假设H0，即在置信度α=0.05的情况下，实施新体育疗法前后的数据有显著差异。由此得出结论：这种新体育疗法对于95%以上的人具有减肥作用，疗效显著。

上述示例针对的还只是来自同一总体的样本数据，下面介绍的两个方法则是针对来自两个总体的双样本数据进行分析。

F-检验双样本方差

示例：在机床上进行一项试验，以确定改进的操作方法是否会增加产品的优质率，以及是否会改变产品优质率的波动性。试验在同一机床上进行，每生产一批零件时除了操作方法外，其他条件都尽可能相同。先后用标准方法和改进方法进行生产，交替进行，各生产了10批零件，得到有关产品优质率的两组样本。样本数据如下。

”F-检验 双样本方差“的入口同上，位于”数据-分析-数据分析“。下面就使用该工具来检验两总体样本的方差。

在弹出的窗口中输入相应参数，即可获得检验结果。

变量1的区域：指定试验前的数据所在单元格区域B1:B13；

变量2的区域：指定试验后的数据所在单元格区域C1:C13；

标志：指定的数据区域包含标志行则勾选；

α值：根据需要指定显著性水平，本例为0.05；

输出区域：选中将结果输入到输出区域。

检验结果如下图。

由检验结果可看出两个总体方差相等，即改进的操作方法与标准方法相比，两者的产品优质率的波动性没有显著变化。

t-检验 双样本等方差假设

由“F-检验 双样本方差“的检验结果可假设两总体的方差相等，因此要检验改进的方法能否提高产品的优质率，可以使用”t-检验 双样本等方差假设“工具来进行检验。

”t-检验 双样本等方差假设“的入口同上，位于”数据-分析-数据分析“。

同样地在弹出的窗口中输入相应参数，得出检验结果。

变量1的区域：指定试验前的数据所在单元格区域B1:B13；

变量2的区域：指定试验后的数据所在单元格区域C1:C13；

假设平均差：根据假设，示例输入0；

标志：指定的数据区域包含标志行则勾选；

α值：根据需要指定显著性水平，本例为0.05；

输出区域：选中将结果输入到输出区域。

检验结果如下图。

从检验结果可看出，t=-4.29<-t单位临界值=-1.73，所以可认为改进的操作方法较原来的方法更好，能明显提高产品的优质率。

以上就是本次Excel的“统计分析功能“的全部内容啦~

小易还会继续分享数据分析的干货知识哦~请保持关注！

整理不易，点赞、转发帮忙点一下~

,