基于Excel的軍事體育考核成績(jī)統(tǒng)計(jì)表的設(shè)計(jì)與實(shí)現(xiàn)

李騰偉，宋 影，茍偉強(qiáng)，彭振華，陳向陽(yáng)

前言

2018年2月，經(jīng)中央軍委批準(zhǔn)，新一代《軍事體育訓(xùn)練大綱》頒布施行，為各級(jí)組織實(shí)施軍事體育訓(xùn)練提供了基本依據(jù)[1]。2020年1月，大綱進(jìn)行滾動(dòng)更新，新增了文職人員、傷病人員考核內(nèi)容與標(biāo)準(zhǔn)，更加符合訓(xùn)練實(shí)際，適應(yīng)實(shí)戰(zhàn)需要。大綱施行四年來(lái)，官兵軍事體育訓(xùn)練熱情高漲，體質(zhì)明顯增強(qiáng)，有效推動(dòng)了部隊(duì)?wèi)?zhàn)斗力提高。

根據(jù)大綱規(guī)定，不同人員類別、不同性別、不同年齡人員涉及的考核課目和考核標(biāo)準(zhǔn)不盡相同。在日常組訓(xùn)過(guò)程中，組訓(xùn)骨干需要根據(jù)人員考核成績(jī)，核算出課目得分或等級(jí)，并最終完成個(gè)人軍事體育成績(jī)綜合評(píng)定。如果采用人工方式統(tǒng)計(jì)考核成績(jī)，存在工作量大、出錯(cuò)率高、耗時(shí)費(fèi)力、效率低下等缺點(diǎn)。目前，部分專家學(xué)者在如何使用自動(dòng)化工具來(lái)提高考核工作效率方面做了一些研究。一些學(xué)者[2-6]利用Excel軟件，通過(guò)應(yīng)用 VLOOKUP 等函數(shù)和公式，實(shí)現(xiàn)了學(xué)生體育成績(jī)的自動(dòng)化統(tǒng)計(jì)。閆麗麗[7]利用Excel函數(shù)實(shí)現(xiàn)了當(dāng)時(shí)軍人體能考核課目成績(jī)的自動(dòng)化統(tǒng)計(jì)。新大綱頒發(fā)后，柳孔明等[8]利用C#語(yǔ)言開(kāi)發(fā)了軍人體能考核成績(jī)計(jì)算系統(tǒng)，CharlesWYQ[9]和Maker張[10]在博客上分享了如何利用Python和Excel完成軍事體育考核成績(jī)的自動(dòng)化計(jì)算與統(tǒng)計(jì)，三位學(xué)者的研究在一定程度上解決了人工統(tǒng)計(jì)成績(jī)效率低下的問(wèn)題，但仍然存在一些不足：一是大綱內(nèi)容涵蓋不全。三位學(xué)者的研究?jī)H涉及通用課目考核，對(duì)大綱要求的專項(xiàng)課目、傷病人員和文職人員考核內(nèi)容并無(wú)涉及，沒(méi)有完全對(duì)標(biāo)大綱。二是開(kāi)發(fā)維護(hù)難度較大。利用C#、Python等軟件開(kāi)發(fā)成績(jī)統(tǒng)計(jì)系統(tǒng)，需要具備專業(yè)的編程知識(shí)，普通官兵不具備開(kāi)發(fā)能力。同時(shí)，當(dāng)大綱內(nèi)容標(biāo)準(zhǔn)發(fā)生變化需要修改系統(tǒng)時(shí)，普通官兵也無(wú)法進(jìn)行維護(hù)更新。三是推廣使用稍有不便。開(kāi)發(fā)出的成績(jī)統(tǒng)計(jì)系統(tǒng)是一個(gè)可執(zhí)行程序，程序的正常運(yùn)行需要具備一定的條件。目前，官兵計(jì)算機(jī)的配置情況千差萬(wàn)別，當(dāng)計(jì)算機(jī)缺少運(yùn)行庫(kù)等依賴文件或者程序出現(xiàn)bug時(shí)，成績(jī)統(tǒng)計(jì)系統(tǒng)將無(wú)法正常運(yùn)行，影響用戶體驗(yàn)。此外，市場(chǎng)上還存在著一些軍事體育考核系統(tǒng)產(chǎn)品，它通過(guò)設(shè)備進(jìn)行身份識(shí)別，能自動(dòng)完成考核數(shù)據(jù)采集和成績(jī)?cè)u(píng)定，但是仍然僅限于組織通用課目考核，而且造價(jià)較貴。因此，對(duì)于日常軍事體育組訓(xùn)來(lái)說(shuō)，普通官兵仍然需要通過(guò)Excel表格來(lái)實(shí)現(xiàn)考核成績(jī)的登記與統(tǒng)計(jì)。所以，能否立足于Excel軟件設(shè)計(jì)出一套完全滿足大綱要求，涵蓋所有類別軍隊(duì)人員的考核成績(jī)統(tǒng)計(jì)表，來(lái)方便官兵進(jìn)行軍事體育組訓(xùn)與考核。本文基于Excel軟件設(shè)計(jì)實(shí)現(xiàn)了一套軍事體育考核成績(jī)統(tǒng)計(jì)表。當(dāng)輸入人員信息和考核成績(jī)后，統(tǒng)計(jì)表能自動(dòng)更新課目及格標(biāo)準(zhǔn)并自動(dòng)完成成績(jī)?cè)u(píng)定，具有完全對(duì)標(biāo)大綱、開(kāi)發(fā)維護(hù)高效和推廣使用方便等優(yōu)點(diǎn)。

