全國(guó)林業(yè)有害生物防治員職業(yè)技能競(jìng)賽成績(jī)統(tǒng)計(jì)的設(shè)計(jì)與實(shí)現(xiàn)

姜 璠，韓 陽(yáng)，徐 鈺，唐 健，溫玄燁，崔東陽(yáng)

(國(guó)家林業(yè)和草原局生物災(zāi)害防控中心，遼寧 沈陽(yáng) 110034)

1 引言

林業(yè)有害生物防治員是指從事林業(yè)有害生物預(yù)防、除治作業(yè)及技術(shù)服務(wù)的人員，截至2019年年底，林業(yè)行業(yè)內(nèi)僅存森林消防員和林業(yè)有害生物防治員兩項(xiàng)技能水平評(píng)價(jià)類職業(yè)[1]。2021年3月在福建省泉州市舉辦了首屆全國(guó)林業(yè)有害生物防治員職業(yè)技能競(jìng)賽。不斷優(yōu)化人才培養(yǎng)機(jī)制，提高隊(duì)伍專業(yè)化、技能化水平，加大林業(yè)有害生物防治工作隊(duì)伍建設(shè)，切實(shí)發(fā)揮防治員的作業(yè)施工作用，科學(xué)開(kāi)展防治，提升森林質(zhì)量，著力構(gòu)建健康穩(wěn)定、優(yōu)質(zhì)高效的森林生態(tài)系統(tǒng)。

本次競(jìng)賽活動(dòng)經(jīng)人力資源和社會(huì)保障部批準(zhǔn)，國(guó)家林草局聯(lián)合中國(guó)就業(yè)培訓(xùn)技術(shù)指導(dǎo)中心、中國(guó)農(nóng)林水利氣象工會(huì)主辦，由國(guó)家林草局人才中心、防控中心、福建省林業(yè)局承辦。

2 辦公軟件的應(yīng)用現(xiàn)狀

2.1 Word軟件

Word[2]軟件具有強(qiáng)大的文字編輯及處理功能，是現(xiàn)代辦公中不可缺少的一款辦公軟件。Word中的一些常用技巧，能夠?qū)?fù)雜的事情簡(jiǎn)單化，變得快捷又方便，能顯著提高辦公效率，其中郵件合并是一個(gè)很實(shí)用的功能[3]。它是與Excel配合使用的典型范例，將存儲(chǔ)在Excel數(shù)據(jù)源文件中提取出來(lái)，以域的形式放在主文檔中指定的位置上，把數(shù)據(jù)庫(kù)記錄和文本組合在一起，形成擁有主文檔格式的內(nèi)容且包含有不同數(shù)據(jù)內(nèi)容的新的Word格式的文檔。郵件合并功能具有批處理能力，在競(jìng)賽中處理裁判員記錄表、檢錄表，只要掌握基本的計(jì)算機(jī)知識(shí)和辦公軟件Word、Excel的運(yùn)用，就可在很短的時(shí)間內(nèi)掌握郵件合并功能的應(yīng)用。將該功能應(yīng)用到競(jìng)賽表單制備工作中，能夠輕松、快捷、準(zhǔn)確地完成那些簡(jiǎn)單、重復(fù)性大的工作。

2.2 Excel軟件

隨著科學(xué)技術(shù)及信息技術(shù)在不間斷的創(chuàng)新，信息化發(fā)展是必然趨勢(shì)。Excel[4]軟件作為一種數(shù)據(jù)存儲(chǔ)、處理工具，具有數(shù)據(jù)容量較大[5]的特點(diǎn)，特別是日常辦公中制作表格、生成圖表最為常見(jiàn)[6]。

現(xiàn)對(duì)中國(guó)知網(wǎng)1997～2021年之間的關(guān)于Excel和數(shù)據(jù)統(tǒng)計(jì)的期刊、文獻(xiàn)進(jìn)行分析、統(tǒng)計(jì)和呈現(xiàn)，從而探索近20年Excel與數(shù)據(jù)統(tǒng)計(jì)研究的狀態(tài)和特點(diǎn)，為更好地開(kāi)展數(shù)據(jù)統(tǒng)計(jì)提供參考。通過(guò)CNKI知網(wǎng)在線分析與CiteSpace[7]可視化軟件，直觀表現(xiàn)Excel研究的樣態(tài)，從發(fā)文趨勢(shì)、被引用頻次、學(xué)科分布、關(guān)鍵詞方面總結(jié)出Excel研究的特點(diǎn)。

