基于Excel VBA的區域試驗考種數據錄入系統的設計與應用

官曉敏，楊中路，陳海峰，邱德珍，陳李淼，張嬋娟，袁松麗，陳水蓮，張曉娟，單志慧，周新安（中國農業科學研究院油料作物研究所，武漢430062）

?

基于Excel VBA的區域試驗考種數據錄入系統的設計與應用

官曉敏，楊中路，陳海峰，邱德珍，陳李淼，張嬋娟，袁松麗，陳水蓮，張曉娟，單志慧，周新安
（中國農業科學研究院油料作物研究所，武漢430062）

為解決區域試驗考種數據錄入工作量大、易出錯、重復計算多的問題，在Excel中建立固定數據區域，將固定數據區域劃分為原始數據輸入區和衍生數據計算區，結合Excel強大的公式計算功能將原始數據的計算自動化，并利用編碼的Excel VBA程序自動完成對數據結果的保存、整理。以大豆考種數據為例介紹了該系統的設計與應用，固定數據區域作為數據處理的“模板”，簡化了大量重復操作。該系統簡單易行，穩定高效，能大幅提高農作物品種區域試驗工作效率，減輕勞動強度，降低錯誤率，具有一定的推廣應用價值。

區域試驗；考種數據；Excel VBA；數據錄入系統

0　引言

農作物品種區域試驗是在同一生態類型區內多個有代表性的試點同時實施的同一組對比試驗，其目的是觀察分析新品種的特征特性、評價其利用價值和適宜推廣區域，其結果直接為新品種審定和推廣提供依據［1-4］。區域試驗對品種的正確評價，很大程度上有賴于對品種間差異做出準確的鑒別，而對品種產量、品質及特征特性的考察和鑒定是品種間差異鑒別的根本［5］。考種是考察品種產量、品質及特征特性的一種方法，是農作物區域試驗過程中的一個重要環節。在區域試驗中，考種項目種類繁多且涉及大量品種，因此產生大量有關品種性狀的數據，如何高效地完成對這些性狀數據的收集、記載、整理、統計、分析、存儲，降低錯誤率，對提高農作物品種試驗工作效率極為重要［6-7］。

Excel辦公軟件是微軟公司推出的一款功能強大、使用方便的電子表格式數據綜合管理與分析系統軟件［8］，它提供了豐富的函數、強大的數據統計分析處理功能，給數據的計算整理帶來了極大的便利［9-12］。近年來計算機技術發展迅速，各種面向用戶需求的計算機軟件應運而生，但目前尚無針對區域試驗考種數據的處理軟件。區域試驗考種項目多、考種樣本量大、數據類型多樣，如果僅靠人工操作逐層挑選、逐項計算匯總的話，重復工作量大；同時對這些數據的整理保存又需要大量重復操作，工作量隨數據量的增加而變大。Excel VBA是微軟開發的一種應用程序共享的通用自動化語言［13］，其編程是對Excel自身功能的集成和擴展，較其他開發工具可以更為快捷地形成一個完整的數據處理軟件［14］，能夠批量處理數據，輕松完成大量重復工作，滿足不同方面的數據處理需求［15-19］。

考種數據錄入系統利用Excel自帶的內部函數完成對考種衍生數據的自動計算并通過Excel VBA編程完成對匯總數據的整理、保存，極大提高了考種數據的處理效率，減輕了區試工作者的工作量。筆者以大豆區域試驗考種數據為例介紹了該系統的設計與運用，旨在揭示該系統的創建過程與使用方法，便于其他作物參考使用。

1　考種數據輸入系統的功能

