Excel VBA數組法在玉米自交系測配試驗列表設計中的應用

宋英博，王囡囡，張洪權，樊偉民，李 于，孟凡祥，李燦東，陳慶山

（1黑龍江省農業(yè)科學院佳木斯分院，黑龍江佳木斯 154007；2東北農業(yè)大學農學院，哈爾濱 150030）

0 引言

在玉米自交系測配試驗中，配合力是考量親本極為重要的指標，也是自交系的一個重要遺傳特性。多年來國內外的學者在玉米配合力方面做了很多的研究[1-3]。育種單位或企業(yè)根據種植面積和試驗站分布來決定自交系測試規(guī)模，根據測試規(guī)模的大小選擇不同的測試方法。常用的自交系配合力測定方法主要有多系測交法、頂交法和雙列雜交法等。通常對自交3～4代(3～S4)的材料進行早代測試獲取一般配合力，約組配1～10萬個雜交組合。通常一般配合力篩選確定后，入選材料再與5～10個父本測試特殊配合力，或精選少量高配合力自交系雙列雜交，對最后入選的組合建立田間試驗列表[4-6]。數量龐大的被測系及測驗種等數據如何迅速錄入計算機，并快速編輯形成田間試驗列表是一項復雜繁重的工作。目前常用的方法是Excel單元格的復制粘貼拆分合并，結合錄制相應的宏和Excel工具箱等方法聯(lián)合間接完成[7-9]。Excel雖然比較高效，但面對一些繁瑣的數據時也存在低效的問題，VBA可以幫我們解決這個問題。VBA是內嵌于Excel軟件中的一個開發(fā)模塊，它可以使Excel更加高效和自動化[10-12]。劉勝達等[13]在Excel中實現(xiàn)了對玉米數量性狀的統(tǒng)計，然后提出用VBA進行高效統(tǒng)計的實現(xiàn)方法，利用VBA大大簡化了操作過程，進而提高了效率。官曉敏等[14]利用編碼的Excel VBA程序自動完成對數據結果的保存、整理，簡化了大量重復操作。前人已將VBA較多的應用到育種統(tǒng)計分析上，但在玉米配合力篩選及組合配制的系統(tǒng)組建、編輯、排列試驗設計方面的研究較少[15-16]。本研究提出一種新的玉米自交系測配試驗設計編碼方法，利用VBA編寫應用程序，完成數據的錄入，生成田間試驗列表，簡單高效的完成玉米自交系測配試驗設計工作，為玉米自交測配試驗設計提供了一種新思路。

1 數組構建

數組是有序排列的同類數據元素的集合。組成數組的各個變量稱為數組的元素或下標變量。本研究利用Excel VBA中數組原理，提出一種新的玉米自交系測配試驗設計編碼方式。實際工作中，需要測配的玉米自交系來源名稱沒有一致的規(guī)律，因此，首先要將玉米自交系名稱轉換成有序排列的同類數據元素的集合。首先定義數組a(m)，下標從1開始，按順序將m個玉米被測系自交系編號C1、C2、C3、C4、C5、C6.....Cm，賦值給數組a(m)，這樣得到的數組是a(1)=C1，a(2)=C2，a(3)=C3，a(4)=C4……a(m)=Cm。每一個被測自交系對應一個唯一數組元素，便于之后的調度。同理將n個測驗種或綜合種按順序編號1A、2A.....nA，賦值給數組b(n)，這樣得到的數組是b(1)=1A，b(2)=2A……b(n)=nA，每一個測驗種或綜合種對應一個唯一數組元素，便于之后的調度，至此數組構建完成。

2 玉米自交系測配試驗列表設計

2.1 玉米自交系測配方法

常用的自交系配合力測定方法主要有多系測交法、頂交法和雙列雜交法。

