Excel在生物统计中应用

1、生物统计实验指导书金卫斌主编Excel在生物统计中应用一、目的要求使学生掌握Excel在生物统计中的根本应用方法。二、材料用具练习纸、铅笔、装有 Excel软件的电脑及输出设备或计算器。三、方法步骤1 .启动 Excel2 .加载“分析工具库在默认的情况下，Excel是没有加载“分析工具库，无法进行统计分析。先查看电脑是 否加载“分析工具库。首先启动Excel,检查“工具菜单中是否有“数据分析命令。如 果没有发现“数据分析命令，就表示未加载“分析工具库。可按照以下步骤来加载“分析工具库。1选取“工具菜单中的“加载宏命令，弹出“加载宏的对话框。如莪宏PT ipx可用加我宏:宵分析工具库提供的 驱

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

3、差非配对资料的t检验一元线性回归与多元线性回归分析*计算多个变量两两之间的相关系数及协方差进行随机和顺序抽样4 .统计分析的粘贴函数Excel提供的统计分析粘贴函数如下：AVERAGE计算算术平均值*BINOMDIST计算二项式分布的概率值CHIDIST计算特定X分布的单尾概率值CHIINV计算一定单尾概率值时的X临界值CHITEST计算独立性检验的 X值CONFIDENCE计算总体平均值的置信区间CORREL计算两组数据的相关系数 *COVAR计算两组数据的协方差FDIST计算特定 F 分布的单尾概率值 *FINV计算一定概率时的临界 F 值 *FTEST计算二个样本方差之比 F 值的概率

4、*GEOMEAN计算几何平均数HARMEAN计算调和平均数INTERCEPT计算直线回归的截距 *MAX计算最大值 *MEDIAN计算一组给定数字的中位数 *MIN计算最小值 *MODE计算一组数据的众数 *NORMDIST计算正态分布的累积函数NORMINV计算正态分布累积函数的逆函数NORMSDIST计算标准正态分布的累积函数NORMSINV计算标准正态分布累积函数的逆函数POISSON计算泊松分布的概率SLOPE计算给定数据的直线回归系数 *STDEV计算样本标准差 *STDEVP计算样本总体的标准差TDIST计算学生氏-t分布的概率值*TINV计算特定概率时学生氏-t分布的临界t值*T

5、TEST计算t检验时的学生氏-t检验相关的概率VAR计算样本的方差 *VARP计算样本总体的方差ZTEST计算 Z 检验的双尾概率值上述带 *者就是我们要学习和掌握的内容5 Excel 的常用根本概念1工作薄和工作表Microsoft Excel 工作簿是计算和储存数据的文件， 每一个工作簿都可以包含多张工作表， 因此，可在单个文件中管理各种类型的相关信息。使用工作表可以对数据进行组织和分析，可以同时在多张工作表上输入和编辑数据，并 且可以对来自不同工作表的数据进行汇总计算。在创立图表之后，既可以将其置于原始数据 所在的工作表上，也可以放置在单独的工作表上。工作表的名称显示于工作簿窗口底部的工

6、作表标签上， 单击这个标签即可进入该工作表。 当前所在的工作表称为活开工作表，在它的标签上标有单下划线。可以在同一工作簿内或两 个工作簿之间对工作表进行改名、添加、删除、移动或复制等编辑操作。在默认情况下，每个工作薄由 3 个工作表组成，用户可以根据需要添加更多的工作表。 每个工作表由 256 列和 65 536 行组成。行和列相交形成单元格，它是存储数据的根本单位。 列用英文字母表示，开始是单个英文字母， 然后是2个英文字母组合，即由AIV共256列。 行用阿拉伯数字表示， 由165 536。每个单元格的定位可以通过该单元格所对应的行数和列 标来确定，如 B6 就表示 B 列第 6 行的单元

7、格。在活开工作表众多的单元格中，其中有一单 元格含有粗边框线，此单元格为活动单元格，在该单元格中可以输入和编辑数据。每进入一 个工作表时， A1 自动为活动单元格。 在活动单元格的右下角有一小黑方块， 这个黑方块称为 填充柄，利用此填充柄可以填充相邻单元格区域的内容。2单元格和区域引用引用是指调用工作表中的数据用于计算分析，是通过标识工作表上的单元格或区域来实 现的。通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区 域的数据，或者在多个计算公式中使用同一单元格的数值。还可以引用同一工作簿不同工作 表、不同工作簿、甚至其他应用程序中的数据。引用不同工作簿中的数据称为外部