1 基本情況

根據(jù)大綱[11]規(guī)定，結(jié)合單位實(shí)際，我部(63620部隊(duì))軍人考核人員依據(jù)崗位特點(diǎn)區(qū)分為“二類人員”和“三類人員”兩類，人員按照身體情況區(qū)分為“正常人員”和“傷病人員”兩類，文職考核人員按照入職時(shí)間區(qū)分為“新招聘文職”和“文職人員”兩類，不同類別人員在考核科目和考核標(biāo)準(zhǔn)上有所區(qū)別。關(guān)于軍事體育考核的內(nèi)容與標(biāo)準(zhǔn)，讀者可參閱文獻(xiàn)[12，13]進(jìn)行了解。下面詳細(xì)介紹軍事體育考核成績(jī)統(tǒng)計(jì)表的設(shè)計(jì)思想與實(shí)現(xiàn)方法。

軍事體育考核成績(jī)統(tǒng)計(jì)表包含12個(gè)工作表，其中3個(gè)工作表(正常人員通用課目成績(jī)表、傷病人員通用課目成績(jī)表和專項(xiàng)課目成績(jī)表)為考核成績(jī)工作表，用于登記統(tǒng)計(jì)人員考核數(shù)據(jù)；其他9個(gè)工作表(通用專項(xiàng)課目及格標(biāo)準(zhǔn)，通用課目一、二、三、四考核標(biāo)準(zhǔn)，文職人員考核標(biāo)準(zhǔn)，傷病人員考核標(biāo)準(zhǔn)，專項(xiàng)課目考核標(biāo)準(zhǔn)和綜合評(píng)定標(biāo)準(zhǔn)等)為考核標(biāo)準(zhǔn)工作表，其數(shù)據(jù)來(lái)源于大綱規(guī)定，不可更改，作為3個(gè)考核成績(jī)工作表的數(shù)據(jù)引用源使用。出于保密需要，本文不涉及軍事體育考核的具體課目名稱及考核標(biāo)準(zhǔn)數(shù)據(jù)，主要與讀者就軍事體育考核成績(jī)統(tǒng)計(jì)表的設(shè)計(jì)思想與實(shí)現(xiàn)方法進(jìn)行交流，為便于理解，在公式中用單元格的列名稱來(lái)表示需要引用的單元格內(nèi)容，同時(shí)受制于篇幅限制，本文對(duì)成績(jī)統(tǒng)計(jì)表涉及的LOOKUP、VLOOKUP、INDEX、MATCH、IF、AND、OR、ISERROR、ROUNDUP、ROUNDDOWN、MAX、SUM、MOD、COUNTIF、COUNTIFS、FIND和DATEDIF等17個(gè)函數(shù)不作詳細(xì)介紹，感興趣的讀者可查閱Excel相關(guān)書(shū)籍[14]。

2 正常人員通用課目成績(jī)表

在正常人員通用課目成績(jī)表中，要實(shí)現(xiàn)輸入性別、人員類別和年齡等基本信息后，可自動(dòng)更新所有課目的及格標(biāo)準(zhǔn)，在輸入人員考核成績(jī)后，能自動(dòng)完成成績(jī)匯總及評(píng)定的功能。正常人員通用課目成績(jī)表的表格設(shè)計(jì)如表1所示，包括基本信息、考核課目、成績(jī)匯總和綜合評(píng)定四部分內(nèi)容。

表1 正常人員通用課目成績(jī)表的表格設(shè)計(jì)

2.1 基本信息

表格的基本信息部分包含姓名、性別、單位、人員類別、出生日期、考核日期和年齡等7列內(nèi)容。其中，姓名、性別、單位三列由人員自行輸入；人員類別提供4個(gè)下拉選項(xiàng)(“二類”“三類”“文職”“新招聘文職”，可通過(guò)設(shè)置單元格的數(shù)據(jù)有效性實(shí)現(xiàn))，供考核人員點(diǎn)選；出生日期和考核日期按照“2020-1-1”的格式輸入，精確至天；年齡由公式DATEDIF(出生日期，考核日期，“y”)自動(dòng)計(jì)算得出。