在Excel中建立4個子表格，用于存儲不同形式的數據（圖1）。Sheet1為數據輸入表，該表分為2個區域，一個為固定區域，用來輸入原始數據（黃底色區域），數據行數根據考種樣本量確定；一個為可變區域，主要是用來編輯公式，根據需要完成對原始數據的處理（綠底色區域），同時可變區域也是數據整理提取的區域（圖1A）。Sheet2為原始結果保存區，即對Sheet1表中所有數據的完整拷貝，累計保留所有品種數據，使原始數據得以完好保存，便于查找（圖1B）。Sheet3為考種表整理：即保存每個品種計算后的數據，便于品種數據比較（圖1C）。Sheet4為產量表，用于存儲小區的產量，方便畝產、增產率等數據的進一步計算、整理（圖1D）。數據輸入表可設計多種數據的輸入，如田間調查數據、考種數據、產量數據等，然后利用VBA代碼分類提取，也可以根據數據類型建立多個輸入表。除在Sheet1的固定區域輸入原始數據外，原始數據的保存、匯總數據的分類整理均利用VBA代碼完成，簡便快捷。

2　設計思路

以大豆區域試驗數據為例，基于Office Excel2010［20］介紹該系統的設計過程及思路。

2.1數據模板的設計

2.1.1表格布局設計 打開Excel，分別建立數據輸入、結果保存、考種表整理、產量表整理等4個電子表格，將涉及到的大豆考種項目，如植株高度、底莢高度、主莖節數等，分別輸入到4個電子版的標題行（圖2）。考種表和產量表中的標題應與數據輸入表相一致。

2.1.2數據輸入表設計 首先根據考種樣本量留出數據輸入區（如用黃底色標示），與考種樣本量無關的數據輸入區亦標示出來，如百粒重、小區產量等項目。在可變區（綠底色標示）輸入計算公式，完成衍生數據的自動計算。

2.2數據的分類整理

打開Excel中的“開發工具”菜單，點擊“宏”并運行“數據保存整理”程序，將可變區域的計算結果分別保存到考種表整理、產量表整理2個表中。該過程與原始數據的保存同步進行。

2.3操作方法與源代碼

將數據模板設計完成后，打開“開發工具”菜單，進入“Visual Basic”編輯器，新建模塊，并將寫好的代碼復制到模塊中，保存后點擊相應的宏代碼即可自動完成數據的批量匯總。源代碼如下：

Sub數據保存整理（）

Sheet1.Activate

Set Myrng=Application.InputBox（"請選擇復制范圍"，

"選擇區域"，"$A$2：$R$22"，Type：=8）

Myrng.Copy

（選擇復制區域，并復制）

Sheet2.Activate

With Sheet2

Myrow=.［a65536］.End（xlUp）.Row+1 .Cells（Myrow，1）.Select

Selection.PasteSpecial Paste：=xlValues，

Operation：=xlNone，SkipBlanks：=False，Transpose：= False

End With

（將輸入表中的原始數據存入Sheet2表中）

Sheet1.［A22：N22］.Copy

Sheet3.Activate

圖1　考種數據輸入系統

圖2　考種數據輸入模板

With Sheet3Datarow=.［a65536］.End（xlUp）.Row+1 .Cells（Datarow，1）.Select

Selection.PasteSpecial Paste：=xlValues，

Operation：=xlNone，SkipBlanks：=False，Transpose：= False

End With

（將A22-N22的計算結果保存到Sheet3表中）

Sheet1.［A22，O22：Q22］.Copy

Sheet4.Activate

With Sheet4

Yeildrow=.［a65536］.End（xlUp）.Row+1 .Cells（Yeildrow，1）.Select

Selection.PasteSpecial Paste：=xlValues，

Operation：=xlNone，SkipBlanks：=False，Transpose：= False

End With

（將A22的品種名稱以及O22-Q22三個重復小區的產量保存到Sheet4電子表中）

If MsgBox（"是否清空原始輸入區內容，繼續輸入下一個？"，vbQuestion+vbYesNo，"Eersoft-提示"）=vbYes Then

Sheet1.［A2：Q21］.ClearContents

Sheet1.Activate

Else

Exit Sub

End If

End Sub

（清空輸入表中原始數據，為下個數據的輸入做準備）

圖3　考種數據輸入系統使用過程

3　結果與分析

