姜 璠，韩 阳，徐 钰，唐 健，温玄烨，崔东阳

(国家林业和草原局生物灾害防控中心，辽宁 沈阳 110034)

1 引言

林业有害生物防治员是指从事林业有害生物预防、除治作业及技术服务的人员，截至2019年年底，林业行业内仅存森林消防员和林业有害生物防治员两项技能水平评价类职业[1]。2021年3月在福建省泉州市举办了首届全国林业有害生物防治员职业技能竞赛。不断优化人才培养机制，提高队伍专业化、技能化水平，加大林业有害生物防治工作队伍建设，切实发挥防治员的作业施工作用，科学开展防治，提升森林质量，着力构建健康稳定、优质高效的森林生态系统。

本次竞赛活动经人力资源和社会保障部批准，国家林草局联合中国就业培训技术指导中心、中国农林水利气象工会主办，由国家林草局人才中心、防控中心、福建省林业局承办。

2 办公软件的应用现状

2.1 Word软件

Word[2]软件具有强大的文字编辑及处理功能，是现代办公中不可缺少的一款办公软件。Word中的一些常用技巧，能够将复杂的事情简单化，变得快捷又方便，能显著提高办公效率，其中邮件合并是一个很实用的功能[3]。它是与Excel配合使用的典型范例，将存储在Excel数据源文件中提取出来，以域的形式放在主文档中指定的位置上，把数据库记录和文本组合在一起，形成拥有主文档格式的内容且包含有不同数据内容的新的Word格式的文档。邮件合并功能具有批处理能力，在竞赛中处理裁判员记录表、检录表，只要掌握基本的计算机知识和办公软件Word、Excel的运用，就可在很短的时间内掌握邮件合并功能的应用。将该功能应用到竞赛表单制备工作中，能够轻松、快捷、准确地完成那些简单、重复性大的工作。

2.2 Excel软件

随着科学技术及信息技术在不间断的创新，信息化发展是必然趋势。Excel[4]软件作为一种数据存储、处理工具，具有数据容量较大[5]的特点，特别是日常办公中制作表格、生成图表最为常见[6]。

现对中国知网1997～2021年之间的关于Excel和数据统计的期刊、文献进行分析、统计和呈现，从而探索近20年Excel与数据统计研究的状态和特点，为更好地开展数据统计提供参考。通过CNKI知网在线分析与CiteSpace[7]可视化软件，直观表现Excel研究的样态，从发文趋势、被引用频次、学科分布、关键词方面总结出Excel研究的特点。

2.2.1 发文趋势

对近20年Excel研究发文量的统计，能够清晰查看研究发文变化的趋势，发文量的变化趋势如图1所示。

图1 发文量的变化趋势

具体可以分为三个阶段：①波动增长期(1998～2007年)，这一阶段的特点是整体波动中保持较慢增长，在这9年间，发文数量保持在4～11篇；②快速增长期(2008～2014年)，这一阶段发文数量从13篇增加到31篇；③快速衰退期(2014～2021年)，这一阶段发文数量从31篇减少到14篇，除了2016年出现回升外，其余年份发文数量快速减少。

2.2.2 被引用频次分析

基于CNKI数据库中的文献计量在线分析可知，CNKI中引用频次最高的论文题为“对田间试验数据的统计处理方法——格拉布斯准则和应用Excel进行方差分析、多重比较(LSD)”[8](表1)，该文在2009年发表于《中国西部科技》，该文研究了以农业生产的试验数据为例，介绍对于数据异常值的去除，在Excel中通过“加载宏”自动进行方差分析，以及利用其函数以最小显著差数进行多重比较的统计方法。

表1 CNKI数据库中被引频次前10的文献

2.2.3 学科分布分析

基于CNKI数据库中的文献计量在线分析可知[9]，计算机软件与应用约占52.97%、医药卫生方针政策与法律法规方针政策、环境科学与资源利用、教育学科方面应用较多，在林业相关研究中Excel应用较少，如图2所示。

