基于Excel的軍事體育考核成績統計表的設計與實現

李騰偉，宋 影，茍偉強，彭振華，陳向陽

前言

2018年2月，經中央軍委批準，新一代《軍事體育訓練大綱》頒布施行，為各級組織實施軍事體育訓練提供了基本依據[1]。2020年1月，大綱進行滾動更新，新增了文職人員、傷病人員考核內容與標準，更加符合訓練實際，適應實戰需要。大綱施行四年來，官兵軍事體育訓練熱情高漲，體質明顯增強，有效推動了部隊戰斗力提高。

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

1 基本情況

根據大綱[11]規定，結合單位實際，我部(63620部隊)軍人考核人員依據崗位特點區分為“二類人員”和“三類人員”兩類，人員按照身體情況區分為“正常人員”和“傷病人員”兩類，文職考核人員按照入職時間區分為“新招聘文職”和“文職人員”兩類，不同類別人員在考核科目和考核標準上有所區別。關于軍事體育考核的內容與標準，讀者可參閱文獻[12，13]進行了解。下面詳細介紹軍事體育考核成績統計表的設計思想與實現方法。

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

2 正常人員通用課目成績表

在正常人員通用課目成績表中，要實現輸入性別、人員類別和年齡等基本信息后，可自動更新所有課目的及格標準，在輸入人員考核成績后，能自動完成成績匯總及評定的功能。正常人員通用課目成績表的表格設計如表1所示，包括基本信息、考核課目、成績匯總和綜合評定四部分內容。

表1 正常人員通用課目成績表的表格設計

2.1 基本信息

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

2.2 考核課目

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

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

表2 體型BMI上限值統計表

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

表3 男性通用課目一及格標準示意表

2.3 成績匯總

成績匯總包含通用課目成績和專項課目成績兩項內容，在通用課目成績列，將二類、三類人員的測試成績按照大綱考核標準轉換為分數，將文職人員的測試成績按照大綱考核標準轉換為等級；在專項成績列，查找引用專項課目成績表中人員的專項考核成績，兩項成績匯總完畢后可作為后續綜合評定的依據。

2.3.1 通用課目成績

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

表4 男性文職人員通用科目一考核標準示意表

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

表5 男性二類、三類人員通用科目一考核標準示意表

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

2.3.2 專項課目成績

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

2.4 綜合評定

根據大綱規定，體型不合格、任一單項課目成績不及格，個人軍事體育訓練成績為不及格。因此，綜合評定時首先應判斷體型是否合格，在體型合格的情況下，再根據不同人員類別的標準進行評定，公式為=IF(體型合格，IF(OR(人員類別=“文職”，人員類別=“新招聘文職”)，文職人員成績評定公式，二類、三類人員成績評定公式)，“不及格”)。

對于文職人員，將大綱中規定的文職人員個人成績評定標準轉換為邏輯表達式，即：如果3項成績中有1項為不及格的，則評定為不及格；否則，如果及格的課目數大于等于2，則評定為及格；否則，如果優秀的課目數大于等于2，同時及格的課目數為0，則評定為優秀；否則，評定為良好。基于邏輯表達式可得出文職人員成績評定公式：IF(COUNTIF(通用課目成績的前三項，“不及格”)>0，“不及格”，IF(COUNTIF(通用課目成績的前三項，“及格”)>=2，“及格”，IF(AND(COUNTIF(通用課目成績的前三項，“優秀”)>=2，COUNTIF(通用課目成績的前三項，“及格”)=0)，“優秀”，“良好”)))。

對于二類、三類人員，二類人員必考專項課目，三類人員選考專項課目，當專項成績為優秀或良好時，人員可參評特級。因此，在考慮專項成績的基礎上，結合通用課目成績的總分可完成個人軍事體育訓練成績的評定，評定標準如表6所示。二類、三類人員成績評定的公式為：IF(AND(通用訓練科目一、二、三、四的評價為“合格”)，IF(人員類別=“三類”，VLOOKUP(專項評定成績，綜合評定標準中的三類人員評定標準區域，1+MATCH(通用課目成績總分，綜合評定標準中的三類人員分數區域，1)，FALSE)，VLOOKUP(專項評定成績，綜合評定標準中的二類人員評定標準區域，1+MATCH(通用課目成績總分，綜合評定標準中的二類人員分數區域，1)，FALSE))，“不及格”)。

表6 個人軍事體育訓練成績評定標準

3 專項課目成績表

專項課目的考核方式與通用課目有所不同，人員類別區分正常人員和傷病人員，正常人員參加1個專項課目組考核，傷病人員參加1個專項課目考核，專項考核內容如表7所示。當性別、人員類別和抽考課目組確定時，相應的考核課目和及格標準也隨之確定。為了表述方便，這里以字母代號表示課目，實際情況中課目A和D可能為同一課目，在編寫公式時要根據實際情況進行處理。

表7 專項考核內容

專項課目成績表的表格設計如表8所示。成績表中人員類別列填寫正常人員或傷病人員，其他基本信息的填寫規則同通用課目成績表中相應列的填寫規則相同。在專項課目成績表中，要實現輸入基本信息、確定抽考課目組后，能自動更新課目名稱和及格標準，在輸入人員考核成績后，能自動完成成績評定的功能。

表8 專項課目成績表的表格設計

3.1 自動更新課目名稱和及格標準

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

表9 專項課目組課目枚舉

3.2 自動完成成績評定

評價列的公式為=IF(成績=0，“缺考”，INDEX({“不及格”，“及格”，“良好”，“優秀”}，MATCH(成績，INDEX((專項課目標準中課目一A、D、G、J、M、O、Q對應的7個考核標準區域)，，ROUNDUP(MAX(年齡-21，3)/3，0)，MATCH(課目一名稱單元格，表7中課目一下方的七個單元格，0))，1)))。評價匯總列是將課目一、二、三評價列的內容復制于此，為后續評定提供依據。