2.2 考核課目

二類、三類人員參加軍事體育考核的考核內(nèi)容包括體型和四個(gè)通用訓(xùn)練課目，文職人員的考核內(nèi)容包括體型和三個(gè)通用訓(xùn)練課目。

體型BMI(Body Mass Index，身體質(zhì)量指數(shù))項(xiàng)包含3列，分別是上限、成績(jī)和評(píng)價(jià)。根據(jù)大綱規(guī)定，不同年齡、不同性別人員的BMI下限值相同，BMI上限值不同，如表2所示。利用公式可查找出不同性別、不同年齡人員的BMI上限值，公式為=VLOOKUP(性別，體型BMI上限值統(tǒng)計(jì)表，1+MATCH(年齡，{0，25，30，40，50})，0)，公式中MATCH(年齡，{0，25，30，40，50})可根據(jù)不同年齡值匹配出相應(yīng)的年齡組別。成績(jī)可由具備BMI測(cè)量功能的身高體重儀測(cè)出。評(píng)價(jià)列是將BMI測(cè)量值和BMI上、下限值進(jìn)行比較分析，如果BMI值為0，則計(jì)為缺考，當(dāng)BMI值位于規(guī)定范圍內(nèi)時(shí)，評(píng)價(jià)結(jié)果為合格，否則為不合格，公式為=IF(成績(jī)=0，“缺考”，IF(AND(成績(jī)>=BMI下限，成績(jī)<=BMI上限)，“合格”“不合格)。

表2 體型BMI上限值統(tǒng)計(jì)表

四個(gè)通用訓(xùn)練課目的設(shè)計(jì)思想與實(shí)現(xiàn)方法基本相同，這里以課目一為例進(jìn)行說(shuō)明。課目一包含3列，分別是標(biāo)準(zhǔn)、成績(jī)和評(píng)價(jià)。根據(jù)大綱規(guī)定，不同性別、不同人員類別、不同年齡人員的考核標(biāo)準(zhǔn)不同，如表3所示。標(biāo)準(zhǔn)列可由公式直接計(jì)算得出，公式為=VLOOKUP(人員類別，IF(性別=“男性”，通用課目一男性所對(duì)應(yīng)的及格標(biāo)準(zhǔn)區(qū)域，通用課目一女性所對(duì)應(yīng)的及格標(biāo)準(zhǔn)區(qū)域)，1+ROUNDUP(MAX(年齡-21，3)/3，0)，0)，其中ROUNDUP(MAX(年齡-21，3)/3，0)函數(shù)可實(shí)現(xiàn)類似于MATCH函數(shù)的年齡與年齡組別對(duì)應(yīng)功能。成績(jī)列可直接輸入，當(dāng)考核課目為計(jì)時(shí)類課目時(shí)，采用直接輸入數(shù)字代替時(shí)間的方式記錄成績(jī)(如輸入1340代替13分40秒)，方便用戶錄入和比較。評(píng)價(jià)列是將成績(jī)值和標(biāo)準(zhǔn)值進(jìn)行比較分析，對(duì)于某些課目，標(biāo)準(zhǔn)值為下限，對(duì)于某些課目，標(biāo)準(zhǔn)值為上限。這里以標(biāo)準(zhǔn)值為下限進(jìn)行舉例，公式為=IF(成績(jī)=0，“缺考”，IF(成績(jī)<標(biāo)準(zhǔn)，“不合格”，“合格”))。由于文職人員不考核通用課目四，因此在通用課目四男性女性文職所對(duì)應(yīng)的及格標(biāo)準(zhǔn)區(qū)域和評(píng)價(jià)區(qū)域均用“——”填寫(xiě)，表示不可用。

表3 男性通用課目一及格標(biāo)準(zhǔn)示意表

2.3 成績(jī)匯總

成績(jī)匯總包含通用課目成績(jī)和專項(xiàng)課目成績(jī)兩項(xiàng)內(nèi)容，在通用課目成績(jī)列，將二類、三類人員的測(cè)試成績(jī)按照大綱考核標(biāo)準(zhǔn)轉(zhuǎn)換為分?jǐn)?shù)，將文職人員的測(cè)試成績(jī)按照大綱考核標(biāo)準(zhǔn)轉(zhuǎn)換為等級(jí)；在專項(xiàng)成績(jī)列，查找引用專項(xiàng)課目成績(jī)表中人員的專項(xiàng)考核成績(jī)，兩項(xiàng)成績(jī)匯總完畢后可作為后續(xù)綜合評(píng)定的依據(jù)。

