利用Excel做专业数据统计

1、Excel可以完成只能由专业软件完成的数据统计和分析，如直方图、相关系数、协方差、各种概率分布、抽样和动态模拟、总体均值判断、均值推断、线性和非线性回归、多元回归分析、时间序列等。本主题将教你完成一些最常用的专业数据分析工作。注意：所有操作将由Excel“分析数据库”工具完成。如果未安装此功能，请依次选择“工具”-“添加宏”，并在安装光盘上加载“分析数据库”。成功加载后，您可以在工具下拉菜单中看到数据分析选项。柱状图一堂课的期中考试结束后，需要统计每个分数段的人数，并给出频率分布直方图和累计频率表进行分析。过去，手动分析的步骤是通过计算每个分数段中的人数来创建一个新表，然后基于该表构建数据的统

2、计直方图。Excel可以用来直接完成这项任务。特定方法描述统计学在一个班的期中考试后，有必要统计成绩的平均值和区间，并给出班级内学生成绩差异的量化标准，从而解决班级间学生成绩不均衡的问题。需要标准偏差等统计值。样本数据分布区间、标准差等。都是描述样本数据范围和波动的统计数据。统计标准差需要得到样本均值，所以计算比较繁琐。这些是描述样本数据的常用变量，在Excel数据分析中使用“描述统计”可以一次性完成。特定方法排名和百分比排名在一个班级的期中考试后，只有结果是按照要求公布的，但是学生和家长要求知道排名。因此，如果要发布年级排名，学生可以通过年级查询找到自己的排名，同时得到该年级位于班级百分位的

3、排名(即该学生是排名前“X%”的学生)。排序操作是Excel的基本操作，Excel“数据分析”中的“排序和百分比排序”可以简化这项工作，直接输出报表。特定方法相关系数和协方差相关系数是描述两个被测变量之间离差程度的指标。用于判断两个被测变量的变化是否相关，即一个变量的较大值是否与另一个变量的较大值相关(正相关)；或者一个变量的较小值是否与另一个变量的较大值相关联(负相关)；或者两个变量的值彼此不相关(相关系数接近于零)。特定方法取样分析工具采样分析工具将数据源区域作为一个整体，从而为其创建一个样本。当人口太多而无法处理或抽取时，可以选择有代表性的样本。如果您确认数据源区域中的数据是周期性的，您

4、还可以在一个周期中的特定时间段内对值进行采样。随机抽样也可以用来满足用户的要求，以确保抽样的代表性。特定方法移动平均数移动平均是将一系列变化的数据按照指定的数据量依次平均，并将其作为数据变化的趋势供分析师参考。移动平均线在生活中也很常见，在气象意义上对四季的定义是移动平均线的最佳应用。让我们看看如何使用Excel来完成移动平均线的统计工作。特定方法回归分析溶液的浓度与色谱仪器中的峰面积成正比。现在，需要建立不同浓度下峰面积对应的标准曲线来测试未知样品的实际浓度。已知8组对应数据，建立标准曲线，对曲线进行评价，给出残差分析数据。这是一个典型的线性拟合问题。人工计算是用最小二乘法找出拟合线的待定参

5、数，同时得到R值，即相关系数的大小。在Excel中，前两个步骤可以通过先绘制然后添加趋势线来完成。特定方法数据分析直方图Excel自带的数据分析功能可以完成许多专业软件才有的数据统计和分析，包括直方图、相关系数、协方差、各种概率分布、抽样和动态模拟、总体均值判断、均值推断、线性和非线性回归、多元回归分析、时间序列等。下面将逐一介绍上述功能的使用，方便普通读者和相关专业人员参加考试和使用。注意：该函数需要使用Excel扩展函数。如果您的Excel没有安装数据分析，请依次选择工具-插件，并在安装光盘上加载分析数据库。成功加载后，您可以在工具下拉菜单中看到数据分析选项。例1一堂课的期中考试结束后，需

