Excel在体育成绩统计中的应用 Office 2003 2010 Excel精选教程

一、前  言

     学生体育《达标》测试成绩的换算与评分，是体育教师学年结束的必须工作。而对一堆体育测试数据的查分与统计，实在是一件非常繁琐的事，不但容易出错，而且不利于按科学合理方法加以分类收集、规范整理，致使大量的第一手数据资源不能为今后的教学提供准确的科学数据信息，这对于每位体育工作者来说都是极大的浪费和憾事。

计算机技术的发展以及在社会各领域的运用提高，特别是在数据的处理、统计、分析和管理等方面，电子表格的运用对各领域带来工作效率的提高。在学校体育管理中，体育教师充分利用电子表格，及时对总的测试数据或某项数据进行分析评价，针对学生的实际，提出具体的改进实施建议有现实意义。

 本文是作者在运用电子表格（Microsoft Excel工作表、金山电子表格）对学生体育成绩的科学管理与统计分析方面的尝试经验，谈谈自己的体会，希望对体育教师有所帮助。

二、  根据体育《达标》成绩统计的特点，建立合理的体育成绩登记表。

     设计制作一个直观明了、方便实用的体育《达标》成绩统计表，不但能让观者一目了然，还能提高教师对测试数据的输入速度，便于分析研究。在表格设计时要按照《达标》标准所需测试项目的内容设定：学号、班级、姓名、性别、各项成绩及得分、总分与等级，还可根据自己的研究需要，在表格中设置学生的出生年月、年龄等项目，以便于对相关数据处理和分析（见图1）。

（图1）

    采用规范和变通的方式登入测试成绩。由于在测试记录体育成绩把14秒5记录成“14"5”、把3分45秒记录成“3'45”，而这些数据在电子表格的函数运算中无法识别，所以必须将它们按英文输入状态下的整数小数进行输入，如：

原始测试成绩	14"5	3'45"	1米86厘米	6米54厘米
录入电脑成绩	14.5	3.45	1.86	6.54

　

三、运用电子表格对《达标》成绩评分的技巧

体育《达标》成绩的评分与其它学科不同，每个单项均以5分为组段，共20分级，最低分为5分，最高分为100分。成绩换算时先按性别找到相应的评分表，然后查某学生某项目成绩应得的分数。如果某项目成绩低于某分数线的成绩但高于下面分数线成绩时，应按下面分数线记分。而且根据《达标》标准要求： ①．单项成绩不得低于30分；  ②．总成绩不得低于250分。这就给体育《达标》成绩评分的函数设计，提出了特殊的要求。

我们常用的电子表格一是Microsoft Excel工作表、一是金山电子表格,这两种操作系统的运用方法基本相同，但也存在着某些区别。

（一）、“IF” 判断语句

说明：执行真假值判断，根据逻辑测试的真假值返回不同的结果。

《达标》等级的判断时在单元格中输入“=IF(H4<30,"不达标",IF(L4<30,"不达标",IF(N4<30,"不达标",IF(P4<30,"不达标",IF(R4<30,"不达标",IF(S4>=420,"优秀",IF(S4>=360,"良好",IF(S4>=250,"及格","不达标"))))))))” （注意括号的对称性）。按下“确定”键（【Enter】或 ），则该学生的《达标》等级就能自动给予评定。

对于初学者来说，可以利用我国自主设计的金山电子表格来对体育《达标》成绩进行科学评分，它虽然不如Microsoft Excel名气大，但在“IF判断语句”上没有象Microsoft Excel那样判断级数只局限于七级以内，这给我们提供了简单、直接评分方法。根据高一年级100米跑成绩评分要求，首先判断学生的性别，再对成绩未定进行判断，然后根据评分标准的要求逐级进行判断，我们 在100米项目的“得分”栏中输入函数： “=IF(C4="男",IF(H4=0,"0",IF(H4<=13.2,"100",IF(H4<=13.5,………IF(H4<=18.9,"5","0"))))))))))))))))))))),IF(H4=0,"0",IF(H4<=15.4,"100",IF(H4<=15.7,"95",………IF(H4<=21.1,"5","0"))))))))))))))))))))))”，【Enter】键，则该学生的100米《达标》得分便能自动算出。

（二）、函数“ROUNDUP”（远离零值，向上舍入数字）。

 例如：按照100米评分标准每0.3秒相差5分，我们可将实测成绩减去满分成绩再除以0.3来运算，具体函数公式如右图：

其它的项目均可以按“ROUNDUP函数”进行设置，这里就不一一列列举。

（三）、“SUM语句”（返回某一单元格区域中所有数字之和。）

语法：SUM(number1,number2, …)

说明：Number1, number2, … 为需要求和的参数。直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。

“SUM语句”是一个求和公式，我们在对《达标》成绩统计时，可利用它来将测试的各单项成绩进行求和，如在图1的“S4单元格”中输入“=SUM(H4+L4+N4+P4+R4)”，然后按“确定”键，则该学生的《达标》总成绩就自动求和了。