通過整合Excel自身的公式計算功能及VBA編程功能，建立了考種數據的高效錄入系統，減少了衍生數據重復計算、數據結果多次整理的工作量，提高了工作效率。不同的考種數據看似復雜，但是通過建立適合的數據輸入“模板”，固定數據項目位置，建立統一的衍生數據計算公式，可以完成數據的自動計算。同時利用Excel VBA程序在數據輸入區域提取、保存數據，自動完成數據整理。系統具有一定的交互性，數據輸入區域的保存范圍可自定義（圖3A），Excel VBA的數據處理速度快，幾乎在瞬間完成（圖3B）。

4　結論與討論

4.1系統優點分析

（1）該系統將Excel和VBA結合，既能夠保存考種的原始數據，又能夠分類保存不同形式的數據，簡單高效。（2）本系統利用Excel的VBA功能實現了自動化操作，將多步操作一次性快速高效完成，減少了人為操作過程，降低了錯誤率。（3）不需要安裝新的軟件或插件，減少了系統資源占用。（4）借助Excel靈活多樣的功能，可實現輸入數據的自定義及數據分析等功能，如可通過對數據輸入區數據有效性的設置，減少輸入錯誤，可通過合理的Excel公式對整理的數據進行再計算、檢查、判斷。（5）該系統開放性較好，通過簡單的修改即可用于其他作物、其他類型數據的錄入應用。

4.2系統缺點分析

（1）模板的建立可能比較麻煩，需要對數據輸入區進行規范，對數據計算區進行公式的定義及編輯，需要有一定的Office操作基礎，但模板一旦建立后就可以一勞永逸；（2）系統使用者需具備一定的Excel VBA的基礎知識，以實現代碼程序的個性化自定義及解決代碼運行過程中出現的問題，可以將代碼自定義為Excel加載宏或在Excel工作表中建立命令按鈕簡化使用操作。（3）系統VBA代碼部分缺乏更個性化的交互功能，如增加對數據整理區域的自定義設置，滿足不同作物、不同類型數據的處理需求。（4）系統的運行需要Excel VBA組件，用戶Office無此組件時需要先安裝。

基于Excel VBA的考種數據錄入系統，能夠大幅提高工作效率，減少人為誤差。筆者以大豆區域試驗考種數據為例對該系統進行了闡述，讀者可以根據自己的需要對源程序進行修改、擴展，并推廣運用到其他類似的試驗工作中，以提高工作效率。對于不會編寫VBA程序的讀者，也可以按照文中闡述的方法直接應用源程序實現相應的統計操作。

［1］王潔，廖琴，胡小軍，等.北方稻區國家水稻品種區域試驗精確度分析［J］.作物學報，2010，36（11）：1870-1876.

［2］ 張斯梅，楊四軍，顧克軍，等.小麥區域試驗產量性狀及其穩定性分析［J］.中國農學通報，2012，28（3）：172-176.

［3］ 許乃銀，張國偉，李健，等.基于HA-GGE雙標圖的長江流域棉花區域試驗環境評價［J］.作物學報，2012，38（12）：2229-2236.

［4］ 吳存祥，李繼存，沙愛華，等.國家大豆品種區域試驗對照品種的生育期組歸屬［J］.作物學報，2012，38（11）：1977-1987.

［5］ 許乃銀，金石橋，李健.我國棉花品種區域試驗重復次數和試點數量的設計［J］.作物學報，2016，42（1）：43-50.

［6］徐淑霞，李振貴，張光.大豆區試產量與主要農藝性狀的灰色關聯度分析［J］.大豆科技，2012，28（1）：28-30.

［7］ 曹婧華，冉彥中，郭金城.玉米考種系統的設計與實現［J］.長春師范學院學報：自然科學版，2011，30（8）：38-41.

［8］ 孔凡洲，于仁成，徐子鈞，等.應用Excel軟件計算生物多樣性指數［J］.海洋科學，2012，36（4）：57-62.

