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

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

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