8、引用，引 用其他程序中的数据称为远程引用。如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B和行10这一单元格的数据，在计算公式中输入B10，或者用鼠标点击该单元格。如果要引用某个区域的数据， 可在计算公式中输入该区域左上角单元格的标识， 后面跟一个冒号 ：， 后面再写上该区域右下角单元格的标识。例如，要引用从单元格 C5到单元格C15这一区域，可输入C5:C15;如要引用从单元格 A5到单元格B10这一区域，即可输入 A5: B10。3绝对引用与相对引用的区别 当生成公式时，对单元格或区域的引用通常基于他们与公式单元格的相对位置。如在单 元格A8中包含公式=8X

9、B5, Microsoft Excel将在距单元格 A8上面一个单元格和右面一个单 元格处的单元格中查找数值，这就是相对引用。当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置 相对应的其他单元格。如单元格 A8 中的公式被复制到单元格 A10 中，单元格 A10 中的公式 已经改为=8 X B7,即从单元格 A8上面和右面一个单元格处的单元格中查找数值。如果在复制公式时，不希望引用发生改变，可使用绝对引用。绝对引用的方式是在相对 引用列标和行数字前加上美元符号，即$B $ 5。如在单元格 A8中包含的公式=8 X$ B $ 5,现在将公式复制到另一单元格中，那么公式

10、中的引用将不发生改变。1试验数据整理1.图表向导的利用用Excel中的“图表向导可以快速准确的绘制图表。它是一连串的对话框，依照它逐 步建立或修改图表。新建图表需四个步骤，修改图表只需两个步骤。步骤1:选择图表类型选择“常用工具栏中的“图表向导按扭，如图3图标或单击菜单栏中“插入再击HI.'CEt-"导4图3图表按钮ll_D IT11ffl“图表命令，激活图表向导对话框，如图4。图4表类型示意图Excel提供了两大类的图形：标准类型和自定义类型，用户可根据需要选择。步骤2:选定数据区域如果在单击图表向导之前，就已选定数据范围，那么图表向导的第二对话框就会自动设定数据区域。如果

11、事先没有设定，用户可自己设定。步骤3:设定图表选项完成步骤2之后，单击“下一步按钮进入步骤3。在步骤3里有很多的选项卡：“标题、 “坐标轴、“网格线、“图例、“数据标志、“数据表。步骤4:指定图表位置当完成步骤3后，单击“下一步按钮即可进入步骤4。可以选定“作为新工作表插入或“作为其中的对象插入。然后单击“完成按钮结束图表向导，完成了图表的制作。2“数据分析工具栏运用现以水稻品种120行产量kg丨为例，说明其方法与步骤。表1水稻品种120行产量kg丨表177215197971592451191191311521671041612141251751881921761759519911616521

12、495158838013815118712613420613798971291431741591651361081411481681631761021941737513014915015511115813118991142152154163123205155131209183971191811871312151111861501551971162542391601791511981241791841681691731811882111751221511711661431902131922311631591771471942271691241591591翻开Excel，输入原始数据和各组的下限，

13、样式见图5，图中从A1单元格到L10ABCrEFGLhIJKLI |11772151979715924511厂11913115216710484)21612141251751761759519?11616510232149515S83SO13®151187126134206137120 493 _9712914317-4159165108141148啖1385IM17610219417375审刚15015515S156e131l涉PlL42S154163123205155131209na7183971191S118713121511118fi150155197192811025423

14、9160179151198124179184血169210917318118821117512215117116614319021322810m231163159177147W227IdP12415915924611264图5原始数据示意图单元格的区域为原始数据，各组的下限值位于M1到M11单元格。2从“工具菜单中选定“数据分析命令，再选定“直方图，确定后出现直方图对话框。3在输入区域中输入$ A $ 1: $ L $ 10,在接收区域中输入$ M $ 1: $ M $ 11,在输 出选项中选定输出区域如$ A $ 12后，单击确定，得到频数分布表。假设在“直方图对话框中选定“图表输出复选框，

