体育教学论文：用Excel实现中学生运动会成绩统计.doc

用excel实现中学生运动会成绩统计 摘要：第十届中学生运动会于2012年7月28日顺利闭幕，本届运动会赛中成绩统计工作在其中扮演了重要的枢纽角色，肩负巨大的任务，最终得以超额。本次成绩统计运用常规excel函数语句来实现各代表队的计分，根据大会规程，尤其是针对双计分办法的特殊性，制订了一套涵盖10个大项，共计203个小项的相关电子表格。计分工作采用赛前做好所有因变量录入语句，赛中输入相应自变量自动计分的模式，较传统手工录入计分更为轻松，更为高效，也更为准确，为赛事各部门工作的顺利进行奠定了坚实的基础。本成绩计分方法简单易学，旨在为日后各类运动会或单项比赛的计分统计工作提供新的思路及依据，更望在相关统计领域得到普遍使用。关键词：excel 中学生 运动会 成绩统计一、问题的提出综合性运动会中的成绩统计工作往往是整个赛事中最重要而又最忙碌的一部分，准确、快速是成绩统计工作的基本要求，输入工作量大，核对数据多，赛中与各赛区联系频繁等一系列问题给这份工作带来很多困难与麻烦。在以往不同层次的各种比赛中，成绩统计工作绝大部分要么采用手工单纯输录，但错误发生的几率较大，而且速度也跟不上赛会需要；要么需装载专项性特别强的软件进行输录，但因为专项性过强或实用权限问题，不容易得到普及。 出于以往成绩统计工作普遍遇到的问题，再加之第十届中学生运动会赛事计分规格较高，统计人员数量非常有限，而且所有项目都涉及到双计分办法的特殊性，笔者突破思维，利用老工具、新方法来达到本届中学生运动会各项目成绩统计的目的。二、设计思路为了在赛中能够快速、准确地录入成绩，准确地进行统计，前期工作就要准备充分，笔者设计用excel电子表格公式函数进行成绩表的统计。按照运动会竞赛规程的规定，比赛前八名的积分、破纪录奖分是固定的，这是定量；而获得前八名的人员或运动队以及成绩是未知的，这是变量。本统计工作思路就是把定量的工作先做，利用excel函数，设计一个成绩统计程序，预先设置好各个需要打印页的页面，然后在收到比赛成绩后只要输入变量，整个统计流程由系统自动完成，同时马上可以把统计表打印出来，全部比赛结束后，基本可以同步打印出简易的成绩册。整个统计工作的流程为：赛前设计统计表赛中通过电子邮箱或传真机收集各项目成绩把成绩输入成绩统计表统计表自动进行积分和奖牌统计赛后打印简易成绩册。根据本次运动会的竞赛规程，自动评分统计表设计为12个excel文件，各文件包含的工作表如下：1、运动会奖牌榜包括有4个工作表：综合奖牌榜、甲组奖牌榜、乙组奖牌榜、各项目奖牌榜。2、运动会团体总分包括有3个工作表：综合团体总分、甲组团体总分、乙组团体总分3、田径成绩统计表、游泳成绩统计表结构相同，分别包括有7个工作表：甲组男子成绩表、甲组女子成绩表、乙组男子成绩表、乙组女子成绩表、总分表、破纪录表、参赛运动员名单4、篮球成绩统计表、排球成绩统计表、足球成绩统计表结构相同，分别包括有4个工作表：总分表、奖牌榜、名次表、参赛运动员名单5、乒乓球成绩统计表包括有3个工作表：总分表、名次表、参赛运动员名单6、武术成绩统计表、定向运动成绩统计表结构相同，分别包括有6个工作表：甲组男子成绩表、甲组女子成绩表、乙组男子成绩表、乙组女子成绩表、总分表、参赛运动员名单7、毽球成绩统计表、健美操成绩统计表结构相同，分别包括有4个工作表：总分表、甲组成绩表、乙组成绩表、参赛运动员名单其中“奖牌榜”和“团体总分”这两个文件的所有数据均来自各个项目的成绩统计表，不需要人工输入数据，只是设置好函数公式，由电脑自动实现统计。只要在各运动项目成绩统计表中输入各单项前八名的运动员姓名、成绩，团体项目只输入队名，由系统根据运动员的姓名生成对应的单位，系统根据各代表队获得的名次自动计算各项目的积分总分和奖牌。三、设计过程1、田径成绩统计表1.1 制定各组别成绩表图1先按打印要求设计好所需要打印的内容及版面，由于获得前八名运动员名单和成绩是不确定的，也即是说这是变量，而运动员所属单位是确定的，这是定量。如图1，“姓名”行和“成绩”行是需要输入的，而“单位”行可用公式：c4= if(c3=,vlookup(c3, 参赛运动员名单!$a$1:$b$310,2,false)。其他项目和组别以相同的方法完成“单位”行的公式。1.2 制定运动员名单表并标记双计分运动员把已经确认的参赛运动员名单复制入参赛运动员名单工作表，双计分运动员在“双计”列输入字母“y”，以便于计算双计分时识别,如图2。图21.3 制定总分表（如图3） 1.3.1 设置各代表队团体总分图3各代表队的团体总分=第一至第八名得分+破纪录得分+名次并列得分+双计分运动员得分(佛山和顺德)。如广州队的团体总分公式为：=sum(d3:n3)1.3.2 设置各代表队名次得分d列为名次并列计分，如广州队甲组名次并列得分的函数公式为（其他代表队积分直接复制-粘贴就可以）：d3 =sumif(甲组男!$m$2:$m$40,总分和奖牌!b4,甲组男!$o$2:$o$40)第一至第八名得分，如广州队甲组第一名得分的函数公式为：e3 =countif(甲组男!c$3:c$23,$b3)*9 径赛单项+countif(甲组男!c$28,$b3)*18 4100米接力+countif(甲组男!c$34,$b3)*18 4400米接力+countif(甲组男!c$36:c$50,$b3)*9 田赛单项+countif(甲组男!c$52,$b3)*18 全能项目+countif(甲组女!c$3:c$23,$b3)*9+countif(甲组女!c$28,$b3)*18+countif(甲组女!c$34,$b3)*18+countif(甲组女!c$36:c$50,$b3)*9+countif(甲组女!c$52,$b3)*18同理，广州队第二名得分统计公式为：f3=countif(甲组男!d$3:d$23,$b3)*7+countif(甲组男!d$28,$b3)*14+countif(甲组男!d$34,$b3)*14+countif(甲组男!d$36:d$50,$b3)*7+countif(甲组男!d$52,$b3)*14+countif(甲组女!d$3:d$23,$b3)*7+countif(甲组女!d$28,$b3)*14+countif(甲组女!d$34,$b3)*14+countif(甲组女!d$36:d$50,$b3)*7+countif(甲组女!d$52,$b3)*14广州队第三名得分统计公式为：g3=countif(甲组男!e$3:e$23,$b3)*6+countif(甲组男!e$28,$b3)*12+countif(甲组男!e$34,$b3)*12+countif(甲组男!e$36:e$50,$b3)*6+countif(甲组男!e$52,$b3)*12+countif(甲组女!e$3:e$23,$b3)*6+countif(甲组女!e$28,$b3)*12+countif(甲组女!e$34,$b3)*12+countif(甲组女!e$36:e$50,$b3)*6+countif(甲组女!e$52,$b3)*12广州队第四名得分统计公式为：h3=countif(甲组男!f$3:f$23,$b3)*5+countif(甲组男!f$28,$b3)*10+countif(甲组男!f$34,$b3)*10+countif(甲组男!f$36:f$50,$b3)*5+countif(甲组男!f$52,$b3)*10+countif(甲组女!f$3:f$23,$b3)*5+countif(甲组女!f$28,$b3)*10+countif(甲组女!f$34,$b3)*10+countif(甲组女!f$36:f$50,$b3)*5+countif(甲组女!f$52,$b3)*10广州队第五名得分统计公式为：i3=countif(甲组男!g$3:g$23,$b3)*4+countif(甲组男!g$28,$b3)*8+countif(甲组男!g$34,$b3)*8+countif(甲组男!g$36:g$50,$b3)*4+countif(甲组男!g$52,$b3)*8+countif(甲组女!g$3:g$23,$b3)*4+countif(甲组女!g$28,$b3)*8+countif(甲组女!g$34,$b3)*8+countif(甲组女!g$36:g$50,$b3)*4+countif(甲组女!g$52,$b3)*8广州队第六名得分统计公式为：j3=countif(甲组男!h$3:h$23,$b3)*3+countif(甲组男!h$28,$b3)*6+countif(甲组男!h$34,$b3)*6+countif(甲组男!h$36:h$50,$b3)*3+countif(甲组男!h$52,$b3)*6+countif(甲组女!h$3:h$23,$b3)*3+countif(甲组女!h$28,$b3)*6+countif(甲组女!h$34,$b3)*6+countif(甲组女!h$36:h$50,$b3)*3+countif(甲组女!h$52,$b3)*6广州队第七名得分统计公式为：k3=countif(甲组男!i$3:i$23,$b3)*2+countif(甲组男!i$28,$b3)*4+countif(甲组男!i$34,$b3)*4+countif(甲组男!i$36:i$50,$b3)*2+countif(甲组男!i$52,$b3)*4+countif(甲组女!i$3:i$23,$b3)*2+countif(甲组女!i$28,$b3)*4+countif(甲组女!i$34,$b3)*4+countif(甲组女!i$36:i$50,$b3)*2+countif(甲组女!i$52,$b3)*4广州队第八名得分统计公式为：l3=countif(甲组男!j$3:j$23,$b3)*1+countif(甲组男!j$28,$b3)*2+countif(甲组男!j$34,$b3)*2+countif(甲组男!j$36:j$50,$b3)*1+countif(甲组男!j$52,$b3)*2+countif(甲组女!j$3:j$23,$b3)*1+countif(甲组女!j$28,$b3)*2+countif(甲组女!j$34,$b3)*2+countif(甲组女!j$36:j$50,$b3)*1+countif(甲组女!j$52,$b3)*2破纪录加分放在n列，如广州队破纪录加分公式为：n3=sumproduct(破纪录情况!$c$4:$c$9=甲组)*(破纪录情况!$e$4:$e$9=总分和奖牌!b3)*18+sumproduct(破纪录情况!$c$12:$c$27=甲组)*(破纪录情况!$e$12:$e$27=总分和奖牌!b3)*9 （注：破全国中学生纪录加18分，破省中学生纪录加9分。）佛山和顺德双计分运动员计分公式放在n列，如佛山双计分公式为：m7=sumif(甲组男!$ak$1:$ak$30,总分和奖牌!b7,甲组男!$aj$1:$aj$3)1.3.3 制定破纪录表，设置破纪录得分对破纪录的计分，专门设计了一个破纪录列表，并在n列用公式统计，如：n3=sumproduct(破纪录情况!$c$4:$c$9=甲组)*(破纪录情况!$e$4:$e$9=总分和奖牌!b3)*18+sumproduct(破纪录情况!$c$12:$c$27=甲组)*(破纪录情况!$e$12:$e$27=总分和奖牌!b3)*9。破纪录列表格式如图4。图41.3.4 处理名次并列得分图5关于名次并列的计分，由于名次并列的现象不多，而计分公式的设计要考虑各个名次是两人(队)还是三人(队)的并列等，公式过于繁琐，所以将并列名次的计分在每个组的名次表中用辅助列列出来，用人工输入所有数据得分。打印时把这些列隐藏，不至于影响版面，如图5。在“统计表”工作表的m列中设计函数公式进行统计。如：m3= sumif(甲组男!$m$1:$m$30,总分和奖牌!b3,甲组男!$o$1:$o$30)+sumif(甲组女!$m$1:$m$30,总分和奖牌!b3,甲组女!$o$1:$o$30)+ sumif(乙组男!$m$1:$m$30,总分和奖牌!b3, 乙组男!$o$1:$o$30)+ sumif(乙组女!$m$1:$m$30,总分和奖牌!b3,乙组女!$o$1:$o$30)1.3.5 设置双计分运动员得分双计分运动员得分，本届运动会共有33名运动员需要进行双计分(表1)，由于双计分的办法比较复杂，按比赛规程的规定：a.单项比赛（单人项目）：在某项目比赛中，佛山或顺德不论任何一队有多少双计分运动员获得名次，仅取其中最好成绩的1名运动员所获名次的得分，按50%分值计入相应代表队,计算式为：双计分运动员获该项目最高名次得分0.5。b.单项比赛（两人以上项目）、田径接力、游泳接力：以参加决定最终名次比赛的人数为基数，双计分方取所获名次得分的人均分数乘以双计分人数再按50%分值计入相应代表队,计算式为：获得名次的双计分运动员人数(名次得分/获得该名次的本队人数)0.5(如1个双计分运动员获接力第一名：=1(18/4)*0.5=2.25)。c.乒乓球团体、篮球、足球项目：以到赛区确认参赛人数为基数，双计分另一方取所获名次得分的人均分数乘以双计分人数再按50%分值计入相应代表队；但双方的计分，最终均不得超过该项目最高名次的分数,计算式为：本项目人均得分双计分人数0.5。以田径为例。表1 双计分人数统计表项目组别性别佛山双计分顺德双计分田径甲组男2田径甲组女32田径乙组男1田径乙组女11游泳甲组男2游泳甲组女12游泳乙组女1武术甲组男1武术甲组女2武术乙组男11武术乙组女21篮球甲组男3篮球甲组女1足球甲组女4乒乓球甲组男1为了简化系统，笔者把各个项目每个组别的双计分运动员在各项目名次表中列出来，用公式统计出这些运动员获得各名次的数量，然后根据规定用人工计算双计分得分输入电脑， 对双计分运动员破纪录的加分同样是用人工计算。如男子甲组田径双计分如图6。图7图61.3.6 设置田径奖牌榜，如图7，r3 =countif(甲组男!$c$3:$c$62,*广州*)+countif(甲组女!$c$3:$c$62,*广州*)s3 =countif(甲组男!$d$3:$d$62,*广州*)+countif(甲组女!$d$3:$d$62,*广州*)t3 =countif(甲组男!$e$3:$e$62,*广州*)+countif(甲组女!$e$3:$e$62,*广州*)u3 =sum(r3:t3)p3 =if(u3=,rank(u3,$u$3:$u$24)2、田径成绩统计表游泳项目的成绩统计方法与田径相同，乒乓球、健美操、武术、毽球与田径项目的成绩统计方法也是相同的，只是没有成绩和破纪录这两项，比田径和游泳更简单一些。3、足、篮、排三大球成绩统计表图8足球、篮球、排球项目的成绩统计完全是相同的，以男子甲组足球为例，如图8。3.1 制定名次表。首先在“名册”工作表中输入经大会确认的各运动队名单，大会规定每个参赛队参赛运动员篮球是18人，在名次表按图8的格式设置好，b列空出来，设置数组公式：c3: =index(名册!$c$2:$c$500,small(if(名册!$b$2:$b$500=名次表!$b3,row(名册!$c$2:$c$500)-1),column(a1) d3: =index(名册!$c$2:$c$500,small(if(名册!$b$2:$b$500=名次表!$b3,row(名册!$c$2:$c$500)-1),column(b1) e3: =index(名册!$c$2:$c$500,small(if(名册!$b$2:$b$500=名次表!$b3,row(名册!$c$2:$c$500)-1),column(c1)f3、g3、h3、c4、d4、e4、f4、g4、h4的公式只是修改此公式中的“column(c1)”为“column(d1)、column(e1) column(f1) column(g1) column(h1) column(i1)”就可以了。图9统计时只需要在b列中输入获得第一名至第八名的运动队，表格自动列出该队参赛运动员名单。3.2 设置团体总分（如图9）如广州队总分f3 =sum(c3:e3)。c3=sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$h$2)*45+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$i$2)*35+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$j$2)*30+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$k$2)*25+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$l$2)*20+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$m$2)*15+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$n$2)*10+sumproduct(名次表!$b$3:$b$26=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$o$2)*5同理，c4=sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$h$2)*45+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$i$2)*35+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$j$2)*30+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$k$2)*25+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$l$2)*20+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$m$2)*15+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$n$2)*10+sumproduct(名次表!$b$29:$b$52=总分及奖牌!$b3)*(名次表!$a$3:$a$26=总分及奖牌!$o$2)*5佛山队双计分得分公式：e7 =sum(名次表!k3:k50)由于女子足球有双计分运动员，所以在“名次表”工作表的j、k、l列用辅助列，计算双计分，如图10，图10k29=if(b29=佛山),45*0.5*sumproduct(名册!$f$1:$f$500=佛山)*(名册!$h$1:$h$500=y)/countif(名册!$f$1:$f$500, 佛山),0)k30=if(b32=$m$2,35*0.5*sumproduct(名册!$f$1:$f$500=佛山)*(名册!$h$1:$h$500=y)/countif(名册!$f$1:$f$500, 佛山),0)k31=if(b35=$m$2,30*0.5*sumproduct(名册!$f$1:$f$500=佛山)*(名册!$h$1:$h$500=y)/countif(名册!$f$1:$f$500, 佛山),0)以一公式只其中的积分改为：“25*0.5”、“20*0.5”、“15*0.5”、“10*0.5”、“5*0.5”3.3 设置奖牌榜 （如图11 ）图11u3 =sumproduct(名次表!$a$2:$a$52=第一名)*(名次表!$b$2:$b$52=总分及奖牌!$t3)v3 =sumproduct(名次表!$a$2:$a$52=第二名)*(名次表!$b$2:$b$52=总分及奖牌!$t3)w3 =sumproduct(名次表!$a$2:$a$52=第三名)*(名次表!$b$2:$b$52=总分及奖牌!$t3)x3 =sum(u3:w3)s3 =rank(x3,$x$3:$x$24)4、定向运动成绩统计表定向运动，与其他项目不同的是，在同组、同项目比赛中，名次并列时，下一名次不空缺，名次并列的代表队名次分双计。所以统计公式要作修改，如广州队第一名得分的公式是：=(countif(甲组男!c$3:c$11,*广州*)+countif(甲组女!c$3:c$11,*广州*)*9+(countif(甲组男!c$17,*广州*)+countif(甲组女!c$17,*广州*)*18而深圳队第三名的得分公式可写成是=(countif(甲组男!e$3:e$11,*深圳*)+countif(甲组女!e$3:e$11,*深圳*)*(8-column(b2)+(countif(甲组男!e$17,*深圳*)+countif(甲组女!e$17,*深圳*)*(16-column(b2)*2) 定向运动项目的团体总分和奖牌榜的计算方法与田径项目相同，不再赘述。5、代表队团体总分统计表本届运动会要计算三个团体总分：综合团体总分、甲组团体总分和乙组团体总分。甲组团体总分和乙组团体总分的格式和计算方式是一样的,仍然是用vlookup函数，如图12。图12如：c3 =vlookup(b3,e:my documents2012-7省中运会2012-7中运会每天成绩2012-7省中运会成绩统计2012-7省中运会田径成绩及总分奖牌.xls总分和奖牌!$b$2:$c$24,2,false)（公式中e:my documents2012-7省中运会2012-7中运会每天成绩2012-7省中运会成绩统计2012-7省中运会田径成绩及总分奖牌.xls总分和奖牌是指定的文件路径）d3 =vlookup($b3,e:my documents2012-7省中运会2012-7中运会每天成绩2012-7省中运会成绩统计2012-7省中运会游泳成绩及总分奖牌.xls总分和奖牌!$b$2:$c$24,2,false)e3=vlookup($b3,e:my documents2012-7省中运会2012-7中运会每天成绩2012-7省中运会成绩统计2012-7省中运会篮球成绩成总分奖牌.xls总分及奖牌!$b$2:$f$24,5,false)总分和排名的公式还是用sum和rank函数。图13综合团体总分如图13，其中甲乙组得分为甲组和乙组团体总分之和，3年竞赛积分是2008年9月1日至2012年4月30日各地自办竞赛和参加相应组别比赛积分之和，此项成绩另外单独计算。a3 =if(g3=,rank(g3,$g$3:$g$24)c3 =vlookup(b3,甲组!$b$2:$m$24,12,false)+vlookup(综合!b3,乙组!$b$2:$i$24,8,false)d3 =c3*0.7h3 =if(a33,a38,a3=15),三等奖,)6、奖牌榜