利用Excel制作竞赛评分自动计算系统

摘 要：利用Excel 函数，函数数组设计一个能自动统计、自动排名、自动显示获奖名单的小程序。适合各种竞赛活动的当众打分、评分，效率高。

关键词：Excel; 函数; 函数数组; 自动统计

中图分类号：G434 文献标识码：A 文章编号：1671-7503（2015）01/03-0142-02

无论学校还是企事业单位，都会有不同规模的比赛。为了使比赛成绩更公平、公开，都要当众打分、评分，为了提高效率，可编制一些小程序来完成计算过程。这些小程序可以用专门的编程语言来写，但专业性太强。笔者采用人们最常用的Excel编写了评委打分自动处理系统，易学好用，灵活性强，自动化程度较高。

此系统共有三个组成部分：初赛区，决赛区，名次表。为了方便，我们假设有15个选手比赛，初赛后选出10个进行决赛，最后胜出6个（根据需要可自行修改）。

下面是详细的编制过程。

新建空白Excel工作簿，将自动建立的Sheet1、Sheet2和Sheet3工作表分别重命名为初赛区、决赛区和名次表（如图1）。

初赛区：主要功能是针对评委给出的分数，去掉两个最高分，两个最低分，根据最后得分排出名次。

图1

根据上表，依次输入评委（1-10）和选手（1-15）的姓名，最终得分、排名。评委1-评委10共10列中填入评委给每个选手的分数。在打分的过程中，最终得分和排名自动计算得出。

最高分：在L3单元格中输入公式“=MAX（B3：K3）”。然后选中L3单元格，向下拖动该单元格右下角的小“十”字图标至最后一行（这种操作称为自动填充）。

最低分：在M3单元格中输入公式“=MIN（B3：K3）”。然后选中M3单元格，按上面的方法自动填充。

最终得分：在N3单元格中输入“=TRIMMEAN（B3：K3，0.2）”。然后选中N3单元格，向下自动填充。该函数的功能是求平均值，在这里主要是指在B3：K3区域内，去掉两个数据后的平均值。公式中的“0.2”表示10个数据中去掉2个（10×0.2），即一个最高分和一个最低分。如果改为“0.4”，表示10个数据中去掉4个（10×0.4），即两个最高分和两个最低分。

排名：在O3单元格中输入"=RANK（N3，N$3：N$17）"。再按上述方法向下自动填充。根据最终得分，按从大到小顺序排名。如果有并列的名次，则下一个名次将按并列数量顺延，如：有两个并列第2 名，则没有第3 名，而是排到第4 名。

在许多情况下，这种排列方式不符合中国人的日常排名习惯。在中国人的习惯中，无论有几个并列第2 名，之后的排名仍应该是第3 名，即并列排名不占用名次。按中国式排名则需将上面公式改为：“=SUMPRODUCT（（N$3：N$17>N3）*（1/（COUNTIF（N$3：N$17，N$3：N$17））））+1”

这是一个数组公式，前面用来判断成绩，大于为1，否则为0，后面用来计算重复数据的个数，不重复为1，重复1次为1/2，重复2次为1/3，对应相乘累加，就得到本次比赛中比这个人成绩高的人数，再加1就是这个人的名次。需要特别注意的是，每当输入或编辑数组公式时都要按下“Ctrl+Shift+Enter”组合键，可以看到在公式的最外层自动加上了一对大括号（不要自己键入花括号，否则，EXCEL认为输入的是一个正文标签）。

决赛区：根据初赛成绩，本系统自动从15个选手中选出前10名参加决赛。名单自动显示在决赛区的第一列。其它列同初赛区。

为了获得决赛区名单，需做两个辅助列。在工作表“初赛区”中，在Q2单元格中输入字段“拼合”，在R2单元格中输入字段“按大小排序”。

在Q3单元格中输入"=INT（CONCATENATE（INT（N3*100），200-ROW（A1）））"。公式中ROW（A1）为A1单元格所在的行数即为1（该公式下拉时依次为2、3、4…），用200来减是为了CONCATENATE函数中的第2个参数保持3位数，CONCATENATE函数是一个拼合函数，这里把N列的数据和它所在的行数拼合成一个数据。这样在对它进行排序后该数据包含了它所在行数的信息。CONCATENATE函数和INT函数套用是为了把原来的文本变为数字。

在R3单元格中输入"=LARGE（Q：Q，ROW（A1））"，即对Q列数值（包含所在行的信息）按大小排列。

在工作表“决赛区”中，A3单元格中输入“=INDEX（初赛区！$A$3：初赛区！$A$17，200-RIGHT（初赛区！R3，3），COLUMN（初赛区！A3））”，即获初赛区中前10名的名单。INDEX函数为引用函数，即根据“200-RIGHT（初赛区！R3，3）”标明的行数，在“初赛区”工作表中$A$3：$A$17单元格矩阵中引用姓名。

名次表：根据决赛成绩，前6名名单自动生成。其中，一等奖1名，二等奖2名，三等奖3名。

在B2单元格中输入“=INDEX（决赛区！$A$3：决赛区！A12，200-RIGHT（决赛区！R3，3），COLUMN（决赛区！A3））”。即引用决赛区中第一名的姓名（如图2）。其它名次同上。

图2

以上就是整个制作过程以及对使用的函数数组的解说，希望能对大家有所帮助。