2.3.1 通用課目成績(jī)

這里以課目一為例進(jìn)行說(shuō)明，公式為=IF(OR(人員類別=“文職”，人員類別=“新招聘文職”)，文職人員公式，二類、三類人員公式)。文職人員公式為INDEX({“不及格”“及格”“良好”，“優(yōu)秀”}，MATCH(成績(jī)，INDEX((新招聘文職男性通用課目一對(duì)應(yīng)標(biāo)準(zhǔn)區(qū)域，新招聘文職女性通用課目一對(duì)應(yīng)標(biāo)準(zhǔn)區(qū)域，文職男性通用課目一對(duì)應(yīng)標(biāo)準(zhǔn)區(qū)域，文職女性通用課目一對(duì)應(yīng)標(biāo)準(zhǔn)區(qū)域)，ROUNDUP(MAX(年齡-21，3)/3，0)，MATCH(人員類別&性別，文職人員枚舉區(qū)域，0))))，公式中MATCH(人員類別&性別，文職人員枚舉，0)實(shí)現(xiàn)的功能是根據(jù)人員類別和性別匹配出要使用哪個(gè)課目考核標(biāo)準(zhǔn)區(qū)域，如當(dāng)人員類別為文職同時(shí)性別為男性時(shí)，使用第3個(gè)標(biāo)準(zhǔn)區(qū)域，考核標(biāo)準(zhǔn)示意如表4所示。

表4 男性文職人員通用科目一考核標(biāo)準(zhǔn)示意表

二類、三類人員公式為INDEX(通用課目一標(biāo)準(zhǔn)的分?jǐn)?shù)區(qū)域，MATCH(成績(jī)，INDEX(IF(性別=“男性”，通用課目一標(biāo)準(zhǔn)中男性對(duì)應(yīng)的考核標(biāo)準(zhǔn)區(qū)域，通用課目一標(biāo)準(zhǔn)中女性對(duì)應(yīng)的考核標(biāo)準(zhǔn)區(qū)域)，，ROUNDUP(MAX(年齡-21，3)/3，0))))。公式中MATCH函數(shù)的語(yǔ)法為MATCH([成績(jī)]，[考核標(biāo)準(zhǔn)]，[match_type])，當(dāng)match_type為1或省略時(shí)，MATCH函數(shù)會(huì)查找小于或等于[成績(jī)]的最大值，[考核標(biāo)準(zhǔn)]參數(shù)中的值必須按升序排列，此種情況適用于考核成績(jī)數(shù)量越多或者時(shí)間越長(zhǎng)，分?jǐn)?shù)越高的課目。當(dāng)參數(shù)為-1時(shí)，MATCH函數(shù)會(huì)查找大于或等于[成績(jī)]的最小值，[考核標(biāo)準(zhǔn)]參數(shù)中的值必須按降序排列，此種情況適用于考核成績(jī)時(shí)間越短，分?jǐn)?shù)越高的課目。男性二類、三類人員通用科目一考核標(biāo)準(zhǔn)示意如表5所示。

表5 男性二類、三類人員通用科目一考核標(biāo)準(zhǔn)示意表

由于文職人員不涉及分?jǐn)?shù)求和，總分列的公式為=IF(OR(人員類別=“文職”，人員類別=“新招聘文職”)，“——”SUM(課目一、二、三、四的分?jǐn)?shù)))。

2.3.2 專項(xiàng)課目成績(jī)

人員參加專項(xiàng)課目考核的相關(guān)信息記錄在專項(xiàng)課目成績(jī)表中，根據(jù)大綱規(guī)定，結(jié)合單位實(shí)際，對(duì)于專項(xiàng)課目，我部文職人員不考，二類人員必考，三類人員選考。因此，不同人員類別情況下，專項(xiàng)成績(jī)的值不同。當(dāng)人員類別為文職人員時(shí)，值為“不考”，人員類別為二類、三類人員時(shí)，值為“優(yōu)秀”“良好”“及格”“不及格”“未考”等。為避免出現(xiàn)人員多次參加考核，無(wú)法精確匹配成績(jī)的情況，采用姓名和考核日期兩個(gè)篩選條件來(lái)匹配成績(jī)。公式為=IF(OR(人員類別=“文職”，人員類別=“新招聘文職”)，“不考”，IF(COUNTIFS(專項(xiàng)課目成績(jī)表的姓名列，姓名，專項(xiàng)課目成績(jī)表的考核日期列，考核日期)=0，“未考”，LOOKUP(1，0/(姓名&考核日期=專項(xiàng)課目成績(jī)表的姓名列區(qū)域&專項(xiàng)課目成績(jī)表的考核日期列區(qū)域)，專項(xiàng)課目成績(jī)表的總評(píng)列區(qū)域)))。其中，函數(shù)COUNTIFS(專項(xiàng)課目成績(jī)表的姓名列，姓名，專項(xiàng)課目成績(jī)表的考核日期列，考核日期)的功能是在專項(xiàng)課目成績(jī)表中通過(guò)姓名和考核日期查找所選人員，如果未查找到，則表示該人未參加考核，如果能查找到，則通過(guò)函數(shù)LOOKUP(1，0/(姓名&考核日期=專項(xiàng)課目成績(jī)表的姓名列區(qū)域&專項(xiàng)課目成績(jī)表的考核日期列區(qū)域)，專項(xiàng)課目成績(jī)表的總評(píng)列區(qū)域)來(lái)匹配該人員的專項(xiàng)考核成績(jī)。

