品种测试体系如何高效获取规范的数据（EXCEL篇）
品种评价研究中，通过对EXCEL数据模板进行数据格式、数据验证、插入公式、工作表保护等设置，使之具有数据的自动计算和数据验证功能，可以实现高效、准确而又低成本的获取测试数据的目的。文末提供一个用于玉米品种测试数据的EXCEL电子表格供参考。
品种测试体系的首要目的是获得试验结果，试验结果的基础是试验数据，高效率的获得规范的试验数据是科学评价品种的关键。在测试体系庞大、测试单位类型多样、测试技术员水平参差不齐的情况下，这对试验主持单位来说是个考验。然而在数据科学和软件技术高度发达的今天，这实际上只是一个小问题，主要是我们的测试体系没有充分利用现有的技术资源，没有享受到技术给我们工作带来的便利。通过对EXCEL进行恰当的设置，制作规范的数据模板，使数据录入者减少因误操作或理解偏差提供不准确数据的机会，能够大大提高后期数据整理与分析的效率，为进一步自动处理数据提供保障。
利用EXCEL制作数据模板主要有以下步骤：第一步是对表格进行基础设计。按照测试体系的要求对需要填报的项目进行组织和分类，然后录入表头、字段名（性状）、序号等基本信息。第二步是调整数据区域的格式。有的区域要设置小数位数，有的区域要设置为文本格式或日期格式。第三步是设置数据验证。一是为数量性状数据区域设置可接受的数据类型和数据范围，并且在单元格录入非法数据时弹出提示。二是为质量性状数据区域设置选择项以提高录入效率和准确性。第四步是编写公式。简单解析产量公式的设计思路。第五步是工作表测试。主要是检测有无公式错误、显示错误、打印错误。第六步是文件锁定。既要锁定工作表防止改变表格内容，又要锁定工作簿防止改变文件结构。
第1步 表格的基础设计
1.1 对性状进行分类和组织，如农艺性状、抗逆性、品质、产量等。设计表格的时候应该考虑到印刷问题，相似的内容安排在同一个工作表内。为了便于阅读和节约纸张，每张表格的字段（性状）不要太多，尤其水平方向上避免跨页。
1.2 选定/新建一个工作表。双击工作表底部名称标签，为工作表设置容易识别的名称作为工作表名。按事先的设计将表头信息、字段名（性状）、序号等内容录入相应位置。根据需要画上框线并修改字体、字号、行高、列宽等。调整行高和列宽是一个反复的过程，它们随着字体、字号、保留小数位数的改变在显示上产生细微的变化。调整的总原则是确保任何合理的数值都能够完整显示。
1.3 根据表格内容进行页面设置。根据需要将页面进行横向或纵向排布，有的时候还需要调整页边距。依次点击[页面布局|页面设置|页边距]可以调整上下左右四个方向的页边距，也可以通过设置[居中方式|水平/垂直]使表格打印出来时更加美观，最后点击[确定]完成设置。在页面布局的视图中，可以直接点击表格上方/下方的部分添加页眉和页脚。
第2步 调整数据区域的格式
设置数据区域格式主要目的是使相同项目的数据保持相同的计算精度和显示宽度，避免数据精度不一致引发计算误差或因数据过宽导致打印出###。
2.1 选择想要拟调整的数据区。选择单个单元格可以直接单击；选择行/列可以单击行号/列号；选择区域可以单击左上角/右下角拖拽到右下角/左上角，也可以单击左上角/右下角然后用shift键+↑ ↓ ← →来扩大选区；相同设置而不相连的区域，可以按住Ctrl键进行多重选择。
2.2 设置小数格式或日期格式。依次点击[开始|数字格式|数字|数值|小数位数/日期]。在小数位数栏填写或选择想保留的位数，或在日期栏选择想要的日期格式。一般来说，叶片数、株高、穗位高在单位是厘米的情况下，不保留小数；其他数字格式的数据保留一位小数即可；个别品质检测项目保留2位小数，以检测报告为准。日期格式选择年-月-日类型以符合国人习惯。最后点击[确定]完成设置
2.3 需要设置为文本格式的原因。A-B格式的数据会被系统自动转换为日期格式，如穗行数10-14会被系统理解为2018-10-14（年份为录入数据当年），转换为数字就是从1970年1月1日计算到2018年10月14日的日数；系统同样会对病级数据1-3、3-7产生相似的误解。所以需要特别注意，录入变幅穗行数、变幅病级的区域要强制设置为文本。
第3步 设置数据验证
为数据区域设置数据验证能够避免错误数据的录入，并在发生错误录入时给予填报者正确的引导。
3.1 选定数据区域。同2.1。
3.2 数量性状数据验证的设置。依次点击[数据|数据工具|数据验证]，弹出数据验证对话框。叶片数、株高（厘米）、穗位高（厘米）的[允许栏]应设置为整数，叶片数范围应在15-25之间，株高范围应介于50-450之间，穗位高范围应介于0-300之间，株高和穗位高要考虑到自交系的情况。倒伏率、倒折率、空杆率、出籽率、双穗率等比率的[允许栏]应设置为小数，范围应介于0-100。穗长和秃尖长的[允许栏]应设置为小数，范围应介于0-30之间。
3.3 数量性状数据验证警告信息的设置。点击数据验证对话框的[出错警告]。样式设置为[警告]；标题栏填写拟提示的主题，本文中填写的是[数据类型或数值范围错误]；错误信息栏填写可能出现的问题并进行恰当的指导，本文中填写的是[空杆率或双穗率应输入数字，保留一位小数，且介于0-100之间。最后点击[确定]完成设置
3.4 质量性状数据验证的设置。质量性状值可以设置为序列，录入者选择即可，避免输入引起不一致，如马齿类型/马齿型。在表格的右侧非数据区域输入拟设置序列的字段名，向下依次输入[/]和性状值。选择数据区域后打开数据验证对话框，[允许栏]选择[序列]，点击来源栏后输入数据区域地址或拖拽选择数据区域，注意不要选择字段名所在的单元格。点击确定后完成设置。
这里需要讨论一下穗行数的问题，一般说来穗行数一般是10-24之间的偶数，但又不能完全把这个性状看为数量性状。如果采集变幅数据，主持人汇总的变幅范围可能很大，没有参考价值。如果采集平均值，汇总得到的非偶数值又不能代表实际情况。所以建议采集此项数据时使用众数，即出现次数最多的值，主持单位使用同样的方法汇总数据。这里把穗行数当做质量性状处理，设置为[10|12|14|16|18|20|22|24]。
为了避免质量性状区域在打印到纸上，需要选择包含质量性状的列号，在右键的快捷菜单中选择[隐藏]。
第4步 公式的编写
EXCEL有强大的函数系统和计算功能。产量、生育期天数、增产率等指标经过标准化后便于比较，而且通过既有数据可以计算出来。
4.1 区试产量公式的解析。
函数If(agr1,agr2,agr3)具有以下功能：首先判断参数agr1。如果为真值TRUE，输出agr2；如果为假值FALSE，输出agr3。本例中，agr1使用COUNTA(E5:F5,I5:J5,M5:N5,$E$2)=7判断以上7个单元格是否都录入了数据；agr2使用AVERAGE(E25*(100-F25),I25*(100-J25),M25*(100-N25))*1000/($E$2*129)计算平均产量；agr3输出“-”。区试产量计算基于以下公式：标准产量=2000/3×粒重×(100-含水量)/(86×计产面积)，平均标准产量=标准产量/n。注意计产面积单元格E2要使用绝对引用“$E$2”。
4.2 生试产量公式的解析
计算公式的结构与区试相同，只不过没有重复。产量的计算基于以下公式：标准产量=1000×(小区鲜重+样品鲜重)×样品粒重×(100-样品含水率)/(129×样品鲜重×计产面积)。
4.3 比较试验公式的思路
最复杂的是比较试验对照产量的计算。假设间比排列试验逢1对照，每两个对照直接视为一组。序号个位为1的单元格判定为普通对照，最后一个品种判断为末尾对照，均直接导入产量数据作为对照产量；非末尾组内品种可以根据品种所处位置找出上对照和下对照的位置，并计算平均对照；末尾组内品种要找到末尾品种作为下对照来计算平均对照。中间的计算过程完成后需要隐藏。
公式编写完成后通过[复制|粘贴]或拖拽单元格右下角的“＋”复制公式到需要计算的其他区域。
第5步 工作表测试
以上所有的设置完成后，将所有数据区域填满进行测试。有的错误只有在输入极值情况下才能被发现，。有时候会出现列宽或行高在个别情况下需要调整的情况，有的数据在视图模式可以完全显示，而打印时却不能完全显示（出现###），所以最后还要通过打印预览确认。
第6步 保护模板文件
6.1 将数据区域取消锁定。右键点击左上角行标和列标的交叉部来选择所有表格，点击[设置单元格格式|保护|☑锁定|确定]，确保全部均锁定。然后选择需要填写、选择的数据区域，用相同的方法取消锁定，公式计算的区域要保持锁定状态。
6.2 保护工作表和工作簿。依次点击[审阅|保护工作表]，再输入两次密码就可以保护工作表了。所有工作表保护完成后就可以保护工作簿，即依次点击[审阅|保护工作簿]，再输入两次密码。最后保存文件。
6.3 文件修正和维护。可以将文件分发给部分填写者进行测试，汇总测试意见后，取消保护后再进行修正或维护。
第7步 其他需要注意的问题
7.1 版本问题
我的截图是在win7+office2016系统下作的，但是当时为了制作模板文件，我特地装了xp+office2003。现在的操作系统和办公软件版本很杂，有操作系统有xp、Vista、win7、win8、win10，办公软件有office 2003、2007、2010、2013、2016。最重要的是office2003向上兼容很差但有很大的使用群体。所以模板文件应该保存为.xls格式。
7.2 字体问题
在字体的设置上，汉字和数字不同。汉字最好使用宋体，虽然宋体很丑，但在各个操作系统上的字体是一样的。xp系统的楷体是楷体GB231280，仿宋是仿宋GB231280，与win7上的楷体和仿宋不一致。数字最好使用Courier New，这是一个等宽字体，不会出现相同位数的数字宽度不相等的意外情况；个人喜欢使用等宽字体Consolas，问题是xp+Office 2003系统可能没有安装这个字体，通用性不强。系统会对不被识别的字体用默认字体替代，win8和office2016会用等线体替代，Vista、win7、office2007、office2010、office2013会用微软雅黑替代，这两个字体都有较大的行间距，容易引起显示不完全等问题。
<延伸技巧1> 显示以厘米为单位的标尺：
[视图|页面布局|□√标尺] 这时行高和列宽外围就会出现标尺；
[文件]-[选项]-[高级]-[显示]-[标尺单位]-[厘米] 这时标尺的单位就是厘米了。
<延伸技巧2> 设置行高或者列宽：
[右键-行标/列标] 会弹出行高/列宽设置对话框，根据需要填入想设定的数值。当将鼠标放置在两行/列直间，鼠标变为横杠和向上下/竖杠和向左右分开的箭头，双击左键可以根据字体自动调整行高/列宽。
<延伸技巧3> 设定打印区域：
打印区域之外的内容可见，但不会打印到纸上。 [拖拽选定拟打印区域|页面布局|打印区域|设置打印区域]
后记：
通过EXCEL电子表格设计的数据表可以实现密集数据的收集与上报，但对于信息不密集的申请类表格，对样式要就较高，WORD更加适合。而且规范化的EXCEL电子表格和WORD电子文档可以通过VBA或PYTHON进行批量的数据汇总。以上内容需要专文探讨。文末提供一个用于玉米品种测试数据的电子表格模板，为同行提供一个参考，各位可以根据需要进行修改。如有表格有任何错误或同行有意见和建议请给我反馈（by_stander@163.com），以便为更多的人提供方便。
玉米品种测试电子表格下载链接
作者的其他文章：
--------如果这篇文章帮助了你，请转发让更多的人看到--------