6、要统计每个分数段的人数，并给出频率分布直方图和累计频率表进行分析。过去，手动分析的步骤是通过计算每个分数段中的人数来创建一个新表，然后基于该表构建数据的统计直方图。您可以使用Excel中的数据分析功能直接完成此任务。操作步骤1.打开原始数据表，制作本例的原始数据需求列表，并确定数据范围。本例为化学等级，因此数据范围确定为0-100。2.在右侧输入数据接受顺序。所谓的“数据接收序列”是分段统计的数据间隔，它包含一组用于定义接收区域的可选边界值。这些值应该按升序排列。在本例中，有多少分数段被用作统计单位。它可以通过拖动生成，也可以根据需要自行设置。在本例中，使用了10点的统计单位。3.选择工具-数

7、据分析-直方图后，出现属性设置框，依次选择：输入区：原始数据区；验收区域：数据验收顺序；如果选择了“输出区域”，新对象将直接插入到当前表格中。选择柏拉图，该复选框可以在输出表中以降序显示数据；如果选择“累计百分比”，累计频率曲线将叠加在直方图上；4.输入完成后，可以立即生成相应的直方图，这张图片需要做很大的调整。主要是：水平和垂直坐标的标题、柱形图的间隔、各种数据的字体和大小等。为了实现柱形图之间的无缝和紧密排列，有必要将“数据系列格式”中“选项”中的“分类间距”调整为“0”。其他细节，请双击需要调整的对象，按照常规方法进行调整，这里就不赘述了。调整后的直方图参考如下用Excel做数据分析，用

8、描述统计在一个班的期中考试后，有必要统计成绩的平均值和区间，并给出班级内学生成绩差异的量化标准，以此作为解决班级间学生成绩不均衡的依据。需要标准偏差等统计值。样本数据分布区间、标准差等。都是描述样本数据范围和波动的统计数据。统计标准差需要得到样本均值，所以计算比较繁琐。这些是描述样本数据的常用变量，在Excel数据分析中使用“描述统计”可以一次性完成。注意：该函数需要使用Excel扩展函数。如果您的Excel没有安装数据分析，请依次选择工具-插件，并在安装光盘上加载分析数据库。加载成功后，您可以在“工具”下拉菜单中看到“数据分析”选项。操作步骤1.打开原始数据表。只要满足行或列中相同的属性值，

9、就不需要制作本例的原始数据。2.选择工具-数据分析-描述统计后，出现属性设置框，依次选择：输入区：原始数据区，可选择多行或多列，注意选择相应的分组方式；如果数据已标记，请选中“在第一行标记”；如果输入区没有标志项，复选框将被清除，Excel将在输出表中生成相应的数据标志；可以在输出区域选择该表、新工作表或新工作簿。汇总统计：包括平均值、标准误差(相对于平均值)、中位数、模式、标准差、方差、峰值、偏斜度、极端范围、最小值、最大值、总和、总数、最大值、最小值和置信度。其中：中值：排序后中间数据的值；模式：出现频率最高的值；峰值：衡量数据分布波动的指标，与正态分布相比为正，与平缓值相比为负；偏斜度：

10、衡量数据峰值偏差的指标，根据峰值在平均值的左侧或右侧为正或负；范围：最大值和最小值之间的差值。Kth最大(小)值：输出表中的一行包含每个数据区中的Kth最大(小)值。平均置信度：当显著性水平为5%时，95%的值可用于计算平均置信度。结果示例如下(本示例演示了两行数据的描述性统计结果):成就学习时间平均的78.平均的62.标准误差2.标准误差1.中位数85中位数68方式98方式78.4标准偏差18.标准偏差14.变化324.变化207.峭度1.峭度1.歪斜-1。歪斜-1。地区85地区68最小值15最小值12最大值100最大值80总和4404总和3523.2观察次数56观察次数56最大值(1)10

11、0最大值(1)80最小值(1)15最小值(1)12信心(95.0%)4.信心(95.0%)3.用电子表格进行数据分析排序和百分比排序在一个班级的期中考试后，只有结果是按照要求公布的，但是学生和家长要求知道排名。因此，如果要发布年级排名，学生可以通过年级查询找到自己的排名，同时得到该年级位于班级百分位的排名(即该学生是排名前“X%”的学生)。点击此处查看主题：用Excel完成专业数据统计和分析排序是电子表格的基本操作。Excel数据分析中的排序和百分比排序可以简化这项工作并直接输出报告。注意：这个函数需要使用Excel扩展函数。如果您的Excel没有安装数据分析，请依次选择工具-插件，并在安装光