2.4 綜合評(píng)定

根據(jù)大綱規(guī)定，體型不合格、任一單項(xiàng)課目成績(jī)不及格，個(gè)人軍事體育訓(xùn)練成績(jī)?yōu)椴患案?。因此，綜合評(píng)定時(shí)首先應(yīng)判斷體型是否合格，在體型合格的情況下，再根據(jù)不同人員類別的標(biāo)準(zhǔn)進(jìn)行評(píng)定，公式為=IF(體型合格，IF(OR(人員類別=“文職”，人員類別=“新招聘文職”)，文職人員成績(jī)?cè)u(píng)定公式，二類、三類人員成績(jī)?cè)u(píng)定公式)，“不及格”)。

對(duì)于文職人員，將大綱中規(guī)定的文職人員個(gè)人成績(jī)?cè)u(píng)定標(biāo)準(zhǔn)轉(zhuǎn)換為邏輯表達(dá)式，即：如果3項(xiàng)成績(jī)中有1項(xiàng)為不及格的，則評(píng)定為不及格；否則，如果及格的課目數(shù)大于等于2，則評(píng)定為及格；否則，如果優(yōu)秀的課目數(shù)大于等于2，同時(shí)及格的課目數(shù)為0，則評(píng)定為優(yōu)秀；否則，評(píng)定為良好。基于邏輯表達(dá)式可得出文職人員成績(jī)?cè)u(píng)定公式：IF(COUNTIF(通用課目成績(jī)的前三項(xiàng)，“不及格”)>0，“不及格”，IF(COUNTIF(通用課目成績(jī)的前三項(xiàng)，“及格”)>=2，“及格”，IF(AND(COUNTIF(通用課目成績(jī)的前三項(xiàng)，“優(yōu)秀”)>=2，COUNTIF(通用課目成績(jī)的前三項(xiàng)，“及格”)=0)，“優(yōu)秀”，“良好”)))。

對(duì)于二類、三類人員，二類人員必考專項(xiàng)課目，三類人員選考專項(xiàng)課目，當(dāng)專項(xiàng)成績(jī)?yōu)閮?yōu)秀或良好時(shí)，人員可參評(píng)特級(jí)。因此，在考慮專項(xiàng)成績(jī)的基礎(chǔ)上，結(jié)合通用課目成績(jī)的總分可完成個(gè)人軍事體育訓(xùn)練成績(jī)的評(píng)定，評(píng)定標(biāo)準(zhǔn)如表6所示。二類、三類人員成績(jī)?cè)u(píng)定的公式為：IF(AND(通用訓(xùn)練科目一、二、三、四的評(píng)價(jià)為“合格”)，IF(人員類別=“三類”，VLOOKUP(專項(xiàng)評(píng)定成績(jī)，綜合評(píng)定標(biāo)準(zhǔn)中的三類人員評(píng)定標(biāo)準(zhǔn)區(qū)域，1+MATCH(通用課目成績(jī)總分，綜合評(píng)定標(biāo)準(zhǔn)中的三類人員分?jǐn)?shù)區(qū)域，1)，F(xiàn)ALSE)，VLOOKUP(專項(xiàng)評(píng)定成績(jī)，綜合評(píng)定標(biāo)準(zhǔn)中的二類人員評(píng)定標(biāo)準(zhǔn)區(qū)域，1+MATCH(通用課目成績(jī)總分，綜合評(píng)定標(biāo)準(zhǔn)中的二類人員分?jǐn)?shù)區(qū)域，1)，F(xiàn)ALSE))，“不及格”)。

表6 個(gè)人軍事體育訓(xùn)練成績(jī)?cè)u(píng)定標(biāo)準(zhǔn)

3 專項(xiàng)課目成績(jī)表

