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

实验一Excel在生物统计中应用一、目的要求使学生掌握Excel在生物统计中的应用方法。二、材料用具练习纸、铅笔、装有Excel软件的计算机及输出设备或计算器。三、方法步骤1．启动Excel2．加载“分析工具库”在默认的情况下，Excel是没有加载“分析工具库”，无法进行统计分析。先查看计算机是否加载“分析工具库”。首先启动Excel，检查“工具”菜单中是否有“数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。可按照下列步骤来加载“分析工具库”。（1）选取“工具”菜单中的“加载宏”命令，弹出“加载宏”的对话框。图图1“加载宏”对话框示意图（2）选中“分析工具库”复选框，单击“确定”按钮，按提示完成加载过程。此时再查看“工具”菜单，就可发现“数据分析”命令，表示安装完成，单击“工具”菜单中“数据分析”命令，得图2，即可进行相关的统计分析。图2“数据分析”图2“数据分析”对话框示意图3．分析工具库提供的统计分析方法分析工具库提供的常用统计分析方法如下：①单向分类资料的方差分析②双向分类交叉分组无重复资料的方差分析③双向分类交叉分组有重复资料的方差分析④描述性统计量计算⑤二总体方差的同质性检验⑥编制频数分布表及绘制直方图⑦排位与百分比排位⑧二样本均数差数的u检验⑨配对资料的t检验⑩等方差非配对资料的t检验eq\o\ac(○,11)异方差非配对资料的t检验eq\o\ac(○,12)一元线性回归与多元线性回归分析eq\o\ac(○,13)计算多个变量两两之间的相关系数及协方差eq\o\ac(○,14)进行随机和顺序抽样4．统计分析的粘贴函数Excel提供的统计分析粘贴函数如下：图5原始数据示意图（1）打开Excel，输入原始数据和各组的下限，样式见图5，图中从A1单元格到L10单元格的区域为原始数据，各组的组中值或下限值位于M1到图5原始数据示意图（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函数可以求总体方差；用VAR函数可以求样本的方差。如在单元格中输入“=VARP（1，2，3，4，5）”得2；在单元格中输入“=VAR（1，2，3，4，5）”得2.5。6．标准差就是方差开平方。用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计算思考练习题的中的方差和标准差。

实验四概率和概率分布一、目的要求掌握概率和概率计算。了解二项分布、正态分布原理，练习主要理论分布的概率计算及几种常用理论分布的使用方法。二、材料用具练习纸、铅笔、装有Excel软件的计算机及输出设备或计算器。三、方法步骤1．二项分布的概率计算例如：观察施用某种农药后棉铃虫的死亡概率为0.65，现对10头棉铃虫喷施该农药，问死亡8头及死亡8头以下的概率是多少？棉铃虫死亡头数服从二项分布。上述两个概率可以用Excel中的函数BINOMDIST来计算，操作方法如下：图7二项分布概率结果对话框示意图在Excel工作表中单击要输出这一计算结果的单元格，然后单击按纽“fx”，在“选择类别”下拉列表框中中选择“统计”，在其下方的“选择函数”列表中选择BINOMDIST函数，单击确定按纽，弹出BINOMDIST函数的对话框。在对话框中的Number_s框中输入8，表示8头死亡；在Trials框中输入10，表示一共进行了10次独立的试验；在Probability_s框中输入0.65，表示棉铃虫死亡率为0.65；在Cumulative框中，有二项FALSE和TRUE供选择，FALSE用于计算单一概率，TRUE用于计算累积概率。如输入FALSE，计算的是死亡8头的概率，如输入TRUE，计算的是死亡8头和图7二项分布概率结果对话框示意图如果需要改变刚才的计算内容，将光标移到该单元格上，此时在编辑栏中显示BINOMDIST（8，10，0.65，FALSE），在此可以直接改变其中的内容。2．正态分布概率的计算（1）一般正态分布概率的计算例如：有一玉米果穗长度的正态总体，其平均数μ=20cm，标准差σ=3.4cm，试计算：①果穗长度x≤13cm的概率。②短果穗中，概率占10%的临界果穗长度是多少？Excel提供了正态分布概率计算的二个函数NORMDIST和NORMINV，NORMDIST用来计算正态分布的概率密度函数值和累积概率函数值，NORMINV用来计算正态分布累积函数的逆函数。用NORMDIST函数计算上例①中概率的步骤为：打开NORMDIST函数对话框，在X后的编辑框中输入13，在Mean后的编辑框中输入20，在Standard_dev后的编辑框中输入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图11标准正态分布结果对话框示意图图10标准正态分布结果对话框示意图NORMSDIST用来计算正态分布的概率密度函数值和累积概率函数值，NORMSINV用来计算正态分布累积函数的逆函数。其操作步骤与一般正态分布完全一样。四、作业将教材中所讲例题做分析练习。某地100例30～40岁健康男子血清总胆固醇(mol·L-1)测定结果如下：4.773.376.143.953.564.234.314.715.694.124.564.375.396.305.217.225.543.935.216.515.185.774.795.125.205.104.704.743.504.694.384.896.255.324.504.633.614.444.434.254.035.854.093.354.084.795.304.973.183.975.165.105.854.795.344.244.324.776.366.384.885.553.044