2.2.1 發(fā)文趨勢(shì)

對(duì)近20年Excel研究發(fā)文量的統(tǒng)計(jì)，能夠清晰查看研究發(fā)文變化的趨勢(shì)，發(fā)文量的變化趨勢(shì)如圖1所示。

圖1 發(fā)文量的變化趨勢(shì)

具體可以分為三個(gè)階段：①波動(dòng)增長(zhǎng)期(1998～2007年)，這一階段的特點(diǎn)是整體波動(dòng)中保持較慢增長(zhǎng)，在這9年間，發(fā)文數(shù)量保持在4～11篇；②快速增長(zhǎng)期(2008～2014年)，這一階段發(fā)文數(shù)量從13篇增加到31篇；③快速衰退期(2014～2021年)，這一階段發(fā)文數(shù)量從31篇減少到14篇，除了2016年出現(xiàn)回升外，其余年份發(fā)文數(shù)量快速減少。

2.2.2 被引用頻次分析

基于CNKI數(shù)據(jù)庫(kù)中的文獻(xiàn)計(jì)量在線分析可知，CNKI中引用頻次最高的論文題為“對(duì)田間試驗(yàn)數(shù)據(jù)的統(tǒng)計(jì)處理方法——格拉布斯準(zhǔn)則和應(yīng)用Excel進(jìn)行方差分析、多重比較(LSD)”[8](表1)，該文在2009年發(fā)表于《中國(guó)西部科技》，該文研究了以農(nóng)業(yè)生產(chǎn)的試驗(yàn)數(shù)據(jù)為例，介紹對(duì)于數(shù)據(jù)異常值的去除，在Excel中通過(guò)“加載宏”自動(dòng)進(jìn)行方差分析，以及利用其函數(shù)以最小顯著差數(shù)進(jìn)行多重比較的統(tǒng)計(jì)方法。

表1 CNKI數(shù)據(jù)庫(kù)中被引頻次前10的文獻(xiàn)

2.2.3 學(xué)科分布分析

基于CNKI數(shù)據(jù)庫(kù)中的文獻(xiàn)計(jì)量在線分析可知[9]，計(jì)算機(jī)軟件與應(yīng)用約占52.97%、醫(yī)藥衛(wèi)生方針政策與法律法規(guī)方針政策、環(huán)境科學(xué)與資源利用、教育學(xué)科方面應(yīng)用較多，在林業(yè)相關(guān)研究中Excel應(yīng)用較少，如圖2所示。

圖2 學(xué)科分布

2.2.4 關(guān)鍵詞的研究

關(guān)鍵詞是論文核心和內(nèi)容精煉的表達(dá)，通過(guò)關(guān)鍵詞分析可以探測(cè)研究的熱點(diǎn)[10]。為達(dá)到全面檢索相關(guān)文獻(xiàn)的目的，檢索時(shí)在中國(guó)知網(wǎng)高級(jí)檢索界面分別輸入“Excel”和“數(shù)據(jù)統(tǒng)計(jì)”進(jìn)行主題檢索，文獻(xiàn)類型選為期刊和碩博論文。通過(guò)查閱檢索到的CNKI文獻(xiàn)的標(biāo)題和摘要，判斷文獻(xiàn)研究?jī)?nèi)容是否與Excel數(shù)據(jù)統(tǒng)計(jì)研究領(lǐng)域有關(guān)，剔除不相關(guān)的文獻(xiàn)后，CNKI文獻(xiàn)221篇，用refwork格式導(dǎo)出并進(jìn)行格式轉(zhuǎn)換成適合CiteSpace分析的基礎(chǔ)數(shù)據(jù)，導(dǎo)入CiteSpace繪制關(guān)鍵詞知識(shí)圖譜，如圖3所示。

通過(guò)對(duì)圖3分析發(fā)現(xiàn)，Excel、數(shù)據(jù)統(tǒng)計(jì)、數(shù)據(jù)處理、數(shù)據(jù)分析、統(tǒng)計(jì)分析等是研究者主要關(guān)注的關(guān)鍵詞[11]，也就是說(shuō)，Excel在進(jìn)行數(shù)據(jù)統(tǒng)計(jì)時(shí)仍然存在一席之地。下面以全國(guó)林業(yè)有害生物防治員職業(yè)技能競(jìng)賽為例進(jìn)行探討。

