实验一 Excel在生物统计中应用一、目的要求使学生掌握Excel在生物统计中的应用方法.二、材料用具练习纸、铅笔、装有Excel软件的计算机与输出设备或计算器.三、方法步骤1．启动Excel2．加载"分析工具库"在默认的情况下,Excel是没有加载"分析工具库",无法进行统计分析.先查看计算机是否加载"分析工具库".首先启动Excel,检查"工具"菜单中是否有"数据分析"命令.如果没有发现"数据分析"命令,就表示未加载"分析工具库".可按照下列步骤来加载"分析工具库".〔1〕选取"工具"菜单中的"加载宏"命令,弹出"加载宏"的对话框.图1 "加载宏"对话框示意图〔2〕选中"分析工具库"复选框,单击"确定"按钮,按提示完成加载过程.此时再查看"工具"菜单,就可发现"数据分析"命令,表示安装完成,单击"工具"菜单中"数据分析"命令,得图2,即可进行相关的统计分析.图2 "数据分析"对话框示意图3．分析工具库提供的统计分析方法分析工具库提供的常用统计分析方法如下：①单向分类资料的方差分析②双向分类交叉分组无重复资料的方差分析③双向分类交叉分组有重复资料的方差分析④描述性统计量计算⑤二总体方差的同质性检验⑥编制频数分布表与绘制直方图⑦排位与百分比排位⑧二样本均数差数的u检验⑨配对资料的t检验⑩等方差非配对资料的t检验异方差非配对资料的t检验一元线性回归与多元线性回归分析计算多个变量两两之间的相关系数与协方差进行随机和顺序抽样4．统计分析的粘贴函数Excel提供的统计分析粘贴函数如下：AVERAGEBINOMDISTCHIDISTCHIINVCHITESTCONFIDENCECORRELCOVARFDISTFINVFTESTGEOMEANHARMEANINTERCEPTMAX MEDIANMINMODENORMDISTNORMINVNORMSDISTNORMSINVPOISSONSLOPESTDEVSTDEVPTDISTTINVTTESTVARVARPZTEST计算算术平均值计算二项式分布的概率值计算特定χ2分布的单尾概率值计算一定单尾概率值时的χ2临界值计算独立性检验的χ2值计算总体平均值的置信区间计算两组数据的相关系数计算两组数据的协方差计算特定F分布的单尾概率值计算一定概率时的临界F值计算二个样本方差之比F值的概率计算几何平均数计算调和平均数计算直线回归的截距计算最大值计算一组给定数字的中位数计算最小值计算一组数据的众数计算正态分布的累积函数计算正态分布累积函数的逆函数计算标准正态分布的累积函数计算标准正态分布累积函数的逆函数计算泊松分布的概率计算给定数据的直线回归系数计算样本标准差计算样本总体的标准差计算学生氏-t分布的概率值计算特定概率时学生氏-t分布的临界t值计算t检验时的学生氏-t检验相关的概率计算样本的方差计算样本总体的方差计算Z检验的双尾概率值5．Excel的常用基本概念〔1〕工作薄和工作表Microsoft Excel工作簿是计算和储存数据的文件,每一个工作簿都可以包含多张工作表,因此,可在单个文件中管理各种类型的相关信息.使用工作表可以对数据进行组织和分析,可以同时在多张工作表上输入和编辑数据,并且可以对来自不同工作表的数据进行汇总计算.在创建图表之后,既可以将其置于原始数据所在的工作表上,也可以放置在单独的工作表上.工作表的名称显示于工作簿窗口底部的工作表标签上,单击这个标签即可进入该工作表.当前所在的工作表称为活动工作表,在它的标签上标有单下划线.可以在同一工作簿内或两个工作簿之间对工作表进行改名、添加、删除、移动或复制等编辑操作.在默认情况下,每个工作薄由3个工作表组成,用户可以根据需要添加更多的工作表.每个工作表由256列和65 536行组成.行和列相交形成单元格,它是存储数据的基本单位.列用英文字母表示,开始是单个英文字母,然后是2个英文字母组合,即由A～IV共256列.行用阿拉伯数字表示,由1～65 536.每个单元格的定位可以通过该单元格所对应的行数和列标来确定,如B6就表示B列第6行的单元格.在活动工作表众多的单元格中,其中有一单元格含有粗边框线,此单元格为活动单元格,在该单元格中可以输入和编辑数据.每进入一个工作表时,A1自动为活动单元格.在活动单元格的右下角有一小黑方块,这个黑方块称为填充柄,利用此填充柄可以填充相邻单元格区域的内容.〔2〕单元格和区域引用引用是指调用工作表中的数据用于计算分析,是通过标识工作表上的单元格或区域来实现的.通过引用,指明在计算时所使用的数据在工作表中的位置,可以使用工作表中不同区域的数据,或者在多个计算公式中使用同一单元格的数值.还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据.引用不同工作簿中的数据称为外部引用,引用其他程序中的数据称为远程引用.如果要引用某一单元格的数据,可在计算公式中输入该单元格的标识,例如要引用在列B和行10这一单元格的数据,在计算公式中输入B10,或者用鼠标点击该单元格.如果要引用某个区域的数据,可在计算公式中输入该区域左上角单元格的标识,后面跟一个冒号〔：〕,后面再写上该区域右下角单元格的标识.例如,要引用从单元格C5到单元格C15这一区域,可输入C5:C15；如要引用从单元格A5到单元格B10这一区域,即可输入A5:B10.〔3〕绝对引用与相对引用的区别当生成公式时,对单元格或区域的引用通常基于他们与公式单元格的相对位置.如在单元格A8中包含公式=8×B5,Microsoft Excel将在距单元格A8上面一个单元格和右面一个单元格处的单元格中查找数值,这就是相对引用.当复制使用相对引用的公式时,被粘贴公式中的引用将被更新,并指向与当前公式位置相对应的其他单元格.如单元格A8中的公式被复制到单元格A10中,单元格A10中的公式已经改为=8×B7,即从单元格A8上面和右面一个单元格处的单元格中查找数值.如果在复制公式时,不希望引用发生改变,可使用绝对引用.绝对引用的方式是在相对引用列标和行数字前加上美元符号,即＄B＄5.如在单元格A8中包含的公式=8×＄B＄5,现在将公式复制到另一单元格中,则公式中的引用将不发生改变.四、作业同学们可以根据教材中实例进行验证性练习.实验二 试验数据整理一、目的要求了解资料搜集整理的意义,更好地掌握次数分布表、图的制作方法,学会利用Excel进行数据整理与图表制作.二、材料用具装有Excel软件的计算机与输出设备.三、方法步骤1．图表向导的利用用Excel中的"图表向导"可以快速准确的绘制图表.它是一连串的对话框,依照它逐步建立或修改图表.新建图表需四个步骤,修改图表只需两个步骤.步骤1：选择图表类型选择"常用"工具栏中的"图表向导"按扭,图标或单击菜单栏中"插入"再击"图表"命令,激活图表向导对话框.Excel提供了两大类的图形：标准类型和自定义类型,用户可根据需要选择.步骤2：选定数据区域如果在单击图表向导之前,就已选定数据范围,那么图表向导的第二对话框就会自动设定数据区域.如果事先没有设定,用户可自己设定.步骤3：设定图表选项完成步骤2之后,单击"下一步"按钮进入步骤3.在步骤3里有很多的选项卡："标题"、"坐标轴"、"网格线"、"图例"、"数据标志"、"数据表".步骤4：指定图表位置当完成步骤3后,单击"下一步"按钮即可进入步骤4.可以选定"作为新工作表插入"或"作为其中的对象插入".然后单击"完成"按钮结束图表向导,完成了图表的制作.2．"数据分析"工具栏运用现以水稻品种120行产量〔kg〕为例,说明其方法与步骤.表1 水稻品种120行产量〔kg〕表17721519797159245119119131152167104161214125175188192176175951991161652149515883801381511871261342061379897129143174159165136108141148168163176102194173751301491501551111581311899114215215416312320515513120918397119181187131215111186150155197116254239160179151198124179184168169173181188211175122151171166143190213192231163159177147194227169124159159图5 原始数据示意图〔1〕打开Excel,输入原始数据和各组的下限,样式见图5,图中从A1单元格到L10单元格的区域为原始数据,各组的组中值或下限值位于M1到M11单元格.〔2〕从"工具"菜单中选定"数据分析"命令,再选定"直方图",确定后出现直方图对话框.〔3〕在输入区域中输入＄A＄1:＄L＄10,在接收区域中输入＄M＄1:＄M＄11,在输出选项中选定输出区域如＄A＄12后,单击确定,得到频数分布表.若在"直方图"对话框中选定"图表输出"复选框,则直方图也同时输出,如图6.注意：双击分类轴,在数据系列"选项中"将分类间距设为0,才得直方图,不然只得到条形图.图6 直方图示意图四、作业1．利用Excel将表中的数据进行排序并分组,然后编制次数分布表.2．利用Excel 将整理后的数据绘制直方图和折线图.实验三 特征数计算一、目的要求掌握各种特征数的计算方法,学会利用Excel进行特征数的计算.二、材料用具练习纸、铅笔、装有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,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函数可以。