excel2000在农业科研与生产中的应用

excel2000在农业科研与生产中的应用

计算机在农业、科学和生产中的应用日益广泛。Excel2000是office2000办公软件的一个重要组件,它的界面友好,可操作性强,使用方便而被广大用户接受。Excel在科研中的应用已有论述,但在多元线性回归分析中的应用还未见报道。本文主要讨论Excel2000在多元线性回归分析中的应用,以期对广大的农业科研工作者有所帮助。1拉菜单使用“宏”函数启动Excel2000,查看“数据分析”和“粘贴函数”选项是否安装。如果安装,单击“工具”下拉菜单中的“加载宏”选项,在“加载宏”对话框加选取“分析工具库”和“分析数据库-VBA函数”,单击确定,“数据分析”和“粘贴函数”安装就绪,便可以使用了。2多元线性回归分析在excel2000中的实现2.1拉菜单站及菜单单单端在此以莫惠栋编:农业试验统计524页的数据为例,分析过程用逐步回归分析说明。(表略)原始数据输完后,就可以进行常量定义了。常量定义有两种方法:第一种:选取X1下的数据,单击菜单栏的“插入”,然后单击下拉菜单中的“名称”,再单击子菜单的“指定”,对数组X1进行常量定义,同样可分别对X2、X3、X4、Y进行常量定义。第二种:单击菜单栏的“插入”,然后单击下拉菜单中的“名称”,再单击子菜单中的“定义”对常量进行定义,也就是对每个变量进行赋值的过程。完成定义以后,就可以用生物统计学中惯用的符号进行统计分析。2.2算“”此步骤可以省略,在此仅简要说明如何套用公式计算一级数据,在第一例输入要计算的变量,如“∑X1”,在其后第二列输入“=sum(X1)”,即对X1进行求和,在第一列输入“∑X12”,其后第二列输入“sumsq(X1),即算出X1的平方和,这样可以算出所有的一级数据。数据计算出来,即用常量定义的第二种方法,对每一个变量进行赋值。2.3生成第二个数据集2.3.1直接使用公式计算方法和一级数据计算的方法一样。2.3.2计算离均差累积比如求“∑X22”,在其后第二列输入“=DEVSQ(X2”,即算出X2数据的离均差平方和;再如计算“∑X1X3”,在其后第二列输入“=COVAR(X1,X3)*COUNTA(X1)”,即可算出“∑X1X3”,因为COVER()是计算协方差的函数,COUNTA()是计算数据个数的函数,而COVER(X1,X3)只需乘以样本容量(当然样本容量要相等),即可算出X1、X3的离均差乘积和。同理可以计算出所有的二级数据。在二级数据的基础上构造矩阵A。2.3.3单性状拟合占击菜单栏中的“工具”,在下拉菜单中单击“数据分析”选项,选取“协方差”,单击“确定”,在“输入区域”中选取原始数据,单击“确定”,即在新工作表组中产生了协方差阵,因是一个对称阵,只列出下三角,补足上三角,所有元素乘以n(即样本容量),就得到一新的矩阵。矩阵主对角线上的元素分别是∑X12、∑X22、∑X32、∑X42、∑Y2,即不同性状的方差。主对角线两侧的是∑X1X2、∑X1X3、∑X1X4、∑X1Y等,即不同性状的离均差乘积和。只需复制前四行四列,即得到矩阵A。2.3.4逆阵向量的生成在矩阵A后,选取与A阵大小相同的区域,点击“粘贴函数”选取“MINVER”,在复选框中选取A阵的所有元素,然后敲击“CTRL+SHIFT+ENTER”,即得A阵的逆阵,在逆阵后输入∑X1Y、∑X2Y、∑X3Y、∑X4Y,构造一个与逆阵行数相同的列向量。选取“粘贴函数MMULT(即逆阵与列向量的乘积),即可分别计算出b1、b2、b3、b4。逆阵主对角线上的元素分别为C11、C22、C33、C44。b12/C11即得因X1的偏回归平方和,同理可算出其它偏回归平方和,把偏回归系数代入模型,即可得到四元回归平方和、离回归平方和。2.3.5偏回归a区利用上面的计算结果进行方差分析,并且f0.05、f0.01的值可由粘贴函数FINV()产生,不用查表。检验结果:因X4的偏回归不显著,剔除X4,即划去A阵的第四行第四列,产生新的矩阵,重复上述步骤,直到假设测验全部显著为止,把X1、X2、X3、Y的均值以及最后算出的b1、b2、b3代入模型即可算出a值,从而得到最优回归方程:Y123=-46.96636+2.013139x1+0.674644x2+7.830227x3,离回归标准差Sy/123=1.316949(克)。3利用ssr软件计算个计算3.1用Excel2000,不需计算机语言编程,即可实现复杂的统计分析,简便易学。3.2整个计算过程直观易懂,并可事先在Excel下建立一个更多变量的模型,并尽量用变量引用