圖3 關(guān)鍵詞知識(shí)圖譜

3 規(guī)則與需求

競(jìng)賽本著公開(kāi)、公平、公正的原則，在友好的氣氛中進(jìn)行。本次共34個(gè)代表隊(duì)參賽，每個(gè)代表隊(duì)選派3名選手參加比賽。參賽選手競(jìng)賽前一經(jīng)確定不許更換，如若發(fā)現(xiàn)取消該代表隊(duì)的所有成績(jī)。

3.1 競(jìng)賽項(xiàng)目

本次全國(guó)林業(yè)有害生物防治員職業(yè)技能競(jìng)賽包含選手理論考試和技能操作2大項(xiàng)，理論考試從防治員應(yīng)知應(yīng)會(huì)的林業(yè)有害生物監(jiān)測(cè)預(yù)報(bào)、檢疫御災(zāi)、防治減災(zāi)等基本知識(shí)入手，設(shè)計(jì)了195個(gè)知識(shí)點(diǎn)對(duì)選手進(jìn)行全面考察。防治技能操作包括有害生物識(shí)別與標(biāo)本制作、松材線蟲(chóng)病防治、生物和物理防治以及噴霧防治4項(xiàng)。

3.2 競(jìng)賽方式

技能操作分為初賽、決賽2個(gè)階段，根據(jù)現(xiàn)場(chǎng)實(shí)際情況，8人為1組，在兩個(gè)賽區(qū)同時(shí)進(jìn)行競(jìng)賽。賽中出現(xiàn)不可抗原因?qū)е虏荒芾^續(xù)競(jìng)賽，可補(bǔ)賽1次，決賽不允許補(bǔ)賽。

3.3 選手編號(hào)

選手編號(hào)分成代表隊(duì)內(nèi)部編號(hào)和比賽編號(hào)，一個(gè)代表隊(duì)的內(nèi)部編號(hào)是由代表隊(duì)名稱+序號(hào)組成，如：北京1、北京2、北京3。比賽編號(hào)是根據(jù)參賽選手總數(shù)隨機(jī)分配(001-102)。

3.4 選手分組

按照同省不同組原則，對(duì)102個(gè)代表隊(duì)內(nèi)部編號(hào)隨機(jī)分組，8名為1組，共分成13個(gè)組，其中第112組為8人組、第13組為6人組。

3.5 賽區(qū)分配

競(jìng)賽共分兩個(gè)賽區(qū)，1～6組在第一賽區(qū)，7～13組在第二賽區(qū)。

3.6 出場(chǎng)順序

每個(gè)競(jìng)技項(xiàng)目在第一賽區(qū)進(jìn)行6輪競(jìng)賽、第二賽區(qū)進(jìn)行7輪競(jìng)賽。每個(gè)競(jìng)技項(xiàng)目小組出場(chǎng)順序按最大限度均勻隨機(jī)分配。

3.7 選手賽道

初賽中各小組的8名選手隨機(jī)分配賽道，決賽中各小組的8名選手根據(jù)初賽分值擇優(yōu)分配。

3.8 評(píng)判標(biāo)準(zhǔn)

本項(xiàng)比賽以競(jìng)速和競(jìng)質(zhì)相結(jié)合的方式判定比賽名次、給出項(xiàng)目得分。

3.8.1 初始名次確定

以選手到達(dá)終點(diǎn)的先后順序排定本組比賽初始名次，同時(shí)到達(dá)者名次并列并占用其下級(jí)名額。

3.8.2 完成質(zhì)量評(píng)判

發(fā)生違規(guī)行為者，每發(fā)生一次初始名次后移1名。

3.8.3 最終名次確定

經(jīng)記錄違規(guī)行為次數(shù)，對(duì)初始名次進(jìn)行整體調(diào)整后，形成最終名次并賦分。

3.9 賦分標(biāo)準(zhǔn)

3.9.1 初始名次賦分

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

3.9.2 最終名次賦分

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

3.10 成績(jī)

3.10.1 個(gè)人成績(jī)