（四）、利用“FLOOR函数”换算中长跑成绩

对中长跑测试时间的换算处理在对中长跑的成绩统计时，由于时间单位的分、秒为六十进制，如果我们按前面方法记入3'45"为3.45，这就会造成55秒的误差，给分析统计工作带来错误的数据。所以必须把3'45"换算成225秒记入，我们可以利用FLOOR函数对其进行换算，在单元格(J4)中输入：“=FLOOR(I4,1)*60+(I4-FLOOR(I4,1))*100”确定，这样就避免了统计分析的错误。

（五）、计算已满年龄

在对学生年龄计算时，首先应确定一个时间为样本我们常常用学年“开学时间”作为样本时间，为了保证每位同学都按这一时间进行折算，则必须通过在列号和行号前面添加美元符号“$”来对单元格地 址的“冻结”达到目的，以使公式中的固定单元格地址保持不变，计算步骤如下：  ①．计算已满的年龄；  ②．按普通方式计算的年龄（后半部分）；  ③．用以判断是否该少一岁：“DATE(YEAR($F$2),MONTH(E4),DAY(E4)),0,1)”，整个计算年龄公式设置为：“=YEAR($F$2)-YEAR(E4)-IF($F$2>=DATE(YEAR($F$2),MONTH(E4),DAY(E4)),0,1)”

四、运用电子表格对《达标》数据的统计

在各班学生《达标》成绩完成评分及等级评定后，还需对其达标等级的计数值进行统计，本年级乃至全校学生按性别、年龄分别进行个所测项目数据的统计分析，以掌握某年龄段男、女生的各项身体素质指标数据，可为我们今后的教学和科研提供准确的信息。

（一）、数据的分类

1、使用“自动排序”

为了对各种数据的分类统计，我们可以按自己的要求对数据进行排序，具体操作步骤如下：  ①．选中需要排序的所有数据；  ②．单击【数据】菜单中的【排序】命令，打开【排序】对话框（如右图所示）；  ③．根据统计要求，在【主要关键字】和【次要关键字】的下拉列表中分别选择“性别”（C列）、“年龄”（F列）进行分类；  ④．按【确定】，则所选中的数据按要求自动进行了分类。

2、使用”分类汇总”

当我们已经在一张工作表中完成了数据输入，需要对所选已做标记的列计算其分类汇总值和汇总值。电子表格提供了“分类汇总”的功能。使用方法：选中需要参与汇总的数据区域，选择菜单“数据/分类汇总”，弹出“分类汇总”对话框：

  ①．“汇总区域”编辑框：用来指定分类汇总的数据列表区域。对话框出现的时候，其中字符串显示的是弹出对话框之前用户选中的区域。可以手工编辑字符串用来指定数据列表区域。当输入焦点在此编辑框中的时候，可以直接用鼠标点击工作表中的任意位置用以选定数据列表区域。可以点击此编辑框右边的按钮 ，以选择数据列表区域。

  ②．“分类字段”下拉列表框：用来指定按照某一列分类,列表框中的数据是自动选取选定的数据列表区域的第一行各个单元格中的数据，用来指代各列。

  ③．“汇总方式”下拉列表框：用来指定对想要汇总的数据列表列使用何种函数进行汇总操作。可以使用的函数是固定的，包括：求和，计数，平均值，最大值，最小值，乘积，计数值。

  ④．“选定汇总项”列表框：用来指定想要参与汇总计算的列，可以多选。列表框中的数据项是自动选取选定的数据列表区域的第一行各个单元格中的数据，用来指代各列。

  ⑤．“有标题行”复选框：用来指示首行是否参与汇总。选中，表示首行不参与汇总。不选中，表示首行参与汇总。默认为选中。

  ⑥．“汇总结果显示在数据下方”复选框：用来指示汇总结果的显示位置,默认为选中,如果需要将汇总结果显示在数据上方，可以不选中此项。进行了以上设置以后，按下“确定”按钮，除了为各分类产生一个汇总行以外，还会为所有分类产生一个总汇总。

（二）、在体育统计工作中，常应用到电子表格的统计函数有：总和——SUM（前面已介绍），平均数——AVERAGE，标准差——STDEVP等。

1、“标准差——STDEVP”（求出某个观测变量的标准差）

①．在Microsoft Excel工作表中点选一个要登入“标准差”空白窗框格。

②．打开菜单栏上的“插入”菜单，在展开菜单上选择“f _x  函数”。

③．在“粘贴函数”对话框中（见右图），选择 “函数分类” 左框中的“统计”项之后，再选择 “函数名” 右框中的“STDEVP”，然后按“确定”按钮。

④．在“STDEVP” 对话框中，选择“Number 1”框右边的“折叠”按钮，此时的操作会返回Microsoft Excel工作表中，只需用鼠标框选引用的区域项类的成绩列之后，再按下停放在列标栏上的“折叠”按钮，又返回“STDEVP” 对话框。

