基于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软件，便可打开运行。在填写报名信息后，参考人员即可知道考核课目的及格标准；考核结束录入成绩后，即可自动完成成绩评定，操作简单高效，推广使用非常方便。

军事体育考核成绩统计表设计完成后，已在部队试用一年有余，结果表明，该表操作简单，评定准确，自动化程度高，深受组训人员的欢迎。基于该表进行成绩统计，能显著提高工作效率，消除人工失误，极大方便军事体育组训与考核。