12、盘上加载分析数据库。成功加载后，您可以在工具下拉菜单中看到数据分析选项。操作步骤1.打开原始数据表。只要满足行或列中相同的属性值，就不需要制作本例的原始数据。2.选择工具-数据分析-描述统计后，出现属性设置框，您可以依次选择；在输入区：中选择数据区。如果有数据标记，请勾选底部的“第一行标记”。分组方式：表示输入区域的数据是按行还是按列考虑，请根据原始数据格式选择；可以在输出区域选择该表、新的工作表组或新的工作簿。3.单击确定查看生成的报告。可以看出，此时生成了一个新的四列表，其中“点”指的是排序后原始数据的序数，与本例中的学生编号相对应，也是一个非常实用的序列；“得分”是排序后的数据系列；“排

13、名”采用重复数据占据相同位置的统计方法；“百分比”按降序排列。为了得到真正的“百分比排名”，需要做一点调整。4.在“百分比”列的下一列中输入“百分比排名”，在第一个单元格中输入公式“=1-G3(对应于“百分比排名”)，然后按回车键。选择单元格并向下拖动，直到它被填满。这样就达到了显示百分位数排名的目的。下图显示了完成的报告示例。用电子表格分析的相关系数和协方差在化学合成实验中，经常需要研究压力随温度的变化。在实验中，在相同条件下，在两个不同的反应器中获得两组温度和压力相关数据。分析了它们与温度的关系，给出了相同条件下不同反应器反应的可靠性。相关系数是描述两个被测变量之间离差程度的指标。用于判断

14、两个被测变量的变化是否相关，即一个变量的较大值是否与另一个变量的较大值相关(正相关)；或者一个变量的较小值是否与另一个变量的较大值相关联(负相关)；或者两个变量的值彼此不相关(相关系数接近于零)。假设(X，Y)是一个二进制随机变量，那么：是随机变量x和Y的相关系数.p是一个数字特征，它衡量随机变量x和y之间线性相关的接近程度。注意：这个函数需要使用Excel扩展函数。如果您的Excel没有安装数据分析，请依次选择工具-插件，并在安装光盘上加载分析数据库。成功加载后，您可以在工具下拉菜单中看到数据分析选项。操作步骤1.打开原始数据表。为了制作这个例子的原始数据，需要满足两组或两组以上的数据，并且

15、任何两个项目的相关系数将作为结果给出。2.选择工具-数据分析-描述统计后，出现属性设置框，依次选择：输入区：选择数据区，注意满足至少两组数据的需要。如果有数据标记，请同时勾选“第一行标记”；分组方式：表示输入区域的数据是按行还是按列考虑，请根据原始数据格式选择；可以在输出区域选择该表、新的工作表组或新的工作簿。3.单击确定查看生成的报告。可以看出，在相应的区域中生成33的矩阵，数据项的交集是其相关系数。显然，数据完全与自身相关，相关系数在对角线上显示为1；两组数据在矩阵上有两个相同的位置，所以右上角的重复部分不显示数据。左下角对应的位置是温度和压力的相关系数A、B和两组压力数据。从数据的统计结

16、论可以看出，温度与压力A和B的相关系数分别达到0.95和0.94，表明它们之间有很好的正相关关系，而两组压力数据之间的相关系数达到0.998，表明在相同条件下，不同反应器的反应一致性很好，换反应器引起的系统误差可以忽略。协方差的统计类似于相关系数的活方法，统计结果还返回一个输出表和一个矩阵，分别表示每对被测变量之间的相关系数和协方差。区别在于相关系数在-1和1之间，而协方差没有有限的范围。相关系数和协方差都是描述两个变量离散程度的指标。使用电子表格作为数据分析的抽样分析工具省教育厅派了一个专家组去检查一所学校的学生试卷。专家组计划进行一次整体抽样调查，并从学校的一个班级中随机抽取25名学生作为调查样本。为了确保结果不是人为的，使用电子表格帮助专家组进行抽查。采样分析工具将数据