Excel函数公式在区域试验中的应用

Excel函数公式在品种区域试验中的应用黑龙江省泰来县瑞丰种业有限责任公司孟九我们在品种区域试验中，经常会用到excel，诸如数据录入，数据整理，数据计算及统计分析，对分析结果进行排名等。一、数据录入，在品种区域试验中，我们常用到的excel数据类型有常规、数值、文本、日期等。数据录入前，我们要对excel表中所需单元格的数据类型进行设置。如图，我们需要对图1中的C列、I列设置为日期函数，以便计算生育日数，生育日数＝“成熟期－出苗期+1”，如在J5单元格内输入公式“＝i5-c5+1”，就可以轻松算出品种2Q01的生育日数为120天。方法，选择C列、I列，点右键－设置单元格格式－数字－日期（如3-14）；对AQ、AR列等设置成数值格式，并保留1位（AP列）或2位（AQ列）小数；对AJ列（穗行数）一定要设置成文本格式，否则遇到12-14行的品种，输入12-14，会错误成12月14日、如图AV19单元格所示。图1二、数据整理，品种区域试验一般采用三、四次重复，各重复采用随机排列，数据录入工作完成后需对数据进行规纳整理，便于计算和汇总。如图3是第二重复的数据，需要排列成如图4按品种区号的大小顺序排列，方法1：选定excel表列18—列31区域，点击排序和筛选－升序；方法2：选定excel表A18—A31单元格区域，点击排序和筛选－升序－扩展选定区域－排序。有时还需将图2的整个试验数据排列成如图5（方法同上，在品种间插入空行就可以得到）按品种、按重复的形式，以便进行平均数及求和计算。注意方法2的数据区域必须连续，区域外数据可能选择不上，不能随同排序。图2图3图4图5三、Excel公式、数组公式的输入、复制与粘贴。图6如图为品种区域试验报表的试验产量原始数据表，图中H6单元格为品种2Q01在第一重复中的小区产量（14%标准水），小区产量＝10穗粒重*（100-籽粒含水量/86*小区鲜重/10穗鲜重），因此我们要在H6单元格输入“＝F6*（100-G6）/86*C6/D6”，enter回车键结束，得到结果34.7。复制H6单元格，选定H7：H20、O6：O20、V6：V20单元格区域，右键粘贴，得到2Q02到2Q15所有品种的小区产量，也可以复制H6单元格，在O6、V6单元格内粘贴，并向下填充，可达到相同效果。输入数组公式，需用组合键“ctrl+shift+enter”结束。四、Excel函数在品种区域试验统计分析中应用。图7如图7的产量数据可以这样录入：1、选定区域试验产量结果表B4：B18单元格；2、输入等号“＝”；3、选定试验产量原始数据表，选择所需数据区域H6：H20；4，同时按下“ctrl+shift+enter”组合键结束。利用excel函数SUM、AVERAGE、DEVSQ计算各品种的小区产量、小区平均产量、SS、MS、F等值。例在E4单元格输入公式“=SUM(B4:D4)”，在F4单元格内输入公式“=AVERAGE(B4:D4)”，在G4单元格内输入公式“=F4/32.5*10000”，其中32.5为小区面积，选定H4：H18单元格，输入公式“=G4:G18/G11*100-100”，G11为对照品种产量，完成对区域试验产量结果表的计算。图8在图8的相应单元格内输入公式，就可以得到如图7的统计分析结果。即C26单元格中输入公式“=DEVSQ(B19:D19)/15”，15为试验品种数；C27单元格中输入公式“=DEVSQ(E4:E18)/3”，3为试验重复次数；C28单元格中输入公式“=C29-C26-C27”，C29单元格中输入公式“=DEVSQ(B4:D18)”，D26单元格中输入公式“=C26/B26”，D27单元格中输入公式“=C27/B27”，D28单元格中输入公式“=C28/B28”，E26单元格中输入公式“=D26/D28”，E27单元格中输入公式“D27/D28”。其中区组间DF（自由度）为重复数－1，材料间DF为材料数－1，总变异为区组间DF*材料间DF，误差自由度为总变异DF－区组间DF－材料间DF或区组间DF*材料间DF。图9如图9，新复极差测验的最小显著差异表中LSR值计算，选定表中B5：L6单元格，输入公式“=B3:L4*SQRT(区域试验产量结果表!D28/3)”，同时按下“ctrl+shift+enter”组合键进行计算，其中SSR值由查表所得，D28为图8中误差MS值，3为重复次数。下表为三次重复，试验材料个数为4—16（含对照）的Duncan’s新复极差检验SSR值表，此表的数据由百度文库下载，并进行了删减。五、产量差异比较。图10区域试验材料的产量平均数差异显著性用字母进行标记。计算各材料平均数差值，与图9的LSR值进行比较。差异显著性5%（C列）用小写字母表示，两材料间无相同字母，则表明这两个材料存在显著差异；1%（D列）用大写字母表示，两材料间无相同字母，则表明这两个材料存在极显著差异。具体方法：1、将所有试验材料按小区平均产量从大到小顺序排列，在最大的平均数后面标上a。2、将标有a的最大平均数与下面的平均数逐个进行比较，凡差异不显著的都标a，直到出现差异显著的改变字母标记b。图10，B4与B5单元格的差值为0.13，此时P值为2，LSR0.05的值为3.34，0.13小于3.34，所以在B5单元格继续标a，往下逐个继续到B10单元格，B4与B10单元格差值为3.53，此时P值为7，LSR0.05值为3.80，3.53小于3.80，还继续标a，到B11单元格，B4与B11两单元格差值为4.13，此时P值为8，LSR0.05为3.83，4.13大于3.83，所以要变换字母为b。3、将改变字母标有b的平均数与上方的平均数逐个比较，凡差异不显著的都标b，直到出现差异显著的不再标记。图10，从改变字母的B11单元格往上逐个比较并标记，直到B6单元格，B11与B5单元格的差值为4，大于3.80，存在显著差异，应停止标记。4、从上方标有字母B的的平均数开始，重复2、3两个步骤，直到所有平均数都进行了标记。图10，从B5单元格开始，重复2、3过程，直到完成。六、根据试验结果对各试验材料进行排名，RUNK函数的应用。图1，AU列顺位需按试验材料的小区产量、公顷产量、比对照增减产进行排列（结果一样），我们与比对照增减产百分比为例，方法1、在AU5单元格内输入公式“=RANK(AR5,$AR$5:$AR$19)”，并向下填充，就可得到各品种的排名结果，AR5:AR19、$AR$5:$AR$19、AR$5:AR$19、$AR5:$AR19的输入可简单的用F4键切换，其中AR5:AR19为相对引用，$AR$5:$AR$19为绝对引用。方法2、选定AU5：AU19单元格，输入公式“=RANK(AR5:AR19,AR5:AR19)”，同时按下组合键“ctrl+shift+enter”结束，排名自动完成。图11为预备试验的产量原始数据表。如图，选定J5：J10单元格，输入公式“=I5:I10/((I4+I11)/2)*100-100”，计算2Y001到2Y006相对于对照2YCK01、2YCK02的增减产百分数。在K4单元格内输入公式“=IF(ISNUMBER(J4),RANK(J4,$J$4:$J$77,),)”，复制K4单元格，并向下粘贴到K77单元格，则所有参与试验的材料顺位自动生成。公式说明：如果J4单元格内为数字，则利用RANK函数进行排名，J4单元格内数值在J4：J77数据区域内排名，否则，K4单元格结果为空，如J5单元格内数据27.90在J4：J77数据区域内排名第16，则自动在K5单元格生成数字16。