EXCEL在体育成绩统计上的运用

./EXCEL在体育成绩统计上的运用EXCEL在体育成绩统计上的运用[摘要]EXCEL是我们熟悉的"表格"处理软件,它支持强大的数据分析和数据处理。基于EXCEL的体育成绩管理、统计、分析,弥补了手工统计的不足,提高体育成绩统计的作用和效率。[关键词]EXCEL体育成绩统计一、引言体育学科在成绩的计算、统计、查询上与其它学科相比有着其特殊性。因为体育成绩的计量单位非常多,如"秒"、"米"、"个"等,但是这些具体的成绩又要折合为分数,才能评判学生的成绩。随着科学的发展,以往要用人工完成的事情,很多可以用计算机来完成了。比如像上面提到的体育成绩计算、统计、查询,我们可以用C语言,FOXPOR等编写一个程序来完成,但是这些软件涉与到复杂的程序编写,我们普通的老师没法做到。EXCEL的出现,为我们在体育成绩的统计上开辟了另一扇窗。二、EXCEL与常用函数Excel是微软公司的办公软件Microsoftoffice的组件之一,是由Microsoft为Windows和AppleMacintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择,可以实现许多方便的功能,给使用者方便。我们在体育成绩的计算、统计、查询上常用的函数有：LOOKUP,SUMIF,COUNTIF,SUM,IF,ROUND等。下面以EXCEL2007为例,举例来介绍这几个函数的用法。1.ROUND函数ROUND<number,num_digits>,它的功能就是根据指定的位数,将数字四舍五入。这个函数有两个参数,分别是number和num_digits。其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。例：公式结果含义=ROUND<74.58,-1>70.00〔向十位四舍五入〕=ROUND<74.58,1>74.6〔小数点后一位四舍五入〕=ROUND<74.58,-2>100.00〔向百位四舍五入〕=ROUND<74.583,2>74.58〔小数点后两位四舍五入〕2.SUM和SUMIF函数SUM函数这里我不再细说了,相信大家对于这个求和函数都能运用。这里我要说一下SUMIF函数,这个函数主要用于成绩统计、分析等。SUMIF<range,criteria,sum_range>,它的功能是根据指定条件对若干单元格求和。Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为32、"32"、">32"或"apples"。Sum_range是需要求和的实际单元格。含义:只有在区域中相应的单元格符合条件的情况下,sum_range中的单元格才求和。如果忽略了sum_range,则对区域中的单元格求和。例：ABC1XX班级得分2乙6.1923丙6.1904丁6.2895戊6.395公式〔结果〕=SUMIF<B2:B5,"=6.1",C2:C5>含义：班级为6.1班的学生的成绩和为<182>3.COUNTIF函数COUNTIF<range,criteria>Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式、单元格引用或文本。例如,条件可以表示为89、"89"、"<89"、"缺考"或B2。例：ABC1XX班级得分2乙6.1923丙6.1904丁6.2895戊6.3956甲6.3缺考公式含义〔结果〕=COUNTIF<B2:B6,6.1>计算第二列中6.1班所在单元格的个数<2>=COUNTIF<B2:B6,B5>计算第二列中与B5〔即6.3〕相同的所在单元格的个数<2>=COUNTIF<C2:C6,"缺考">计算第三列中缺考的所在单元格的个数<<1>注意必须使用引号>这个公式也可写成=COUNTIF<C2:C6,C6>=COUNTIF<C2:C6,">=90">-COUNTIF<C2:C6,">=95">计算第三列中值大于或等于90且小于95的单元格个数<2>此公式可以解释为,计算第三列中大于90的人数减去大于且等于95的人数4.IF函数IF函数是较为常用的逻辑函数之一,它执行真假值判断,根据逻辑计算的真假值,返回不同结果。可以使用函数IF对数值和公式进行条件检测。函数语法:IF<logical_test,value_if_true,value_if_false>Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。Value_if_truelogical_test为TRUE时返回的值。例如,如果本参数为文本字符串"缺考"而且logical_test参数值为TRUE,则IF函数将显示文本"缺考"。如果logical_test为TRUE而value_if_true为空,则本参数返回0<零>。如果要显示TRUE,则应为本参数使用逻辑值TRUE。Value_if_true也可以是其他公式。例如,=IF<A1=89,"良好","合格">A1=89就是一个逻辑表达式,如果单元格A1中的值等于89,表达式即为TRUE<这里就是"良好">,否则为FALSE〔这里就是"合格"〕。本参数可使用任何比较运算符。函数IF可以嵌套七层,用value_if_false与value_if_true参数可以构造复杂的检测条件。含义：IF<logical_test,value_if_true,value_if_false>函数可以解释为,如果TEST成立,则执行TRUE,否则执行FALSE。5.LOOKUP函数LOOKUP<lookup_value,lookup_vector,result_vector>Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。要点：Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则,函数LOOKUP不能返回正确的结果。文本不区分大小写。Result_vector只包含一行或一列的区域,其大小必须与lookup_vector相同。含义：如果函数LOOKUP找不到lookup_value,则查找lookup_vector中小于或等于lookup_value的最大数值。如果lookup_value小于lookup_vector中的最小值,函数LOOKUP返回错误值#N/A。例：立定跳远成绩计算制作上表,由于立定跳远男女标准不同,所以我们加入IF函数,在E2中输入：=IF<B2="女",LOOKUP<D2,L$3:L$21,M$3:M$21>,LOOKUP<D2,K$3:K$21,M$3:M$21>>结果可以得出甲立定跳远2.23米的得分是10分。拖动E2公式,可以相应得出其它学生的立定跳远得分。公式解释：如果B2是"女",则在L3到L21列查找D2对应的位置,然后返回其对应行的M列的数；否则在K3至K21列查找D2对应的位置,然后返回其对应的行的M列的数。从上图可以看出,B2="女",D2=2.23,对应的L3至L21列的数没有,所以D2则对应比2.23小且最接近2.23的数,那就是1.86,所以公式计算结果为10。例2：50mX8成绩计算根据如果函数LOOKUP找不到与学生所得成绩相对应的lookup_value,则查找lookup_vector中小于或等于lookup_value的最大数值。而通常情况下在评径赛成绩时却与之相反,如上图,表2中的50mx8成绩中,10分为1′24″和9.8分为1′26″,如果你跑了1′25″就打10分了,这肯定是不行的。所以在径赛中我们要将成绩作负数处理〔如上图,表3〕这样保证了输入1′25时不是打10分,而是打9.8分由于lookup_value小于lookup_vector中的最小值,函数LOOKUP会返回错误信息,所以在上表3中第一行<即最小值行>前插了一个非常小的数值,如"-50",以保证函数返回值的正确。6.RANK函数RANK<number,ref,order>umber为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字,指明排位的方式。如果order为0〔零〕或省略,MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零,MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。例：1XX班级得分排名2乙6.1923丙6.1904丁6.2895戊6.39516甲6.3缺考公式含义结果=RANK<C3,C2:C6,1>C3即90分在C2至C6中的升序排位<2>=RANK<C3,C2:C6,0>C3即90分在C2至C6中的降序排位<3>含义：函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如,在一列按升序排列的整数中,如果整数10出现两次,其排位为5,则11的排位为7〔没有排位为6的数值〕。由于某些原因,用户可能使用考虑重复数字的排位定义。在前面的示例中,用户可能要将整数10的排位改为5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位〔顺序=非零值〕或按照降序计算排位〔顺序=0或被忽略〕的情况都是正确的。消除重复数排位的公式例：ABCDE1XX班级得分重复排名无重复排名2乙6.192233丙6.190444丁6.289555戊6.395116甲6.39222我们在E2中输入=RANK<C2,C$2:C$6,0>+COUNTIF<C$2:C2,C2>-1,向下拖动复制,可以得到无重复的排名。这里要含义一下,$加在C2中是阻止向下拖动复制时C2向D2,E2,F2…变化。8.OR函数OR仅当所有参数值均为逻辑"假〔FALSE〕"时返回结果逻辑"假〔FALSE〕",否则都返回逻辑"真〔TRUE〕",即如果其中一个条件为真,则返回结果为真。三、体育成绩统计、查询模板设计下面以小学六年级的立定跳远和50mX8为例,制作一个体育成绩管理模板。新建工作表打开EXCEL,新建一EXCEL文档,在工作表区分别建立以下工作表1.1在成绩工作表里输入相关的各指标数值,如图下所示。当然我们根据需要可以输入更多的项目。其中性别一列采用数据有效性进行设置。具体方法:选取D2:D40单元格区域→单击"数据"菜单→"有效性"→在打开的"数据有效性"对话框中作如下图所示的设置:选取"设置"选项卡,在"允许"下拉列表框中选择"序列",在"来源"文本框中输入"男,女"<注意中间的逗号是在英文状态下输入的>,单击"确定"即可。1.2根据《国家体质健康标准》在标准工作表中输入如下成绩〔为了方便时间的录入,我们以"."表示分,"2.11",即2分11秒。也可以将录入区改为标准时间格式,比如"2：11"〕2.各项成绩计算2.1计算"立定跳远"得分由于六年级立定跳远的男、女生成绩标准不同,所以我们用上了IF函数。在F2中输入：=IF<D2="女",LOOKUP<E2,标准!D$4:D$21,标准!E$4:E$21>,LOOKUP<E2,标准!C$4:C$21,标准!E$4:E$21>>函数解释：如果D2为女,则执行LOOKUP<E2,标准!D$4:D$21,标准!E$4:E$21>,否则执行LOOKUP<E2,标准!C$4:C$21,标准!E$4:E$21>。其中LOOKUP<E2,标准!C$4:C$21,标准!E$4:E$21>的意思是：在标准工作表中的D3至D21列,查找与E2对应的数。这里"标准！"是指向某工作表的意思。结果如图：根据此公式我们可以轻松得出不同性别的学生的立定跳远成绩。如果成绩为空,会自动"缺考"。为了快速录入,可以将E2:E40表格选中,右键——设置单元格格式——数字——自定义——类型中输入00"."00,确定。以后在E2：E40单元格中,只要输入211,表格就会自动将其"变"为"02.11",效果不等于"2.11"。所以还得在标准工作表中,将标准也改为"02.11"的格式。如下图：2.2计算"50mX8"得分由于六年级50mX8男女成绩标准相同。所以我们只需要在H2中编辑以下公式：=LOOKUP<-G2,标准!A$3:A$21,标准!B$3:B$21>含义：由于我们录入成绩是以正数录入,而"标准"中我们设置的是负数,为了正确对应数据,所以在G2前加入"-"号。结果如图：2.3总成绩的计算总成绩,就是立定跳远和50mX8得分的总合,所以在I2中输入公式：=IF<F2="缺考","缺考",IF<H2="缺考","缺考",F2+H2>>结果如图：2.4名次统计在名次统计中,我们将用到RANK函数。我们在J2中录入：=IF<I2="缺考","缺考",RANK<I2,I$2:I$40,0>>含义：降序,有重名排名；I40可以根据学生人数改成I100,I400等。结果如图:3．成绩统计打开成绩统计工作表,录入如图内容：3.1统计立定跳远的具体分数在B4中录入：=COUNTIF<成绩!F$2:F$40,"=10">；B5中录入：=COUNTIF<成绩!F$2:F$40,">=9.5">-B4；B6中录入：=COUNTIF<成绩!F$2:F$40,">=9">-B5-B4B7…B12中录入：=COUNTIF<成绩!F$2:F$40,"缺考">含义：B5中的函数后面"-B4",意思是统计了成绩大于等于9.5分的人数减去等于成绩为10分的人的人数…50mX8的具体分数,以与总成绩情况的统计和立定跳远具体分数的统计想同,这里不再多举例。结果如图：3.2各班具体分数情况统计各班的总分统计,首先得把各班分出来,然后再相加。用"筛选"功能的话,如果单元格不连贯的话,也不好用公式统计。怎么办呢？还好上面有"SUMIF"函数。3.2.1各班部分统计我们在B16中录入：=SUMIF<成绩!B$2:B$40,6.1,成绩!I$2:I$40>含义：在成绩工作表的B2:B40中查找和本工作表"6.1"相同的数据,然后把它们对应I2:I40的数据相加。3.2.2各班平均分统计由于上一步已经统计了各班的总分,所以现在只要用COUNTIF函数在成绩工作表中统计各班人数即可。在B17中录入函数：=B16/COUNTIF<成绩!$B$2:$B$40,"6.1">含义：用B16除以"COUNTIF<成绩!$B$2:$B$40,"6.1">"3.3.3各班分数段成绩统计各班分数段成绩统计要在成绩工作表中找出,班级、分数段、人数,所以就涉与到条件求和,如图,在B18中录入=SUM<<成绩!I$2:I$40=20>*<成绩!B$2:B$40=6.1>>按CTRL+SHIFT+ENTER结束,得到{=SUM<<成绩!I$2:I$40=20>*<成绩!B$2:B$40=6.1>>}的数组。含义：统计成绩工作表中,I2:I40中"=20",并且B2:B40中"=6.1"的单元格个数。其它分数段也可以照例录入函数,如下图：=SUM<<成绩!B$2:B$40=6.1>*<成绩!I$2:I$40>=18>*<成绩!I$2:I$40<>"缺考">>-B18EXCEL图表根据各项数据,我们可以制作各种相关的数据图表,比如：这是以全年级的立定跳远和50Mx8各分数段的数据图。3.3.5等级评定根据学生总分,还可以为学生自动打出成