使用Excel实现测评得分统计

钱力涛

摘 要：在日常工作中，经常会有各类比赛或测评活动，而准确的统计和反馈，可以提高活动效率，并体现公开和公平原则。本文以Excel为工作环境，制作了一个高效的测评统计表。

关键词：测评;统计;Excel

1 问题的提出

放假前，教代会召开会议，听取学校领导班子做工作报告。在会议中表决通过了《学校章程》和《发展规划》，并对中层干部进行了民主评议。会后，笔者参与了民主评议的统分工作，感觉输入分值、计算平均分、排名，操作繁琐，特制作了一个Excel文件来简化操作。

选择Excel作为开发环境，有三个原因。一是Excel作为微软Office的办公套件之一，在计算机上的普及率相当高，而且不同版本间的兼容性也很好。二是Excel呈现数据的方式简单直观，有较为丰富的函数，适合对数据的操作。三是在表格开发的后期，可以结合简单的VBA技术（Visual Basic for Applications，一种宏语言），实现复杂的功能。[1]

2 步骤和制作

基本思路是，针对每一名被测评的中层，输入每个代表的打分后，去掉三个最高分和三个最低分，对剩余分数取平均值作为这名中层的得分。打分数据输入完毕后，也就是全体中层的每人平均分算出后，对得分进行总的排名。

根据上面的思路，得出Excel的制作流程：制作表格—设定区域的数据有效性——首行/首列固定——去掉最高分和最低分后计算平均分——最后得分的排名——表格美化。

2.1 制作表格

制作标题行，第一行合并单元格，输入“教代会‘中层干部民主评议评分表”。第二行输入序号、中层姓名、最后得分、排名和代表序号（如图所示）。

2.2 数据有效性

对表格中的E3至BB52区域进行数据有效性设置，只能输入0-100范围内的整数，否则数据无效。另外，对此区域的数据设置条件格式，用特殊颜色标明0分和100分。

2.3 首行/首列固定

由于需要固定表格的前兩行和前三列，所以，鼠标选择想要固定的行与列相交位置的单元格，也就是D3单元格，再接着点击“冻结拆分窗格”，就可以实现同时固定前两行和前三列。（固定的行和列是不包括被选择的单元格，只是被选择单元格的上侧和左侧）

2.4 去掉最高最低分后计算平均分

使用TRIMMEAN函数，TRIMMEAN 函数是先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。其语法格式是TRIMMEAN（array，percent）。所以，在表格中的C3单元格，输入以下内容“=TRIMMEAN（E3：BB3，6 / COUNTA（E3：BB3））”（再按上述方法向下自动填充）。其含义是，从E3到BB3，一共50个数据点的集合中（全体代表对某一名中层的打分），去除6个数据点（去掉3个最高分，去掉3个最低分），剔除后，对剩余数据取平均值。

为了数字的简洁和观看的方便，对“最后得分”列（C列）的单元格格式设置为2位小数的数值类型。

2.5 排名

使用RANK函数实现排名，其语法形式是RANK（number，ref，[order]）。所以，在表格中的D3单元格，输入以下内容“=RANK（C3，C$3：C$36，）”（再按上述方法向下自动填充）。其含义是，C3单元格的数据在从C3到C36的范围中的排名（一共34名中层）。

2.6 表格美化

如果希望表格漂亮，字体是第一步。所以，将表格中的字体换成微软雅黑。其次，取消网格线，并对标题行部分加上一点颜色。最后，隔行填充颜色，这样可以使输入数据或查看表格的人更清晰的观看某行情况。

3 扩展和完善

每年年终时，学校都会举办“教师教学能力大赛”，为了使比赛成绩更加透明，评委需要当众打分，记分员也要迅速的计算出平均分和排名。同样可以使用上述步骤和方法，制作Excel文件，提高工作效率。

此外，在排名环节中，RANK函数根据最终得分，按从大到小的顺序排名。如果发生并列名次，则下一名次将按并列数顺延。比如：有两个人并列第3名，则没有第4名，直接是第5名。

很显然，这种排名方式不符合我们的日常习惯。在我们的日常生活中，无论有多少人并列第3名，之后的排名仍应该是第4名。所以，在上述步骤中，按习惯式排名则需要将D3单元格的公式改为“=SUMPRODUCT（（C$3：C$36>C3）*（1/（COUNTIF（C$3：C$36，C$3：C$36））））+1”。

该公式的后半部分用来计算重复排名的个数，不重复为1，重复一次为二分之一，重复两次是三分之一，对应相乘累加后，就得到本次比赛中比该选手成绩高的人数，再加一次就是该选手的排名。[2]

4 结语

通过对Excel的使用，制作了可用于测评打分、总分排名的统计工作表，借助信息化手段，实现公正、高效，提高了工作效率。

参考文献：

[1]吕子燕.基于Excel的比赛计时评分程序的实现[J].中小学电教，2015（Z2）：135-137.

[2]柳淇.利用Excel制作竞赛评分自动计算系统[J].中小学电教，2015（Z1）：142-143.