多系測交法可同時測定自交系的一般配合力和特殊配合力。通常選用生產上常用的高配合力自交系或已知的在育種中準備重點利用的高配合力自交系做測驗種，根據測交中產量和其他性狀比較結果，既可選出高配合力的被測系，也能同時選出適于生產利用的優(yōu)良高產雜交種。因此，這種測交方法應用比較普遍。優(yōu)點適宜數量較多的是自交系測試，多用于國外種業(yè)和國內大型種業(yè)，缺點是被測系之間未測交。以2018年5000個被測自交系和2個測驗種為例，被測自交系按順序編號C1、C2、C3、C4、C5、C6......C5000，2個測驗種編號為1A和2A。分別建立數組a(5000)和b(2)，可以配制10000個玉米組合，如圖1所示。例如18C3-2A表示2018年度被測系中第3號自交系C3與2號測驗種雜交的組合。在Excel中將有10000行這樣的字符串，表示不同來源的被測自交系和測驗種雜交組合。通過對玉米組合字符串的觀察，發(fā)現(xiàn)這10000行字符串有規(guī)律，可以將字符串看成“18+X+Y”的組合，變量X可以看作數組a(5000)，變量Y中1A和2A反復有規(guī)律重復，變量Y可以看作數組b(2)，將5000個被測自交系作為變量X與每一個變量Y相連接，然后將新組建的字符串賦予新數組輸入到Excel表格中。從而實現(xiàn)被測系與測驗種按順序逐一雜交生成列表，列表的每一行表示一個不同玉米組合，至此配合力測定列表設計完成，數組將無序的自交系和測驗種聯(lián)系起來并迅速建立了列表。

圖1 雜交組合列表

頂交法只能測一般配合力，用雙交種或綜合種作測驗種，優(yōu)點是測配種少，工作量輕，缺點是不能測定特殊配合力。具體的測交方法是m個被測系C1、C2、C3、C4、C5、C6.....Cm與1個測驗種1A分別測交，分別建立數組a(m)和b(n)，將m個被測系與測驗種1A逐一連接生成字符串。然后將每個字符串賦予新數組輸入到Excel表格中，生成一般配合力列表，下一季度做測交組合的產量比較試驗。頂交法常與雙列雜交法配合使用。

完全雙列雜交是指一組親本間進行所有可能的雜交。參與完全雙列雜交的自交系數一般不超過10個，被測系C1、C2、C3、C4、C5、C6.....Cm(m＜10)，其生育期不可相差太大。如果被測系數量過多，可先按生育期的早、中、晚分組后進行。不完全雙列雜交是指把供試親本按試驗要求分為2組，只進行組間雜交，不進行組內雜交。當一組親本數為m，另一組親本為n時，共有mn個組合，稱為不完全雙列雜交[17]。第一組親本為A1、A2、A3、A4、A5、A6……Am，第二組親本為B1、B2、B3、B4、B5、B6.....Bn。分別建立數組a(m)和b(n)，利用數組a(m)中的每個親本與數組b(n)中各親本連接成字符串，將每一個字符串賦值于新數組中，再將新數組輸入到表格中，最后完成不完全雙列雜交設計。

利用Excel VBA數組法能處理Excel常規(guī)功能所不能完成的工作，將多個連續(xù)的操作合并成一步操作，可開發(fā)出更多功能強大的自動化程序。提高工作效率，減少人為誤差。以上操作過程由Excel VBA語言實現(xiàn)，如圖2所示。

圖2 常用配合力測定方法

2.2 玉米組合種植行數的編輯

在玉米組合田間鑒定試驗中，常需要對組合行數進行編輯。根據試驗需要來確定種植行數。在Excel中我們將各玉米組合字符串分別賦予一個新數組，這樣就建立了玉米雜交組合數組。每個玉米組合種植X行，就將新數組第一個玉米組合字符串分別輸入Excel中A列X行，然后按照數組的順序以此類推，全部輸入到A列中，這樣就將玉米各組合依次分別種X行。以上操作過程由Excel VBA語言實現(xiàn)了玉米組合種植行數的編輯。

2.3 玉米組合復配親本統(tǒng)計

玉米自交系通過一般配合力和特殊配合力的選擇后，實現(xiàn)了下一步晉級。玉米雜交組合通常要經過多年多地的反復測試，晉級的組合需要重新復配?？梢岳肊xcel VBA數組法，通過晉級的組合列表統(tǒng)計被測自交系與測試父本出現(xiàn)的頻率個數，從而實現(xiàn)復配親本的統(tǒng)計，如圖3所示。Excel VBA有利于入選組合的快速挑選再配制，實現(xiàn)工作中的科學統(tǒng)籌。通過逐一查找搜索晉級玉米組合親本出現(xiàn)個數，可以較高效的完成入選親本的統(tǒng)計，以上操作過程均由Excel VBA語言實現(xiàn)。