專項(xiàng)課目的考核方式與通用課目有所不同，人員類別區(qū)分正常人員和傷病人員，正常人員參加1個(gè)專項(xiàng)課目組考核，傷病人員參加1個(gè)專項(xiàng)課目考核，專項(xiàng)考核內(nèi)容如表7所示。當(dāng)性別、人員類別和抽考課目組確定時(shí)，相應(yīng)的考核課目和及格標(biāo)準(zhǔn)也隨之確定。為了表述方便，這里以字母代號(hào)表示課目，實(shí)際情況中課目A和D可能為同一課目，在編寫(xiě)公式時(shí)要根據(jù)實(shí)際情況進(jìn)行處理。

表7 專項(xiàng)考核內(nèi)容

專項(xiàng)課目成績(jī)表的表格設(shè)計(jì)如表8所示。成績(jī)表中人員類別列填寫(xiě)正常人員或傷病人員，其他基本信息的填寫(xiě)規(guī)則同通用課目成績(jī)表中相應(yīng)列的填寫(xiě)規(guī)則相同。在專項(xiàng)課目成績(jī)表中，要實(shí)現(xiàn)輸入基本信息、確定抽考課目組后，能自動(dòng)更新課目名稱和及格標(biāo)準(zhǔn)，在輸入人員考核成績(jī)后，能自動(dòng)完成成績(jī)?cè)u(píng)定的功能。

表8 專項(xiàng)課目成績(jī)表的表格設(shè)計(jì)

3.1 自動(dòng)更新課目名稱和及格標(biāo)準(zhǔn)

由于不同性別的抽考課目組不同，因此當(dāng)性別改變時(shí)，抽考課目組的下拉菜單要隨之改變，同時(shí)當(dāng)點(diǎn)選不同抽考課目組時(shí)，課目一、二、三的名稱和標(biāo)準(zhǔn)也應(yīng)隨之改變。實(shí)現(xiàn)方法是：第一步：通過(guò)數(shù)據(jù)有效性菜單設(shè)置，將性別列下單元格的數(shù)據(jù)有效性設(shè)為“男性、女性”。第二步：點(diǎn)擊名稱管理器新建名稱，名稱名為“男性”，名稱的引用位置為表7中男性下方對(duì)應(yīng)的4個(gè)單元格，按照類似方法新建名稱名為“女性”的含3個(gè)課目組的名稱。第三步：設(shè)置抽考課目組下方單元格的數(shù)據(jù)有效性，將有效性條件的允許設(shè)為“序列”，來(lái)源設(shè)為=INDIRECT(人員性別單元格)。至此，可實(shí)現(xiàn)點(diǎn)選不同性別時(shí)顯示不同抽考課目組選項(xiàng)。第四步：輸入公式。以課目一為例，名稱列的公式為=INDEX(表9中課目一下方的七個(gè)單元格，MATCH(抽考課目組單元格，表9中專項(xiàng)課目組下方的七個(gè)單元格，0))。課目一的標(biāo)準(zhǔn)列的公式為=VLOOKUP(課目名稱，通用專項(xiàng)課目及格標(biāo)準(zhǔn)工作表中的專項(xiàng)課目及格標(biāo)準(zhǔn)區(qū)域，1+ROUNDUP(MAX(年齡-21，3)/3，0)，F(xiàn)ALSE)。此時(shí)，可通過(guò)點(diǎn)選性別及抽考課目組來(lái)實(shí)現(xiàn)自動(dòng)更新考核課目和及格標(biāo)準(zhǔn)。

表9 專項(xiàng)課目組課目枚舉

3.2 自動(dòng)完成成績(jī)?cè)u(píng)定

評(píng)價(jià)列的公式為=IF(成績(jī)=0，“缺考”，INDEX({“不及格”，“及格”，“良好”，“優(yōu)秀”}，MATCH(成績(jī)，INDEX((專項(xiàng)課目標(biāo)準(zhǔn)中課目一A、D、G、J、M、O、Q對(duì)應(yīng)的7個(gè)考核標(biāo)準(zhǔn)區(qū)域)，，ROUNDUP(MAX(年齡-21，3)/3，0)，MATCH(課目一名稱單元格，表7中課目一下方的七個(gè)單元格，0))，1)))。評(píng)價(jià)匯總列是將課目一、二、三評(píng)價(jià)列的內(nèi)容復(fù)制于此，為后續(xù)評(píng)定提供依據(jù)。