⑤．按“确定”按钮，Microsoft Excel工作表原要登入“标准差”空白窗框格中会自动生成“立定跳远”的成绩的“标准差”值。

2、利用AVERAGE函数求平均值

AVERAGE函数：返回参数平均值（算术平均）。

语法：AVERAGE(number1,number2, …)

说明：Number1, number2, … 要计算平均值的 1～30 个参数。参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。

方法同上，不再重复。

五、电子表格的应用技巧

（一）、公式的输入（选中单元格即可进行公式输入）

具体操作：单元格内的一系列数值、单元格引用、名称、函数和运算符的集合，可共同产生新的值。①．公式总是以等号 （=）开始的。②．单击将要在其中输入公式的单元格；③．在编辑栏上点击“＝”（等号）；④．输入公式内容；⑤．按 Enter键或点击编辑栏的按钮 确定输入的公式；如果要取消输入内容，可以点击编辑栏 按钮 。

在输入公式时，电子表格提供了自动感应的功能，可以显示出输入函数的相关信息，如右图：

公式输入或者编辑后，程序会检验输入的公式是否合乎语法，如果选择的函数有语法错误，会弹出消息框提示。

（二）、快速输入变量公式（相对引用）

电子表格提供了快速输入各种数字、公式方法。当我们将第一位学生所有的成绩计算公式或函数公式设置好后，方法①：用鼠标点击所需复制的单元格，将光标拖至单元格的右下方，我们会看见出现了一个小“+”，用鼠标左键按住“+”向下拉至所需到达的单元格，这样，就将第一位学生成绩计算公式或函数公式变量地进行了复制；方法  ②：选中需本函数公式的所有下面的单元格，按【Ctrl+Shift+Enter】,即得。

（三）、锁定单元格

当整个统计表设置好后，为了防止在输入数据时误改公式，我们还需将部分单元格进行锁定。操作方法：①选定整个表格，然后选择“格式→单元格”，选择其中的“保护”，将其中的“锁定”取消；  ②选定要保护的单元格，进行上述同样的操作，将其重新“锁定”；  ③选择菜单中的“工具→保护→保护工作表”，系统提醒输入密码并【确定】，单元格已被保护了；  ④要修改对应单元内容是，必须选择“工具→保护→撤销保护工作表”并输入相应密码。

（四）、命名和增添多个“电子”工作表卡

在新建电子工作表左下角的表卡内只给出了三个表卡，而我们往往是按班级进行体育成绩的处理和分析的，这就要求建立多个工作表卡，并给每个工作表卡命名（见图1）。

1、重命名电子工作表卡：将鼠标移至电子工作表左下角的“Sheet 1”，按下鼠标右键，会出现一对话框，点击“重命名”，输入新名称，按【确定】键完成。

2、增添多个电子表卡：用鼠标移至电子工作表左下角的“Sheet 1”，按下鼠标右键，会出现一对话框，点击“插入….”，按提示选中“工作表”，【确定】，便增加了一个电子表卡；重复以上操作，可增添多个电子表卡。

（五）、数据链接（绝对引用）

当我们完成一个班的表格设置并作为模板，复制到本年及其他班级，而为了能将全年级的成绩进行统计分析，还需将各班的成绩汇总到一张电子工作表卡内，并创建“高一年级”工作表卡，将本年级各班的成绩汇总到年级工作表卡内，但不能进行单纯的复制，而且要随各班成绩的更改而改变，这就需要进行数据的链接。链接方法：1、选中本班全体学生的所有数据进行复制；2、在适当的单元格内点击鼠标右键，在对话框中点击“选择性粘贴…”；3、按提示选中“全部”，按【确定】键完成。（因年级工作表卡内都是链接数据，应将其进行“保护、锁定”）

（六）、打印的设置

由于各班的人数较多，很难在在一页内打印完，为了便于查看，需每页都应有标题栏，所以必须将标题行进行每页打印，还不能影响表中的数据。电子表格已为用户考虑了此项功能，我们可以对其进行设置：用鼠标点击“文件→页面设置→工作表→顶端标题行”，根据所需输入“$1:$3”（如图1中的第1至第3行），按【确定】键，完成。

六、结束语

总之，对于电子表格的操作和运用，应遵循“熟能生巧”的规律，通过多学习、多实践，来进一步提高自己的应用水平和工作效率。在深化教育改革、“以科研促教学”的今天，用电子表格来处理学生的体育测试成绩，不但可以使自己辛勤劳动的教学成果得到保存，而且可为自己今后的教学科研提供第一手资料，还可以使学校的体育管理更加科学化。作者通过运用电子表格对学生体育《达标》成绩进行统计分析，较大地提高了教学管理和科研能力，虽然水平还有限，但通过努力相信一定能为教育教学和科研服务，对今后教育改革进一步深化起到积极的促进作用。