15、那么直方图也同时输出，如图6。接做频率3102?120913S16156IB17424192222109220724632641其他0.I直方图学习文档仅供参考I/- £图6直方图示意图2. 特征数计算在生物统计分析中主要计算平均数和变异数。平均数主要有算术平均数、几何平均数及 中数，变异数主要有极差、方差、标准差、变异系数。1算术平均数用 AVERAGE 函数可用来计算算术平均数，例如一组数据2，3， 4，5，6 的平均数可在单元格中键入“ =AVERAGE 2，3，4，5，6得 4。如果这 5个数据分别存放于 A1 到 A5 的字段中，那么键入“ = AVERAGE A1 : A

16、5 得 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 求得最小值， 两者的差数就是极差。

17、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 本身没有提供函数计算变异系数,我们可

18、以 通过 STDEV 计算样本标准差,用 AVERAGE 计算平均数,两者相除即得变异系数。3. 统计假设测验1 .在Excel工作表中输入数据EC1A法B袪21£1432315432D27542120C51025761312S715219862010910151110910图7原始数据示意图2 选择计算命令这是一个配对设计的两样本平均数显著性测验。选择“工具菜单的“数据分析命令， 在弹出的“数据分析对话框中，双击“t-检验：平均值的成对二样本分析选项，那么弹出“ t-检验：平均值的成对二样本分析对话框。图8 t-检验：平均值成对二样本分析对话框示意图3 .填写区域分别填写变量1的区

19、域：$B$1:$B$11,变量2的区域：$C$1:$C$11，由于我们进行的是等均值的检验，填写假设平均差为0,由于数据的首行包括标志项，所以选择“标志复选框,再填写显著水平a为0.05，如图13所示，然后点击“确定按扭，那么可以得到以下列图所示的结 果。A吕C1t书验:成对双样丰均喳分析fl斗3A?云：去411.518,5533. Will.11124. 2777777861010Firi皓松相并系数0.18262613g假设平均羞ngdf910t St at-4. 02656619iip仃Qt单尾0.00149418712t甲尾临界1.8331129231.3pa<=t裁足0. 00

20、298837514T取星临黒学电文档1仅供参图9 t-检验结果对话框示意图4 结果分析如图14所示，表中分别给出了两组两种处理方法病痕数的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值，DF是假设检验的自由度，t统计量是两个样本差值减去假设平均差之后再除于标准误差的结果，“P(T < t)单尾是单尾检验的显著水平，“t单尾临界是单尾检验t的临界值，“P(TWt)双尾是双尾检验的显著水平，“t双尾临界是双尾检验t的临界值。由图表的结果可以看出 t统计量均大于两个临界值，所以，在5%显著水平下，A、B两种处理方法对纯化病毒的效应差异显著。对于非配对数据，可选择“工具菜单的“

21、数据分析命令，在弹出的“数据分析对话框中，双击“ t-检验：平均值的成组二样本分析选项，那么弹出“t-检验：平均值的成组二样本分析对话框。选择数据后得到分析结果。5.作业题：5.9,5.22.4. 方差分析一以A、B、C、D4种药剂处理水稻种子，其中 察值cm，得结果如表2，试进行方差分析。A为对照，每处理各得cm4个苗咼观表2不同药剂处理的水稻苗高药剂ABCD19212022观察值2324182521271927132015221.将资料1表中的数据复制到 Excel表中，其输入格式如图15。图10原始数据示意图2 在“工具菜单项选择择“数据分析命令，弹出“分析工具对话框，选中列表中“方差分

22、析：单因素方差分析选项，单击“确定按钮，在接着弹出的对话框中输入数 据范围$B$2:$E$5，a值设为0.05或0.01。3 单击“确定按钮，得方差分析表，如图16:11V2方差分析 差异源SSdfFF crit13组间104334. 666673. 5254240. 048713 3. 490295114组内118129 8333331516总计2221&图11资料1的方差分析示意图F>F，不同药剂处理苗高差异到达显著水平。二用生长素处理豌豆，共 6个处理。豌豆种子发芽后，分别在每一箱中移植 4株, 每组6个木箱，每箱1个处理。试验共有4组24箱，试验时按组排于温室中， 使同组

23、各项环 境一致，然后记录各箱第一朵花时 4株豌豆的总节间数，结果如表 3，试作方差分析。表3不同生长素处理豌豆的试验结果区组处理InmIV对照未处理60626160赤霉素65656865动力精63616160吲哚乙酸64676361硫酸腺嘌吟62656264马来酸616262651.将资料表3中数据资料复制到Excel中，输入格式如图17。AB |CDE -4II【11n 2对赠、未处 理6062百13赤霉素65裁豳654动力蒂636161605专乙霰64翻6361 |6碇醸眼惑626264 j7马来酪616265 P图12原始数据示意图2 在“工具菜单中选择“数据分析命令，弹出“分析工具对话

