用EXCEL做学生体育锻炼达标情况统计

1、用EXCEL做学生体育锻炼达标情况统计你知道体育老师最恼火的工作是什么吗？就我的经验，体弃老师最恼火的工作是给学生作 体冇锻炼达标成绩的换算工作。他们要根据国家体育锻炼标准成绩评分表将学生的测试成绩 换算成100分制来作统讣和评优。但他们用人工对照换算非常麻烦，工作量也很大。有些老师对 数据的敏感度很高，于是他们能将评分标准背卜来，这样当然能加快工作速度。但并不是每个体 弃老师都拥有这一背功”，所以我给我周用的体育老师搞了这样一个自动 统讣的模板。你可将 这一方法介绍给你们的体ff老师。（一）测试成绩与100分制的自动套算以国家体冇锻炼标准测验成绩大学男生评分表之跳远项目为例。首先，将某一班级

2、学 生测试成绩录入Excel工作表中（图3-18）ABCD1学生体育锻炼达标统计表2跳远3测试成绩冰）换算成100分制415. 61524. 25634. 05图3-18再利用可嵌套的IF函数，在D4单元格中连续输入=IF(C4=4. M, IF(C4= 1,100. IF(C4=5. 02. 93. IF(C4=4. 94. 90,IF(C4=4. 86. 85, IF(C4=4. 7&80. IF(C4=4. 7, 75, IF(C4=4. 62, 70, 65),IF(C4=3. 98, IF(C4=4. 46. 60JF(C4=4. 38. 55, IF(C4=43, 50, IF(C

3、4=4. 22, 45,IF(C4=4. 14, 40, IF(C4=4. 06, 35, 30)回车确认后将会在DI单元格中自动得到与5. 61米”对应的100分制成绩“100”，再利用“单元格的“引用”功能， 将D4中的换算公 式用鼠标“填充柄”方式（即将鼠标移至D4单 元格右下方使之变为黑“十”字）依次拖放到D5,D6等单元格，相应地在D3, D6等单元格 将得到与C5,C6等单元格相对应的100分制成 绩，见图3-19。相应地，可在后而单元格中输 入苴它项目的测试成绩，并输 入相应的换算公 式，即可得到其它项目的100制成绩。ABCD1学空体育锻炼达标统计表2跳远3测试成绩换算成100

4、分制415. 61100524 2545634 0530743 983A图3-19说明：IF函数的一般格式是IF（条件,值1,值2）功能：当条件为真时取值1否则取值2;并允许在值1或值2位置嵌套下一个IF条件，嵌套 层数最多不超过7层。在国家体育锻炼标准评分表中，从30分到100分每5分一个段，于是 可分7个条件到“值1”中，另分7个条件到“值2”中。假设需要，还可在“值1”条件中的 “值1”中再嵌套条件，因此理论上说，可以嵌套足够多的条件。所以再复杂的条件也是可以在 这个表格中得到解决的（详细使用规则可参看相关书籍或从Excel的使用帮助中得到，此处不再 赞述）。话说回来，除在体育锻炼达标成

5、绩换算中需要这样多的嵌套条件外，在其他问题中尚未 遇到需要嵌套这么多条件的问题。（-）学生成绩分布情况的自动统计现在我们要对学生达标情况进行统汁。 假设将对一个班30名学生的跳远测试成绩进行统讣。先要在前一步中将原始数据输入到工作表的C4C33单元格中，再在前一步中将对应100分制成绩换 算到单元格D4-D33中。然后在G2-G5单元格分别输入“优秀”、“良好”、“及格”、“不及 格”。假设优秀的条件是成绩不低于85分，良好的成绩不低于75分，及格 成绩不低于60分，60分以下为不及格，于是在H2-H5单元格中分别输入二COUNTIF （D4:D33, =85）,=C0UNTIF （D4:D3

6、3, ” =75“）-COUNTIF （D4:D33,二85”）=COUNTIF（D4:D33, ”二60”）-COUNTIF （D4:D33,二75”）=COUNTIF （D4:D33,60）其作用是将D4-D33中大于或等于85的数据个数记入H2单元格，将大于或等于75小于85的数据 个数记入H3单元格，将大于或等于60小于75的数据个数记入H4单元格，将小于60的数据个数 记入H5单元格，即可分别得到各分数段的学生人数（图3-20）a（三）绘制统计图利用Excel软件可以很方便的绘制统计图。首先用鼠标选中需要绘制统计图的数拯源，此例中为单元格G2-H5,然后在“插入”菜单中选择“ 即因表

7、”功能，系统将弹岀建立图表的向导，按照向导提示可在四步之内建好自己所需要的统计图，如图3-21（柱形图） 和图3-22（饼图）。ABCDFGHI1学生体育锻炼达标统计表成绩分布情况统计2MH:远85及以上优秀143J-P测试成绩换算成10D分乃期良好4415.6110060-74及格5524. 254560以下 不及格7图3-205学生休育锻炼测试达标柱形统计图3-217生体育锻炼测试达标饼形统计图不及格23%厂、优秀良好图3-22（四）VL00KUP函数11、 此工作之成绩换算中还有一种町自动查找并匹配成绩的方法， 那就是HL00KUP和VL00KUP函数。（1）功能：在表格或数值数组的首列

8、査找指左的数值，并由此返回表格或数组当前行中 指立列处的数值。当比较值位于数据表首列时，可以使用函数VL00KUP代替函数HLOOKUPo在VTOOKUP中的V代表垂直。（2）语法：VLOOKUP （lookiip*-alue, table_array, col_mdex_num, range_lookiip）（3）参数说明：Lookup_wlue为需要在数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组 参数进行运算的单个公式。数组区域共用一个公式：数组常量是用作参数的一组常量。）第一 列中查找的数值。Lookiip. value nf以为数值、引用或文本字符串。Table.ami

9、y为需要在英中查找数拯的数据表。町以使用对区域或区域名称的引用，例如数 据库或列表。如果range_lookup为TRUE,则table_array的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE：否则，函数VLOOKUP不能返回正确的数值。如果rangelookup01优秀良好及格不及幣13% *5为FALSE, table array不必进行排序。I文字來源于0伍ce2003之帮助通过在“数据”菜单中的“排序”中选择“升序S可将数值按升序排列。Table_anay的第一列中的数值可以为文本、数字或逻借值。文本不区分大小写。Col_mdex_niim为ta

10、ble_array中待返回的匹配值的列序号。Col_index_num为1时，返回table_array第一列中的数值：col_mdex_num为2,返回tab 1 e_array第二列中的数值， 以此类推。如果col_mdex_num小于1,函数TOOKUP返回错误值如果col_index_num人于table_array的列数， 函数VLOOKUP返回错误值#REF!。Rangejookiip为一逻辑值， 指明函数VLOOKUP返回时是精确匹配还是近似匹配。 如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookupjalue的最 大数值：如果iaiig

11、e_value为FALSE,函数T00KUP将返回精确匹配值。如果找不到，则返回错误 值#N/A.如果函数TOOKUP找不到lookiip_value.且rangeJookiip为TRUE,则使用小于 等于lookup_valu已的最大值。如果1 ookup_value小于table_array第一列中的最小数值，函数VTOOKUP返回错误 值#N/A.如果函数XTL00KUP找不到lookup J alue且range.lookiip为FALSE,函数VLOOKUP返回错 误值#N/Ao(4)示例：首先，我们需要一张成绩对照表，可放在sheet?工作表中(也町与要处理的数据在同表中)，如图3-23：ABC1跳远（米）分数（100分制）23. 923031063544. 141054.224564. 305074.385584. 466094. 5465104.6270114.7075124.7880134.8685144. 9490155. 0295165.1010017w M Sheetl 5heet2 /5heet3 /图3。3所要处理数据同前图3-18,仍在sheetl工作表中。在D4单元格中输入=TOOKUP （C4, Sheet2SA:$B. 2）回车确认后将会在D4单元格中自动得到与C4单元格中“5.61