实验一Excel在生物统计中应用剖析

1、实验一 Excel在生物统计中应用一、目的要求使学生掌握Excel在生物统计中的应用方法。二、材料用具练习纸、铅笔、装有Excel软件的计算机及输出设备或计算器。三、方法步骤1启动Excel2加载“分析工具库”在默认的情况下，Excel是没有加载“分析工具库”，无法进行统计分析。先查看计算机是否加载“分析工具库”。首先启动Excel，检查“工具”菜单中是否有“数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。可按照下列步骤来加载“分析工具库”。（1）选取“工具”菜单中的“加载宏”命令，弹出“加载宏”的对话框。图1 “加载宏”对话框示意图（2）选中“分析工具库”复选框，单

2、击“确定”按钮，按提示完成加载过程。此时再查看“工具”菜单，就可发现“数据分析”命令，表示安装完成，单击“工具”菜单中“数据分析”命令，得图2，即可进行相关的统计分析。图2 “数据分析”对话框示意图3分析工具库提供的统计分析方法分析工具库提供的常用统计分析方法如下：单向分类资料的方差分析双向分类交叉分组无重复资料的方差分析双向分类交叉分组有重复资料的方差分析描述性统计量计算二总体方差的同质性检验编制频数分布表及绘制直方图排位与百分比排位二样本均数差数的u检验配对资料的t检验等方差非配对资料的t检验异方差非配对资料的t检验一元线性回归与多元线性回归分析计算多个变量两两之间的相关系数及协方差进行随

3、机和顺序抽样4统计分析的粘贴函数Excel提供的统计分析粘贴函数如下：AVERAGEBINOMDISTCHIDISTCHIINVCHITESTCONFIDENCECORRELCOVARFDISTFINVFTESTGEOMEANHARMEANINTERCEPTMAX MEDIANMINMODENORMDISTNORMINVNORMSDISTNORMSINVPOISSONSLOPESTDEVSTDEVPTDISTTINVTTESTVARVARPZTEST计算算术平均值计算二项式分布的概率值计算特定2分布的单尾概率值计算一定单尾概率值时的2临界值计算独立性检验的2值计算总体平均值的置信区间计算两组数

4、据的相关系数计算两组数据的协方差计算特定F分布的单尾概率值计算一定概率时的临界F值计算二个样本方差之比F值的概率计算几何平均数计算调和平均数计算直线回归的截距计算最大值计算一组给定数字的中位数计算最小值计算一组数据的众数计算正态分布的累积函数计算正态分布累积函数的逆函数计算标准正态分布的累积函数计算标准正态分布累积函数的逆函数计算泊松分布的概率计算给定数据的直线回归系数计算样本标准差计算样本总体的标准差计算学生氏-t分布的概率值计算特定概率时学生氏-t分布的临界t值计算t检验时的学生氏-t检验相关的概率计算样本的方差计算样本总体的方差计算Z检验的双尾概率值5Excel的常用基本概念（1）工作薄

5、和工作表Microsoft Excel工作簿是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此，可在单个文件中管理各种类型的相关信息。使用工作表可以对数据进行组织和分析，可以同时在多张工作表上输入和编辑数据，并且可以对来自不同工作表的数据进行汇总计算。在创建图表之后，既可以将其置于原始数据所在的工作表上，也可以放置在单独的工作表上。工作表的名称显示于工作簿窗口底部的工作表标签上，单击这个标签即可进入该工作表。当前所在的工作表称为活动工作表，在它的标签上标有单下划线。可以在同一工作簿内或两个工作簿之间对工作表进行改名、添加、删除、移动或复制等编辑操作。在默认情况下，每个工作薄由3个工

6、作表组成，用户可以根据需要添加更多的工作表。每个工作表由256列和65 536行组成。行和列相交形成单元格，它是存储数据的基本单位。列用英文字母表示，开始是单个英文字母，然后是2个英文字母组合，即由AIV共256列。行用阿拉伯数字表示，由165 536。每个单元格的定位可以通过该单元格所对应的行数和列标来确定，如B6就表示B列第6行的单元格。在活动工作表众多的单元格中，其中有一单元格含有粗边框线，此单元格为活动单元格，在该单元格中可以输入和编辑数据。每进入一个工作表时，A1自动为活动单元格。在活动单元格的右下角有一小黑方块，这个黑方块称为填充柄，利用此填充柄可以填充相邻单元格区域的内容。（2）