選手初賽總成績(jī)由各項(xiàng)初賽成績(jī)累加構(gòu)成。個(gè)人初賽成績(jī)不進(jìn)行全體排名，13個(gè)小組的第1名直接進(jìn)入決賽，第2名中成績(jī)最好的前3名進(jìn)入決賽。成績(jī)并列的按個(gè)人比賽總用時(shí)調(diào)整排名，繼續(xù)并列的依次按第二、四、三、一項(xiàng)目比較選手用時(shí)，仍無(wú)法區(qū)分的最終抽簽確定順序。

3.10.2 團(tuán)體成績(jī)

團(tuán)體成績(jī)由代表隊(duì)選手的初賽總成績(jī)累加構(gòu)成。成績(jī)并列的比較代表隊(duì)選手比賽總用時(shí)，繼續(xù)并列的依次按第二、四、三、一項(xiàng)目比較代表隊(duì)選手總用時(shí)，仍無(wú)法區(qū)分的最終抽簽確定順序，團(tuán)體成績(jī)不含決賽成績(jī)。

3.11 個(gè)人決賽

3.11.1 決賽選手產(chǎn)生及初始名次排序

決賽由16名選手組成，13個(gè)小組的第1名直接闖進(jìn)決賽，第2名中成績(jī)排名前3的進(jìn)入決賽；成績(jī)并列的比較技能競(jìng)賽總用時(shí)，繼續(xù)并列的依次按技能競(jìng)賽第二、四、三、一項(xiàng)比較選手用時(shí)，仍無(wú)法區(qū)分的最終抽簽確定順序。產(chǎn)生1～16名決賽初始名次。

3.11.2 決賽分組和最終決賽名次

決賽分兩組進(jìn)行，個(gè)人總分前8名組成決賽第1組，爭(zhēng)奪大賽個(gè)人1～8名；個(gè)人總分后8名組成決賽第2組，爭(zhēng)奪大賽個(gè)人9～16名。

3.12 表單制備

比賽前按賽區(qū)、出場(chǎng)順序、小組、代表隊(duì)、人員、編號(hào)、賽道，制備檢錄表；按賽區(qū)、出場(chǎng)順序、小組、編號(hào)、賽道，為裁判員提供所有裁判表單及備用表單。

小組比賽結(jié)束后要實(shí)時(shí)為播音組統(tǒng)計(jì)并打印小組個(gè)人成績(jī)及排名。每個(gè)項(xiàng)目比賽結(jié)束后要及時(shí)為播音組統(tǒng)計(jì)并打印團(tuán)體成績(jī)及排名。初賽結(jié)束后要為組委會(huì)統(tǒng)計(jì)并打印準(zhǔn)確的個(gè)人成績(jī)、團(tuán)體成績(jī)以及排名信息。

4 設(shè)計(jì)與實(shí)現(xiàn)

由于時(shí)間緊、任務(wù)重，資金緊張，考慮到技術(shù)可行性、網(wǎng)絡(luò)安全性及系統(tǒng)穩(wěn)定性等方面因素，為更加直觀、圓滿完成這次計(jì)分任務(wù)，通過(guò)Excel軟件快速實(shí)現(xiàn)成績(jī)結(jié)果的統(tǒng)計(jì)[12]，具體從以下幾方面進(jìn)行敘述：

4.1 基礎(chǔ)信息

4.1.1 個(gè)人信息

通過(guò)參賽選手信息匯總表，獲取代表隊(duì)、選手姓名。如圖4所示。

圖4 個(gè)人信息

(1)內(nèi)部編號(hào)。利用RANDBETWEEN隨機(jī)函數(shù)為每位選手賦予一個(gè)隨機(jī)數(shù)，利用RANK排序函數(shù)確定選手在代表隊(duì)的位置，最后生成內(nèi)部編號(hào)。這樣可以使得每位選手獲得代表隊(duì)內(nèi)部1號(hào)的幾率相等。

例如：選中C2單元格，輸入“= A2&RANK(E2,E2:E4)”。

(2)比賽編號(hào)。利用已經(jīng)生成的隨機(jī)數(shù)，確定每位選手在全體選手中的位置，設(shè)置單元格為“"000"”格式即為比賽編號(hào)。可使用SUM求和函數(shù)和IF條件函數(shù)[13]判斷比賽編號(hào)是否有重復(fù)。使得每位選手在全體選手中獲得001號(hào)的幾率相等。

