试验与统计分析上机实习指导.doc

田间试验与统计分析上机实习指导利用Excel进行统计分析黄亚群 常金华 祝丽英编生物类专业用河北农业大学教材出版科保定 2006.7概述Microsoft Excel电子表格具有强大的统计分析功能，利用电于表格可以解决生物科学、农业试验和生产实际中数据的常见的统计分析问题。其统计分析过程主要通过内置的“分析工具库”和粘贴函数来完成。一、分析工具库的安装在默认的情况下，Excel并没有安装“分析工具库”，只有在安装后才能使用。可先在“工具”菜单中检查一下是否有“数据分析”条目。如没有，可按以下步骤安装： 通过“工具”菜单中打开“加载宏”表单，选择“分析工具库”，再按“确定”。见图1-1。图-1“加载宏滚单打开“工具”菜单，选定“数据分析”并单击它，就可得到图1-2。图1-2二、分析工具库提供的统计分析方法 分析工具库提供的常用统计分析方法如下： 单向分组资料的方差分析 无重复资料的方差分析有重复资料的方差分析描述性统计量计算二总体方差的同质性检验 编制频数分布表及绘制直方图 排位与百分值比排位 二样本均数差数的U检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样 抽样三、统计分析的粘贴函教Excel提供的函数种类l 财务函数l 数据库函数l 日期与时间函数l 文本函数l 数学和三角函数l 逻辑函数l 统计函数l 信息函数l 查找与引用函数l 工程函数在统计函数模块中有76个统计函数，这些函数覆盖了统计基本理论与分析方法。Excel提供的常用统计分析粘贴函数有：1. AVEDEV 返回数据点与其平均值的绝对偏差的平均值2. AVERAGE 返回参数的平均值3. AVERAGEA 返回参数的平均值，包括数字、文本和逻辑值4. BETADIST 返回累积的 Beta 概率密度函数5. BETAINV 返回累积的 Beta 概率密度函数的反函数6. BINOMDIST 返回一元二项式分布概率7. CHIDIST 返回 chi 平方分布的单尾概率8. CHIINV 返回 chi 平方分布的反单尾概率9. CHITEST 返回独立性检验值10. CONFIDENCE 返回总体平均值的置信区间11. CORREL 返回两个数据集之间的相关系数12. COUNT 计算参数列表中数字的个数13. COUNTA 计算参数列表中值的个数14. COVAR 返回协方差，即成对偏移乘积的平均数15. CRITBINOM 返回使累积二项式分布小于等于临界值的最小值16. DEVSQ 返回偏差的平方和17. EXPONDIST 返回指数分布18. FDIST 返回 F 概率分布19. FINV 返回反 F 概率分布20. FISHER 返回 Fisher 变换21. FISHERINV 返回反 Fisher 变换22. FORECAST 根据线性趋势返回值23. FREQUENCY 以向量数组的形式返回频率分布24. FTEST 返回 F 检验的结果25. GAMMADIST 返回 gamma 分布26. GAMMAINV 返回反 gamma 累积分布27. GAMMALN 返回 gamma 函数的自然对数，(x)28. GEOMEAN 返回几何平均值29. GROWTH 根据指数趋势返回值30. HARMEAN 返回调和平均值31. HYPGEOMDIST 返回超几何分布32. INTERCEPT 返回线性回归线截距33. KURT 返回数据集的峰值34. LARGE 返回数据集中第 k 个最大值35. LINEST 返回线性趋势的参数36. LOGEST 返回指数趋势的参数37. LOGINV 返回反对数正态分布38. LOGNORMDIST 返回累积对数正态分布函数39. MAX 返回参数列表中的最大值40. MAXA 返回参数列表中的最大值，包括数字、文本和逻辑值41. MEDIAN 返回给定数字的中值42. MIN 返回参数列表中的最小值43. MINA 返回参数列表中的最小值，包括数字、文本和逻辑值44. MODE 返回数据集中出现最多的值45. NEGBINOMDIST 返回负二项式分布46. NORMDIST 返回正态累积分布47. NORMINV 返回反正态累积分布48. NORMSDIST 返回标准正态累积分布49. NORMSINV 返回反标准正态累积分布50. PEARSON 返回 Pearson 乘积矩相关系数51. PERCENTILE 返回区域中的第 k 个百分位值52. PERCENTRANK 返回数据集中值的百分比排位53. PERMUT 返回给定数目对象的排列数54. POISSON 返回 Poisson 分布55. PROB 返回区域中的值在上下限之间的概率56. QUARTILE 返回数据集的四分位数57. RANK 返回某数在数字列表中的排位58. RSQ 返回 Pearson 乘积矩相关系数的平方59. SKEW 返回分布的偏斜度60. SLOPE 返回线性回归直线的斜率61. SMALL 返回数据集中的第 k 个最小值62. STANDARDIZE 返回正态化数值63. STDEV 基于样本估算标准偏差64. STDEVA 基于样本估算标准偏差，包括数字、文本和逻辑值65. STDEVP 计算基于整个样本总体的标准偏差66. STDEVPA 计算整个样本总体的标准偏差，包括数字、文本和逻辑值67. STEYX 返回通过线性回归法预测每个 x 的 y 值时所产生的标准误差68. TDIST 返回学生的 t 分布69. TINV 返回学生的 t 分布的反分布70. TREND 返回沿线性趋势的值71. TTEST 返回与学生的 t 检验相关的概率72. VAR 基于样本估算方差73. VARA 基于样本估算方差，包括数字、文本和逻辑值74. VARP 基于整个样本总体计算方差75. VARPA 基于整个样本总体计算方差，包括数字、文本和逻辑值76. ZTEST 返回 z 检验的双尾 P 值四、Excel的常用基本概念 （一） 工作簿和工作表Microsoft Excel工作簿是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此可在单个文件中管理各种类型的相关信息。使用工作表可以对数据进行组织和分析。可以同时在多张工作表上输入并编辑数据，并且可以对来自不同工作表的数据进行汇总计算。在创建图表之后，既可以将其置于原数据所在的工作表上，也可以放置在单独的图表工作表上。工作表的名称显示于工作簿窗口底部的工作表标签上。单击工作表标签即可进入该工作表。当前所在的工作表称为活动工作表，在它的标签上标有单下划线。可以在同一工作簿内或两个工作薄之间对工作表进行改名、添加、删除、移动或复制等操作。在默认情况下，每个工作薄由3个工作表组成，用户可以根据需要添加更多的工作表。每个工作表由256列和65536行组成。行和列相交形成单元格，它是存储数据的基本单位。列用英文字母表示，开始是单个英文字母，然后是2个英文字母组合，即由AIV共256列。行用阿拉伯数字表示，由165536。每个单元格的定位可以通过该单元格所对应的行数及列标来确定，如 B6就表示B列第6行的单元格。在活动工作表众多的单元格中，其中有一单元格含有粗边框线，该单元格称为活动单元格，在该单元格中可以输入和编缉数据。每进入一个工作表时，A1自动为活动单元格。在活动单元格的右下角有一小黑方块，这个黑方块称为填充柄，利用此填充柄可以填充相邻单元格区域的内容。（二）单元格和区域引用引用是指调用工作表中的数据用于计算分析，它是通过标识工作表上的单元格或区域来实现的。通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区域的数据，或者在多个计算公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据。引用不同工作簿中的数据称为外部引用，引用其他程序中的数据称为远程引用。如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B和行10这一单元格的数据，在计算公式中输入B10。或者用鼠标点击该单元格。如果要引用某个区域的数据，可在计算公式中输入该区域左上角单元格的标识，后面跟一个冒号（：），后面再写上该区域右下角单元格的标识。例如，如果要引用从单元格C5到单元格C15这一区域，可输入C5：C15，如果要引用从单元格A5到单元格B10这一区域，可输入A5：B10。（三） 绝对引用与相对引用的区别当生成公式时，对单元格或区域的引用通常基于它们与公式单元格的相对位置。如在单元格A6中包含公式=3B5；Microsoft Excel将在距单元格A6上面一个单元格和右面一个单元格处的单元格中查找数值。这就是相对引用。当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置相对应的其它单元格。如单元格A6中的公式被复制到单元格A8中。单元格A5中的公式已经改为=3B 7，即从单元格 A8上面和右面一个单元格处的单元格中查找数值。如果在复制公式时，不希望引用发生改变，请使用绝对引用，绝对引用的方式是在相对引用列标和行数字前加上美元符号，即B5。如在单元格A6中包含的公式=3B5，现在将公式复制到另一单元格中，则公式中的引用将不发生改变。（四） 计算出现的错误信息输入计算公式及在公式中引用函数后、如果系统看不懂该公式，会在单元格中显示错误信息。下面列出了一些常见的错误信息、可能产生的原因以及解决的办法。 错误原因：公式产生的结果太长，单元格容纳不下。 解决办法：适当增加列的宽度。 NIV0 ！ 错误原因：除数为零。在公式中，除数使用了空白单元格或包含零值的单元格引用。 解决办法：修改单元格引用，或在用做除数的单元格中输入不为零的值。 NA 错误原因：表示在函数和公式中没有可用的数值可以引用。 解决办法：检查公式中引用的单元格的数据，并输人正确数据。 NAME？ 错误原因：删除了公式中使用名称或使用了不存在的名称以及拼写错误。 解决办法：确认使用的名称确实存在。 NULL！ 错误原因：使用了不正确的区域运算或不正确的单元格引用。 解决办法：如果要引用2个不相交的区域，请使用联合运算符（逗号）。例如，=SUM（B2：B5，D2：D5）表示对这2个不相交区域的引用。 NUM！ 错误原因：在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大或太小，Excel不能表示。如在计算临界t值或F值时，输入的概率值大于1或小于0。 解决办法：检查数字是否超出限定区域，函数内的参数是否正确。 REF！ 错误原因：删除了由其他公式引用的单元格或将移动单元格粘贴到由其他引用的单元格中。解决办法：检查引用单元格是否被删除，或者启动相应的应用程序。 VALUE！ 错误原因：需要数字或逻辑值时输入了文本。 解决办法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。实验一 数据整理一、 数据整理的内容1. 数据清单结构的建立与数据录入数据清单是指包含相关数据的一系列工作表的数据行。数据清单可以作为数据库使用，其中行表示记录，列表示字段。数据清单的第一行中包含列标，通常称列的名称为变量或标志，或字段，行的名称为观察值，每一个单格中包含了一个变量所发生的数值。2. 数据排序与分组一般来说，录入数据清单的数据是无序的，不能反映现象的本质与规律。为了使用的方便，要将其进行排序、分组，以便使数据按要求排列，同时使性质相同的数据归为一组，从而让它们之间的差异性显示出来。3. 编制次数分布表与累计次数分布表次数分布表可以表明各组间的单位数在总体中所出现的次数或所占的比重，揭示总体的内部结构特征。累计次数分布则能够表明某一组段以上或以下数据所出现的次数或比重。4. 统计图各种各样的统计图可以形象、直观地表明数据的分布形态与发展变化的趋势。二、 数据整理工具1. 数据排序表1.1 100个麦穗的每穗小穗数18151719161520181917171817161820191716181716171918181717171818151618181817201918171915171717161718181719191719171816181717191616171717161716181918181919201516191817182019171817171615161817181617191917Excel可以根据用户的要求对数据清单的行或列数据进行排序。排序时，Excel 将利用指定的排序顺序重新排列行、列或各单元格，从而使研究对象的规则性更加简洁地表现出来。例1.1 随机抽取100个小麦麦穗，计数每穗小穗数，未加整理的原始数据列于表1-1。利用Excel操作步骤： 录入原始数据 在菜单中选择“数据”中的“排序”选项，弹出排序对话框（图2-1）。 图2-1 图2-2 在排序对话框窗口中，选择“主要关键字”列表中的“每穗小穗数”作为排序关键字，并选择按“递增”排序，在 “列表”中选择 “有标题行”。 若没有输入 “每穗小穗数”标题行，在 “列表”中选择 “无标题行”。然后单击“确定”按钮，即可得到排序的结果如图2-2所示。从数据排序结果可以看出，每穗小穗数从15个到20个变动范围的递增顺序排列。 若想进一步知道某一种类型小穗数有多少个，可利用Excel中的“分类汇总”功能。在“数据”菜单中选择“分类汇总”选项，Excel便会自动地创建公式，生成自动分级显示的数据清单。但要注意在分类汇总前需要先对数据进行排序。 在数据清单中使用分类汇总的方法如下： 先选择需要分类汇总的数据区域A1：A100，然后选择“数据”菜单中的“分类汇总”选项，则打开“分类汇总”对话框如图2-3所示。 图2-3 图2-4在“分类字段”的下拉式列表中选择要进行分类的列标题，在“汇总方式”的下拉式列表中选择进行汇总的方式，本例中选择按 “每穗小穗数”进行分类，选择按“计数”进行汇总，单击“确定”按钮，便得到分类汇总的结果如图2-4所示。 在对数据进行了分类汇总之后，可以观察到“每穗小穗数”为15个有6个，“每穗小穗数”为16个有15等等。2. 利用频数分布函数进行分组频数分布函数 （FEQUENCY）的语法形式为：FREQUENCY(data_array，bins_array)其中：Data_array为用来编制频数分布的数据，Bins_array为频数或次数的接收区间。频数分布函数（FEQUENCY）可以对一列垂直数组返回某个区域中数据的频数分布。建立频数分布的步骤： 先对数据进行排序，以了解全部数据的变动范围； 选择全部数据的分组组数； 确定分组的组限； 最后对各组数值所出现的频数进行计数。例1.2 以例1.1数据为例说明之。打开“100个每穗小穗数.xls”工作表。在单元格C2中输入“分组”，在单元格D2中输入“频数”。在C3：C8区域中依次输入15，16，17，18，19，20，作为频数接受区域，它们分别表明每穗小穗数由15个、16个等的个数。选定D3：D8区域，在 “插入”菜单中单击“函数”选项，打开“插入函数”对话框如图2-5及图2-6所示。 图2-5 图2-6 图2-7在数据区域“Data_array”中输入单元格A2：A101，在数据接受区间Bins_array 中输入单元格C3：C8，在对话窗口中可以看到其相应的频数是6，15，32，25，17，5，0。由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应先按下F2，再按下Ctrl +Shift+Enter组合键，得到频数分布如图2-7所示。另外，直接利用Excel函数公式也可以得到同样结果。用鼠标选定单元格D3：D9，注意不要释放选定区域。在D3单元格中输入频数分布函数公式： =FREQUENCY(A2：A101，C3：C8) 在这个公式中，数据区域为A2：A101，接受区间为C3：C8，按Ctrl +Shift组合键，同时敲“回车”键，得到频数分布与上面相同。从频数分布结果可以看出，每穗小数以17个为最多，且以每穗小数17个为中心对称分布。 3. 利用透视表进行数据整理利用透视表进行数据整理可分为单变量表格与双变量表格，前者又称为单向表，后者又称为交叉表。 （1） 单向表 例1.3 某移动通讯公司要制定新一年的销售计划，需要向市场了解移动用户对数字移动电话类型的需求。经过问卷设计，在某地区抽取了50个样本进行问卷调查，获得关于移动电话用户消费行为的一些数据资料。试根据工作表中的数据分析该地区移动电话用户的电话类型偏好。城乡每周通话次数数字移动电话类型性别城乡每周通话次数数字移动电话类型性别城市20神州行男城市20长白行男农村20神州行男城市20长白行男农村40神州行男城市16全球通男城市30全球通男城市20长白行男城市10全球通男城市10全球通女城市20全球通女城市8长白行男城市20长白行男城市15长白行男城市40神州行男城市10全球通男城市60全球通男城市9全球通男城市20全球通男城市4长白行男城市20全球通男城市10全球通女城市20全球通男城市15全球通女城市20全球通男城市20全球通男城市20长白行男城市10全球通女城市20全球通男城市15全球通男城市25全球通男城市20全球通男城市30全球通男城市6全球通女城市7全球通女城市1全球通男城市20全球通男城市30全球通男城市10全球通男城市20全球通男城市10神州行男城市10全球通女城市13全球通男城市20全球通男城市30全球通男城市20全球通男城市40全球通男城市10长白行男城市30全球通男城市20全球通男 打开“数据整理.xls”工作表。 单击“数据”菜单中的“数据透视表和图表报告”选项，Excel弹出数据透视表向导对话框如图2-8所示。 图2-8 图2-9根据向导要求，第1步是指定“数据源类型”和确定所需创建的“报表类型”。选择“数据清单或数据库”作为数据源，选择“数据透视表”作为报表类型。最后单击“下一步”。 第2步是确定数据区域，本例中数据所覆盖区域为A1：D51单元格，单击“下一步”按钮，向导给出第3步操作对话框窗口如图2-9所示。第3步确定数据透视表的位置。选择现有工作表，并利用鼠标将数据透视表的位置确定在E1单元格中，如果单击“完成”按钮，可以结束操作。单击“布局”，Excel弹出图2-10对话框窗图2-10口，选择右边的“数字移动电话类型”字段，并将它拖 到左边的“行” 区，再选择“数字移动电话类型” 字段，将其拖放在“数据”区域中，显示为“计数项： 数字移动电话类型”，以便进行数据图2-11汇总。双击数据区域中的“计数项：数字移动电话类型”字段，打开“数据透视表字段”对话框，在“汇总方式”列表中选择“计数”，如图2-12。图2-12如果想选择数据显示方式，可单击图右侧的 “选项”按钮，则EXCEL 弹出“数据显示方式”。在“数据显示方式”的下拉选项中确定“占同列数据总和的百分比”选项， 单击“确定”按钮，则版式调整完毕。再单击 “确定”按钮，回到数据透视表步骤3，如图2-13所示。 图2-13单击数据透视表步骤3中的“完成”按钮，将数字移动电话类型移动到汇总表中得数字移动电话调查单向表如图2-14所示。单向表描述了各移动电话用户对移动电话类型偏好的分布情况。从图中可以看出“全球通”是该地区的主要消费特征，其次是“长白行”，因而该公司应当注重这两种电话类型的开发与服务。图2-14（2） 两向表两向表用于表示两个用文字表示的品质变量之间的关系。如上例中，若该公司市场部经理希望根据上例中的调查结果，确认不同性别的用户，其移动电话类型偏好是否存在着不同。打开 “移动电话调查”工作表。从“数据”菜单中选择“数据透视表和图表报告”选项，得到“数据透视表对话框”窗口。在第1步之中选择“数据清单或数据库”与“数据透视表”，单击“下一步”按钮。在第2步中，确定数据区域A1：D51，单击“下一步”按钮。在第3步中，确定数据透视表的位置，如将其位置调整到“H1”单元格。然后，单击“布局”按钮，打开“布局”对话框如图2-15所示。 图2-15将对话框右边的“数字移动电话类型”字段拖到左边的“行”区作为分组变量，再将右边的“性别” 字段拖到“列”区，作为交叉分析的变量，最后将右边的“性别” 字段拖放到“数据”区域中，并选择计数项。如果希望结果是以百分比的形式显示，则可双击数据区域中的“计数项，性别”，则会弹出“数据透视表字段”对话框。选择“占同列数据总和的百分比”选项，来表示所出现的频率，最后，单击“确定”按钮，又回到“布局”对话框。单击“确定”按钮，回到第3步，要注意数据透视表的位置定在单元格H1，最后，单击“完成”按钮，得到交叉表如图2-16所示。 图2-16从上面的交叉表中可以看出，对于移动电话类型来讲，其性别之间有较大的差异，女性偏好“全球通”。当然这只是一种数据整理的结果，要想准确地说明移动电话消费是否存在着性别差异，还需要使用统计推断方法进行假设检验。 4 统计图（1） 绘制统计图的基本步骤Excel 提供“图表向导”工具，帮助创建适合于数据信息和分析目的的各种图表，下面先简单描述一下图表操作的基本步骤。使用“图表向导”工具制作图表之前，选定数据所在的单元格，如果希望数据的行列标题显示在图表中，则选定区域还应包括含有标题的单元格。 单击“插入”菜单中的“图表”选项，按照“图表向导”中4个步骤的指令进行操作。第1步“图表类型”：每种类型的图表都有两种以上的子类型，选中所需要的图形以及它的子图形，单击“下一步”按钮。第2步“图表数据源”：选定包含数据和行列标志的工作表单元格，即便工作表包含多个行列标志，也能在图表中显示它们。生成图表时，应将各行列标志包含到选定区域中。若预览图表看去很合适，则表示所选数据区正确。单击“下一步” 按钮。 第3步“图表选项”：为选定的图表设置某些标准选项。修改这些设置时，随时查看预览图表可以帮助我们确定设置是否合适。单击“下一步” 按钮。 第4步，“图表位置”：可以将图表放置在工作表上，或者单独置于一张新的图表工作表上，在这一步中，可以为图表工作表命名，或者选择现有工作表的名称，单击“完成”按钮。 （2） 单变量值数据单批数据可以表示成折线图、条形图、饼图等图形，它们的绘制大致相同，仍以例1.1 100个麦穗的每穗小数为例说明之。步骤： 打开“数据整理.xls”工作表。 在Excel的“插入”菜单中选择“图表”选项，Excel会启动图表向导， 弹出“图表向导”对话框窗口。在 “图表类型”列表中选择 “柱形图”（图2-17），在“子图表类型”列表中选择“三维簇状柱形图”， 单击“下一步” 按钮，进入数据源对话框。 图2-17图2-18在这步对话框中，主要是选择数据源如图2-18所示。单击“数据区域”右端的“压缩对话框”按钮，打开工作表，在工作表上将鼠标指向单元格B2，按下鼠标左键，拖到K11单元格，再单击“压缩对话框”按钮，回到源数据对话框。确定F14：F19单元格作为此图的数据源，同时，也应注意数据系列是以行还是以列方式排列，如果正确，此时，便有一个预览的图形如图2-19所示，如满意可单击“下一步”按钮，进入“图表选项”对话框。 图2-19在对话框 “名称”中输入“100个麦穗的每穗小数”； 在“图例”页面中选择底部，至于其他功能则不需改动与填加，这些过程都会在图表预览框中看到，如果满意，单击“完成”按钮结束，得到图形图2-20。 图2-20双击所绘制的图形，则会得到图2-21的对话框。单击“字体”页面，在“字体”列表中选择“宋体”，在“字形”列表中选择“常规”，在“字号”列表选择“10”。 图2-21从所获得的图中可以清楚地看到，在调查的100个麦穗的每穗小数中，每穗17个小穗数最多。条形图和柱形图可交换使用。Excel的条形图是水平条形，Excel柱形图为竖直条形。两种图表都可以用来表示与一组或几组分类相关的数值，它可用于不同现象的比较，也可以采用时间顺序描述现象的发展趋势。在条形图或柱形图中，各长条或柱的宽度与各长条间的距离彼此均等，条的长度与所代表的变量值成比例。（3） 多变量值数据箱线图例14 从某大学经济管理专业二年级学生中随机抽取11人，对8门主要课程的考试进行调查，得如下结果。课程名称学生编号1234567891011英语7690977170938683788581经济数学6595517478639182757155西方经济学9381768866798392788678市场营销学7487856990807784917470财务管理6875708473607681886875基础会计学7073926578879070667968统计学5591687384817069946271计算机应用基础8578819570678272808177根据上表计算出11名学生各科考试成绩的下四分位数、最大值、最小值和上四分位数如下：学生编号下四分位数最大值最小值上四分位数166.5935580.5276.5957390.5369975188.5470956586.5570906681665936084776.5917088871926983.5976.5946689.51069.58662831169815577.5在Excel的“插入”菜单中选择“图表”选项，Excel会启动图表向导，弹出“图表向导”对话框窗口。在“图表类型”列表中选择“柱形图”，在“子图表类型”列表中选择“股价图”（图2-22）单击“下一步”按钮，进入数据源对话框。按上表形式输入数据，按下“确定”，完成，即可得到11名学生课程考试成绩的箱线图图2-23。 图2-22 图2-23（4） 直方图分析工具 利用“工具”下拉菜单中的“数据分析”工具，同样可以作图。如果在Excel的“工具”菜单中没有找到“数据分析选项，必须在Excel 中重新安装“分析工具库”内容（见概述）。a) 直方图分析工具库的内容直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和累积频数。在“工具”菜单中，单击“数据分析”选项，弹出数据分析 对话框，如图2-24所示。图2-24在“分析工具”列表框中，单击“直方图”分析工具，则会弹出“直方图”对话框如图2-25所示。 图2-25在“输入区域”中，输入A2：A102，然后选定标志，并在输出区域中输入单元格D1，选定图表输出，表示需要直方图与频数表。单击“确定”按钮，直方图分析工具便生成所需图。实验二 数据描述与分析一、 用Excel函数进行数据描述与分析（一） 集中趋势或集中性的测定与分析图3-1 数据分布的形状在图3-1数据分布中，各个变量值与中间位置的距离越近，所出现的次越多，与中间位置的距离越远，所出现的次数越少，从而形成了以中间值为中心的集中趋势。统计学中通常用均值来描述总体分布的集中趋势。在Excel中既可手工创建公式计算各种平均数，也可利用Excel中的统计函数。在“统计函数”类别中用于集中趋势测定的常用函数有三种：均值、中位数和众数。1 均值函数（AVERAGE）均值函数是应用比较广泛的一种平均数函数，它能够计算一组数据的算术平均数。其语法结构为：AVERAGE(number1，number2， .)。其中：number1， number2， .为要计算平均数的 130 个参数。2 中位数函数（MEDIAN）中位数函数用于描述居于数据分布中心位置的数值。其语法结构为：MEDIAN(number1，number2， .)。其中：number1，number2，. 是需要找出中位数的1 30 个数字参数。3 众数函数（MODE）众数函数用于计算一组数据中出现频率最多的数值。其语法结构为：MODE(number1，number2， )。其中：number1， number2， 是用于众数计算的 1 30个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。4 切尾均值函数（TRIMMEAN）返回数据分布中心部分的算术平均值。其语法结构为：TRIMMEAN(array，percent) 。 其中：Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例。例21 以表1.1 100个麦穗的每穗小穗数为例计算平均数。 打开“数据整理.xls”工作簿，选择“100个麦穗的每穗小穗数”工作表。 在单元格B7中输入“均值”，在B8中输入“中位数”， 在B9中输入“众数”。选定单元格C7，单击“插入”菜单，选择“函数”选项，Excel会弹出“粘贴函数”对话框窗口。在“函数分类”列表中选择“统计”，在“函数名”列表中选均值函数“AVERAGE”。单击“确定”， 则弹出“AVERAGE”函数对话窗口。 在“Number1”区域中输入数据A2：A102后，对话窗口底部便显示出计算结果17。如果对话窗口中没有计算结果，便说明计算有错误，需要再检查一下。单击“确定”按钮，计算完成。也可以直接在单元格C7中输入均值函数公式“=AVERAGE(A2:A102)”，然后敲回车键，得到同样结果。在单元格C8中输入公式“=MEDIAN（A2：A102）”计算中位数。在单元格C9中输入公式“=MODE（A2：A102）”计算众数。（二）、 离散性的测定与分析如图3-2所示，三个不同的曲线表示三个不同的总体，其均值相同，但离中趋势不同。图3-2 表示离散性数据示意图描述方差离散性的统计数有：方差与标准差、分位差、变异系数、极差等1 标准差在Excel中用于计算标准差的函数有两个。a) 总体标准差函数（STDEVP） 总体标准差函数用来反映相对于均值的离散程度。其语法结构为：STDEVP(number1，number2，)。其中，number1，number2，为对应于样本总体的 1 30个参数。b) 样本标准差函数(STDEV) 样本标准差函数用来计算样本中相对于均值的离散程度。其语法结构为：STDEV(number1，number2，)其中，Number1，number2，为对应于总体样本的 1 302 分位差四分位距是总体中第3四分位数与第1四分数之差四分位距可包括位于总体分布中心的50%，它能集中地反映总体的特征。总体第50%位置的数值是第2四分位数Q2；位于总体第75%位置的数值是第3四分位数Q3 。四分位数函数QUARTILE可以计算一组数据的四分位数。其语法结构为：QUARTILE(array，quart) 其中： Array为计算四分位数的数组或数据区域，Quart决定返回第几个四分位数。如果 quart 等于0，函数 QUARTILE 返回最小值； quart 等于1，函数 QUARTILE 返回第1四分位数 ，(第25 个百分排位)；quart 等于2，函数QUARTILE 返回中位数(第 50 个百分排位)；如果 quart 等于3， 函数 QUARTILE 返回第3四分位数(第 75 个百分排位)；如果 quart 等于4，函数 QUARTILE 返回最大值。3 变异系数 CV= STDEV(number1，number2，)/ AVERAGE(number1，number2，)4 极差（Range）R=MAX(number1，number2，)-MIN(number1，number2，)（三）、分布形态的测定与分析对于一组数据，不仅要描述其集中趋势、离中趋势，而且也要描述其分布形态。这是因为一个总体如果均值相同，标准差相同，但也可能分布形态不同。另外，分布的形态有助于识别整个总体的数量特征。总体的分布形态可以从两个角度考虑，一是分布的对称程度，另一个是分布的高低。前者的测定参数称为偏度或偏斜度，后者的测定参数称为峰度。在统计分析中，用偏度指标对其进行测定。偏度数值等于零，说明分布为对称；偏度数值大于零，说明分布呈现右偏态；如果偏度数值小于零，说明分布呈左偏态。峰度是掌握分布形态的另一个指标，它能够描述分布的平缓或陡峭。如果峰度数值等于0，说明分布为正态；如果峰度数值大于0，说明分布呈陡峭状态；如果峰度值小于0，则说明分布形态趋于平缓。Excel 提供了计算偏度与峰度的工作表函数。1. 偏度偏度函数（SKEW）用于计算次数分布的不对称程度。其语法结构为：SKEW(number1，number2，) 其中：number1，number2为需要计算偏斜度的 1 30 个参数。2. 峰度峰度函数（KURT）用于反映次数分布的尖锐度或平坦度。其语法结构为：KURT(number1，number2， )其中：number1，number2， 为需要计算其峰值的 1 30 个参数。二、 描述统计分析工具的使用Excel描述统计工具计算与数据的集中趋势、离中趋势、偏度等有关的描述性统计指标。选择“描述工具”工作表。打开“工具”菜单，选择“数据分析”选项，打开数据分析对话框如图3-4所示。图3-4双击“描述统计”项或先单击此项再选择“确定”按钮，描述统计对话框打开如图3-5所示。图3-5由于所选数据范围包括一个标志名称，单击“标志位于第一行”选项边上的复选框。单击“输出区域”项，旁边出现了一个输入框，单击此框出现插入符，单击B1，在输入框中出现输出地址“$B$1”，这是输出结果的左上角起始位置。单击“汇总统计”，如不选此项，则Excel省略部分输出结果。单击“确定”按钮，将产生输出结果，如图3-6所示。图3-6结果输出在B、C两栏的1到16行中。如果结果只是 3行，说明没有选择“汇总统计”，可以重图3-7新打开对话框进行选择。输出区域将为每个数据集产生两列信息。左边一列包含统计标志项，右边一列包含统计值。根据所选择的“分组方式”选项的不同，Excel 将为输入表中的每一行或每一列生成一个两列的统计表。 新工作表组 单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始粘贴计算结果。如果需要给新工作表命名，请在右侧编辑框中键入名称。 新工作簿 单击此选项，可创建一新工作簿，并在新工作簿的新工作表中粘贴计算结果。 汇总统计 如果需要 Excel 在输出表中生成下列统计结果，请选中此项。这些统计结果有：均值、标准差、中位数、众数、标准误差、方差、峰值、偏度、全距、最小值、最大值、总和、总个数、第K个最大值、第K个最小值和置信度。 平均数置信度 如果需要在输出表的某一行中包含均值的置信度，请选中此项，然后在右侧的编辑框中，输入所要使用的置信度。例如，数值 95% 可用来计算在显著性水平为 5% 时的均值置信度。 第 k个最大值 如果需要在输出表的某一行中包含每个区域的数据的第 k 个最大值，请选中复选框，然后在右侧的编辑框中，输入 k 的数值。如果输入 1，则这一行将包含数据集中的最大数值。 第 k 个最小值 如果需要在输出表的某一行中包含每个区域的数据的第 k 个最小值，请选复选取框，然后在右侧的编辑框中，输入 k 的数值。如果输入 1，则这一行将包含数据集中的最小数值。 实验三 概率分布与抽样分布概率分布可分为离散型随机变量的概率分布和连续型随机变量的概率分布。在此主要介绍二项分布和正态分布。 一、二项分布在 Excel中，常用的二项分布函数有三个，即： 二项分布函数 BINOMDIST，用于计算二项分布的概率值； CRITBINOM函数，用于计算大于等于临界值的累积二项分布最小值； NEGBINOMDIST负二项分布函数，用于返回负二项分布的概率。 例3.1 假定某一足球队员在12码线罚球的命中率为75%（即P=0.75）求罚球4次命中2次的概率。操作步骤如下： 打开Excel的“插入”菜单，选择“函数”选项，打开“粘贴函数”对话框。 在“函数分类”列表中选择“统计”，在“函 数名”列表中选择二项分布函数BINOMDIST。单击“确定”按钮，打开二项分布函数对话框如图4-1所示。图4-1根据题意得知，成功次数为2，试验次数为4，成功概率为0.75，使用概率分布函数（False），将这些已知资料填入对话框中，单击“确定”按钮，得知罚球4次命中2次的概率为 0.21。若问至多命中2次的概率。与上面的计算相同，只是采用累积分布函数，输入各选项资料如图4-2所示。图4-2单击“确定”按钮，得知至多命中2次的概率为 0.2617。 二、正态分布正态分布函数NORMDIST 正态分布函数NORMDIST用于计算给定均值和标准差的正态分布的累积函数。其语法结构为：NORMDIST(x，mean，standard_dev，cumulative)。 其中：x为需要计算其分布的数值；Mean为分布的均值；Standard_dev 为分布的标准差；Cumulative 为一逻辑值，指明函数的形式。 如果 cumulative 为 TRUE，函数 NORMDIST 返回累积分布函数；如果为 FALSE，返回概率密度函数。 标准正态分布函数 NORMSDIST 标准正态分布函数 NORMSDIST用于计算标准正态分布的累积函数，该分布的均值为 0，标准偏差为 1。其语法结构为：NORMSDIST(z)。 其中：Z为需要计算其分布的数值。 正态分布函数的反函数NORMINV 正态分布函数的反函数NORMINV能够根据已知概率等参数确定正态分布随机变量值。 其语法结构为： NORMINV(probability，mean，standard_dev) 其