7、单元格和区域引用引用是指调用工作表中的数据用于计算分析，是通过标识工作表上的单元格或区域来实现的。通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区域的数据，或者在多个计算公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据。引用不同工作簿中的数据称为外部引用，引用其他程序中的数据称为远程引用。如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B和行10这一单元格的数据，在计算公式中输入B10，或者用鼠标点击该单元格。如果要引用某个区域的数据，可在计算公式中输入该区域左上角单元格的标识，后面跟一个冒号

8、（：），后面再写上该区域右下角单元格的标识。例如，要引用从单元格C5到单元格C15这一区域，可输入C5:C15；如要引用从单元格A5到单元格B10这一区域，即可输入A5:B10。（3）绝对引用与相对引用的区别当生成公式时，对单元格或区域的引用通常基于他们与公式单元格的相对位置。如在单元格A8中包含公式=8×B5，Microsoft Excel将在距单元格A8上面一个单元格和右面一个单元格处的单元格中查找数值，这就是相对引用。当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置相对应的其他单元格。如单元格A8中的公式被复制到单元格A10中，单元格A10中的公式已

9、经改为=8×B7，即从单元格A8上面和右面一个单元格处的单元格中查找数值。如果在复制公式时，不希望引用发生改变，可使用绝对引用。绝对引用的方式是在相对引用列标和行数字前加上美元符号，即B5。如在单元格A8中包含的公式=8×B5，现在将公式复制到另一单元格中，则公式中的引用将不发生改变。四、作业同学们可以根据教材中实例进行验证性练习。实验二试验数据整理一、目的要求了解资料搜集整理的意义，更好地掌握次数分布表、图的制作方法，学会利用Excel进行数据整理与图表制作。二、材料用具装有Excel软件的计算机及输出设备。三、方法步骤1图表向导的利用用Excel中的“图表向导”可以快速

10、准确的绘制图表。它是一连串的对话框，依照它逐步建立或修改图表。新建图表需四个步骤，修改图表只需两个步骤。步骤1：选择图表类型选择“常用”工具栏中的“图表向导”按扭，图标或单击菜单栏中“插入”再击“图表”命令，激活图表向导对话框。Excel提供了两大类的图形：标准类型和自定义类型，用户可根据需要选择。步骤2：选定数据区域如果在单击图表向导之前，就已选定数据范围，那么图表向导的第二对话框就会自动设定数据区域。如果事先没有设定，用户可自己设定。步骤3：设定图表选项完成步骤2之后，单击“下一步”按钮进入步骤3。在步骤3里有很多的选项卡：“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表

11、”。步骤4：指定图表位置当完成步骤3后，单击“下一步”按钮即可进入步骤4。可以选定“作为新工作表插入”或“作为其中的对象插入”。然后单击“完成”按钮结束图表向导，完成了图表的制作。2“数据分析”工具栏运用现以水稻品种120行产量（kg）为例，说明其方法与步骤。表1 水稻品种120行产量（kg）表17721519797159245119119131152167104161214125175188192176175951991161652149515883801381511871261342061379897129143174159165136108141148168163176102194173

12、751301491501551111581311899114215215416312320515513120918397119181187131215111186150155197116254239160179151198124179184168169173181188211175122151171166143190213192231163159177147194227169124159159图5 原始数据示意图（1）打开Excel，输入原始数据和各组的下限，样式见图5，图中从A1单元格到L10单元格的区域为原始数据，各组的组中值或下限值位于M1到M11单元格。（2）从“工具”菜单中选定“数据

13、分析”命令，再选定“直方图”，确定后出现直方图对话框。（3）在输入区域中输入A1:L10，在接收区域中输入M1:M11，在输出选项中选定输出区域如A12后，单击确定，得到频数分布表。若在“直方图”对话框中选定“图表输出”复选框，则直方图也同时输出，如图6。注意：双击分类轴，在数据系列“选项中”将分类间距设为0，才得直方图，不然只得到条形图。图6 直方图示意图四、作业1利用Excel将表中的数据进行排序并分组，然后编制次数分布表。2利用Excel 将整理后的数据绘制直方图和折线图。实验三特征数计算一、目的要求掌握各种特征数的计算方法，学会利用Excel进行特征数的计算。二、材料用具练习纸、铅笔、

14、装有Excel软件的计算机及输出设备或计算器。三、方法步骤在生物统计分析中主要计算平均数和变异数。平均数主要有算术平均数、几何平均数及中数，变异数主要有极差、方差、标准差、变异系数。1算术平均数用AVERAGE函数可用来计算算术平均数，例如一组数据2，3，4，5，6的平均数可在单元格中键入“=AVERAGE（2，3，4，5，6）”得 4。如果这5个数据分别存放于A1到A5的字段中，则键入“= AVERAGE（A1:A5）”得4。通常“平均数”是指算术平均数。2几何平均数用GEOMEAN函数可用来计算几何平均数，例如6，15，35，105，260的几何平均数可在单元格中键入“=GEOMEAN（6

