全國林業有害生物防治員職業技能競賽成績統計的設計與實現

姜 璠，韓 陽，徐 鈺，唐 健，溫玄燁，崔東陽

(國家林業和草原局生物災害防控中心，遼寧 沈陽 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。將此次競賽數據作為數據來源，進一步提高數據分析的敏感性和準確性，客觀全面地分析競賽數據，優化競賽項目、規則，為繼續辦好競賽提供科學依據。