图2 学科分布

2.2.4 关键词的研究

关键词是论文核心和内容精炼的表达，通过关键词分析可以探测研究的热点[10]。为达到全面检索相关文献的目的，检索时在中国知网高级检索界面分别输入“Excel”和“数据统计”进行主题检索，文献类型选为期刊和硕博论文。通过查阅检索到的CNKI文献的标题和摘要，判断文献研究内容是否与Excel数据统计研究领域有关，剔除不相关的文献后，CNKI文献221篇，用refwork格式导出并进行格式转换成适合CiteSpace分析的基础数据，导入CiteSpace绘制关键词知识图谱，如图3所示。

通过对图3分析发现，Excel、数据统计、数据处理、数据分析、统计分析等是研究者主要关注的关键词[11]，也就是说，Excel在进行数据统计时仍然存在一席之地。下面以全国林业有害生物防治员职业技能竞赛为例进行探讨。

图3 关键词知识图谱

3 规则与需求

竞赛本着公开、公平、公正的原则，在友好的气氛中进行。本次共34个代表队参赛，每个代表队选派3名选手参加比赛。参赛选手竞赛前一经确定不许更换，如若发现取消该代表队的所有成绩。

3.1 竞赛项目

本次全国林业有害生物防治员职业技能竞赛包含选手理论考试和技能操作2大项，理论考试从防治员应知应会的林业有害生物监测预报、检疫御灾、防治减灾等基本知识入手，设计了195个知识点对选手进行全面考察。防治技能操作包括有害生物识别与标本制作、松材线虫病防治、生物和物理防治以及喷雾防治4项。

3.2 竞赛方式

技能操作分为初赛、决赛2个阶段，根据现场实际情况，8人为1组，在两个赛区同时进行竞赛。赛中出现不可抗原因导致不能继续竞赛，可补赛1次，决赛不允许补赛。

3.3 选手编号

选手编号分成代表队内部编号和比赛编号，一个代表队的内部编号是由代表队名称+序号组成，如：北京1、北京2、北京3。比赛编号是根据参赛选手总数随机分配(001-102)。

3.4 选手分组

按照同省不同组原则，对102个代表队内部编号随机分组，8名为1组，共分成13个组，其中第112组为8人组、第13组为6人组。

3.5 赛区分配

竞赛共分两个赛区，1～6组在第一赛区，7～13组在第二赛区。

3.6 出场顺序

每个竞技项目在第一赛区进行6轮竞赛、第二赛区进行7轮竞赛。每个竞技项目小组出场顺序按最大限度均匀随机分配。

3.7 选手赛道

初赛中各小组的8名选手随机分配赛道，决赛中各小组的8名选手根据初赛分值择优分配。

3.8 评判标准

本项比赛以竞速和竞质相结合的方式判定比赛名次、给出项目得分。

3.8.1 初始名次确定

以选手到达终点的先后顺序排定本组比赛初始名次，同时到达者名次并列并占用其下级名额。

3.8.2 完成质量评判

发生违规行为者，每发生一次初始名次后移1名。

3.8.3 最终名次确定

经记录违规行为次数，对初始名次进行整体调整后，形成最终名次并赋分。

3.9 赋分标准

3.9.1 初始名次赋分

第一名100分、第二名95分、第三名90分、第四名85分、第五名80分、第六名75分、第七名70分、第八名 65分。

3.9.2 最终名次赋分

第一名100分、第二名90分、第三名85分、第四名80分、第五名75分、第六名70分、第七名65分、第八名60分。

3.10 成绩

3.10.1 个人成绩

选手初赛总成绩由各项初赛成绩累加构成。个人初赛成绩不进行全体排名，13个小组的第1名直接进入决赛，第2名中成绩最好的前3名进入决赛。成绩并列的按个人比赛总用时调整排名，继续并列的依次按第二、四、三、一项目比较选手用时，仍无法区分的最终抽签确定顺序。