例如：選中D2單元格中輸入“=RANK(E2,E:E)”。

使用“=IF(SUM(D:D)=5253,"分配成功","分配失敗")”來(lái)驗(yàn)證。

4.1.2 代表隊(duì)信息

使用INDIRECT引用函數(shù)從個(gè)人信息中獲得，如圖5所示。

圖5 代表隊(duì)信息

例如：選中B2單元格中輸入“=INDIRECT("個(gè)人信息!A"&ROW()*3-2)”。

4.1.3 出場(chǎng)順序

以6階拉丁方為基礎(chǔ)，適當(dāng)調(diào)整，平衡各隊(duì)的出場(chǎng)順序，如圖6所示。

圖6 出場(chǎng)順序

4.1.4 排名賦分

根據(jù)賦分標(biāo)準(zhǔn)，制定排名賦分表，如圖7所示。

圖7 排名賦分

4.1.5 決賽賽道

根據(jù)決賽賽道分配原則，制定決賽賽道分配表，如圖8所示。

圖8 決賽賽道分配

4.2 初賽分組

4.2.1 初賽分組信息表

根據(jù)規(guī)則要求，制定初賽分組信息表，如圖9所示。

圖9 初賽分組信息

4.2.2 隨機(jī)分組

為了達(dá)到同省不同組的結(jié)果，采取每隊(duì)抽出1名選手進(jìn)行分組的策略。各代表隊(duì)選取1名，利用RANDBETWEEN隨機(jī)函數(shù)給每位選手賦予一個(gè)隨機(jī)數(shù)，利用RANK排序函數(shù)確定分段中的位置，利用QUOTIENT取整函數(shù)分組。可以使用SUM求和函數(shù)、IF條件函數(shù)判斷是否有重復(fù)分配。

例如：選中F2單元格，輸入“=代表隊(duì)!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,"分組成功","分組失敗")”來(lái)驗(yàn)證。

最后，由于第13組是3次分配剩余人員組成的，可能存在同省同組的可能性，需要人為驗(yàn)證。

4.2.3 按組歸類

把隨機(jī)分配的小組信息按一定順序歸類。

根據(jù)小組號(hào)，使用IF條件函數(shù)和SMALL最小值函數(shù)獲得符合條件的選手位置，使用INDIRECT引用函數(shù)取得選手的內(nèi)部編號(hào)。

例如：選中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確認(rèn)[14]。

4.3 檢錄信息

4.3.1 檢錄信息表

按比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序整理檢錄信息表，并進(jìn)行組內(nèi)參賽選手比賽賽道隨機(jī)分配，如圖10所示。

圖10 檢錄信息

4.3.2 初賽分組信息

使用LOOKUP查詢函數(shù)，根據(jù)比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序，在出場(chǎng)順序中找出初賽分組。

例如：選中D2單元格，輸入“=LOOKUP(1,0/((出場(chǎng)順序!A:A=C2)*(出場(chǎng)順序!F:F=B2)),出場(chǎng)順序!B:B)”。

4.3.3 內(nèi)部編號(hào)信息

根據(jù)比賽項(xiàng)目、比賽場(chǎng)地、初賽分組，使用IF條件函數(shù)和SMALL最小值函數(shù)獲得符合條件的選手位置，使用INDIRECT引用函數(shù)取得選手的內(nèi)部編號(hào)。

例如：E2單元格中輸入“=INDIRECT("初賽分組!B"&SMALL(IF(初賽分組!A:A=D2,ROW(初賽分組!A:A)),COUNTIF(D$2:D2,D2)))”，按Ctrl+Shift+Enter確認(rèn)。

4.3.4 隨機(jī)分配賽道

使用RANDBETWEEN隨機(jī)函數(shù)獲得一個(gè)隨機(jī)數(shù)，使用RANK求出分段排序位置。

例如：選中G2單元格，輸入“=RANDBETWEEN(1,9999)”。

選中F2單元格，輸入“=RANK(G2,G2:G9)”。

4.3.5 代表隊(duì)

根據(jù)內(nèi)部編碼，使用LOOKUP查找函數(shù)，在個(gè)人信息中獲得代表隊(duì)值。

例如：選中H2單元格，輸入“=LOOKUP(1,0/(E2=個(gè)人信息!C:C),個(gè)人信息!A:A)。

