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

EXCEL在体育成绩统计上的运用EXCEL在体育成绩统计上的运用【摘要】EXCEL是我们熟悉的“表格”处理软件，它支持强大的数据分析和数据处理。基于EXCEL的体育成绩管理、统计、分析，弥补了手工统计的不足，提高体育成绩统计的作用和效率。【关键词】EXCEL体育成绩统计一、引言体育学科在成绩的计算、统计、查询上与其它学科相比有着其特殊性。因为体育成绩的计量单位非常多，如“秒”、“米”、“个”等，但是这些具体的成绩又要折合为分数，才能评判学生的成绩。随着科学的发展，以往要用人工完成的事情，很多可以用计算机来完成了。比如像上面提到的体育成绩计算、统计、查询，我们可以用C语言，FOXPOR等编写一个程序来完成，但是这些软件涉及到复杂的程序编写，我们普通的老师没法做到。EXCEL的出现，为我们在体育成绩的统计上开辟了另一扇窗。二、EXCEL及常用函数Excel是微软公司的办公软件Microsoftoffice的组件之一，是由Microsoft为Windows和AppleMacintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择，可以实现许多方便的功能，给使用者方便。我们在体育成绩的计算、统计、查询上常用的函数有：LOOKUP，SUMIF，COUNTIF，SUM，IF，ROUND等。下面以EXCEL2007为例，举例来介绍这几个函数的用法。ROUND函数ROUND(number,num_digits)，它的功能就是根据指定的位数，将数字四舍五入。这个函数有两个参数，分别是number和num_digits。其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。例：公式二R0UND(74.58,-1)二R0UND(74.58,1)=ROUND(74.58,-2)=ROUND(74.583,2)结果 含义70.00（向十位四舍五入）74.6（小数点后一位四舍五入）100.00（向百位四舍五入）74.58（小数点后两位四舍五入）SUM和SUMIF函数SUM函数这里我不再细说了，相信大家对于这个求和函数都能运用。这里我要说一下SUMIF函数，这个函数主要用于成绩统计、分析等。SUMIF（range,criteria,sum_range），它的功能是根据指定条件对若干单元格求和。Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、〃32〃、〃＞32〃或"apples"。Sum_range是需要求和的实际单元格。含义：只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。例：ABC1姓名班级得分2乙6.1923丙6.1904丁6.2895戊6.395公式（结果）二SUMIF（B2:B5,〃=6.1〃,C2:C5） 含义：班级为6.1班的学生的成绩和为（182）COUNTIF函数COUNTIF（range,criteria）Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为89、〃89〃、〃＜89〃、〃缺考〃或B2。例：ABC1姓名班级得分2乙6.1923丙6.1904丁6.2895戊6.3956甲6.3缺考公式 含义 （结果）二C0UNTIF（B2:B6,6.1）计算第二列中6.1班所在单元格的个数 （2）二C0UNTIF（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的人数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。LOOKUP函数LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector 为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。要点：Lookupvector的数值必须按升序排序：...、-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。例：立定跳远成绩计算ABABCDEFHIJKL1吃寸卿50nX8得分26.32.23 1 5 .1-265018成錶男立罡立立定跳远删Z12.11.35E.4 ' 1.EL-50616.1L56.91.38-2.1160036.2LS61.37-2.16.31X10.L6E12.31.32乙■[1.12-2.006.61.511.3967-2.026.91.521.i6.3S-l.sa7.21.581.466.6■■-L.557.51.621.56.910-L.517-81.6T1.547.2II-L.478-11.T1.577.51--L.438.41.74：1.6E7.813-L.39S.71.791.£78.114-L.3G91.S31.71a.41--L.329.21.271.71a.?16-1.39.41.891.?791?-L.299.61.911.9.218-L.269.S1.931.BL9.*19-L.24101.951.B39.620-0.1101.9T1.E：59.82101.981.E610制作上表，由于立定跳远男女标准不同，所以我们加入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成绩计算5QX8■我绩例2：50mX8成绩计算5QX8■我绩5DI81.2410-501.26&.8-2.1A1.239.6-2.L1.39.4-2.061,339.2-2.021.369-1.581.39B.7-1.551.43E.4-1.511.47E.1-1.471.51?.3-1.131.557；5-1.39l.sa7.2-1.362.026.9-1.332.066.6-1.32.16.3-1.282,116~1-26506-1.21根据如果函数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”，以保证函数返回值的正确。RANK函数RANK（number,ref,order）umber为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。例：1姓名班级得分排名26.19236.19046.28956.395166.3缺考公式二RANK(C3,C2:C6,1)公式二RANK(C3,C2:C6,1)二RANK(C3,C2:C6,0)结果⑵⑶C3即90分在C2至C6中的升序排位C3即90分在C2至C6中的降序排位

