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

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

《EXCEL在体育成绩统计上的运用.docx》由会员分享，可在线阅读，更多相关《EXCEL在体育成绩统计上的运用.docx（19页珍藏版）》请在冰点文库上搜索。

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还有很多函数值得我们去探究。