基于Excel VBA的區(qū)域試驗(yàn)考種數(shù)據(jù)錄入系統(tǒng)的設(shè)計(jì)與應(yīng)用

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

?

基于Excel VBA的區(qū)域試驗(yàn)考種數(shù)據(jù)錄入系統(tǒng)的設(shè)計(jì)與應(yīng)用

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

為解決區(qū)域試驗(yàn)考種數(shù)據(jù)錄入工作量大、易出錯(cuò)、重復(fù)計(jì)算多的問題，在Excel中建立固定數(shù)據(jù)區(qū)域，將固定數(shù)據(jù)區(qū)域劃分為原始數(shù)據(jù)輸入?yún)^(qū)和衍生數(shù)據(jù)計(jì)算區(qū)，結(jié)合Excel強(qiáng)大的公式計(jì)算功能將原始數(shù)據(jù)的計(jì)算自動(dòng)化，并利用編碼的Excel VBA程序自動(dòng)完成對數(shù)據(jù)結(jié)果的保存、整理。以大豆考種數(shù)據(jù)為例介紹了該系統(tǒng)的設(shè)計(jì)與應(yīng)用，固定數(shù)據(jù)區(qū)域作為數(shù)據(jù)處理的“模板”，簡化了大量重復(fù)操作。該系統(tǒng)簡單易行，穩(wěn)定高效，能大幅提高農(nóng)作物品種區(qū)域試驗(yàn)工作效率，減輕勞動(dòng)強(qiáng)度，降低錯(cuò)誤率，具有一定的推廣應(yīng)用價(jià)值。

區(qū)域試驗(yàn)；考種數(shù)據(jù)；Excel VBA；數(shù)據(jù)錄入系統(tǒng)

0　引言

農(nóng)作物品種區(qū)域試驗(yàn)是在同一生態(tài)類型區(qū)內(nèi)多個(gè)有代表性的試點(diǎn)同時(shí)實(shí)施的同一組對比試驗(yàn)，其目的是觀察分析新品種的特征特性、評價(jià)其利用價(jià)值和適宜推廣區(qū)域，其結(jié)果直接為新品種審定和推廣提供依據(jù)［1-4］。區(qū)域試驗(yàn)對品種的正確評價(jià)，很大程度上有賴于對品種間差異做出準(zhǔn)確的鑒別，而對品種產(chǎn)量、品質(zhì)及特征特性的考察和鑒定是品種間差異鑒別的根本［5］?？挤N是考察品種產(chǎn)量、品質(zhì)及特征特性的一種方法，是農(nóng)作物區(qū)域試驗(yàn)過程中的一個(gè)重要環(huán)節(jié)。在區(qū)域試驗(yàn)中，考種項(xiàng)目種類繁多且涉及大量品種，因此產(chǎn)生大量有關(guān)品種性狀的數(shù)據(jù)，如何高效地完成對這些性狀數(shù)據(jù)的收集、記載、整理、統(tǒng)計(jì)、分析、存儲，降低錯(cuò)誤率，對提高農(nóng)作物品種試驗(yàn)工作效率極為重要［6-7］。

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

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

1　考種數(shù)據(jù)輸入系統(tǒng)的功能

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

2　設(shè)計(jì)思路

以大豆區(qū)域試驗(yàn)數(shù)據(jù)為例，基于Office Excel2010［20］介紹該系統(tǒng)的設(shè)計(jì)過程及思路。

2.1數(shù)據(jù)模板的設(shè)計(jì)

2.1.1表格布局設(shè)計(jì) 打開Excel，分別建立數(shù)據(jù)輸入、結(jié)果保存、考種表整理、產(chǎn)量表整理等4個(gè)電子表格，將涉及到的大豆考種項(xiàng)目，如植株高度、底莢高度、主莖節(jié)數(shù)等，分別輸入到4個(gè)電子版的標(biāo)題行（圖2）?？挤N表和產(chǎn)量表中的標(biāo)題應(yīng)與數(shù)據(jù)輸入表相一致。

