基于Excel VBA的区域试验数据批量提取汇总软件的开发与应用  

Regional Trial Data Bulk Extraction and Aggregation Software Based on Excel VBA:Development and Application

作　　者：官晓敏 杨中路[1] 陈海峰[1] 邱德珍[1] 周新安[1] Guan Xiaomin;Yang Zhonglu;Chen Haifeng;Qiu Dezhen;Zhou Xin’an(Oil Crops Research Institute,Chinese Academy of Agricultural Sciences,Wuhan 430062,Hubei,China)

机构地区：[1]中国农业科学研究院油料作物研究所,武汉430062

出　　处：《农学学报》2020年第9期72-77,共6页Journal of Agriculture

基　　金：国家重点研发计划项目“南方大豆优质高产广适新品种培育”(2017YFD0101500)。

摘　　要：为了快速准确完成区域试验中同一品种在不同试验点数据的汇总整理,笔者利用Excel VBA编写了此软件。软件通过循环批量打开源数据Word文件提取其中的多个表格,并将相同表格内容汇总放入指定Excel工作表中。通过计算,实现了源数据文件的后台批量打开,实现了每个文件中相同表格数据的提取,实现了相同表格中同一品种在不同试点数据的汇总。在提取表格每行数据时,通过计算每行数据在汇总表中的行号,实现了表格数据一次性提取拆分,提高了汇总效率,为后续的数据处理提供了基础。软件操作简单,运行速度快,汇总效率高。本软件能够实现区域试验数据以及具有类似特点数据的快速高效汇总,在Word和Excel数据交互方面具有较大的应用推广价值。To complete data aggregation of the same variety in different trial points in regional trials efficiently,the authors developed the software based on Excel VBA.The software opens the word documents with source data one by one and extracts data from multiple tables in a file by executing multiple loops,then exports the table data from the same location in different files to a specified Excel worksheet.By computing,it can open file in batches in the background and realize the extraction of the same table data in each file and the summary of the same variety data in different trial points quickly.In the process of table data extraction,it could split and extract the table data once by calculating the row number in the summary table,which satisfies the requirement of regional trial data’s bulk extraction and aggregation with high efficiency and lays a foundation for subsequent data processing.The software is easy to operate and has fast running speed and high efficiency.It is not only suitable for data summary of regional trial,but also for other similar data summary and processing.It has certain value of popularization and application in data interaction between Office Word and Excel.

关 键 词：区域试验 EXCEL VBA 软件开发 Word表格数据 数据汇总 

分 类 号：S565.1[农业科学—作物学]