鹿鼎记08版 迅雷下载:第四篇——第31章 使用分析工具库分析数据

本章将分统计分析、方差分析和预测分析３个部分向读者介绍分析工具库中各种分析工具的使用。其中统计分析主要介绍如何应用描述统计和直方图工具来描述和观察数据的重要特征，以及应用ｔ－检验、Ｆ－检验和ｚ－检验工具根据样本数据判断总体数据的颁布特征。方差分析主要介绍如何应用单因素方差分析和双因素方差分析等工具对科学实验的结果进行分析，鉴别有关因素对实验结果的影响。预测分析主要介绍如何应用移动平均、指数平滑和回归等工具通过对历史数据的分析和计算，预测将来可能的发展变化趋势。通过学习让读者掌握各种分析工具的基本操作，并能够灵活运用各种分析工具对数据进行深层次的分析，为决策提供科学的依据。
注意：默认情况下，Excel不自动加载分析工具库。因此，要学习和应用本章节的内容，而Excel的“工具”菜单中没有“数据分析”命令时，需要手工加载分析工具库。具体操作步骤如下。
步骤１．单击“工具”菜单中的“加载宏”命令，这时将弹出“加载宏”对话框。
步骤２．勾选“加载宏”对话框中的“分析工具库”复选框。单击“确定”。
这以后Excel的工具菜单中将出现“数据分析”命令，当需要应用各种数据分析工具时，直接执行“数据分析”命令即可。此后每次启动Excel时，分析工具库都会自动加载，加载过程需要占用一定的系统响应时间。
如果不再需要进行数据分析操作时，可以采用类似的方法卸载分析工具库。
３１．１　统计分析
统计分析就是以概率论为理论基础，根据试验或观察得到的数据来研究随机现象，对研究对象的客观规律做出种种合理的估计和判断。统计分析的内容十分丰富，本节主要介绍如何利用Excel的分析工具进行描述统计和假设检验。
３１．１．１描述统计
描述统计的任务是描述随机变量的统计规律性。要完整地描述随机变量的统计特性需要颁布函数。但在实际问题中，求随机变量的分布函数是比较困难的。很多时候也不需要去全面考察随机变量的变化规律，而只需知道随机变量的某些特征。
例如，在研究某一地区居民的消费水平时，在许多场合只需知道该地区的平均消费水平；又如在分析某个年龄段儿童的生长发育情况时，常常关心的是该年龄段儿童的平均身高、平均体重；再如检查一批灯泡的质量时，既需要注意灯泡的平均寿命，又需要注意灯泡寿命与平均寿命的偏离程度，平均寿命较长、偏离程度较小，质量就较好。尽管这些数值不能完整地描述随机变量，但能描述随机变量在某些方面的重要特征。这些数字特征在理论和实践上都具有重要意义。
随机变量的常用统计量有平均值、标准误差、标准偏差、方差、最大值、最小值、中值、峰值、众数、偏斜度等。其中，平均值描述了随机变量的集中程度，而方差描述了随机变量相对于平均值的离散程序，是最常用的两个统计量。
１．描述统计　］
当需要计算一组数据的一些常用统计量时，可以使用Excel提供的统计函数来实现。例如AVERAGE（平均值）、STDEV（标准偏差）、VAR（样本方差）、KURT（峰值）、SKEVV（偏斜度）、MEDIAN（中值，也称中位数，即在一组数据中居于中间的数）、MODE（模式，也称众数，即在一组数据中出现频率最高的数值）等。但更方便快捷的方法是利用Excel提供的描述统计工具，它可以同时给出一组数据的许多常用统计量。
示例３１．１　计算考试成绩的多项统计量
图给出了某个班级３门课程的考试成绩。现需要根据这些成绩计算出平均值、方差和标准差等统计量，进行初步的分析。
学号 高等数学 计算机原理 法学概论
20050101 98 93 95
20050102 73 79 95
20050103 74 71 95
20050104 87 83 95
20050105 93 88 90
20050106 85 82 95
20050107 99 90 95
20050108 83 81 95
20050109 91 92 95
20050110 75 82 85
20050111 66 80 85
20050112 72 77 95
20050113 88 73 95
20050114 86 79 95
20050115 69 63 90
20050116 76 70 85
20050117 68 60 95
20050118 87 82 85
20050119 78 78 95
20050120 78 76 85
20050121 83 81 90
20050122 72 70 95
20050123 69 79 90
20050124 58 64 95
20050125 83 78 85
20050126 74 75 95
20050127 65 85 90
20050128 82 76 95
20050129 70 74 95
应用描述统计工具对数据进行基本统计分析的具体操作步骤如下：
步骤１．单击“工具”菜单中的“数据分析”命令，弹出“数据分析”对话框。
步骤２．在“数据分析”对话框的“分析工具”列表中，选择“描述统计”工具。单击“确定”，这时将弹出“描述统计”对话框。
步骤３．指定输入数据的有关参数。
●输入区域：指定要分析的数据所在的单元格区域。本例指定需要分析的成绩数据所在的C1：E55单元格区域。
●分组方式：指定输入数据是以行还是以列方式排列的。一般情况下Excel会根据指定的输入区域自动选择。
●标志位于第一行复选框：若输入区域包括标志行，则必须勾选此复选框。否则，Excel自动以列1、列2、列3……作为数据的列标志。本例指定的输入区域包含了标志行，所以勾选此复选框。
步骤４．指定输出的有关选项
●输出区域：根据需要可以指定输出到当前工作表的某个单元格区域，这时需在输出区域框键入输出单元格区域的左上角单元格地址；也可以指定输出到新工作表，这时需要输入工作表名称；还可以指定输出到新工作簿。本例中将结果输出到输出   区域，并输入输出区域的左上角单元格地址Ｇ１。
●汇总统计：若勾选，则显示描述统计结果，否则不显示结果。本例勾选汇总统计复选框。
●平均数置信度：如果需要输出包含均值的置信度，则勾选此复选框，并输入所要使用的置信度。本例键入９５％，表明要计算在显著性水平为５％时的均值置信度。
●第Ｋ大值：根据需要指定要输出数据中的第几个最小值。本例勾选第Ｋ小值复选框，并输入３，表示要求输出第３大的数值。
●第Ｋ小值：根据需要指定要输出数据中的第几个最小值。本例勾选第Ｋ小值复选框，并输入３，表示要求输出第３小的数值。
有关参数输入完毕，单击“确定”按钮。这时Excel将描述统计结果存放在当前工作表以G1起始的单元格区域中。
高等数学 　 计算机原理 　 法学概论
平均 78.27778 平均 78.11111 平均 92.40741
标准误差 1.529449 标准误差 0.999534 标准误差 0.556719
中位数 79 中位数 78 中位数 95
众数 83 众数 79 众数 95
标准差 11.23911 标准差 7.345045 标准差 4.091033
方差 126.3176 方差 53.94969 方差 16.73655
峰度 0.067846 峰度 0.036383 峰度 -0.53807
偏度 -0.36481 偏度 0.034158 偏度 -1.11885
区域 52 区域 33 区域 10
最小值 48 最小值 60 最小值 85
最大值 100 最大值 93 最大值 95
求和 4227 求和 4218 求和 4990
观测数 54 观测数 54 观测数 54
置信度(95.0%) 3.067687 置信度(95.0%) 2.004811 置信度(95.0%) 1.116637
从分析结果可以看出：高等数学和计算机原理两门课程的成绩分布比较正常，平均值都是７８；中值分别为７９和７８；模式（众数）分别为８３和７９；而峰值和偏斜度都接近于０，说明这两门课的成绩基本上服从正态分布。而法学概论课程的成绩平均值为９２，中值和模式都是９５，偏斜度是更是达到近－１．１２，说明该课程的成绩分布过于集中，而且成绩偏高，可能是试题难度偏低或是评判标准偏松。
２．直方图
许多学校的教学管理除了要求给出成绩以外，还要求给出试卷分析报告。其中成绩分布的直方图是必不可少的基本内容。虽然可以使用Ex
cel提供的函数和图表向导完成，但是使用分析工具库的直方图工具更加方便。
示例３１．２制作试卷成绩直方图
制作直方图的具体操作步骤如下。
步骤１．定义组距，即一组按升序排列的边界值（上界）。Excel将统计在当前边界点和相邻的高值边界点之间的数据个数，并据此绘制直方图。因为要分析的是成绩数据，所以通常按优、良、中、及格和不及格分类。在成绩工作表的C57：C62单元格区域设置的组距。
组距
59.5
69.5
79.5
89.5
99.5
注意：组距可以根据需要自行设置，其大小可以相等，也可以不等。通常组距数据的精度应比实际数据多一位。如果不设置组距，直方图工具将自动在数据的最小值和最大值之间创建均匀的组距。
步骤２．单击“工具”菜单中的“数据分析”命令。在“数据分析”对话框的“分析工具”列表中，选择“直方图”工具，单击“确定”。这时将弹出“直方图”对话框。
步骤３．指定输入数据的有关参数。
●输入区域：指定要分析的数据所在的单元格区域。本例指定需要分析的高等数学课程的成绩数据所在的C2：C55。
●接收区域：指定组距数据所在的单元格区域Ｃ58：C62。
●标志：本例指定的数据未包含标志，所以不勾选该复选框。
步骤４．指定输出的有关选项。
●输出区域：本例选中将结果输出到输出区域，并输入输出区域的左上角单元格地址Ｇ１。
●输出方式：根据需要确定是否选择柏拉图、累积百分率、图表输出复选框。若选定柏拉图，则统计结果按频率从大到小的顺序排序；若选定累积百分率，则统计结果中增加一列频率累积百分比数值，并同时在直方图中添加累积百分比折线；若选定图表输出，则根据统计结果画出直方图。本例只勾选图表输出复选框。计算结果和绘制的直方图，如图所示。
接收 频率
59.5 4
69.5 9
79.5 14
89.5 19
99.5 7
其他 1
在Excel所给出的直方图分析结果中，称为“频率”的数据实际上是“频数”。比如，59.5组距中的4表示不及格的有4个；69.5组距中的9表示小于69.5且大于59.5的数据有9个…而且图中的其他项是指数据中大于组距上限的数据个数，在本例中实际就是100分的数据个数。
３１．１．２　假设检验
假设检验的基本思想可以应用小概率原理来解释，即小概率事件在一次试验中是几乎不可能发生的。如果对于总体的某个假设是真实的，那么不支持这一假设的小概率事件Ａ在一次试验中竟然发生了，人们就有理由怀疑该假设的真实性，从而拒绝该假设。
例如某工厂家说明其生产的一种自动加工设备的工作温度服从正态公布，平均工作温度是８０℃。那么这个样本的结果是否和厂家的说明有显著差异呢？如果样本测试的温度是100℃或更高，人们的直观就可以怀疑假设的真实性而否定它。而现在测试的结果虽然有差异，但是差异很小。由于样本本身有一定的随机性，这时就需要对原假设和样本的差异进行分析。类似的根据样本观测值来判断总体假设是否成立的问题就是假设检验问题。
处理假设检验问题的一般步骤是：
●根据实际问题的要求，提出原假设Ｈ０及备择假设Ｈ1。
●给定显著性水平α和样本容量n（α的值视具体情况而定，通常取0.1，0.05，0.01及 0.005等值）。
●确定检验统计量和拒绝域的形式。
●按Ｐ｛拒绝Ｈ０／Ｈ1为真｝＝α求出拒绝域。
●根据样本观测值所求出的统计量确定是接受还是拒绝原假设Ｈ０。
１．ｔ－检验成对二样本分析
有时为了比较两种产品、两种仪器、两种方法等的差异，常在相同的条件下作对比试验，得到一批成对的观察值，然后分析观察数据作出推断。这种方法被称为逐对比较法。在假设检验中利用ｔ统计量进行检验，因而称为基于成对数据的ｔ－检验。
示例３１．３使用ｔ－检验分析某体育疗法对减肥的作用
要分析一种新体育疗法是否对减肥具有显著作用。现随机抽取了１２位病人进行试验。假设治疗前后，除参加了这种新体育疗法外，其余的一切条件都尽可能做到相同。现需要根据试验前后测得的体重数据判断这种新体育疗法对减肥是否具有显著作用。
该问题实质上就是要应用ｔ－检验统计值来判断试验前后的成对数据平均值是否有显著差异。传统计算步骤为：
先根据试验前的数据x1,x2,…xn,试验后的数据y1,y2…yn,计算出成对观测样本差d1,d2,…dn,其中di=xi-yi.
假设d1,d2,…dn来自正态总体Ｎ（μd,σ2)这里μd,σ2均属未知。若新体育疗法对体重没有舒适影响，则治疗前后各对数据的差异d1,d2,…dn属随机误差。而随机误差可以认为服从均值为0的正态分布。因此检验新体育疗法是否对减肥具有显著作用，就归结为在显著性水平α下，检验假设
Ｈ０：μd＝０（即认为μx=μy,试验前后观测数据平均值无显著差异，新体育疗法没有显著作用）
Ｈ1:μd≠０(即认为μx≠μy,试验前后观测数据平均值无显著差异，新体育疗法有显著作用）
其中，μx为试验前观测样本ｘ的均值，μy为试验后观测样本y的均值，μd为成对观测样本d的均值。
注意：假设检验的原假设接受时只是认为否定它的根据不充分，而不是认为它绝对正确。所以，应根据实际问题合理设置原假设和备择假设。
检验统计量为服从ｔ公布的统计量：
ｔ＝ｄ－ｏ／ｓd/N^2/1
其中ｄ和ｓd分别为成对观测样本差的样本均值和样本方差。
检验的拒绝域：对于给定的α,当│t│〉ta/2(n-1)时拒绝假设Ｈ０。　　从上面的分析可知，要进行成对观测值ｔ－检验，需要进行大量的计算和查表。现利用Excel提供的t-检验：平均值的成对二样本分析”工具，可以迅速地得到检验结果。其具体操作步骤如下：
步骤1.将需要检验的成对观测数据输入到工作表中。
步骤２．参照３１．１．１小节的操作步骤调出“数据分析”对话框。
步骤３．在“数据分析”对话框的“分析工具”列表中，选择“ｔ－检验：平均值的成对二样本分析”工具，单击“确定”。这里将弹出“ｔ－检验”平均值的成对二样本分析”对话框。
步骤４．指定输入数据的有关参数。
●变量1的区域：指定试验前的数据所在的单元格区域B1：B13。
●变量２的区域：指定试验后的数据所在的单元格区域C1：C13。
●假设平均差：根据实际问题，输入假设成对观测样本的ｄ的均值μd。本例假设样本X与样本y的平均值相等，所以输入0。
●标志：本例指定的数据区域包含标志行，所以勾选该复选框。
●α值：根据需要指定显著性水平。本例输入法0.05。
步骤５．指定输出的有关选项。
●输出区域：本例选中将结果输出到输出区域，并输入输出区域的左上角单元格地址Ｅ１。单击“确定”按钮。
分析检验结果可以看出，样本的ｔ统计量等于4.745664，大于t双尾临界值2.200986，所以拒绝原假设Ｈ０，即在置信度α为0.05的情况下，实施新体育疗法前后的数据胡显著差异。因此可得出结论：这种新体育疗法对于95%以上的人具有减肥的作用，疗效显著。
许多实际问题需要对来自两个总体的双样本数据进行分析。例如，在机床上进行一项试验，以确定改进的操作方法是否会增加产品的优质率，以及是否会改变产品优质率的波动性。试验在同一机床上进行，每生产一批零件时除了操作方法外，各生产了１０批零件，得到有关产品优质率的两组样本。根据这两组样本来推断改进的操作方法能否提高产品优质率，以及是否会改变产品优质率的波动性，就是典型的双样本假设检验问题。下面应用Excel的“F-检验 双样本方差”和“t-检验 双样本等方差假设”分析工具进行分析。
２．Ｆ－检验 双样本方差
示例３１．４　首先设标准方法和改进方法的观察值为分别为x1,x2,…xn和y1,y2…yn，是分别来自正态总体Ｎ（μ１，σ2