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

实验一 Excel 在生物统计中应用 一、目的要求 使学生掌握 Excel 在生物统计中的应用方法。二、材料用具 练习纸、铅笔、装有 Excel 软件的计算机及输出设备或计算器。三、方法步骤 1启动 Excel 2加载“分析工具库”在默认的情况下，Excel 是没有加载“分析工具库”，无法进行统计分析。先查看计算机是否加载“分析工具库”。首先启动 Excel，检查“工具”菜单中是否有“数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。可按照下列步骤来加载“分析工具库”。（1）选取“工具”菜单中的“加载宏”命令，弹出“加载宏”的对话框。（2）选中“分析工具库”复选框，单击“确定”按钮，按提示完成加载过程。此时再查看“工具”菜单，就可发现“数据分析”命令，表示安装完成，单击“工具”菜单中“数据分析”命令，得图 2，即可进行相关的统计分析。图 1“加载宏”对话框示意图 3分析工具库提供的统计分析方法 分析工具库提供的常用统计分析方法如下：单向分类资料的方差分析 双向分类交叉分组无重复资料的方差分析 双向分类交叉分组有重复资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制频数分布表及绘制直方图 排位与百分比排位 二样本均数差数的 u 检验 配对资料的 t 检验 等方差非配对资料的 t 检验 11异方差非配对资料的 t 检验 12一元线性回归与多元线性回归分析 13计算多个变量两两之间的相关系数及协方差 14进行随机和顺序抽样 4统计分析的粘贴函数 Excel 提供的统计分析粘贴函数如下：AVERAGE BINOMDIST CHIDIST CHIINV CHITEST CONFIDENCE CORREL COVAR FDIST FINV FTEST GEOMEAN HARMEAN INTERCEPT 计算算术平均值 计算二项式分布的概率值 计算特定 2分布的单尾概率值 计算一定单尾概率值时的 2临界值 计算独立性检验的 2值 计算总体平均值的置信区间 计算两组数据的相关系数 计算两组数据的协方差 计算特定 F 分布的单尾概率值 计算一定概率时的临界 F 值 计算二个样本方差之比 F 值的概率 计算几何平均数 计算调和平均数 计算直线回归的截距 图 2“数据分析”对话框示意图 MAX MEDIAN MIN MODE NORMDIST NORMINV NORMSDIST NORMSINV POISSON SLOPE STDEV STDEVP TDIST TINV TTEST VAR VARP ZTEST 计算最大值 计算一组给定数字的中位数 计算最小值 计算一组数据的众数 计算正态分布的累积函数 计算正态分布累积函数的逆函数 计算标准正态分布的累积函数 计算标准正态分布累积函数的逆函数 计算泊松分布的概率 计算给定数据的直线回归系数 计算样本标准差 计算样本总体的标准差 计算学生氏-t 分布的概率值 计算特定概率时学生氏-t 分布的临界 t 值 计算 t 检验时的学生氏-t 检验相关的概率 计算样本的方差 计算样本总体的方差 计算 Z 检验的双尾概率值 5Excel 的常用基本概念（1）工作薄和工作表 Microsoft Excel 工作簿是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此，可在单个文件中管理各种类型的相关信息。使用工作表可以对数据进行组织和分析，可以同时在多张工作表上输入和编辑数据，并且可以对来自不同工作表的数据进行汇总计算。在创建图表之后，既可以将其置于原始数据所在的工作表上，也可以放置在单独的工作表上。工作表的名称显示于工作簿窗口底部的工作表标签上，单击这个标签即可进入该工作表。当前所在的工作表称为活动工作表，在它的标签上标有单下划线。可以在同一工作簿内或两个工作簿之间对工作表进行改名、添加、删除、移动或复制等编辑操作。在默认情况下，每个工作薄由 3 个工作表组成，用户可以根据需要添加更多的工作表。每个工作表由 256 列和 65 536 行组成。行和列相交形成单元格，它是存储数据的基本单位。列用英文字母表示，开始是单个英文字母，然后是 2 个英文字母组合，即由 AIV 共 256列。行用阿拉伯数字表示，由 165 536。每个单元格的定位可以通过该单元格所对应的行数和列标来确定，如 B6 就表示 B 列第 6 行的单元格。在活动工作表众多的单元格中，其中有一单元格含有粗边框线，此单元格为活动单元格，在该单元格中可以输入和编辑数据。每进入一个工作表时，A1 自动为活动单元格。在活动单元格的右下角有一小黑方块，这个黑方块称为填充柄，利用此填充柄可以填充相邻单元格区域的内容。（2）单元格和区域引用 引用是指调用工作表中的数据用于计算分析，是通过标识工作表上的单元格或区域来实现的。通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区域的数据，或者在多个计算公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据。引用不同工作簿中的数据称为外部引用，引用其他程序中的数据称为远程引用。如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B 和行 10 这一单元格的数据，在计算公式中输入 B10，或者用鼠标点击该单元格。如果要引用某个区域的数据，可在计算公式中输入该区域左上角单元格的标识，后面跟一个冒号（：），后面再写上该区域右下角单元格的标识。例如，要引用从单元格 C5 到单元格 C15 这一区域，可输入 C5:C15；如要引用从单元格 A5 到单元格 B10 这一区域，即可输入 A5:B10。（3）绝对引用与相对引用的区别 当生成公式时，对单元格或区域的引用通常基于他们与公式单元格的相对位置。如在单元格 A8 中包含公式=8B5，Microsoft Excel 将在距单元格 A8 上面一个单元格和右面一个单元格处的单元格中查找数值，这就是相对引用。当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置相对应的其他单元格。如单元格 A8 中的公式被复制到单元格 A10 中，单元格 A10 中的公式已经改为=8B7，即从单元格 A8 上面和右面一个单元格处的单元格中查找数值。如果在复制公式时，不希望引用发生改变，可使用绝对引用。绝对引用的方式是在相对引用列标和行数字前加上美元符号，即B5。如在单元格 A8 中包含的公式=8B5，现在将公式复制到另一单元格中，则公式中的引用将不发生改变。四、作业 同学们可以根据教材中实例进行验证性练习。实验二 试验数据整理 一、目的要求 了解资料搜集整理的意义，更好地掌握次数分布表、图的制作方法，学会利用 Excel 进行数据整理与图表制作。二、材料用具 装有 Excel 软件的计算机及输出设备。三、方法步骤 1图表向导的利用 用 Excel 中的“图表向导”可以快速准确的绘制图表。它是一连串的对话框，依照它逐步建立或修改图表。新建图表需四个步骤，修改图表只需两个步骤。步骤 1：选择图表类型 选择“常用”工具栏中的“图表向导”按扭，图标或单击菜单栏中“插入”再击“图表”命令，激活图表向导对话框。Excel 提供了两大类的图形：标准类型和自定义类型，用户可根据需要选择。步骤 2：选定数据区域 如果在单击图表向导之前，就已选定数据范围，那么图表向导的第二对话框就会自动设定数据区域。如果事先没有设定，用户可自己设定。步骤 3：设定图表选项 完成步骤 2 之后，单击“下一步”按钮进入步骤 3。在步骤 3 里有很多的选项卡：“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表”。步骤 4：指定图表位置 当完成步骤 3 后，单击“下一步”按钮即可进入步骤 4。可以选定“作为新工作表插入”或“作为其中的对象插入”。然后单击“完成”按钮结束图表向导，完成了图表的制作。2“数据分析”工具栏运用 现以水稻品种 120 行产量（kg）为例，说明其方法与步骤。表 1 水稻品种 120 行产量（kg）表 177 215 197 97 159 245 119 119 131 152 167 104 161 214 125 175 188 192 176 175 95 199 116 165 214 95 158 83 80 138 151 187 126 134 206 137 98 97 129 143 174 159 165 136 108 141 148 168 163 176 102 194 173 75 130 149 150 155 111 158 131 189 91 142 152 154 163 123 205 155 131 209 183 97 119 181 187 131 215 111 186 150 155 197 116 254 239 160 179 151 198 124 179 184 168 169 173 181 188 211 175 122 151 171 166 143 190 213 192 231 163 159 177 147 194 227 169 124 159 159（1）打开 Excel，输入原始数据和各组的下限，样式见图 5，图中从 A1 单元格到 L10单元格的区域为原始数据，各组的组中值或下限值位于 M1 到 M11 单元格。（2）从“工具”菜单中选定“数据分析”命令，再选定“直方图”，确定后出现直方图对话框。（3）在输入区域中输入A1:L10，在接收区域中输入M1:M11，在输出选项中选定输出区域如A12 后，单击确定，得到频数分布表。若在“直方图”对话框中选定“图表输出”复选框，则直方图也同时输出，如图 6。注意：双击分类轴，在数据系列“选项中”将分类间距设为 0，才得直方图，不然只得到条形图。四、作业 1利用 Excel 将表中的数据进行排序并分组，然后编制次数分布表。2利用 Excel 将整理后的数据绘制直方图和折线图。图 5 原始数据示意图 图 6 直方图示意图 实验三 特征数计算 一、目的要求 掌握各种特征数的计算方法，学会利用 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 来计算，操作方法如下：在 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 头和 8 头以下的概率。按确定按钮后，在选定的单元格中就显示所需要的结果。如果需要改变刚才的计算内容，将光标移到该单元格上，此时在编辑栏中显示 BINOMDIST（8，10，0.65，FALSE），在此可以直接改变其中的内容。2正态分布概率的计算（1）一般正态分布概率的计算 例如：有一玉米果穗长度的正态总体，其平均数=20cm，标准差=3.4cm，试计算：果穗长度 x13cm 的概率。短果穗中，概率占 10%的临界果穗长度是多少？Excel 提供了正态分布概率计算的二个函数 NORMDIST 和 NORMINV，NORMDIST 用来计算正态分布的概率密度函数值和累积概率函数值，NORMINV 用来计算正态分布累积函数的逆函数。用 NORMDIST 函数计算上例中概率的步骤为：打开 NORMDIST 函数对话框，在 X图 7 二项分布概率结果对话框示意图 后的编辑框中输入 13，在 Mean 后的编辑框中输入 20，在 Standard_dev 后的编辑框中输入3.4，在 Cumulative 后的编辑框中输入 TRUE，然后按确定按钮，显示累积分布函数值为0.0197。如果在 Cumulative 后的编辑框中输入 FALSE，这时函数 NORMDIST 就返回概率密度函数值，结果显示 0.0141，即正态曲线在 x=13 时的纵高。用NORMINV 函数计算上例中 x 的步骤为：打开 NORMINV 函数对话框，在 Probability 后的编辑框中输入 0.1，在 Mean 后的编辑框中输入 20，在 Standard_dev 后的编辑框中输入 3.4，然后按确定按钮，显示区间点为 15.6，即果穗长在 15.6cm 以下的果穗占总体的 10%。（2）标准正态分布概率计算 Excel 提供了标准正态分布概率计算的二个函数 NORMSDIST 和 NORMSINV，图 8 正态分布概率结果对话框示意图 图 9 正态分布概率结果对话框示意图 NORMSDIST 用来计算正态分布的概率密度函数值和累积概率函数值，NORMSINV 用来计算正态分布累积函数的逆函数。其操作步骤与一般正态分布完全一样。四、作业 将教材中所讲例题做分析练习。某地 100 例 3040 岁健康男子血清总胆固醇(molL-1)测定结果如下：4.77 3.37 6.14 3.95 3.56 4.23 4.31 4.71 5.69 4.12 4.56 4.37 5.39 6.30 5.21 7.22 5.54 3.93 5.21 6.51 5.18 5.77 4.79 5.12 5.20 5.10 4.70 4.74 3.50 4.69 4.38 4.89 6.25 5.32 4.50 4.63 3.61 4.44 4.43 4.25 图 10 标准正态分布结果对话框示意图 图 11 标准正态分布结果对话框示意图 4.03 5.85 4.09 3.35 4.08 4.79 5.30 4.97 3.18 3.97 5.16 5.10 5.85 4.79 5.34 4.24 4.32 4.77 6.36 6.38 4.88 5.55 3.04 4.55 3.35 4.87 4.17 5.85 5.16 5.09 4.52 4.38 4.31 4.58 5.72 6.55 4.76 4.61 4.17 4.03 4.47 3.40 3.91 2.70 4.60 4.09 5.96 5.48 4.40 4.55 5.38 3.89 4.60 4.47 3.64 4.34 5.18 6.14 3.24 4.90 计算平均数、标准差和变异系数。