EXCEL在体育成绩统计上的运用.docx
《EXCEL在体育成绩统计上的运用.docx》由会员分享,可在线阅读,更多相关《EXCEL在体育成绩统计上的运用.docx(15页珍藏版)》请在冰豆网上搜索。
![EXCEL在体育成绩统计上的运用.docx](https://file1.bdocx.com/fileroot1/2023-1/30/b71ede61-ad15-4edf-8631-919afcbf3814/b71ede61-ad15-4edf-8631-919afcbf38141.gif)
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,则对区域中的单元格求和。
例:
ABC
1姓名班级得分
2乙6.192
3丙6.190
4丁6.289
5戊6.395
公式(结果)
=SUMIF(B2:
B5,"=6.1",C2:
C5)含义:
班级为6.1班的学生的成绩和为(182)
3.COUNTIF函数
COUNTIF(range,criteria)
Range为需要计算其中满足条件的单元格数目的单元格区域。
Criteria为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式、单元格引用或文本。
例如,条件可以表示为89、"89"、"<89"、"缺考"或B2。
例:
ABC
1姓名班级得分
2乙6.192
3丙6.190
4丁6.289
5戊6.395
6甲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为按照升序排列的列表。
例:
1姓名班级得分排名
2乙6.192
3丙6.190
4丁6.289
5戊6.3951
6甲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或被忽略)的情况都是正确的。
消除重复数排位的公式
例:
ABCDE
1姓名班级得分重复排名无重复排名
2乙6.19223
3丙6.19044
4丁6.28955
5戊6.39511
6甲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为例,制作一个体育成绩管理模板。
1、新建工作表
打开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-B4
B7…
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<>"缺考"))-B18
3.3.4EXCEL图表
根据各项数据,我们可以制作各种相关的数据图表,比如:
这是以全年级的立定跳远和50Mx8各分数段的数据图。
3.3.5等级评定
根据学生总分,还可以为学生自动打出成绩等级。
打开成绩工作表→在K1中输入“等级”→在K2编辑栏中输入“=IF(OR(I2="缺考",F2="缺考",H2="缺考"),"缺考",IF(OR(I2<12,F2<6,H2<6,),"不及格",IF(I2<14,"及格",IF(I2<8,"良","优"))))
如图:
4.成绩查询及宏的录制
打开查询工作表,输入如下图内容:
利用LOOKUP函数,在C2中输入:
=LOOKUP($B$2,成绩!
$A$2:
$A$40,成绩!
$C$2:
$C$40)
D2,E2,F2…中按照C2方法输入相应函数。
另外,由于学生在编号在成绩工作表中有可能不是按照升序排列,这样“LOOKUP”函数就有可能出错。
所以我们在此录制一个宏,来使成绩工作表的编号以及其扩展按照升序排列。
方法如下:
点击视图—宏—录制宏—宏名中输入“编号升序”---确定—点开成绩工作表---选中编号栏—点击开始---排序和筛选---升序排列---点开成绩查询工作表—视图---宏---停止录制。
这样当我们要用学号来查询学生成绩时,首先点开视图—宏—查看宏—选中“编号升序”—执行。
然后在编号中随意输入一学生编号,如60004,可以得出如图的结果:
这样我们在输入编号查询学生成绩就不会出错了。
同样的方法,我们可以在姓名查询行中输入相应函数。
5.模板保存
当制作完成以上工作表之后,为了防止误删除、改写函数,EXCEL还有数据保护功能。
在工作表中选中函数单元格,点右键—设置单元格格式—保护—隐藏—确定—点击审阅—保护工作表—输入密码(或者不要密码)--确定。
这样这些函数就被保护、隐藏起来了。
(有#N/A是因为在编号中没有输入数据)
(有保护状态)
完成所有保护工作之后,点击保存,输入文件名,选择保存为模板。
6.结论
学校体育成绩管理在整个教学活动中占有举足轻重的作用,而EXCEL则在学校体育的成绩管理、分析上是不能或缺的工具。
这里只是举例说明了部分函数在体育成绩管理上的作用,EXCEL还有很多函数值得我们去探究。