24、框，选中“方差 分析：无重复双因素方差分析选项，单击“确定按钮，在接着弹出的对话框中输入数据范围 $B$2:$E$7, a 值设为 0.05 或 0.01。3 单击“确定按钮，得方差分析表，如图18:17方差分析LS19SSdfMSFF-valueF crLt!L 65.875513.1754. 5649660. 0099184. 555614列5-45833331. 8194440. 6304140. 6066225. 416965214乱 29167152.88S1112223总计114.62523nA图13资料2的方差分析示意图在变异来源栏目：行代表不同处理，列代表不同区组。分析结果F&

25、gt;F，不同处理间差异到达极显著。三施用 Ai、A2、A33种肥料于Bi、B2、B33种土壤，以小麦为指示作物，每处理组 合种3盆，得产量结果g于表4。试作方差分析。表4种肥料施入3种土壤的小麦产量g土壤种类肥料种类盆Bi油砂B2二合B3白僵1A1231A2231A3231 .将资料表4中数据资料复制到 Excel中，输入格式如图19。ABC|D1油砂？Bi 二合 >心白倡2囲，19.617.6321.218.34P 20.1 :16.417!512313.3614213.?147111匕13.9812.814.212g138 13.614£1013.71J314 图14原始

26、数据示意图 学习文档仅供参考2 在“工具菜单中选择“数据分析命令，在弹出“分析工具对话框中选中“方 差分析：可重复双因素方差分析命令，单击“确定按钮，在接着弹出的对话框中输入数 据范围$A$1:$D$10, a值设为0.05或0.01。3 单击“确定按钮，得方差分析表，如图20：29方差分析30差异源SSdfMSFP-valueF cri t31样本179. 38072的.603796.672362. 36E-10& 01290532列3. 9607412L980372.1345310. 147277& 012905】33交互19. 2414844. S1U375.184830

27、. 0058734. 57903634内部16.7180.9277783536总计 219. 2832G图15资料3方差分析示意图在变异来源栏里，样本代表肥类因素，列代表土类，交互代表肥类X土类的互作，内部 代表误差。F测验结果说明：肥类间和肥类X土类的互作的效应到达极显著。多重比拟请参考教材。同学们可自行进行比拟。四、作业 8.2,9.1 , 9.125. 直线相关回归某水稻研究所，进行水稻品种生育期与产量试验，其局部品种生育期与产量结果列于F表，试作回归相关分析。生育期为x,单位：天；产量为 y,单位：kg/667m2。表7水稻品种生育期与产量试验结果表品种编号12345678生育期 x天

28、112114116119123128129131产量 y kg/667m23713824154414384614794741 输入数据。将上述资料录入某一Excel空白工作簿中。图16始数据示意图2 数据分析计算单击“工具菜单中的“数据分析命令，弹出数据分析对话框，在该对话框“分析 工具列表中选中“回归选项，单击“确定按钮，在随后弹出的“回归对话框中,输入选项区中“ X值输入区域输入框中输入数据范围为$B$2:$B$9，在“ Y值输入区域输入框中输入数据范围为$C$2:$C$9 ;并在“数据区域中选择置信度设为95%或99% ；在输出选项区中选择，如在本工作表中输出结果那么选择“输出区域，如在

29、其他工作表中图17数据分析对话框示意图厂标蟲直1置涪度世-當数为雷JC X确疋 |取消|帮助|输出逵明席揃出区城辺.新工惟哀组CE: 新工栏薄迥F舷 r标准残差©mt25J厂強差图 r舞性1以合関正态分布厂正态阳寧国QD输出结果那么选择“新工作表组，如在其他工作簿中输出结果那么选择“新工作簿，本例中 选择在本工作表中输出结果；在“残差选项区选定“残差选项，然后单击“确定按 钮即弹出汇总结果。图18回归对话框示意图12 SUMARY OUTPITT13IE1713_192021222324252627餡2930311415Jtul-tiplc 5? E SquaTrAdjured M 存建误差(X 954659E&萨1n 91157545(X甜&啊4旳了12.99?92693Fl方