專項(xiàng)成績(jī)列的公式為=IF(人員類別=“正?！?，正常人員評(píng)價(jià)公式，傷病人員評(píng)價(jià)公式)。根據(jù)大綱規(guī)定，正常人員專項(xiàng)訓(xùn)練課目組成績(jī)采用“優(yōu)秀、良好、及格、不及格”四級(jí)制評(píng)定，最低訓(xùn)練課目成績(jī)?yōu)檎n目組成績(jī)。即評(píng)價(jià)匯總區(qū)域的課目成績(jī)中，但凡有課目成績(jī)?yōu)椴患案駮r(shí)，專項(xiàng)成績(jī)?cè)u(píng)定為不及格，當(dāng)不滿足以上條件時(shí)，但凡有課目成績(jī)?yōu)榧案駮r(shí)，專項(xiàng)成績(jī)?cè)u(píng)定為及格，以此類推。正常人員評(píng)價(jià)公式為IF(OR(COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“缺考”)>0，COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“不及格”)>0)，“不及格”，IF(COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“及格”)>0，“及格”，IF(COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“良好”)>0，“良好”，“優(yōu)秀”)))。對(duì)于39歲以下的二類傷病人員來(lái)說(shuō)，須任選1個(gè)專項(xiàng)訓(xùn)練科目進(jìn)行考核，成績(jī)?cè)u(píng)定為及格或不及格。因此，但凡評(píng)價(jià)匯總區(qū)域的課目成績(jī)有1個(gè)優(yōu)秀、良好或者及格的，成績(jī)?cè)u(píng)定為及格。傷病人員評(píng)價(jià)公式為IF(OR(COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“優(yōu)秀”)>0，COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“良好”)>0，COUNTIF(課目評(píng)價(jià)匯總區(qū)域，“及格”)>0)，“及格”，“不及格”)。

4 傷病人員通用課目成績(jī)表

傷病人員通用課目成績(jī)表與正常人員通用課目成績(jī)表的設(shè)計(jì)思想是相同的，實(shí)現(xiàn)上在課目選擇時(shí)有所區(qū)別。根據(jù)大綱規(guī)定，傷病人員可以在傷病人員通用訓(xùn)練選考科目中自選4個(gè)進(jìn)行考核(在選考科目1～8中任選3個(gè)，在選考科目9～11中必選1個(gè))，課目成績(jī)?cè)u(píng)定為及格或不及格。

傷病人員通用課目成績(jī)表的表格設(shè)計(jì)如表10所示。成績(jī)表基本信息部分的填寫(xiě)規(guī)則同通用課目成績(jī)表中相應(yīng)列的填寫(xiě)規(guī)則相同。在傷病人員通用課目成績(jī)表中，要實(shí)現(xiàn)輸入基本信息、選定不同考核課目后，能自動(dòng)更新課目及格標(biāo)準(zhǔn)，在輸入人員考核成績(jī)后，能自動(dòng)完成成績(jī)?cè)u(píng)定的功能。

表10 傷病人員通用課目成績(jī)表的表格設(shè)計(jì)

4.1 自動(dòng)更新課目及格標(biāo)準(zhǔn)

對(duì)選考科目一、二、三名稱列下方的單元格進(jìn)行數(shù)據(jù)有效性設(shè)置，設(shè)置來(lái)源為8個(gè)選考科目，對(duì)選考科目四名稱列下方的單元格進(jìn)行數(shù)據(jù)有效性設(shè)置，設(shè)置來(lái)源為3個(gè)選考科目，不同考核人員通過(guò)點(diǎn)選課目名稱的下拉菜單實(shí)現(xiàn)考核課目定制。當(dāng)人員基本信息和課目名稱確定后，及格標(biāo)準(zhǔn)隨之更新，標(biāo)準(zhǔn)列的公式為=VLOOKUP(IF(COUNTIF(傷病人員課目類別，性別&課目名稱)>0，性別&課目名稱，性別&人員類別&課目名稱)，傷病人員通用訓(xùn)練選考科目考核標(biāo)準(zhǔn)區(qū)域，1+ROUNDUP(MAX(年齡-21，3)/3，0)，F(xiàn)ALSE)。

4.2 自動(dòng)完成成績(jī)?cè)u(píng)定

選考科目一、二、三、四中的成績(jī)列和評(píng)價(jià)列的填寫(xiě)規(guī)則與正常人員通用課目成績(jī)表中相應(yīng)列的填寫(xiě)規(guī)則相同，可根據(jù)具體情況使用不同函數(shù)來(lái)實(shí)現(xiàn)相應(yīng)功能。通用成績(jī)列的公式為=IF(AND(體型合格，四個(gè)選考科目均合格)，“及格”，“不及格”)。專項(xiàng)成績(jī)列的公式為=IF(OR(人員類別=“三類”，AND(人員類別=“二類”，年齡>=40))，“不考”，IF(COUNTIFS(專項(xiàng)課目成績(jī)表的姓名列，姓名，專項(xiàng)課目成績(jī)表的考核日期列，考核日期)=0，“未考”，LOOKUP(1，0/(姓名&考核日期=專項(xiàng)課目成績(jī)表的姓名列區(qū)域&專項(xiàng)課目成績(jī)表的考核日期列區(qū)域)，專項(xiàng)課目成績(jī)表的總評(píng)列區(qū)域)))。