同樣方法可獲得選手姓名、比賽編號(hào)。

4.4 檢錄表單制作

使用郵件合并功能[15]按比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序制作并打印出檢錄信息表單，實(shí)現(xiàn)步驟如圖11所示。

圖11 郵件合并功能實(shí)現(xiàn)過(guò)程

4.4.1 制定檢錄表單

新建Word文件，如圖11所示。使用郵件合并功能，關(guān)聯(lián)數(shù)據(jù)源。在比賽項(xiàng)目、比賽賽區(qū)、出場(chǎng)次序、比賽小組、選手編號(hào)、代表隊(duì)、選手姓名的位置，依次插入對(duì)應(yīng)的域。

圖11 檢錄表設(shè)計(jì)

4.4.2 建立數(shù)據(jù)源

按比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序、小組，依次把各賽道的選手信息整理到一行。比賽編號(hào)設(shè)定為“"000"”格式，如圖13所示。

圖13 檢錄數(shù)據(jù)源表設(shè)計(jì)

4.4.3 獲取數(shù)據(jù)信息

根據(jù)比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序、初賽分組、比賽賽道，使用LOOKUP查詢函數(shù)，在檢錄信息中獲得符合條件的選手位置，返回代表隊(duì)的值。

例如：選中E2單元格，輸入“=LOOKUP(1,0/((檢錄信息!$A:$A=檢錄數(shù)據(jù)源!$A2)*(檢錄信息!$B:$B=檢錄數(shù)據(jù)源!$B2)*(檢錄信息!$C:$C=檢錄數(shù)據(jù)源!$C2)*(檢錄信息!$D:$D=檢錄數(shù)據(jù)源!$D2)*(檢錄信息!$F:$F=--LEFT(檢錄數(shù)據(jù)源!E$1,1))),檢錄信息!$H:$H)”。

同樣方法獲取選手姓名、比賽編號(hào)，調(diào)到檢錄表第一條記錄，進(jìn)行批量打印。

4.5 裁判表單制作

按比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序打印檢錄信息表單。

4.5.1 制定裁判表單

新建Word文件，如圖14所示。使用郵件合并功能，關(guān)聯(lián)數(shù)據(jù)源。在比賽項(xiàng)目、比賽賽區(qū)、出場(chǎng)次序、比賽賽道、比賽小組、選手編號(hào)的位置，依次插入對(duì)應(yīng)的域。

圖14 裁判記錄表設(shè)計(jì)

4.5.2 建立數(shù)據(jù)源

按比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序、初賽小組、比賽賽道、選手編號(hào)整理到一行。比賽編號(hào)設(shè)定為“"000"”格式，如圖15所示。

圖15 裁判數(shù)據(jù)源表設(shè)計(jì)

4.5.3 獲取數(shù)據(jù)信息

根據(jù)比賽項(xiàng)目、比賽場(chǎng)地、出場(chǎng)順序、初賽分組、比賽賽道，使用LOOKUP函數(shù)，在檢錄信息中獲得符合條件的選手位置，返回選手編號(hào)值。

例如：選中F2單元格，輸入“=LOOKUP(1,0/((檢錄信息!A:A=A2)*(檢錄信息!B:B=B2)*(C2)*(檢錄信息!D:D=D2)*(檢錄信息!F:F=E2)),檢錄信息!$J:$J)”。

調(diào)到裁判記錄表第一條記錄，進(jìn)行批量打印。

4.6 成績(jī)資料

4.6.1 初賽成績(jī)表

根據(jù)競(jìng)賽規(guī)則，制定初賽成績(jī)表，如圖16所示。

圖16 初賽成績(jī)

比賽項(xiàng)目、比賽小組、代表編號(hào)可以從檢錄信息表中直接調(diào)取。比賽編號(hào)設(shè)定為“"000"”格式，比賽用時(shí)設(shè)定為“"mm:ss.00"”格式。比賽成績(jī)、比賽小組進(jìn)行數(shù)據(jù)篩選。

例如：選中A2單元格，輸入“=檢錄信息!A2”

4.6.2 成績(jī)錄入

根據(jù)比賽項(xiàng)目、比賽小組進(jìn)行選擇。按照裁判表的比賽結(jié)果，依次添加比賽用時(shí)、違規(guī)次數(shù)。

