excel2000在農(nóng)業(yè)科研與生產(chǎn)中的應(yīng)用

excel2000在農(nóng)業(yè)科研與生產(chǎn)中的應(yīng)用

計算機(jī)在農(nóng)業(yè)、科學(xué)和生產(chǎn)中的應(yīng)用日益廣泛。Excel2000是office2000辦公軟件的一個重要組件,它的界面友好,可操作性強(qiáng),使用方便而被廣大用戶接受。Excel在科研中的應(yīng)用已有論述,但在多元線性回歸分析中的應(yīng)用還未見報道。本文主要討論Excel2000在多元線性回歸分析中的應(yīng)用,以期對廣大的農(nóng)業(yè)科研工作者有所幫助。1拉菜單使用“宏”函數(shù)啟動Excel2000,查看“數(shù)據(jù)分析”和“粘貼函數(shù)”選項是否安裝。如果安裝,單擊“工具”下拉菜單中的“加載宏”選項,在“加載宏”對話框加選取“分析工具庫”和“分析數(shù)據(jù)庫-VBA函數(shù)”,單擊確定,“數(shù)據(jù)分析”和“粘貼函數(shù)”安裝就緒,便可以使用了。2多元線性回歸分析在excel2000中的實現(xiàn)2.1拉菜單站及菜單單單端在此以莫惠棟編:農(nóng)業(yè)試驗統(tǒng)計524頁的數(shù)據(jù)為例,分析過程用逐步回歸分析說明。(表略)原始數(shù)據(jù)輸完后,就可以進(jìn)行常量定義了。常量定義有兩種方法:第一種:選取X1下的數(shù)據(jù),單擊菜單欄的“插入”,然后單擊下拉菜單中的“名稱”,再單擊子菜單的“指定”,對數(shù)組X1進(jìn)行常量定義,同樣可分別對X2、X3、X4、Y進(jìn)行常量定義。第二種:單擊菜單欄的“插入”,然后單擊下拉菜單中的“名稱”,再單擊子菜單中的“定義”對常量進(jìn)行定義,也就是對每個變量進(jìn)行賦值的過程。完成定義以后,就可以用生物統(tǒng)計學(xué)中慣用的符號進(jìn)行統(tǒng)計分析。2.2算“”此步驟可以省略,在此僅簡要說明如何套用公式計算一級數(shù)據(jù),在第一例輸入要計算的變量,如“∑X1”,在其后第二列輸入“=sum(X1)”,即對X1進(jìn)行求和,在第一列輸入“∑X12”,其后第二列輸入“sumsq(X1),即算出X1的平方和,這樣可以算出所有的一級數(shù)據(jù)。數(shù)據(jù)計算出來,即用常量定義的第二種方法,對每一個變量進(jìn)行賦值。2.3生成第二個數(shù)據(jù)集2.3.1直接使用公式計算方法和一級數(shù)據(jù)計算的方法一樣。2.3.2計算離均差累積比如求“∑X22”,在其后第二列輸入“=DEVSQ(X2”,即算出X2數(shù)據(jù)的離均差平方和;再如計算“∑X1X3”,在其后第二列輸入“=COVAR(X1,X3)*COUNTA(X1)”,即可算出“∑X1X3”,因為COVER()是計算協(xié)方差的函數(shù),COUNTA()是計算數(shù)據(jù)個數(shù)的函數(shù),而COVER(X1,X3)只需乘以樣本容量(當(dāng)然樣本容量要相等),即可算出X1、X3的離均差乘積和。同理可以計算出所有的二級數(shù)據(jù)。在二級數(shù)據(jù)的基礎(chǔ)上構(gòu)造矩陣A。2.3.3單性狀擬合占擊菜單欄中的“工具”,在下拉菜單中單擊“數(shù)據(jù)分析”選項,選取“協(xié)方差”,單擊“確定”,在“輸入?yún)^(qū)域”中選取原始數(shù)據(jù),單擊“確定”,即在新工作表組中產(chǎn)生了協(xié)方差陣,因是一個對稱陣,只列出下三角,補(bǔ)足上三角,所有元素乘以n(即樣本容量),就得到一新的矩陣。矩陣主對角線上的元素分別是∑X12、∑X22、∑X32、∑X42、∑Y2,即不同性狀的方差。主對角線兩側(cè)的是∑X1X2、∑X1X3、∑X1X4、∑X1Y等,即不同性狀的離均差乘積和。只需復(fù)制前四行四列,即得到矩陣A。2.3.4逆陣向量的生成在矩陣A后,選取與A陣大小相同的區(qū)域,點擊“粘貼函數(shù)”選取“MINVER”,在復(fù)選框中選取A陣的所有元素,然后敲擊“CTRL+SHIFT+ENTER”,即得A陣的逆陣,在逆陣后輸入∑X1Y、∑X2Y、∑X3Y、∑X4Y,構(gòu)造一個與逆陣行數(shù)相同的列向量。選取“粘貼函數(shù)MMULT(即逆陣與列向量的乘積),即可分別計算出b1、b2、b3、b4。逆陣主對角線上的元素分別為C11、C22、C33、C44。b12/C11即得因X1的偏回歸平方和,同理可算出其它偏回歸平方和,把偏回歸系數(shù)代入模型,即可得到四元回歸平方和、離回歸平方和。2.3.5偏回歸a區(qū)利用上面的計算結(jié)果進(jìn)行方差分析,并且f0.05、f0.01的值可由粘貼函數(shù)FINV()產(chǎn)生,不用查表。檢驗結(jié)果:因X4的偏回歸不顯著,剔除X4,即劃去A陣的第四行第四列,產(chǎn)生新的矩陣,重復(fù)上述步驟,直到假設(shè)測驗全部顯著為止,把X1、X2、X3、Y的均值以及最后算出的b1、b2、b3代入模型即可算出a值,從而得到最優(yōu)回歸方程:Y123=-46.96636+2.013139x1+0.674644x2+7.830227x3,離回歸標(biāo)準(zhǔn)差Sy/123=1.316949(克)。3利用ssr軟件計算個計算3.1用Excel2000,不需計算機(jī)語言編程,即可實現(xiàn)復(fù)雜的統(tǒng)計分析,簡便易學(xué)。3.2整個計算過程直觀易懂,并可事先在Excel下建立一個更多變量的模型,并盡量用變量引用