含义：函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数10出现两次，其排位为5,则11的排位为7（没有排位为6的数值）。于按照升序计算排位（顺序是正确的。消除重复数排位的公式例：姓于按照升序计算排位（顺序是正确的。消除重复数排位的公式例：姓乙丙丁戊甲123456BCDE班级得分重复排名无重复排名6.192236.190446.289556.395116.39222Nn咸绩成绩统计成绩查询标准S7Nn咸绩成绩统计成绩查询标准S71.1在成绩工作表里输入相关的各指标数值,如图下所示。当然我们根据需要可以输入更多的项目。我们在E2中输入二RANK（C2,C$2:C$6,O）+COUNTIF（C$2:C2,C2）-1，向下拖动复制，可以得到无重复的排名。这里要含义一下，$加在C2中是阻止向下拖动复制时C2向D2,E2,F2…变化。8.0R函数OR仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”，即如果其中一个条件为真，则返回结果为真。三、体育成绩统计、查询模板设计下面以小学六年级的立定跳远和50mX8为例，制作一个体育成绩管理模板。1、新建工作表1、打开EXCEL,新建一EXCEL文档，在工作表区分别建立以下工作表26

其中性别一列采用数据有效性进行设置。具体方法:选取D2:D40单元格区域一单击“数据”菜单一“有效性”一在打开的“数据有效性”对话框中作如下图所示的设置:选取“设置”选项卡,在“允许”下拉列表框中选择“序列”,在“来源”文本框中输入“男,女”（注意中间的逗号是在英文状态下输入的）,单击“确定”即可。1.2根据《国家体质健康标准》在标准工作表中输入如下成绩2ECDE1六年级体育成绩标准£50IB男宜定立立定3-50£4-3.11F00516.3山1Ou166-2.E.61.511.39S7-2.0E6.91.52L46.33-1.58T.Z1.5B6.69-1.557.51.E21.56.910-1.517.81.671.54T.211-1.17S.11.71.577.512-1.+3S.41.741.62T.S13-1.398.71.7B1.678«114-1.3691.331.T13.415-1.339.21.97J..T48.T16-1.39.41.091.T?9IT-1.ZB旦61.911.T99.Z18-1.2&9.81.931.81-1.24101.951.8320-0,11Q1.?7lr359.S210i-.-t1.5B1.S610（为了方便时间的录入，我们以“.”表示分，“2.11”，即2分11秒。也可以将录入区改为标准时间格式，比如“2：11”）2.各项成绩计算计算“立定跳远”得分由于六年级立定跳远的男、女生成绩标准不同，所以我们用上了IF函数。在F2中输入：=IF（D2二〃女〃，L00KUP（E2,标准!D$4:D$21,标准!E$4:E$21）,L00KUP（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对应的数。这里“标准！”是指向某工作表的意思。结果如图：

■.-60001 6. 160002 6. 160003 6. 16CJ04 6. 160005 6. 16CiiiO6 6. 1E.C'JOT 6, 16000S 6. 16CiiiO9 6. 26DQLQ 6. 2■.-60001 6. 160002 6. 160003 6. 16CJ04 6. 160005 6. 16CiiiO6 6. 1E.C'JOT 6, 16000S 6. 16CiiiO9 6. 26DQLQ 6. 260011 6. 213 &0012 6. 26DQL3 6. 260014： 6. 2&0015 6. 260016 6. 210 6001T 6. 2LT5 7.£1.5 61-£B 8.1L55 6.31-55 7b21-71 乩41-B S.1根据此公式我们可以轻松得出不同性别的学生的立定跳远成绩。如果成绩为空，会自动“缺考”。为了快速录入，可以将E2:E40表格选中，右键——设置单元格格式——数字——自定义——类型中输入00”.”00，确定。以后在E2：E40单元格中，只要输入211，表格就会自动将其“变”为“02.11”，效果不等于“2.11”。所以还得在标准工作表中，将标准也改为“02.11”的格式。如下图：E比0吞计专用谟蛋単无擀格式UUWWS式轴酣・主战自毛爼徹?粘屯[理二!1~^]曲]如]麻|isjpami[h]in55■rx■— 』au<■■TrMp/7ru®";*7Jb<f■恤・.・"・.PFFl：«-E]*,«.0J„3；[SI^]i.[Jf-Z] DO)DEF性别立定跳远得分男02.1110女01.456.3男01.556.3男宝定址远:jm.n..■.阳QQ.QQQQb0Q快书00.10OOuLO£01.51仏39e01.S201.40&b301.5301.466.601.S201.506„901.6701.54：7.201.P001.57化501.7+01„627.801.7901B67eKi01.8301.71B.401.8701.74：乩了01.as0E7T01.9101-799.201.9301.919.401.9501.339』01.9T01-359_801.9301-sa102.2计算“50mX8”得分由于六年级50mX8男女成绩标准相同。所以我们只需要在H2中编辑以下公式：二L00KUP（-G2,标准!A$3:A$21,标准!B$3:B$21）含义：由于我们录入成绩是以正数录入，而“标准”中我们设置的是负数，为了正确对应数据，所以在G2前加入“-”号。结果如图：血 审I* 二山皿UF 椁准LA舉油$21』标准佰$謝E也口— , E 匚MffiH：-宮WOOL6-13&DO026_1*— , E 匚MffiH：-宮WOOL6-13&DO026_1*&D0A36_156-16flfl遊6-1T&300SB_1e&30U?B-l9&300SB_110&30U9ii&3010艮212&d0il良213&0012e^214臥伯艮2]560014氐2]6&0016丘217&0C-16E^E：IBMC-1?6.2得廿1.991.723.41_*41.7E8.4£431.95亂61.331.5&7-21-521.75缶T:LS91.5$S.31-52u.-sn—c<&3&1O