3.10.2 团体成绩

团体成绩由代表队选手的初赛总成绩累加构成。成绩并列的比较代表队选手比赛总用时，继续并列的依次按第二、四、三、一项目比较代表队选手总用时，仍无法区分的最终抽签确定顺序，团体成绩不含决赛成绩。

3.11 个人决赛

3.11.1 决赛选手产生及初始名次排序

决赛由16名选手组成，13个小组的第1名直接闯进决赛，第2名中成绩排名前3的进入决赛；成绩并列的比较技能竞赛总用时，继续并列的依次按技能竞赛第二、四、三、一项比较选手用时，仍无法区分的最终抽签确定顺序。产生1～16名决赛初始名次。

3.11.2 决赛分组和最终决赛名次

决赛分两组进行，个人总分前8名组成决赛第1组，争夺大赛个人1～8名；个人总分后8名组成决赛第2组，争夺大赛个人9～16名。

3.12 表单制备

比赛前按赛区、出场顺序、小组、代表队、人员、编号、赛道，制备检录表；按赛区、出场顺序、小组、编号、赛道，为裁判员提供所有裁判表单及备用表单。

小组比赛结束后要实时为播音组统计并打印小组个人成绩及排名。每个项目比赛结束后要及时为播音组统计并打印团体成绩及排名。初赛结束后要为组委会统计并打印准确的个人成绩、团体成绩以及排名信息。

4 设计与实现

由于时间紧、任务重，资金紧张，考虑到技术可行性、网络安全性及系统稳定性等方面因素，为更加直观、圆满完成这次计分任务，通过Excel软件快速实现成绩结果的统计[12]，具体从以下几方面进行叙述：

4.1 基础信息

4.1.1 个人信息

通过参赛选手信息汇总表，获取代表队、选手姓名。如图4所示。

图4 个人信息

(1)内部编号。利用RANDBETWEEN随机函数为每位选手赋予一个随机数，利用RANK排序函数确定选手在代表队的位置，最后生成内部编号。这样可以使得每位选手获得代表队内部1号的几率相等。

例如：选中C2单元格，输入“= A2&RANK(E2,E2:E4)”。

(2)比赛编号。利用已经生成的随机数，确定每位选手在全体选手中的位置，设置单元格为“"000"”格式即为比赛编号。可使用SUM求和函数和IF条件函数[13]判断比赛编号是否有重复。使得每位选手在全体选手中获得001号的几率相等。

例如：选中D2单元格中输入“=RANK(E2,E:E)”。

使用“=IF(SUM(D:D)=5253,"分配成功","分配失败")”来验证。

4.1.2 代表队信息

使用INDIRECT引用函数从个人信息中获得，如图5所示。

图5 代表队信息

例如：选中B2单元格中输入“=INDIRECT("个人信息!A"&ROW()*3-2)”。

4.1.3 出场顺序

以6阶拉丁方为基础，适当调整，平衡各队的出场顺序，如图6所示。

图6 出场顺序

4.1.4 排名赋分

根据赋分标准，制定排名赋分表，如图7所示。

图7 排名赋分

4.1.5 决赛赛道

根据决赛赛道分配原则，制定决赛赛道分配表，如图8所示。

图8 决赛赛道分配

4.2 初赛分组

4.2.1 初赛分组信息表

根据规则要求，制定初赛分组信息表，如图9所示。

图9 初赛分组信息

4.2.2 随机分组

为了达到同省不同组的结果，采取每队抽出1名选手进行分组的策略。各代表队选取1名，利用RANDBETWEEN随机函数给每位选手赋予一个随机数，利用RANK排序函数确定分段中的位置，利用QUOTIENT取整函数分组。可以使用SUM求和函数、IF条件函数判断是否有重复分配。

例如：选中F2单元格，输入“=代表队!B2&1”。

