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

1、EXCEL在体育成绩统计上的运用EXCEL在体育成绩 统计上的运用EXCEL在体育成绩统计上的运用【摘 要】 EXCEL是我们熟悉的“表格”处理软件，它支持强大的数据分析和数据处理。基于EXCEL的体育成绩管理、统计、分析，弥补了手工统计的不足,提高体育成绩统计的作用和效率。【关键词】EXCEL 体育成绩 统计一、引言体育学科在成绩的计算、统计、查询上与其它学科相比有着其特殊性。因为体育成绩的计量单位非常多,如“秒”、“米”、“个”等，但是这些具体的成绩又要折合为分数,才能评判学生的成绩。随着科学的发展，以往要用人工完成的事情，很多可以用计算机来完成了。比如像上面提到的体育成绩计算、统计、查询

2、，我们可以用C语言，FOXPOR等编写一个程序来完成，但是这些软件涉及到复杂的程序编写，我们普通的老师没法做到。EXCEL的出现，为我们在体育成绩的统计上开辟了另一扇窗。二、EXCEL及常用函数Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择，可以实现许多方便的功能，给使用者

3、方便。我们在体育成绩的计算、统计、查询上常用的函数有：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（小数点

4、后一位四舍五入）=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。S

5、um_range 是需要求和的实际单元格。含义:只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。如果忽略了 sum_range，则对区域中的单元格求和。例： A B C 1 姓名 班级 得分 2 乙 6.1 923 丙 6.1 904 丁 6.2 895 戊 6.3 95公式（结果） =SUMIF(B2:B5,=6.1,C2:C5) 含义：班级为6.1班的学生的成绩和为(182) 3. COUNTIF函数COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件

6、，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 89、89、=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_

7、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可以嵌套七层,用

8、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_vecto

9、r 的数值可以为文本、数字或逻辑值。要点：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。例：立定

10、跳远成绩计算制作上表，由于立定跳远男女标准不同，所以我们加入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

11、的数，那就是1.86，所以公式计算结果为10。例2：50mX8成绩计算根据如果函数LOOKUP找不到与学生所得成绩相对应的lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。而通常情况下在评径赛成绩时却与之相反，如上图，表2中的50mx8成绩中，10分为124和9.8分为126，如果你跑了125就打10分了，这肯定是不行的。所以在径赛中我们要将成绩作负数处理（如上图，表3）这样保证了输入125时不是打10分，而是打9.8分由于lookup_value小于lookup_vector中的最小值，函数LOOKUP会返回错误信息，所以在上表3中第一

12、行(即最小值行)前插了一个非常小的数值，如“-50”，以保证函数返回值的正确。 6.RANK 函数RANK(number,ref,order) umber 为需要找到排位的数字。 Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。 Order 为一数字，指明排位的方式。 如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 例：1 姓名 班级 得分 排名2 乙 6.1 92 3 丙 6.1 9

13、0 4 丁 6.2 89 5 戊 6.3 95 16 甲 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。这可通过将下列修正因素添加到按排位返回的

14、值来实现。该修正因素对于按照升序计算排位（顺序 = 非零值）或按照降序计算排位（顺序 = 0 或被忽略）的情况都是正确的。 消除重复数排位的公式例： A B C D E1 姓名 班级 得分 重复排名 无重复排名 2 乙 6.1 92 2 33 丙 6.1 90 4 44 丁 6.2 89 5 55 戊 6.3 95 1 16 甲 6.3 92 2 2我们在E2中输入 =RANK(C2,C$2:C$6,0)+COUNTIF(C$2:C2,C2)-1 ，向下拖动复制，可以得到无重复的排名。这里要含义一下，$加在C2中是阻止向下拖动复制时C2向D2,E2,F2变化。8.OR函数OR 仅当所有参数值均

15、为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”，即如果其中一个条件为真，则返回结果为真。三、体育成绩统计、查询模板设计下面以小学六年级的立定跳远和50mX8为例，制作一个体育成绩管理模板。1、 新建工作表打开EXCEL,新建一EXCEL文档, 在工作表区分别建立以下工作表1.1在成绩工作表里输入相关的各指标数值,如图下所示。当然我们根据需要可以输入更多的项目。其中性别一列采用数据有效性进行设置。具体方法:选取D2:D40单元格区域单击“数据”菜单“有效性”在打开的“数据有效性”对话框中作如下图所示的设置:选取“设置”选项卡,在“允许”下拉列表框中

16、选择“序列”,在“来源”文本框中输入“男,女”(注意中间的逗号是在英文状态下输入的),单击“确定”即可。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(E

17、2,标准!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

18、.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中录入

19、：=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-B4B7B12中录入：=COUNTIF(成绩!F$2:F$40,缺考)含义：B5中的函数后面“-B4”，意思是统计了成绩大于等于9.5分的人数减去等于成绩为10分的人的人

20、数 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函数在成绩工作表中统

21、计各班人数即可。在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”的单元格

22、个数。其它分数段也可以照例录入函数，如下图：=SUM(成绩!B$2:B$40=6.1)*(成绩!I$2:I$40=18)*(成绩!I$2:I$40缺考)-B183.3.4 EXCEL图表根据各项数据，我们可以制作各种相关的数据图表，比如： 这是以全年级的立定跳远和50Mx8各分数段的数据图。3.3.5 等级评定根据学生总分，还可以为学生自动打出成绩等级。打开成绩工作表在K1中输入“等级”在K2编辑栏中输入“=IF(OR(I2=缺考,F2=缺考,H2=缺考),缺考,IF(OR(I212,F26,H26,),不及格,IF(I214,及格,IF(I28,良,优)如图：4.成绩查询及宏的录制打开查询工

23、作表，输入如下图内容：利用LOOKUP函数，在C2中输入：=LOOKUP($B$2,成绩!$A$2:$A$40,成绩!$C$2:$C$40)D2，E2,F2中按照C2方法输入 相应函数。另外，由于学生在编号在成绩工作表中有可能不是按照升序排列，这样“LOOKUP”函数就有可能出错。所以我们在此录制一个宏，来使成绩工作表的编号以及其扩展按照升序排列。方法如下：点击视图宏录制宏宏名中输入“编号升序”-确定点开成绩工作表-选中编号栏点击开始-排序和筛选-升序排列-点开成绩查询工作表视图-宏-停止录制。这样当我们要用学号来查询学生成绩时，首先点开视图宏查看宏选中“编号升序”执行。然后在编号中随意输入一

24、学生编号，如60004，可以得出如图的结果：这样我们在输入编号查询学生成绩就不会出错了。同样的方法，我们可以在姓名查询行中输入相应函数。5模板保存当制作完成以上工作表之后，为了防止误删除、改写函数，EXCEL还有数据保护功能。在工作表中选中函数单元格，点右键设置单元格格式保护隐藏确定点击审阅保护工作表输入密码（或者不要密码）-确定。这样这些函数就被保护、隐藏起来了。（有#N/A是因为在编号中没有输入数据）（有保护状态）完成所有保护工作之后，点击保存，输入文件名，选择保存为模板。6.结论学校体育成绩管理在整个教学活动中占有举足轻重的作用,而EXCEL则在学校体育的成绩管理、分析上是不能或缺的工具。这里只是举例说明了部分函数在体育成绩管理上的作用，EXCEL还有很多函数值得我们去探究。