［9］ 張梅，陳玉光，李韋祿，等.基于Microsoft Excel統計函數的農業氣象預報模型研究［J］.中國農學通報，2014，30（2）：309-313.

［10］霍世清，張靜，馮崗.EXCEL在裂區試驗統計分析中的應用［J］.中國農學通報，2011，27（30）：159-163.

［11］劉霞，路永貴，閆當萍.EXCEL在農藥毒力測定中的應用［J］.中國農學通報，2009，25（19）：206-208.

［12］許乃銀，李健.棉花區試總結中“品種評述”批量生成系統的構建與應用［J］.中國棉花，2014，41（6）：17-20.

［13］鮑祥生，梁兵，周海燕，等.VBA和EXCEL函數結合編程在數據處理中的應用［J］.石油工業計算機應用，2009，64（4）：9-12

［14］朱培育，朱佳苗，趙俊香，等.EXCEL VBA數據處理軟件開發［J］.地震地磁觀察與研究，2006，27（8）：108-115.

［15］郝才超，薛霆虓.EXCEL VBA批量處理在錄井砂地比統計中的應用［J］.工程地球物理學報，2012，145（6）：1672-7940.

［16］陳海生，彭峰，劉玉國.Excel VBA在醫院基本藥物數據統計中的應用［J］.中國現代應用藥學，2015，32（12）：1494-1498

［17］楊振宇，楊海智，楊信東.用EXCEL中的VBA編寫“質量性狀遺傳分析”相關程序及其在農業上的應用［J］.吉林農業大學學報，2012，34（6）：692-696.

［18］桂嘉偉.基于EXCEL VBA的審計自動抽樣系統設計與應用研究［J］.中國管理信息化，2015，18（21）：53-56.

［19］袁文華.用EXCEL VBA建立差旅費審核系統［J］.中國管理信息化，2015，18（1）：42-44.

［20］陳偉，王維，鄒燕飛.淺談EXCEL 2010重復數據的處理方法［J］.電腦知識與技術，2015，11（22）：128-129.

Design and Application of Agronomic Traits of Regional Trial Data Entry System Based on Excel VBA

Guan Xiaomin，Yang Zhonglu，Chen Haifeng，Qiu Dezhen，Chen Limiao，Zhang Chanjuan，Yuan Songli，Chen Shuilian，Zhang Xiaojuan，Shan Zhihui，Zhou Xin’an
（Oil Crops Research Institute，Chinese Academy of Agricultural Sciences，Wuhan 430062，Hubei，China）

In order to decrease the workload of regional trial data input，reduce the error rate and improve the work efficiency，a data entry system was established.Firstly，a fixed data input area was construct in Excel and the input area was divided into the original data input area and the derived data computing area.Then the computerized original data calculation was completed through Excel’s powerful function of formula.Finally，the preservation and processing of result data were accomplished by Excel VBA program.Using the fixed data area as a template to process data could simplify repetitive operations.Soybean agronomic character data was taken for an example to evaluate the system.The results indicated that the system was simple，stable and efficient，and could greatly improve work efficiency，reduce labor intensity as well as error rate，and had a certain value of popularization and application.

Regional Trial；Agronomic Character Data；Excel VBA；Data Entry System

S565.1

A論文編號：cjas16030020

國家自然科學基金“大豆抗大豆花葉病毒病主效QTL的精細定位與候選基因發掘”（341014101002404）。

官曉敏，女，1989年出生，湖北宜城人，研究實習員，碩士，研究方向為國家大豆品種區域試驗和春大豆育種。通信地址：430062湖北省武漢市武昌區徐東二路2號中國農業科學研究院油料作物研究所，E-mail：guximihubei@163.com。

周新安，男，1963年出生，湖北靳春人，研究員，博士，研究方向為南方大豆遺傳育種與營養高效轉基因研究。通信地址：430062湖北省武漢市武昌區徐東二路2號中國農業科學研究院油料作物研究所，E-mail：zhouxinan@caas.cn。

2016-03-23，

2016-06-12。