&.TB&.THTB艮LedI2.3总成绩的计算总成绩，就是立定跳远和50mX8得分的总合，所以在12中输入公式:=IF(F2="缺考〃，〃缺考〃，IF(H22.3总成绩的计算总成绩，就是立定跳远和50mX8得分的总合，所以在12中输入公式:=IF(F2="缺考〃，〃缺考〃，IF(H2二〃缺考〃，〃缺考〃,F2+H2))结果如图：&0004&000]&00Q2&0CH33600-JS&aao760QQ3&00Q9&OCHO6(1(111&0012aaais&aa：L4&Q015&0016bdon名次统计在名次统计中，我们将用到RANK函数。我们在J2中录入：=IF(I2二〃缺考〃，〃缺考〃，RANK(I2,I$2:I$40,0))含义：降序，有重名排名；140可以根据学生人数改成1100,1400等。结果如图:XECXECE1:吩“柱■&性刖:.<■■'-2SCCDL6_11.5呂&.11-72■46CODS6_11.7Z■3flCOCHbl1.956fliBtsSLIi■的7smet6_11.738&.11.5S96CCC®6_11-35LO他㈤11fiCCluEl2l.TBL26)X)11&21»5L36>X)12&.2[■的%GiZClSEl21-S5LS6)X)141-55诃gccisEl21.71116)X)16&21.B闻fitci?6_呂LT6)X)1@氐21.5&za他19031-5TtiEl3l.TSZ2flOOSLfit32■曲洽fiCCZE6_31-7924何吊氐31-7BsEl31.791.54?.E13-1.557.E15.Z.OT6.312.l.St?.6Li.].47S.11G-1.210LH.址希1.5?.g15.2.1S12.1.017.81G-3．成绩统计打开成绩统计工作表，录入如图内容：ABCDp1成绩统计2全年级底绩情况3■ 成芸:i龙跳远?3*S410^2f：^f59.5^及二上上60廿及U上Ld讨倉臥上_lJ8.云及臥二L浹期丄上a呂井及二上g了分及以上£分■及二上11占分以下IB1115氏1班6.2班E・3班6+4班16皑分IT平均分IB20分人数18分至19+9分人数T1右分至1匚9分人数越分至1圧9分人数..不及格缺考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的具体分数，以及总成绩情况的统计和立定跳远具体分数的统计想同，这里不再多举例。结果如图：PS IzCOUHTIEr ![$2:It4«x^>=1 i-TEABC D&P1成绩分析表5全土级我多爭况3战织前垢:盼210E9-5曲及卸上101纷直H上4B9号及以上201蛉M上::T轧丘灵旦上14咔阴上S&分更以上139iz^ut09尬及以上1219犠寿人載310E分及以二411盼以下001222各班具体分数情况统计各班的总分统计，首先得把各班分出来，然后再相加。用“筛选”功能的话，如果单元格不连贯的话，也不好用公式统计。怎么办呢？还好上面有“SUMIF”函数。3.2.1各班部分统计我们在B16中录入：=SUMIF（成绩!B$2:B$40,6.1,成绩!1$2:1$40）含义：在成绩工作表的B2:B40中查找和本工作表“6.1”相同的数据，然后把它们对应12:140的数据相加。各班平均分统计由于上一步已经统计了各班的总分，所以现在只要用COUNTIF函数在成绩工作表中统计各班人数即可。在B17中录入函数：3.3.3各班分数段成绩统计各班分数段成绩统计要在成绩工作表中找出，班级、分数段、人数，所以就涉及到条件求和，如图，在B18中录入=SUM（（成绩！1$2:1$40=20）*（成绩!B$2:B$40=6.1）） 按CTRL+SHIFT+ENTER结束，得到{=SUM（（成绩!I$2:I$40=20）*（成绩!B$2:B$40=6.1））}的数组。ELB ”卞拄|{=SUUM成领！主0=20）和成锁!汩肌片氐1））}AD14156.1班6.2班6.3班6.4班总分'.IO.T130.7-.92.4124.517平均分13.3013.8716.0313.S320分人数0

含义：统计成绩工作表中，12:140中“=20”,并且B2:B40中“=6.1”的单元格个数。其它分数段也可以照例录入函数，如下图：=SUM（（成绩!B$2:B$40=6.1）*（成绩!1$2:1$40>=18）*（成绩!1$2:1$40<>〃缺考〃））-B18ABcDE F G_a14156.1班6,2班6.3班6.4班16总分-.10.^13B.7192.4124.3平均分13.BO1^.8715.0313.83pz20分人数0Lfj18分至19-9分人数 II1!1EXCEL图表根据各项数据，我们可以制作各种相关的数据图表，比如这是以全年级的立定跳远和50