15、，15，35，105，260）”得38.63。3中数计算如果观察值次数是偶数，则取中间两个观察值的算术平均数作为中数。用MEDIAN函数可以计算中数。如在单元格中输入“=MEDIAN（2，3，4，7，8）”得4；在单元格中输入“=MEDIAN（2，3，4，7）”得3.5。4极差资料中最大值减去最小值是极差，可以利用MAX求得最大值，用MIN 求得最小值，两者的差数就是极差。5方差 平方和除以自由度即为方差。用VARP函数可以求总体方差；用VAR函数可以求样本的方差。如在单元格中输入“=VARP（1，2，3，4，5）”得2；在单元格中输入“=VAR（1，2，3，4，5）”得2.5。6标准差就是方

16、差开平方。用STDEVP可以计算总体标准差；用STDEV则可以计算样本标准差。如在单元格中输入“=STDEVP（1，2，3，4，5）”得1.414214；在单元格中输入“=STDEV（1，2，3，4，5）”得1.581139。7变异系数标准差除以平均数就是变异系数。但Excel本身没有提供函数计算变异系数，我们可以通过STDEV计算样本标准差，用AVERAGE计算平均数，两者相除即得变异系数。四、作业1利用Excel 查找最大值、最小值，计算算术平均数等特征数。2利用Excel 计算思考练习题的中的方差和标准差。实验四 概率和概率分布一、目的要求掌握概率和概率计算。了解二项分布、正态分布原理，

17、练习主要理论分布的概率计算及几种常用理论分布的使用方法。二、材料用具练习纸、铅笔、装有Excel软件的计算机及输出设备或计算器。三、方法步骤1二项分布的概率计算例如：观察施用某种农药后棉铃虫的死亡概率为0.65，现对10头棉铃虫喷施该农药，问死亡8头及死亡8头以下的概率是多少？棉铃虫死亡头数服从二项分布。上述两个概率可以用Excel中的函数BINOMDIST来计算，操作方法如下：图7 二项分布概率结果对话框示意图在Excel工作表中单击要输出这一计算结果的单元格，然后单击按纽“fx”，在“选择类别”下拉列表框中中选择“统计”，在其下方的“选择函数”列表中选择BINOMDIST函数，单击确定按纽

18、，弹出BINOMDIST函数的对话框。在对话框中的Number_s框中输入8，表示8头死亡；在Trials框中输入10，表示一共进行了10次独立的试验；在Probability_s框中输入0.65，表示棉铃虫死亡率为0.65；在Cumulative框中，有二项FALSE和TRUE供选择，FALSE用于计算单一概率，TRUE用于计算累积概率。如输入FALSE，计算的是死亡8头的概率，如输入TRUE，计算的是死亡8头和8头以下的概率。按确定按钮后，在选定的单元格中就显示所需要的结果。如果需要改变刚才的计算内容，将光标移到该单元格上，此时在编辑栏中显示 BINOMDIST（8，10，0.65，FAL

19、SE），在此可以直接改变其中的内容。2正态分布概率的计算（1）一般正态分布概率的计算例如：有一玉米果穗长度的正态总体，其平均数=20cm，标准差=3.4cm，试计算：果穗长度x13cm的概率。短果穗中，概率占10%的临界果穗长度是多少？Excel提供了正态分布概率计算的二个函数NORMDIST和NORMINV，NORMDIST用来计算正态分布的概率密度函数值和累积概率函数值，NORMINV用来计算正态分布累积函数的逆函数。用NORMDIST函数计算上例中概率的步骤为：打开NORMDIST函数对话框，在X后的编辑框中输入13，在Mean后的编辑框中输入20，在Standard_dev后的编辑框中

20、输入3.4，在Cumulative后的编辑框中输入TRUE，然后按确定按钮，显示累积分布函数值为0.0197。如果在Cumulative后的编辑框中输入FALSE，这时函数NORMDIST就返回概率密度函数值，结果显示0.0141，即正态曲线在x=13时的纵高。图8 正态分布概率结果对话框示意图用NORMINV函数计算上例中x的步骤为：打开NORMINV函数对话框，在Probability后的编辑框中输入0.1，在Mean后的编辑框中输入20，在Standard_dev后的编辑框中输入3.4，然后按确定按钮，显示区间点为15.6，即果穗长在15.6cm以下的果穗占总体的10%。图9 正态分布概率结果对话框示意图（2）标准正态分布概率计算 Excel提供了标准正态分布概率计算的二个函数NORMSDIST和NORMSINV，图11 标准正态分布结果对