2.1.2數(shù)據(jù)輸入表設(shè)計(jì) 首先根據(jù)考種樣本量留出數(shù)據(jù)輸入?yún)^(qū)（如用黃底色標(biāo)示），與考種樣本量無關(guān)的數(shù)據(jù)輸入?yún)^(qū)亦標(biāo)示出來，如百粒重、小區(qū)產(chǎn)量等項(xiàng)目。在可變區(qū)（綠底色標(biāo)示）輸入計(jì)算公式，完成衍生數(shù)據(jù)的自動(dòng)計(jì)算。

2.2數(shù)據(jù)的分類整理

打開Excel中的“開發(fā)工具”菜單，點(diǎn)擊“宏”并運(yùn)行“數(shù)據(jù)保存整理”程序，將可變區(qū)域的計(jì)算結(jié)果分別保存到考種表整理、產(chǎn)量表整理2個(gè)表中。該過程與原始數(shù)據(jù)的保存同步進(jìn)行。

2.3操作方法與源代碼

將數(shù)據(jù)模板設(shè)計(jì)完成后，打開“開發(fā)工具”菜單，進(jìn)入“Visual Basic”編輯器，新建模塊，并將寫好的代碼復(fù)制到模塊中，保存后點(diǎn)擊相應(yīng)的宏代碼即可自動(dòng)完成數(shù)據(jù)的批量匯總。源代碼如下：

Sub數(shù)據(jù)保存整理（）

Sheet1.Activate

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

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

Myrng.Copy

（選擇復(fù)制區(qū)域，并復(fù)制）

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

（將輸入表中的原始數(shù)據(jù)存入Sheet2表中）

Sheet1.［A22：N22］.Copy

Sheet3.Activate

圖1　考種數(shù)據(jù)輸入系統(tǒng)

圖2　考種數(shù)據(jù)輸入模板

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的計(jì)算結(jié)果保存到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三個(gè)重復(fù)小區(qū)的產(chǎn)量保存到Sheet4電子表中）

If MsgBox（"是否清空原始輸入?yún)^(qū)內(nèi)容，繼續(xù)輸入下一個(gè)？"，vbQuestion+vbYesNo，"Eersoft-提示"）=vbYes Then

Sheet1.［A2：Q21］.ClearContents

Sheet1.Activate

Else

Exit Sub

End If

End Sub

（清空輸入表中原始數(shù)據(jù)，為下個(gè)數(shù)據(jù)的輸入做準(zhǔn)備）

圖3　考種數(shù)據(jù)輸入系統(tǒng)使用過程

3　結(jié)果與分析

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

4　結(jié)論與討論

4.1系統(tǒng)優(yōu)點(diǎn)分析

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

4.2系統(tǒng)缺點(diǎn)分析

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

基于Excel VBA的考種數(shù)據(jù)錄入系統(tǒng)，能夠大幅提高工作效率，減少人為誤差。筆者以大豆區(qū)域試驗(yàn)考種數(shù)據(jù)為例對該系統(tǒng)進(jìn)行了闡述，讀者可以根據(jù)自己的需要對源程序進(jìn)行修改、擴(kuò)展，并推廣運(yùn)用到其他類似的試驗(yàn)工作中，以提高工作效率。對于不會編寫VBA程序的讀者，也可以按照文中闡述的方法直接應(yīng)用源程序?qū)崿F(xiàn)相應(yīng)的統(tǒng)計(jì)操作。

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

［2］ 張斯梅，楊四軍，顧克軍，等.小麥區(qū)域試驗(yàn)產(chǎn)量性狀及其穩(wěn)定性分析［J］.中國農(nóng)學(xué)通報(bào)，2012，28（3）：172-176.

［3］ 許乃銀，張國偉，李健，等.基于HA-GGE雙標(biāo)圖的長江流域棉花區(qū)域試驗(yàn)環(huán)境評價(jià)［J］.作物學(xué)報(bào)，2012，38（12）：2229-2236.

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