选中G2单元格，输入“=RANDBETWEEN(1,99999)”。

选中H2单元格，输入“=RANK(G2,$G$2:$G$35)”。

选中I2单元格，输入“=IF(QUOTIENT(H2-1,8)+1=5,13,QUOTIENT(H2-1,8)+1) ”，8人为1组，剩余2人归到13组。

使用“=IF(SUM(F:F)=(1+34)*34/2*3,"分组成功","分组失败")”来验证。

最后，由于第13组是3次分配剩余人员组成的，可能存在同省同组的可能性，需要人为验证。

4.2.3 按组归类

把随机分配的小组信息按一定顺序归类。

根据小组号，使用IF条件函数和SMALL最小值函数获得符合条件的选手位置，使用INDIRECT引用函数取得选手的内部编号。

例如：选中A2单元格，输入“=QUOTIENT(ROW()-2,8)+1”选中B2单元格，输入“=INDIRECT("F"&SMALL(IF(I:I=A2,ROW(I:I)),COUNTIF(A$1:A2,A2)))”，按Ctrl+Shift+Enter确认[14]。

4.3 检录信息

4.3.1 检录信息表

按比赛项目、比赛场地、出场顺序整理检录信息表，并进行组内参赛选手比赛赛道随机分配，如图10所示。

图10 检录信息

4.3.2 初赛分组信息

使用LOOKUP查询函数，根据比赛项目、比赛场地、出场顺序，在出场顺序中找出初赛分组。

例如：选中D2单元格，输入“=LOOKUP(1,0/((出场顺序!A:A=C2)*(出场顺序!F:F=B2)),出场顺序!B:B)”。

4.3.3 内部编号信息

根据比赛项目、比赛场地、初赛分组，使用IF条件函数和SMALL最小值函数获得符合条件的选手位置，使用INDIRECT引用函数取得选手的内部编号。

例如：E2单元格中输入“=INDIRECT("初赛分组!B"&SMALL(IF(初赛分组!A:A=D2,ROW(初赛分组!A:A)),COUNTIF(D$2:D2,D2)))”，按Ctrl+Shift+Enter确认。

4.3.4 随机分配赛道

使用RANDBETWEEN随机函数获得一个随机数，使用RANK求出分段排序位置。

例如：选中G2单元格，输入“=RANDBETWEEN(1,9999)”。

选中F2单元格，输入“=RANK(G2,G2:G9)”。

4.3.5 代表队

根据内部编码，使用LOOKUP查找函数，在个人信息中获得代表队值。

例如：选中H2单元格，输入“=LOOKUP(1,0/(E2=个人信息!C:C),个人信息!A:A)。

同样方法可获得选手姓名、比赛编号。

4.4 检录表单制作

使用邮件合并功能[15]按比赛项目、比赛场地、出场顺序制作并打印出检录信息表单，实现步骤如图11所示。

图11 邮件合并功能实现过程

4.4.1 制定检录表单

新建Word文件，如图11所示。使用邮件合并功能，关联数据源。在比赛项目、比赛赛区、出场次序、比赛小组、选手编号、代表队、选手姓名的位置，依次插入对应的域。

图11 检录表设计

4.4.2 建立数据源

按比赛项目、比赛场地、出场顺序、小组，依次把各赛道的选手信息整理到一行。比赛编号设定为“"000"”格式，如图13所示。

图13 检录数据源表设计

4.4.3 获取数据信息

根据比赛项目、比赛场地、出场顺序、初赛分组、比赛赛道，使用LOOKUP查询函数，在检录信息中获得符合条件的选手位置，返回代表队的值。

例如：选中E2单元格，输入“=LOOKUP(1,0/((检录信息!$A:$A=检录数据源!$A2)*(检录信息!$B:$B=检录数据源!$B2)*(检录信息!$C:$C=检录数据源!$C2)*(检录信息!$D:$D=检录数据源!$D2)*(检录信息!$F:$F=--LEFT(检录数据源!E$1,1))),检录信息!$H:$H)”。