專項成績列的公式為=IF(人員類別=“正常”，正常人員評價公式，傷病人員評價公式)。根據大綱規定，正常人員專項訓練課目組成績采用“優秀、良好、及格、不及格”四級制評定，最低訓練課目成績為課目組成績。即評價匯總區域的課目成績中，但凡有課目成績為不及格時，專項成績評定為不及格，當不滿足以上條件時，但凡有課目成績為及格時，專項成績評定為及格，以此類推。正常人員評價公式為IF(OR(COUNTIF(課目評價匯總區域，“缺考”)>0，COUNTIF(課目評價匯總區域，“不及格”)>0)，“不及格”，IF(COUNTIF(課目評價匯總區域，“及格”)>0，“及格”，IF(COUNTIF(課目評價匯總區域，“良好”)>0，“良好”，“優秀”)))。對于39歲以下的二類傷病人員來說，須任選1個專項訓練科目進行考核，成績評定為及格或不及格。因此，但凡評價匯總區域的課目成績有1個優秀、良好或者及格的，成績評定為及格。傷病人員評價公式為IF(OR(COUNTIF(課目評價匯總區域，“優秀”)>0，COUNTIF(課目評價匯總區域，“良好”)>0，COUNTIF(課目評價匯總區域，“及格”)>0)，“及格”，“不及格”)。

4 傷病人員通用課目成績表

傷病人員通用課目成績表與正常人員通用課目成績表的設計思想是相同的，實現上在課目選擇時有所區別。根據大綱規定，傷病人員可以在傷病人員通用訓練選考科目中自選4個進行考核(在選考科目1～8中任選3個，在選考科目9～11中必選1個)，課目成績評定為及格或不及格。

傷病人員通用課目成績表的表格設計如表10所示。成績表基本信息部分的填寫規則同通用課目成績表中相應列的填寫規則相同。在傷病人員通用課目成績表中，要實現輸入基本信息、選定不同考核課目后，能自動更新課目及格標準，在輸入人員考核成績后，能自動完成成績評定的功能。

表10 傷病人員通用課目成績表的表格設計

4.1 自動更新課目及格標準

對選考科目一、二、三名稱列下方的單元格進行數據有效性設置，設置來源為8個選考科目，對選考科目四名稱列下方的單元格進行數據有效性設置，設置來源為3個選考科目，不同考核人員通過點選課目名稱的下拉菜單實現考核課目定制。當人員基本信息和課目名稱確定后，及格標準隨之更新，標準列的公式為=VLOOKUP(IF(COUNTIF(傷病人員課目類別，性別&課目名稱)>0，性別&課目名稱，性別&人員類別&課目名稱)，傷病人員通用訓練選考科目考核標準區域，1+ROUNDUP(MAX(年齡-21，3)/3，0)，FALSE)。

4.2 自動完成成績評定

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

根據大綱規定，對于三類人員及40歲以上的二類人員，專項成績為“不考”，通用課目成績作為綜合評定成績。對于39歲以下的二類人員，專項成績分為“未考”“不及格”“及格”三種情況，僅當通用和專項課目成績均為及格的情況下，綜合評定成績為合格。因此，綜合評定的公式為=IF(專項成績=“不考”，通用成績，IF(AND(通用成績=“及格”，專項成績=“及格”)，“及格”，“不及格”))。

5 其他說明

上述介紹的軍事體育考核成績統計表適用于海拔2000米以下的地面二類人員、三類人員、文職人員和傷病人員。對于新入伍人員，由于考核課目和標準比較簡單，讀者可使用函數和公式自行設計實現。對于高原地區人員、一類人員和水面艦艇二類人員，由于考核課目和考核標準不同，讀者可根據大綱標準參照本文設計出適用于其自身的成績統計表。此外，在Excel中，可以通過一些設置使統計表在操作上更易用，界面上更友好。比如，在考核標準工作表中，可設置工作表保護來避免數據被誤修改；在考核成績工作表中，可在相應成績列設置條件格式，當成績為不及格時，單元格填充為紅色，當成績為缺考時，單元格填充為黃色，用顏色來對不同人員進行標記。

6 結語

本文基于Excel軟件，依據大綱考核規定和考核標準，利用函數和公式實現了地面二類人員、三類人員、文職人員和傷病人員的通用及專項課目考核成績統計。總體來說有三個優點：一是完全對標大綱。軍事體育考核成績統計表完全依據大綱標準進行設計，涵蓋所有類別軍隊人員，組訓人員可基于此表對本單位人員進行考核登記統計，真正實現了“一表通考”。二是開發維護高效。只要讀者具備一定的Excel基礎知識，并明白了考核成績統計表的設計思想和實現方法，便可對表格進行維護更新。當大綱考核規定和標準發生變化時，讀者可根據最新要求及時對考核成績統計表進行更新。三是推廣使用方便。考核成績統計表僅為一個Excel文檔，不依賴于運行庫等其他程序文件，只要電腦中裝有Excel軟件，便可打開運行。在填寫報名信息后，參考人員即可知道考核課目的及格標準；考核結束錄入成績后，即可自動完成成績評定，操作簡單高效，推廣使用非常方便。

軍事體育考核成績統計表設計完成后，已在部隊試用一年有余，結果表明，該表操作簡單，評定準確，自動化程度高，深受組訓人員的歡迎。基于該表進行成績統計，能顯著提高工作效率，消除人工失誤，極大方便軍事體育組訓與考核。