4.6.3 初始名次

按照每場(chǎng)比賽用時(shí)，使用RANK排序函數(shù)確定初始名次。

例如：選中F2單元格，輸入“=IF(D2="","",RANK(D2,D2:D9,1))”。

4.6.4 調(diào)整排序

使用INDEX函數(shù)，根據(jù)初始名次通過(guò)排名賦分表進(jìn)行賦分。每違規(guī)1次扣除5.1分進(jìn)行折合。

例如：選中G2單元格，輸入“=IF(D2="","",INDEX(排名賦分!$B$1:$I$2,2,F2))”。

選中H2單元格，輸入“=IF(D2="","",G2-E2*5.1)”。

4.6.5 最終名次

使用RANK排序函數(shù)，根據(jù)折合分?jǐn)?shù)確定最終名次，使用INDEX函數(shù)，根據(jù)最終名次通過(guò)排名賦分表進(jìn)行賦分。

選中I2單元格，輸入“=IF(D2="","",RANK(H2,H2:H9))”。

選中I2單元格，輸入“=IF(D2="","",INDEX(排名賦分!$B$1:$I$3,3,I2))”。

當(dāng)前2名并列時(shí)要按照規(guī)定排序。

4.7 成績(jī)統(tǒng)計(jì)

4.7.1 小組成績(jī)

通過(guò)對(duì)小組組內(nèi)最終得分進(jìn)行排序，如圖17所示。

圖17 小組成績(jī)排序

根據(jù)比賽項(xiàng)目、比賽小組、排序名次，使用函數(shù)在競(jìng)賽資料表中獲取符合條件的位置，返回選手編號(hào)、比賽用時(shí)、最終得分，再根據(jù)選手編號(hào)，使用函數(shù)在個(gè)人信息表中，取得代表隊(duì)、選手姓名。比賽編號(hào)設(shè)定為“"000"”格式，比賽用時(shí)設(shè)定為“"mm:ss.00"”格式，I1單元格做比賽項(xiàng)目數(shù)據(jù)有效性設(shè)置，K1單元格做比賽小組數(shù)據(jù)有效性設(shè)置。

例如：選中D4單元格，輸入“=INDIRECT("競(jìng)賽資料!C"&$H$2+ROW()-10)”。

選中B4單元格，輸入“=LOOKUP(1,0/(D4=個(gè)人信息!D:D),選手成績(jī)!A:A)”。

4.7.2 個(gè)人成績(jī)

在個(gè)人信息表中，依次增加理論成績(jī)、第一項(xiàng)、第二項(xiàng)、第三項(xiàng)、第四項(xiàng)、總成績(jī)6列，如圖18所示。

圖18個(gè)人信息

個(gè)人總成績(jī)是各項(xiàng)初賽成績(jī)累加構(gòu)成。理論成績(jī)需要手動(dòng)輸入，各項(xiàng)初賽成績(jī)可以根據(jù)比賽項(xiàng)目、比賽編號(hào)，使用LOOKUP查詢函數(shù)，從競(jìng)賽資料中獲得。個(gè)人成績(jī)不進(jìn)行全體排名。