同样方法获取选手姓名、比赛编号，调到检录表第一条记录，进行批量打印。

4.5 裁判表单制作

按比赛项目、比赛场地、出场顺序打印检录信息表单。

4.5.1 制定裁判表单

新建Word文件，如图14所示。使用邮件合并功能，关联数据源。在比赛项目、比赛赛区、出场次序、比赛赛道、比赛小组、选手编号的位置，依次插入对应的域。

图14 裁判记录表设计

4.5.2 建立数据源

按比赛项目、比赛场地、出场顺序、初赛小组、比赛赛道、选手编号整理到一行。比赛编号设定为“"000"”格式，如图15所示。

图15 裁判数据源表设计

4.5.3 获取数据信息

根据比赛项目、比赛场地、出场顺序、初赛分组、比赛赛道，使用LOOKUP函数，在检录信息中获得符合条件的选手位置，返回选手编号值。

例如：选中F2单元格，输入“=LOOKUP(1,0/((检录信息!A:A=A2)*(检录信息!B:B=B2)*(C2)*(检录信息!D:D=D2)*(检录信息!F:F=E2)),检录信息!$J:$J)”。

调到裁判记录表第一条记录，进行批量打印。

4.6 成绩资料

4.6.1 初赛成绩表

根据竞赛规则，制定初赛成绩表，如图16所示。

图16 初赛成绩

比赛项目、比赛小组、代表编号可以从检录信息表中直接调取。比赛编号设定为“"000"”格式，比赛用时设定为“"mm:ss.00"”格式。比赛成绩、比赛小组进行数据筛选。

例如：选中A2单元格，输入“=检录信息!A2”

4.6.2 成绩录入

根据比赛项目、比赛小组进行选择。按照裁判表的比赛结果，依次添加比赛用时、违规次数。

4.6.3 初始名次

按照每场比赛用时，使用RANK排序函数确定初始名次。

例如：选中F2单元格，输入“=IF(D2="","",RANK(D2,D2:D9,1))”。

4.6.4 调整排序

使用INDEX函数，根据初始名次通过排名赋分表进行赋分。每违规1次扣除5.1分进行折合。

例如：选中G2单元格，输入“=IF(D2="","",INDEX(排名赋分!$B$1:$I$2,2,F2))”。

选中H2单元格，输入“=IF(D2="","",G2-E2*5.1)”。

4.6.5 最终名次

使用RANK排序函数，根据折合分数确定最终名次，使用INDEX函数，根据最终名次通过排名赋分表进行赋分。

选中I2单元格，输入“=IF(D2="","",RANK(H2,H2:H9))”。

选中I2单元格，输入“=IF(D2="","",INDEX(排名赋分!$B$1:$I$3,3,I2))”。

当前2名并列时要按照规定排序。

4.7 成绩统计

4.7.1 小组成绩

通过对小组组内最终得分进行排序，如图17所示。

图17 小组成绩排序

根据比赛项目、比赛小组、排序名次，使用函数在竞赛资料表中获取符合条件的位置，返回选手编号、比赛用时、最终得分，再根据选手编号，使用函数在个人信息表中，取得代表队、选手姓名。比赛编号设定为“"000"”格式，比赛用时设定为“"mm:ss.00"”格式，I1单元格做比赛项目数据有效性设置，K1单元格做比赛小组数据有效性设置。

例如：选中D4单元格，输入“=INDIRECT("竞赛资料!C"&$H$2+ROW()-10)”。

选中B4单元格，输入“=LOOKUP(1,0/(D4=个人信息!D:D),选手成绩!A:A)”。

4.7.2 个人成绩

在个人信息表中，依次增加理论成绩、第一项、第二项、第三项、第四项、总成绩6列，如图18所示。