［5］ 許乃銀，金石橋，李健.我國棉花品種區(qū)域試驗(yàn)重復(fù)次數(shù)和試點(diǎn)數(shù)量的設(shè)計(jì)［J］.作物學(xué)報(bào)，2016，42（1）：43-50.

［6］徐淑霞，李振貴，張光.大豆區(qū)試產(chǎn)量與主要農(nóng)藝性狀的灰色關(guān)聯(lián)度分析［J］.大豆科技，2012，28（1）：28-30.

［7］ 曹婧華，冉彥中，郭金城.玉米考種系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)［J］.長春師范學(xué)院學(xué)報(bào)：自然科學(xué)版，2011，30（8）：38-41.

［8］ 孔凡洲，于仁成，徐子鈞，等.應(yīng)用Excel軟件計(jì)算生物多樣性指數(shù)［J］.海洋科學(xué)，2012，36（4）：57-62.

［9］ 張梅，陳玉光，李韋祿，等.基于Microsoft Excel統(tǒng)計(jì)函數(shù)的農(nóng)業(yè)氣象預(yù)報(bào)模型研究［J］.中國農(nóng)學(xué)通報(bào)，2014，30（2）：309-313.

［10］霍世清，張靜，馮崗.EXCEL在裂區(qū)試驗(yàn)統(tǒng)計(jì)分析中的應(yīng)用［J］.中國農(nóng)學(xué)通報(bào)，2011，27（30）：159-163.

［11］劉霞，路永貴，閆當(dāng)萍.EXCEL在農(nóng)藥毒力測定中的應(yīng)用［J］.中國農(nóng)學(xué)通報(bào)，2009，25（19）：206-208.

［12］許乃銀，李健.棉花區(qū)試總結(jié)中“品種評述”批量生成系統(tǒng)的構(gòu)建與應(yīng)用［J］.中國棉花，2014，41（6）：17-20.

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

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

［15］郝才超，薛霆虓.EXCEL VBA批量處理在錄井砂地比統(tǒng)計(jì)中的應(yīng)用［J］.工程地球物理學(xué)報(bào)，2012，145（6）：1672-7940.

［16］陳海生，彭峰，劉玉國.Excel VBA在醫(yī)院基本藥物數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用［J］.中國現(xiàn)代應(yīng)用藥學(xué)，2015，32（12）：1494-1498

［17］楊振宇，楊海智，楊信東.用EXCEL中的VBA編寫“質(zhì)量性狀遺傳分析”相關(guān)程序及其在農(nóng)業(yè)上的應(yīng)用［J］.吉林農(nóng)業(yè)大學(xué)學(xué)報(bào)，2012，34（6）：692-696.

［18］桂嘉偉.基于EXCEL VBA的審計(jì)自動(dòng)抽樣系統(tǒng)設(shè)計(jì)與應(yīng)用研究［J］.中國管理信息化，2015，18（21）：53-56.

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

［20］陳偉，王維，鄒燕飛.淺談EXCEL 2010重復(fù)數(shù)據(jù)的處理方法［J］.電腦知識與技術(shù)，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

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

官曉敏，女，1989年出生，湖北宜城人，研究實(shí)習(xí)員，碩士，研究方向?yàn)閲掖蠖蛊贩N區(qū)域試驗(yàn)和春大豆育種。通信地址：430062湖北省武漢市武昌區(qū)徐東二路2號中國農(nóng)業(yè)科學(xué)研究院油料作物研究所，E-mail：guximihubei@163.com。

周新安，男，1963年出生，湖北靳春人，研究員，博士，研究方向?yàn)槟戏酱蠖惯z傳育種與營養(yǎng)高效轉(zhuǎn)基因研究。通信地址：430062湖北省武漢市武昌區(qū)徐東二路2號中國農(nóng)業(yè)科學(xué)研究院油料作物研究所，E-mail：zhouxinan@caas.cn。

2016-03-23，

2016-06-12。