根據(jù)大綱規(guī)定，對(duì)于三類人員及40歲以上的二類人員，專項(xiàng)成績(jī)?yōu)椤安豢肌?，通用課目成績(jī)作為綜合評(píng)定成績(jī)。對(duì)于39歲以下的二類人員，專項(xiàng)成績(jī)分為“未考”“不及格”“及格”三種情況，僅當(dāng)通用和專項(xiàng)課目成績(jī)均為及格的情況下，綜合評(píng)定成績(jī)?yōu)楹细?。因此，綜合評(píng)定的公式為=IF(專項(xiàng)成績(jī)=“不考”，通用成績(jī)，IF(AND(通用成績(jī)=“及格”，專項(xiàng)成績(jī)=“及格”)，“及格”，“不及格”))。

5 其他說(shuō)明

上述介紹的軍事體育考核成績(jī)統(tǒng)計(jì)表適用于海拔2000米以下的地面二類人員、三類人員、文職人員和傷病人員。對(duì)于新入伍人員，由于考核課目和標(biāo)準(zhǔn)比較簡(jiǎn)單，讀者可使用函數(shù)和公式自行設(shè)計(jì)實(shí)現(xiàn)。對(duì)于高原地區(qū)人員、一類人員和水面艦艇二類人員，由于考核課目和考核標(biāo)準(zhǔn)不同，讀者可根據(jù)大綱標(biāo)準(zhǔn)參照本文設(shè)計(jì)出適用于其自身的成績(jī)統(tǒng)計(jì)表。此外，在Excel中，可以通過(guò)一些設(shè)置使統(tǒng)計(jì)表在操作上更易用，界面上更友好。比如，在考核標(biāo)準(zhǔn)工作表中，可設(shè)置工作表保護(hù)來(lái)避免數(shù)據(jù)被誤修改；在考核成績(jī)工作表中，可在相應(yīng)成績(jī)列設(shè)置條件格式，當(dāng)成績(jī)?yōu)椴患案駮r(shí)，單元格填充為紅色，當(dāng)成績(jī)?yōu)槿笨紩r(shí)，單元格填充為黃色，用顏色來(lái)對(duì)不同人員進(jìn)行標(biāo)記。

6 結(jié)語(yǔ)

本文基于Excel軟件，依據(jù)大綱考核規(guī)定和考核標(biāo)準(zhǔn)，利用函數(shù)和公式實(shí)現(xiàn)了地面二類人員、三類人員、文職人員和傷病人員的通用及專項(xiàng)課目考核成績(jī)統(tǒng)計(jì)。總體來(lái)說(shuō)有三個(gè)優(yōu)點(diǎn)：一是完全對(duì)標(biāo)大綱。軍事體育考核成績(jī)統(tǒng)計(jì)表完全依據(jù)大綱標(biāo)準(zhǔn)進(jìn)行設(shè)計(jì)，涵蓋所有類別軍隊(duì)人員，組訓(xùn)人員可基于此表對(duì)本單位人員進(jìn)行考核登記統(tǒng)計(jì)，真正實(shí)現(xiàn)了“一表通考”。二是開(kāi)發(fā)維護(hù)高效。只要讀者具備一定的Excel基礎(chǔ)知識(shí)，并明白了考核成績(jī)統(tǒng)計(jì)表的設(shè)計(jì)思想和實(shí)現(xiàn)方法，便可對(duì)表格進(jìn)行維護(hù)更新。當(dāng)大綱考核規(guī)定和標(biāo)準(zhǔn)發(fā)生變化時(shí)，讀者可根據(jù)最新要求及時(shí)對(duì)考核成績(jī)統(tǒng)計(jì)表進(jìn)行更新。三是推廣使用方便?？己顺煽?jī)統(tǒng)計(jì)表僅為一個(gè)Excel文檔，不依賴于運(yùn)行庫(kù)等其他程序文件，只要電腦中裝有Excel軟件，便可打開(kāi)運(yùn)行。在填寫(xiě)報(bào)名信息后，參考人員即可知道考核課目的及格標(biāo)準(zhǔn)；考核結(jié)束錄入成績(jī)后，即可自動(dòng)完成成績(jī)?cè)u(píng)定，操作簡(jiǎn)單高效，推廣使用非常方便。

軍事體育考核成績(jī)統(tǒng)計(jì)表設(shè)計(jì)完成后，已在部隊(duì)試用一年有余，結(jié)果表明，該表操作簡(jiǎn)單，評(píng)定準(zhǔn)確，自動(dòng)化程度高，深受組訓(xùn)人員的歡迎?；谠摫磉M(jìn)行成績(jī)統(tǒng)計(jì)，能顯著提高工作效率，消除人工失誤，極大方便軍事體育組訓(xùn)與考核。