例如：選中H2單元格，輸入“=IFERROR(=IFERROR(LOOKUP(1,0/((競(jìng)賽資料!$A:$A="第一項(xiàng)")*(競(jìng)賽資料!$C:$C=$D2)),競(jìng)賽資料!$D:$D),"")”。

選中L2單元格，輸入“=SUM(G2:K2)”。

4.7.3 團(tuán)體成績(jī)

在代表隊(duì)信息表中，依次增加團(tuán)體總分、團(tuán)體排名2列，如圖19所示。

圖19 代表隊(duì)信息

由代表隊(duì)的3名選手個(gè)人成績(jī)構(gòu)成，可根據(jù)代表隊(duì)在個(gè)人信息中，使用SUMIF求和函數(shù)進(jìn)行條件求和，再使用RANK排序函數(shù)對(duì)團(tuán)體成績(jī)進(jìn)行全體排名。

例如：選中C2單元格，輸入“=IF(SUMIF(個(gè)人信息!A:A,B2,個(gè)人信息!L:L)=0,"",SUMIF(個(gè)人信息!A:A,B2,個(gè)人信息!L:L))”

選中D2單元格，輸入“=IF(C2="","",RANK(C2,C:C))”

通過(guò)“=IF(SUM(D:D)=COUNT(D:D)*(COUNT(D:D)+1)/2,"排名成功","排名失敗")”校驗(yàn)排名是否有重復(fù)，如果排名并列，可按照規(guī)定進(jìn)行對(duì)比。

4.8 決賽

4.8.1 決賽名單表

根據(jù)規(guī)則，制定決賽名單表，如圖20所示。

圖20 決賽名單

4.8.2 組內(nèi)排名

在初賽分組表中增加總分成績(jī)、組內(nèi)排名2列，如圖21所示。

圖21組內(nèi)排名

根據(jù)內(nèi)部編號(hào)，使用LOOKUP查詢函數(shù)，在個(gè)人信息表中，獲得總分成績(jī)。使用RANK排序函數(shù)按組排序。

例如：選中C2單元格，輸入“=LOOKUP(1,0/(B2=個(gè)人信息!C:C),個(gè)人信息!L:L)”。

選中D2單元格，輸入“=RANK(C2,C2:C9)”。

4.8.3 決賽名單

在初賽分組表中，使用IF條件函數(shù)判斷是否為組內(nèi)第一，使用SMALL最小值函數(shù)獲得符合條件的選手位置，使用INDIRECT引用函數(shù)取得選手的內(nèi)部編號(hào)。

例如：選中G2單元格，輸入“=INDIRECT("初賽分組!B"&SMALL(IF(初賽分組!D:D=1,ROW(初賽分組!D:D)),A2))”，按Ctrl+Shift+Enter確認(rèn)。

根據(jù)內(nèi)部編號(hào)，使用LOOKUP查詢函數(shù)，在個(gè)人信息表中，獲得初賽小組、代表隊(duì)、選手姓名、比賽編號(hào)、總分成績(jī)。

例如：選中B2單元格，輸入“="第"&LOOKUP(1,0/(G2=初賽分組!B:B),初賽分組!A:A)&"組"”。

5 總結(jié)與展望

本文從技能競(jìng)賽的實(shí)際工作需要出發(fā)，充分利用郵件合并功能實(shí)現(xiàn)了檢錄表、裁判員記錄表的自動(dòng)填寫(xiě)、批量打印，極大降低了時(shí)間成本、減少了勞動(dòng)量；充分利用Excel軟件完成了競(jìng)賽成績(jī)的統(tǒng)計(jì)，能夠直觀反映數(shù)據(jù)信息，操作便利、方便修改，能夠進(jìn)行數(shù)據(jù)合理存儲(chǔ)，數(shù)據(jù)存儲(chǔ)量大，通過(guò)調(diào)用函數(shù)、編寫(xiě)計(jì)算公式，直接得到想要的計(jì)算結(jié)果，計(jì)算過(guò)程方便、簡(jiǎn)潔。計(jì)算人員需要結(jié)合實(shí)際計(jì)算內(nèi)容進(jìn)行軟件操作及相關(guān)函數(shù)、公式的簡(jiǎn)單學(xué)習(xí)，無(wú)需學(xué)習(xí)編程知識(shí)，并且Excel的通用性較強(qiáng)，直接下載軟件即可，不需要進(jìn)行適配。如果計(jì)算結(jié)果存在問(wèn)題，彈出相應(yīng)的錯(cuò)誤提示，進(jìn)一步保障計(jì)算穩(wěn)定性。

下一階段，將組織技術(shù)力量研究開(kāi)發(fā)“全國(guó)林業(yè)有害生物防治員職業(yè)技能競(jìng)賽成績(jī)管理服務(wù)平臺(tái)”，使用B/S架構(gòu)，瀏覽器端(Browser)使用HTML5、CSS、Javascript等作為渲染交互組合，與服務(wù)端通過(guò)AJAX做異步交互，數(shù)據(jù)庫(kù)使用Mysql。將此次競(jìng)賽數(shù)據(jù)作為數(shù)據(jù)來(lái)源，進(jìn)一步提高數(shù)據(jù)分析的敏感性和準(zhǔn)確性，客觀全面地分析競(jìng)賽數(shù)據(jù)，優(yōu)化競(jìng)賽項(xiàng)目、規(guī)則，為繼續(xù)辦好競(jìng)賽提供科學(xué)依據(jù)。