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

1、利用Excel制作竞赛评分自动计算系统摘要：利用Excel函数，函数数组设计一个能自动统计、自动排名、自动显示获奖名单的小程序。适合各种竞赛活动的当众打分、评分，效率高。无论学校还是企事业单位，都会有不同规模的比赛。为了使比赛成绩更公平、公开，都要当众打分、评分，为了提高效率，可编制一些小程序来完成计算过程。这些小程序可以用专门的编程语言来写，但专业性太强。笔者采用人们最常用的Excel编写了评委打分自动处理系统，易学好用，灵活性强，自动化程度较高。此系统共有三个组成部分：初赛区，决赛区，名次表。为了方便，我们假设有15个选手比赛，初赛后选出10个进行决赛，最后胜出6个（根据需要可自行修改）。

2、下面是详细的编制过程。新建空白Excel工作簿，将自动建立的Sheet1、Sheet2和Sheet3工作表分别重命名为初赛区、决赛区和名次表（如图1）。初赛区：主要功能是针对评委给出的分数，去掉两个最高分，两个最低分，根据最后得分排出名次。图1根据上表，依次输入评委（1-10）和选手（1-15）的姓名，最终得分、排名。评委1-评委10共10列中填入评委给每个选手的分数。在打分的过程中，最终得分和排名自动计算得出。最高分：在L3单元格中输入公式“=MAX（B3:K3）"。然后选中L3单元格，向下拖动该单元格右下角的小“十”字图标至最后一行（这种操作称为自动填充）。最低分：在M3单元格中

3、输入公式“=MIN（B3:K3）"。然后选中M3单元格，按上面的方法自动填充。最终得分:在N3单元格中输入“=TRIMMEANB3:K3,0.2）”。然后选中N3单元格，向下自动填充。该函数的功能是求平均值，在这里主要是指在B3：K3区域内，去掉两个数据后的平均值。公式中的“0.2”表示10个数据中去掉2个（10X0.2）,即一个最高分和一个最低分。如果改为“0.4”，表示10个数据中去掉4个（10X0.4）,即两个最高分和两个最低分。排名：在O3单元格中输入”=RANK（N3,N$3:N$17）”。再按上述方法向下自动填充。根据最终得分，按从大到小顺序排名。如果有并列的名次，则下一

4、个名次将按并列数量顺延，如：有两个并列第2名，则没有第3名，而是排到第4名。在许多情况下，这种排列方式不符合中国人的日常排名习惯。在中国人的习惯中，无论有几个并列第2名，之后的排名仍应该是第3名，即并列排名不占用名次。按中国式排名则需将上面公式改为：“=SUMPRODU（CTN$3：N$17>N3）*（1/（COUNTIF（N$3：N$17，N$3：N$17）+1”决赛区：根据初赛成绩，本系统自动从15个选手中选出前10名参加决赛。名单自动显示在决赛区的第一列。其它列同初赛区。为了获得决赛区名单，需做两个辅助列。在工作表“初赛区”中，在Q2单元格中输入字段“拼合”，在R2单元格中

5、输入字段“按大小排序”。在Q3单元格中输入”=INT（CONCATENATENT（N3*100）,200-ROW（A1）"。公式中ROWA1）为A1单元格所在的行数即为1（该公式下拉时依次为2、3、4），用200来减是为了CONCATENATED中的第2个参数保持3位数，CONCATENATE函数是一个拼合函数，这里把N列的数据和它所在的行数拼合成一个数据。这样在对它进行排序后该数据包含了它所在行数的信息。CONCATENATED和INT函数套用是为了把原来的文本变为数字。在R3单元格中输入”=LARGE（Q:QROWA1）”,即对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-RIG