秩次分析法的ExcelVBA程序設計_圖文

1、劉紅艷,趙應忠,楊敏敏 (中國農業(yè)科學院油料作物研究所.湖北武漢430062摘要以Excel電子表格為平臺,利用內嵌的VBA語言編寫了一段用于秩次分析的宏代碼。該程序具有單因素方差分析的功能,并可 計算出平均數(shù)分級值H.、秩次值H:和環(huán)境區(qū)分指教Y。等關鍵參數(shù),大大提高數(shù)據(jù)處理效率。關鍵詞秩次分析法;VBA;宏代碼中圖分類號S126文獻標識碼A 文章編號05176611(2009351755403programming of Rank Analysis Method with Excel VBALIU Hongyah et ai (Oil Crops Institute,Chinese Aca

2、demy of A酊euhural Sciences,Wuhan,Hubei 430062Abstract With ExceI鸛the platform.a flec“on of n'mcro code waB compiled with embedded VBA language.This program had tIle funetion ofofleway 8a'IOV8and the grade value of average(HI,rank value(H2and envimmnental index(YMetc key parameters cou/d be c

3、aleulat ed州th it.And the efliciency of data processing could be greatly improved.Key words Rank analysis method:VBA;Macro code秩次分析法是由金文林等提出的一種新的非參數(shù)統(tǒng)計方法¨J,主要應用于作物區(qū)域試驗中產量性狀等數(shù)據(jù)資料的分析評價。該方法以品種的小區(qū)產量為出發(fā)點,通過差異顯著性多重比較,首先計算出平均數(shù)分級值H。,然后將其換算為秩次值H2,進而求出環(huán)境區(qū)分指數(shù)Y。,最后對品種的秩次值均值進行多重比較分析。秩次分析法可以處理方差不同質、數(shù)據(jù)非均衡的區(qū)試資料,

4、因而比常規(guī)的聯(lián)合方差分析法具有更大的靈活性。該方法已經在小豆¨o、苦養(yǎng)p1、冬小麥、玉米61、花生71、大豆83和春大白裂91、芝麻等作物區(qū)試資料分析中得到了廣泛應用。秩次分析法的統(tǒng)計學原理并不復雜,但計算過程較繁瑣。常見的統(tǒng)計學軟件如sPss、SAS和DPs等都沒有提供相應的統(tǒng)計模塊,手工計算不但工作量大,而且容易出錯,這限制了該方法的應用。因此,開發(fā)相關的統(tǒng)計分析軟件顯得十分必要。筆者以廣泛使用的Microsoft Excel電子表格為基礎,利用軟件內嵌的VBA語言(Visual Basic for Application進行二次編程開發(fā)¨”,編寫了一段可以直接求算H,

5、、H2和YM等關鍵參數(shù)的宏代碼,大大提高了數(shù)據(jù)分析的效率。1秩次分析法的關鍵參數(shù)及其算法秩次分析法的統(tǒng)計學原理見金文林等的報道¨1,主要計算步驟包括:對區(qū)試資料進行單向方差分析;求算品種的平均數(shù)分級值礬;根據(jù)q值求算品種的秩次值衛(wèi):根據(jù)也值計算環(huán)境區(qū)分指數(shù)%;計算品種在有效試驗點下的平均秩次值,并以此為依據(jù)進行多重比較和顯著性測驗。I由以上分析可知,秩次分析法涉及到3個關鍵參數(shù),下面給出其定義及算法。(1平均數(shù)分級值日:分級值日。肼是指在第M環(huán)境下,平均數(shù)顯著低于i品種平均數(shù)的供試品種數(shù)目,該值越大,說明i品種在第肘環(huán)境條件下的優(yōu)勢越大。其計算公式為:H-M/=XbM#(i,.=1,

6、2,口(當天0一賈0<坳舶時,基金項目 作者簡介 收稿日期農業(yè)部油料作物遺傳改良重點開放實驗室開放課題(200604資助;國家“863”項目(2001AA241153。劉紅艷(1975一,女,湖北南漳人.碩士,助理研究員,從事芝麻育種工作。2009-08.14b枷=o;當瓦一I_SD時,6刪=1。其中,L妒舶=廄×k式中,霹為某個環(huán)境點下的試驗誤差均方;k為試驗重復次 數(shù);。為具有誤差項自由度下兩尾概率的臨界值。(2秩次值呸M:是指在第肘環(huán)境條件下,i品種在所有 n個品種中平均值的排序名次,是反映品種在各環(huán)境條件下 優(yōu)勢強弱的一個指標,將分級值以。排序后求得:將每個環(huán) 境條件下

7、的分級值風脅從大到小排列,設喝。值最大,則令i 品種的秩次值%M為1;設H。肺值最小,則令,品種的秩次值 月為n,其余類推,排列出各品種的秩次值。若從第”個品 種起共有W個品種的分級值相同,那么這加個品種的秩次值 均定為”+(W一1/2。最后,計算出各品種在有效環(huán)境點下 的平均秩次值(鞏。(3環(huán)境區(qū)分指數(shù)(%:是指第肘環(huán)境下區(qū)分品種差異 能力的一個統(tǒng)計數(shù),其計算公式如下:y0=(暖脅一C/P x 100%(i=I,2,口式中,C=t,(口+1(2v+1/6;C7=口(口+12/4;P=CC;t, 為某個環(huán)境點下的供試品種數(shù)。k值越小說明該環(huán)境對品 種的差異區(qū)分能力越弱,%值越大說明環(huán)境對品種的

8、區(qū)分 能力越強。2秩次分析法的程序設計設計程序時,依據(jù)前面提到的秩次分析法的步驟,逐段 編寫代碼。首先設計一段宏代碼用于單向方差分析,檢查單 個試驗點產量資料的顯著性,并將方差分析結果輸出Excel 表格中。根據(jù)方差分析結果,采用條件語句進行判斷,若顯 著則繼續(xù)運算分析,不顯著則停止計算,退出程序。其次。設 計另外一段宏代碼計算品種的平均數(shù)及其H。值,輸出到Ex. cel表格的指定位置。最后,設計宏代碼,將H.值換算為秩 次值H2,并計算環(huán)境區(qū)分指數(shù)。為了降低編程難度,可先將 品種按平均數(shù)大小降序排列,然后計算參數(shù)H.、H2和Y。,最 后將品種順序復原。3秩次分析法宏代碼的使用方法打開帶有“秩

9、次分析法”宏代碼的Excel文件,在當前工 作表的第一個單元格開始輸入原始數(shù)據(jù),注意行為品種數(shù) (品種數(shù)不少于2個,列為重復數(shù)(重復數(shù)也不少于2個。 也可從其他工作表中直接將小區(qū)產量數(shù)據(jù)“復制”、“粘貼”37卷35期 劉紅艷等秩次分析法的Excel VBA程序設計 17555過來。檢查數(shù)據(jù)格式無誤且無數(shù)據(jù)缺失情況后即可運行宏。運行宏的方法有2種,第1種方法是從Microsoft Excel 菜單中順序選擇:工具I宏I宏即可打開宏的列表,找到宏名 稱“秩次分析法”,點擊運行;第2種方法更加快捷的方式是在工作表中創(chuàng)建一個命令按鈕(圖1,通過右鍵菜單將其指 向為上面的宏后直接點擊命令按鈕運行,十分方

10、便。運行宏后,立即給出一個方蒡分析結果列表(圖1,EK列,若方差顯著,其余3個關鍵參數(shù)H。、H:和Y。也同時被求出(圖l,MQ列;若方差不顯著,則程序停止計算,并彈 出一個“方差分析不顯著,終止計算”的消息提示框。將需要 的結果導出保存到其他合適的地方后輸入新的數(shù)據(jù),又可重 新運行。這樣,很多試驗點的數(shù)據(jù)均可很快完成計算,效率 大大提高,所得數(shù)據(jù)可進行下一步的多重比較和顯著性 檢驗. 圈l 原始數(shù)據(jù)輸入格式(A、B、C列及運行結果(EQ列 .1Raw dammpm format IACtollandrimingresultslEQ伽l哪4CodeFinder程序的源代碼該程序的代碼比較簡單,可

11、以向作者直接索取或自己在Excel的“Visual Basic編輯器”中輸入。注意以逗號開頭的 語句為注釋語句,僅方便讀者理解,可以不輸入,不影響程序 正常運行(為節(jié)省篇幅,有關程序容錯、輸出格式設置的語句 已省略。該程序在Excel 2003中通過調試。程序開始at Sub秩次分析法(Dim Row As Illteg日,Col AsInteger,i A8Integer,j As IntegerDim m AsInteger,nAsInteger,b AsInteger Dim clAs Integer,e2As Integer,c3AsInteger,aqHAsDoubhDimtemp_

12、arry(As DoubleDim myDataAsRangeDimmyReslt舡姆選定數(shù)據(jù)區(qū)域IfCells(1,1=”ThenMgt30x(”請從第一單元格開始輸入數(shù)據(jù),行為品種數(shù),列為重復 數(shù)”ExitSubEndSetmyData=SelectionAetiveSheet.Range(Cells(1,COl+1,Cells(9999,255.Clear-Contents制作結果輸出表頭cdle(1,COl 4-2=“方差分析“ Colts(2.Col+2=”差異源” Cell5(3,Cot+2=”組間” Cells(4,COl 4-2=”組內”Cells(5.COl+2=”總計”Co

13、ils(2.Col+3=”ss” CeUs(2.Col+4=”df'。Cells(2。Col 4-5=”MS”CeUs(2,Col+6=FCells(2.COl4-7=”P.value”CeHs(2。Col+8=”LSD(0.05” Cells(1。Col 4-10=”品種序號” Cells(1,Col+11=”均值”Cells(1,Col+12=”Hl” Cells(1,Col 4-13=”H2” Ceu8(1,Col+14=”Y_”7計算組間Ss,df,MSReDim temp_anT(1ToRwFor i=lToRowcells(i+l。COl 4-10=iNextiarryCo

14、l17556安徽農業(yè)科學 2009年Celh(3,Col+4=Row1Cells(3,Col+5=Cells(3,Col+3/Ceth(3,COl+4計算組內SS;df,MSFor i=l To RowNextiCells(4,Col+4=(Col一1RowCoils(4,Col+5=Cells(4,COl+3/Celh(4,Col 4-47計算總變異sS,df,MSCells(5,Col+4=Col。Row1Coils(5,Col+5=Celh(5,Col+3/Cells(5.Col+4計算F,P.Value,LSDCol+6。Cells(3,Col+4,CeUs(4,Col +4Cells

15、(3.COl+8.Formula=”=s;a(2”&Cells(4,COl+5&/&Col&”FInv(0.05,”&Cells(3,Col+4&“,”&Ceils(4。 C0丑+4&”7判斷是否作迸一步分析IfCegs(3。Col+7>0.05ThenMsgBox(“方差不顯著。停止運算”Exit SubElseEnd If均值排序myRe8lt.Sort Kcyl:=Range(Cells(2,C01+11,Cells(Row+1,Col+ 11。Orderl:2dDescending。Header:=xlNo,Orde

16、rCustom:=I,MatehCase: =False,Orientation:=xlTopToBottom,SortMethod:。xlPinYin,DataOptienl:=xl SortNormal計算HlFor i=l To Rowm=0Forj=i+l To RowIf Pd】s(CeUs(i+1。Col+11.Cells(j+1,Col+11 >=Cells(3.Col 4-8Thenb=1Elseb=0EIld Ifm=b+m 計算H2For i=l To Rowi=m+iIfi>Row ThenGoTo Y-vWue:Endm=0Forj=i+1To RowIfC

17、ells(i+1,Col+12=Ceus(j+1,Col+12Then b=lElseb=0EndIfm=b+m求H1值相等的個數(shù)NextjFor 11=0ToCells(i+n+1,COl+13春i+m/28qH=8qH+(i+m/2(i+m/2NextllNexti計算YMY value:cl=Row。(Bow+1(2Row+1/6c2=Row。(Bow+1(Bow+I/4c3:c1一以Ceils(2,Col+14=(sqHc2100/c3排序復原myReslt.Sort Keyl:-姆(CellB(2,COl+10。Cells(Row+l, Col+10,Orderl:=xlAscend

18、ing。Header:=xlNo,OrderCustom:21,Match-Case:=False,Orientation:=xlTopToBottom.SortMethod:2xIPinYin,DataOptionl: =xlsortNormaiEnd Sub程序結束5討論Microsoft Excel是最常用的數(shù)據(jù)處理軟件,秩次分析法 程序以Excel電子表格為基礎,其使用界面簡潔友好,數(shù)據(jù)錄 入和結果輸出十分方便,運行方法簡單,兼容性和可移植性 強,適合廣大科研人員使用。由前述介紹可知,秩次分析法的分析過程比較繁瑣,且 步驟較多,運算工作量較大,方差分析時還需調用第三方軟 件或Excel

19、文件自帶的統(tǒng)計分析工具。最小顯著差數(shù)LSD 的計算、品種均值的多重比較及顯著性檢驗、H。、H2、值的計 算均需手工操作,很容易出錯。特別是對于多年、多點、多品 (下轉第17561頁 m=+眥+,0瞰N37卷35期 齊凌峰不同磷化鋁施藥量的熏蒸效果研究 1756I處理E Treatment E/mg/kg駐間h1瓦鬲函F磊i瓦五1百忑西莉彈百珊譯酉 竺夏匭工委至莖匭五匭工互至王匭五匭工薟至主匭鱉2燮i塞 點1sie l點2sile 2點3site 3點l siIe l點2site 2點3site 3點l sjte l點2site 2點3site 3Ii;焉 tie DIleNote:The te

20、mperature is 2627and the telatiYe humidity is 68%一69%.2.2不同施藥量的殺蟲效果 統(tǒng)計熏蒸后各處理蟲籠內的煙草甲活蟲數(shù):成蟲、幼蟲以在3d內反復多次以毛筆輕觸,蟲體始終無反應視作死亡,帶卵炯葉需在實驗室培養(yǎng)7d以觀察孵化情況。由表2可知。各處理投放的煙草甲成蟲、幼蟲在熏蒸后均全部死亡;空白對照煙草甲成蟲死亡率僅為28.3%,幼蟲死亡率為40.O%,大多數(shù)成蟲、幼蟲存活了下來。且附在煙葉上的蟲卵在實驗室條件下可孵化出幼蟲?？芍?在箱內P地濃度達到200ms/kg且保持該濃度以上96h,可確保有效殺死煙箱內各蟲態(tài)的煙草甲。襄2各處理熏蒸后煙草甲

21、存活情況Talkie 211姆survival sj蜘m廿ons of Lasiaderma serricorne山lder d珊毫r-mt treatments after soffocafing處理 Treatment投放蟲數(shù) 熏蒸后存活數(shù)量 死亡率% Insect number Survival quantity after suffocating Survival tale成蟲幼蟲 成蟲 幼蟲 孵化幼蟲 成蟲幼蟲 Adult I.,alVa Adult Larva Hatching larva Adult L丑rva對照 60604.336A 606000B 6060O O .c 60

22、600OD 6060O OE 6060O O 1428.3O l.0O 100.OO l.O0l.OO 100.O40.0100.0100.O100.0100.0t00.03結論在溫度為26、濕度為69%的條件下,密封良好的煙 垛,2.0g/m3的磷化鋁施藥量8h后就可使煙箱內PM濃度 達到200mg/kg;保持96h以上時能有效殺死煙箱內的各蟲 態(tài)的煙草甲。煙箱內PH,濃度及增加速率與垛內空間的 PH,濃度及增加速率存在一定的差異,為確保熏蒸效果,應以 箱內PH,濃度檢測為準。試驗選用片煙煙箱均無內膜,考慮 到內膜會阻礙PH,的滲透.有內膜煙箱熏蒸時應適當延長熏 蒸時間,并適當增加用藥量。參

23、考文獻【1薛寶燕.食料與共生菌對煙草甲的影響及煙草甲防治技術研究【D. 合肥;安徽農業(yè)大學.2005:l10.3陳和潤.磷化鋁防治煙葉倉儲害蟲技術初探J.植保技術與推廣, 2001(21:20-21.4孫瑞立.幾種煙葉倉庫殺蟲技術的比較J.云南煙草.1999(3:39 41.5丁碧軍.如何提高磷化鋁熏殺煙倉害蟲的效果J.上海煙業(yè),1998(3: 12一13.6姜永嘉,宋紀真.介紹一種新型貯煙害蟲熏蒸卉l卜_煙葉專用復合型. 鍘餾(j炳草糧,1997(5:31.7高念昭.磷化鋁,敵殺死在煙貯害蟲上的應用J.貴州煙草.1994(1: 4950.+-卜+一+-+-+一+-卜-+-+-+-+-+一+-

24、+-+一+-_卜+-+一+-卜-+.+一+-+-+-+-卜-1._-1。+一。卜。卜-1。一。卜-。一。卜-。卜(上接第17556頁種的大型區(qū)域試驗數(shù)據(jù)資料,計算工作量十分龐大。采用該程 序后所有的計算均可在瞬問完成,效率大大提高。但需要注意 的是,數(shù)據(jù)必須從第l行第l列開始連續(xù)輸入.中間不能留空, 數(shù)據(jù)塊內部也不能有空單元格,否則程序容易出錯。該程序最 多可分析的品種數(shù)及重復數(shù)分別為9000、200個以上。雖然該程序可大大減輕秩次分析法的計算工作量,但目 前還不能完成全部的計算分析過程。求算出玩、幔和等 參數(shù)后,需要根據(jù)實際情況對區(qū)試資料進行合理取舍,剔除 環(huán)境區(qū)分指數(shù)yM過低(如低于80%

25、的試驗點,計算出有效 環(huán)境點下品種的秩次值平均值,并進行多重比較及分析 評價。參考文獻1金文林,白瓊巖.作物區(qū)試中品種產量性狀評價的秩次分析法J.作物學報,1999.25(3:632638.2金文株作物醫(yī)試中品種穩(wěn)定性評價的秩次分析模型J.作物學報, 2000.26(6:925930.3金文林,呂志軍,王俊霞.等,秧次分析法在小豆區(qū)試中應用的評價 j.作物雜志,1999(6:9一11.4高小麗,孫健敏.馮佰利,等.國家苦養(yǎng)區(qū)試品種產量性能及穩(wěn)定性分 析J.干旱地厭農業(yè)研究,2003.21(4:10一14.5董玉武,劉自華,白玉龍,等.秩次分析法對冬小麥新種質產量性狀評 價的有效性J.植物遺傳資

26、源學報.2006,7(2:179182.6何川,鄭祖平.秩次分析法t,Ta03-非平衡玉米區(qū)試產量的應用研究J. 玉米科學,2005,13(4:5355.【7段惠敏,劉自華,董玉武,等,秩次分析法評價農作物品種區(qū)域試驗 J.中國油料作物學報,2003.25(2:12一14.【8金文林,自瓊巖.應片j秩次分析法對大豆區(qū)試中品種產量表現(xiàn)的評價 J.北京農學院學報.1999,14(4:l一5.9金文林,濮紹京,陳立軍。等.用秩次分析法評價春大白菜品種區(qū)試的 高產穩(wěn)產性J.園藝學報,2005.32(1:138140.11伍云輝Excel VBA應用高效開發(fā)M.北京:電子工業(yè)出版社,2007. 秩次分析

27、法的Excel VBA 程序設計作者:劉紅艷 , 趙應忠 , 楊敏敏作者單位:中國農業(yè)科學院油料作物研究所,湖北武漢,430062刊名:安徽農業(yè)科學 英文刊名:JOURNAL OF ANHUI AGRICULTURAL SCIENCES年,卷(期:2009,37(35參考文獻(11條1. 金文林;白瓊巖 作物區(qū)試中品種產量性狀評價的秩次分析法 期刊論文-作物學報 1999(032. 金文林 作物區(qū)試中品種穩(wěn)定性評價的秩次分析模型 期刊論文-作物學報 2000(063. 金文林;呂志軍;王俊霞 秩次分析法在小豆區(qū)試中應用的評價 期刊論文-作物雜志 1999(064. 高小麗;孫健敏;馮佰利 國家

28、苦蕎區(qū)試品種產量性能及穩(wěn)定性分析 期刊論文-干旱地區(qū)農業(yè)研究 2003(045. 董玉武;劉自華;白玉龍 秩次分析法對冬小麥新種質產量性狀評價的有效性 期刊論文-植物遺傳資源學報 2006(026. 何川;鄭祖平 秩次分析法評價非平衡玉米區(qū)試產量的應用研究 期刊論文-玉米科學 2005(047. 段惠敏;劉自華;董玉武 秩次分析法評價農作物品種區(qū)域試驗 期刊論文-中國油料作物學報 2003(028. 金文林;自瓊巖 應用秩次分析法對大豆區(qū)試中品種產量表現(xiàn)的評價 期刊論文-北京農學院學報 1999(049. 金文林;濮紹京;陳立軍 用秩次分析法評價春大白菜品種區(qū)試的高產穩(wěn)產性 期刊論文-園藝學報 2005(