摘 要:利用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
以上就是整个制作过程以及对使用的函数数组的解说,希望能对大家有所帮助。