基于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。