圖3 玉米組合復配親本統(tǒng)計列表窗體

2.4 與常規(guī)方法的比較

目前常規(guī)的方法是Excel基礎的復制粘貼合并，結合錄制相應的宏和Excel超級工具箱等方法間接完成。以上的常規(guī)方法需要熟練掌握Excel操作的人員完成，步驟繁瑣，費時費力。為了驗證數組法的高效性，設計如下實驗，比較分析2種方法所用時間，分別將5000、20000、40000份被測自交系與2份測試父本雜交，雙行種植，在測配組合中選取總數的10%統(tǒng)計玉米組合親本出現(xiàn)的頻率個數。記錄5次測試時間的平均值、常規(guī)方法與VBA數組法所用時間（見表1）。計算機配置為：CPU為AMD A8-5600K APU With Radeon(tm)，HD Graphics 3.6GHz，內存為4GB。

表1 2種方法的比較

3 討論與結論

3.1 討論

在玉米育種中，Excel已廣泛應用于育種統(tǒng)計分析和試驗列表設計上，雖然Excel提高了一定效率，但在實際工作中還存在過多的手工重復操作[17-19]。Excel VBA是一種在可視化開發(fā)環(huán)境下用于開發(fā)自動化應用程序的語言，可以創(chuàng)建自定義的解決方案[20-21]。在實際玉米育種工作中，玉米自交系測配與統(tǒng)計試驗設計是育種企業(yè)職業(yè)經理和育種專家常備技能，本研究通過Excel VBA的使用可以執(zhí)行特定功能或重復性高的操作，它使Excel形成了獨立的編程環(huán)境。通過建立用戶窗體工程，將復雜的操作編程調度，利用Excel VBA數組法可以由計算機代替人工進行相應的操作?？梢蕴岣哂N企業(yè)職業(yè)經理和育種專家工作效率，與其他育種試驗設計軟件比較，Excel VBA數組法不需要購買專業(yè)軟件，具有推廣成本低、前景廣闊等特點。

3.2 結論

與常規(guī)方法比較，當5000份被測自交系與2份測試父本雜交時，常規(guī)方法所用時間為357 min，Excel VBA數組方法所用時間僅為1.4 min，常規(guī)方法所用時間是Excel VBA數組方法的255倍。當20000份被測自交系與2份測試父本雜交時，常規(guī)方法所用時間為1356 min，VBA方法所用時間僅為4.2 min，常規(guī)方法所用時間是VBA方法的322倍。當40000份被測自交系與2份測試父本雜交時，常規(guī)方法所用時間為2659 min，VBA方法所用時間僅為6.4 min，常規(guī)方法所用時間是VBA方法的415倍?？梢奅xcel VBA數組方法運行的效率要遠遠高于常規(guī)方法，同時可以簡化人員的操作步驟，避免人為操作的失誤，實現(xiàn)手工重復步驟自動化。

本研究提出一種新的玉米自交系測配設計編碼方式，按順序將m個被測系自交系編號為C1、C2、C3、C4、C5、C6.....Cm，將測驗種或另一組自交系按順序編號為1A.....nA，分別建立數組a(m)和b(n)。利用數組法實現(xiàn)玉米組合親本按順序逐一雜交生成列表，通過玉米自交系測配試驗過程所需具體步驟，層層篩選晉級，保留優(yōu)秀自交系和雜交組合，并統(tǒng)計復配鑒定，建立田間試驗列表。Excel VBA數組方法可以高效完成玉米自交系配合力測定列表、編輯種植行數生成田間列表和晉級組合統(tǒng)計所用親本再復配組合等任務。與常規(guī)方法比較，Excel VBA數組方法具有操作簡單易學、運行速度快、適用范圍更廣泛。隨著被測自交系數量的增加，Excel VBA數組方法優(yōu)勢更明顯，可以提高玉米自交系測配試驗設計效率。