图18个人信息

个人总成绩是各项初赛成绩累加构成。理论成绩需要手动输入，各项初赛成绩可以根据比赛项目、比赛编号，使用LOOKUP查询函数，从竞赛资料中获得。个人成绩不进行全体排名。

例如：选中H2单元格，输入“=IFERROR(=IFERROR(LOOKUP(1,0/((竞赛资料!$A:$A="第一项")*(竞赛资料!$C:$C=$D2)),竞赛资料!$D:$D),"")”。

选中L2单元格，输入“=SUM(G2:K2)”。

4.7.3 团体成绩

在代表队信息表中，依次增加团体总分、团体排名2列，如图19所示。

图19 代表队信息

由代表队的3名选手个人成绩构成，可根据代表队在个人信息中，使用SUMIF求和函数进行条件求和，再使用RANK排序函数对团体成绩进行全体排名。

例如：选中C2单元格，输入“=IF(SUMIF(个人信息!A:A,B2,个人信息!L:L)=0,"",SUMIF(个人信息!A:A,B2,个人信息!L:L))”

选中D2单元格，输入“=IF(C2="","",RANK(C2,C:C))”

通过“=IF(SUM(D:D)=COUNT(D:D)*(COUNT(D:D)+1)/2,"排名成功","排名失败")”校验排名是否有重复，如果排名并列，可按照规定进行对比。

4.8 决赛

4.8.1 决赛名单表

根据规则，制定决赛名单表，如图20所示。

图20 决赛名单

4.8.2 组内排名

在初赛分组表中增加总分成绩、组内排名2列，如图21所示。

图21组内排名

根据内部编号，使用LOOKUP查询函数，在个人信息表中，获得总分成绩。使用RANK排序函数按组排序。

例如：选中C2单元格，输入“=LOOKUP(1,0/(B2=个人信息!C:C),个人信息!L:L)”。

选中D2单元格，输入“=RANK(C2,C2:C9)”。

4.8.3 决赛名单

在初赛分组表中，使用IF条件函数判断是否为组内第一，使用SMALL最小值函数获得符合条件的选手位置，使用INDIRECT引用函数取得选手的内部编号。

例如：选中G2单元格，输入“=INDIRECT("初赛分组!B"&SMALL(IF(初赛分组!D:D=1,ROW(初赛分组!D:D)),A2))”，按Ctrl+Shift+Enter确认。

根据内部编号，使用LOOKUP查询函数，在个人信息表中，获得初赛小组、代表队、选手姓名、比赛编号、总分成绩。

例如：选中B2单元格，输入“="第"&LOOKUP(1,0/(G2=初赛分组!B:B),初赛分组!A:A)&"组"”。

5 总结与展望

本文从技能竞赛的实际工作需要出发，充分利用邮件合并功能实现了检录表、裁判员记录表的自动填写、批量打印，极大降低了时间成本、减少了劳动量；充分利用Excel软件完成了竞赛成绩的统计，能够直观反映数据信息，操作便利、方便修改，能够进行数据合理存储，数据存储量大，通过调用函数、编写计算公式，直接得到想要的计算结果，计算过程方便、简洁。计算人员需要结合实际计算内容进行软件操作及相关函数、公式的简单学习，无需学习编程知识，并且Excel的通用性较强，直接下载软件即可，不需要进行适配。如果计算结果存在问题，弹出相应的错误提示，进一步保障计算稳定性。

下一阶段，将组织技术力量研究开发“全国林业有害生物防治员职业技能竞赛成绩管理服务平台”，使用B/S架构，浏览器端(Browser)使用HTML5、CSS、Javascript等作为渲染交互组合，与服务端通过AJAX做异步交互，数据库使用Mysql。将此次竞赛数据作为数据来源，进一步提高数据分析的敏感性和准确性，客观全面地分析竞赛数据，优化竞赛项目、规则，为继续办好竞赛提供科学依据。