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

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

2、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_dig

4、its 则是希望得到的数字的小数点后的位数。例 ：公式 结果 含义=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) ，它的功能是根据指定条件对若干单元

5、格求和。Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。Sum_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 班

6、的学生的成绩和为(182) 3. COUNTIF 函数COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 89、“89“、“=90“)-COUNTIF(C2:C6,“=95“) 计算第三列中值大于或等于 90 且小于 95 的单元格个数 (2)此公式可以解释为，计算第三列中大于 90 的人数减去大于且等于 95 的人数4. IF 函数 IF 函数是较为常用的逻辑函数之一,它执行真假值判断,根据逻辑计算的真假值,返回不同结

7、果。可以使用函数 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。V

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

9、(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 只包含一行或一列的区域，其大小必须与 l

10、ookup_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 分。

11、拖动 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 中小于或等于

12、lookup_value 的最大数值。而通常情况下在评径赛成绩时却与之相反，如上图，表 2 中的 50mx8 成绩中，10 分为 124和 9.8 分为 126，如果你跑了 125就打 10 分了，这肯定是不行的。所以在径赛中我们要将成绩作负数处理（如上图，表 3）这样保证了输入 125 时不是打 10 分，而是打 9.8 分由于 lookup_value 小于lookup_vector 中的最小值，函数 LOOKUP 会返回错误信息，所以在上表 3 中第一行(即最小值行)前插了一个非常小的数值，如“-50”，以保证函数返回值的正确。 6.RANK 函数RANK(number,ref,orde

13、r) umber 为需要找到排位的数字。 Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。 Order 为一数字，指明排位的方式。 如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 例：1 姓名 班级 得分 排名2 乙 6.1 92 3 丙 6.1 90 4 丁 6.2 89 5 戊 6.3 95 16 甲 6.3 缺考公式 含义 结果=RANK(C3,C2:C6,1) C3 即 90

14、分在 C2 至 C6 中的升序排位 (2) =RANK(C3,C2:C6,0) C3 即 90 分在 C2 至 C6 中的降序排位 (3)含义：函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。 由于某些原因，用户可能使用考虑重复数字的排位定义。在前面的示例中，用户可能要将整数 10 的排位改为 5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位（顺序 = 非零值）或按照降序计算排位（顺序 = 0 或被忽略）的情况

15、都是正确的。 消除重复数排位的公式例：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 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”，

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

17、的),单击“确定”即可。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

18、)，否则执行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”。所以还得在标准工

19、作表中，将标准也改为“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 函数。我

20、们在 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-B4B7B12 中录入：=COUNTIF(成绩!F$2:F$40,“缺考“)含义：B5 中的函数后面“-B4”，意思是

21、统计了成绩大于等于 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 各班平均分统

22、计由于上一步已经统计了各班的总分，所以现在只要用 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$4

23、0=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“缺考“)-B183.3.4 EXCEL 图表根据各项数据，我们可以制作各种相关的数据图表，比如：这是以全年级的立定跳远和 50Mx8 各分数段的数据图。3.3.5 等级评定根据学生总分，还可以为学生自动打出成绩等级。打开成绩工作表在 K1 中输入“等级”在 K2 编辑栏中输入“=IF(OR(I2=“缺考“,F2=“缺考“,H2=“缺考

24、“),“缺考“,IF(OR(I212,F26,H26,),“不及格“,IF(I214,“及格“,IF(I28,“良“,“优“)如图：4.成绩查询及宏的录制打开查询工作表，输入如下图内容：利用 LOOKUP 函数，在 C2 中输入：=LOOKUP($B$2,成绩!$A$2:$A$40,成绩!$C$2:$C$40)D2，E2,F2中按照 C2 方法输入 相应函数。另外，由于学生在编号在成绩工作表中有可能不是按照升序排列，这样“LOOKUP”函数就有可能出错。所以我们在此录制一个宏，来使成绩工作表的编号以及其扩展按照升序排列。方法如下：点击视图宏录制宏宏名中输入“编号升序”-确定点开成绩工作表-选中

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