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

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

2、OXPO等编写一个程序来完成，但是 这些软件涉及到复杂的程序编写，我们普通的老师没法做到。EXCEL勺出现，为我们在体育成绩的统计上开辟了另一扇窗。二、EXCEL及常用函数Excel是微软公司的办公软件 Microsoft office 的组件之一，是由Microsoft为 Windows 和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、 统计分析和辅助决策操作，广泛地 应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择，可以实现许多方便的功能，给使用者方便。我们在

3、体育成绩的计算、统计、查询上常用的函数有：LOOKUP SUMIF, COUNTIF SUMIF，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（小数点后一位四舍五入）=ROU

4、ND(74.58, -2)100.00（向百位四舍五入）=ROUND(74.583, 2)74.58（小数点后两位四舍五入）2. SUM 和 SUMIF函数SUM函数这里我不再细说了，相信大家对于这个求和函数都能运用。这里我要说一下SUMIF 函数，这个函数主要用于成绩统计、分析等。SUMIF（ra nge,criteria,sum_ra nge），它的功能是根据指定条件对若干单元格求和。Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32&q

5、uot;或"apples"。Sum_ra nge是需要求和的实际单元格。含义：只有在区域中相应的单元格符合条件的情况下，sum_ra nge中的单元格才求和。如果忽略了 sum_range，则对区域中的单元格求和。例：A B C1 姓名 班级得分2 乙6.1923 丙6.1904 丁6.2895 戊6.395公式（结果）=SUMIF（B2:B5,"=6.1",C2:C5）含义：班级为6.1班的学生的成绩和为（182）3. COUNTIF 函数COUNTIF（ra nge,criteria）Range为需要计算其中满足条件的单元格数目的单元格区域。Crit

6、eria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 89、"89"、"<89"、"缺考"或B2。例:A B C1姓名班级得分2乙6.1923丙6.1904丁6.2895戊6.3956甲6.3缺考公式含义（结果）=COUNTIF（B2:B6,6.1）计算第二列中6.1班所在单元格的个数（2）=COUNTIF（B2:B6,B5）计算第二列中与B5 （即6.3 ）相同的所在单元格的个数（2）=COUNTIF（C2:C6,"缺考"）计算第三列中缺考的所在单元格

7、的个数（注意必须使用引号） 这个公式也可写成 =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）Lo

8、gical_test 表示计算结果为TRUE或 FALSE勺任意值或表达式。Value_if_truelogical_test 为TRUE时返回的值。例如,如果本参数为文本字符串“缺考”而 且logical_test 参数值为TRUE贝U IF函数将显示文本“缺考”。如果logical_test 为TRUE 而value_if_true 为空,则本参数返回0（零）。如果要显示TRUE则应为本参数使用逻辑值 TRUE Value_if_true也可以是其他公式。例如,=IF（A仁89, ”良好”，”合格” ）A仁89就是一个逻辑表达式,如果单元格A1中的 值等于89,表达式即为TRUE这里就是“

9、良好”）,否则为FALSE（这里就是“合格”）。本参 数可使用任何比较运算符。函数IF可以嵌套七层，用value_if_false 及value_if_true参数可以构造复杂的检测条件。含义：IF(logical_test,value_if_true,value_if_false)函数可以解释为，如果 TEST成立,则执行TRUE否则执行FALSE5. LOOKUP 函数LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value 为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数 字、文本、逻辑值或包

10、含数值的名称或引用。Lookup_vector 为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或 逻辑值。要点：Lookup vector 的数值必须按升序排序： 、-2、-1、0、1、2、.、A-Z、FALSETRUE否则，函数LOOKUP不能返回正确的结果。文本不区分大小写。Result_vector只包含一行或一列的区域，其大小必须与 lookup_vector 相同。含义：如果函数LOOKUP找不到lookup_value，则查找lookup_vector 中小于或等于 lookup_value 的最大数值。 如果lookup_value 小于lookup_

11、vector 中的最小值，函数 LOOKUP返回错误值#N/A。例：立定跳远成绩计算in11234151 61 r1U20：LCP退碾空定削远2 SE.S2. 23LD I 1. 2&E.11. 35乩4九3LE, 11.36. ?1. 38邑2L56K 3°邑31. 822 i1. i2HIJXItECO50極6立立电-2*11600啊-2. 1t.3:.i：.16e.c1. 511.6-2.Q2a. 91.021. 4気31. 58& 67.B1.63L f?氐9-nT.H1. 6T1/547. E476.1l,T157T- 5-L.43B.41.陀1,627.日

12、-1,39E.71. 79l,fi78, 1-t,3G91. &33. 45.2l.ST1. Fta. ?-1b39.41. B91” ?9-L.9-61. 911. F99. 2-,.2h9.Kt. 93l.Bl9. 4-1.24101.鮎1. BS乩6-0. 1101. 9"仁B69. 91. 931. BG10制作上表，由于立定跳远男女标准不同，所以我们加入IF函数，在E2中输入：=IF(B2="女"丄OOKUP(D2,L$3:L$21,M$3:M$21),LOOKUP(D2,K$3:K$21,M$3:M$21)结果可以得出甲立定跳远2.23米的得分

13、是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的最大数值。而通常情况下在

14、评径赛成绩时却与之相反，如上图， 表2中的50mx8成绩中，10分为T 24和9.8分为1 26，如果你跑了 1 25就打10 分了，这肯定是不行的。所以在径赛中我们要将成绩作负数处理（如上图，表3）这样保证了输入T 25时不是打10分，而是打9.8分由于lookup_value小于lookup_vector中的最小值， 函数LOOKU会返回错误信息，所以在上表3中第一行（即最小值行）前插了一个非常小的数值， 如“ -50 ”，以保证函数返回值的正确。6. RANK函数RANK（ number,ref,order）umber为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中

15、的非数值型参数将被忽略。Order为一数字，指明排位的方式。如果order为0 （零）或省略，Microsoft Excel对数字的排位是基于ref为按照降序排列 的列表。女口果order不为零，Microsoft Excel对数字的排位是基于ref为按照升序排列的列表。例:1姓名班级得分排名2乙6.1923丙6.1904丁6.2895戊6.39516甲6.3缺考公式含义结果=RANK(C3,C2:C6,1)C3即90分在C2至C6中的升序排位(2)=RANK(C3,C2:C6,0)C3即90分在C2至C6中的降序排位(3)含义：函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排

16、位。 例如，在一列 按升序排列的整数中，如果整数10出现两次，其排位为5,则11的排位为7 （没有排位为 6的数值）。由于某些原因，用户可能使用考虑重复数字的排位定义。 在前面的示例中，用户可能要将整数 10的排位改为5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对 于按照升序计算排位（顺序=非零值）或按照降序计算排位（顺序 =0或被忽略）的情况都 是正确的。消除重复数排位的公式例：A BCDE1姓名班级得分重复排名无重复排名2乙6.192233丙6.190444丁6.289555戊6.395116甲6.39222我们在E2中输入=RANK(C2,C$2:C$6,O)+C

17、OUNTIF(C$2:C2,C2)-1，向下拖动复制，可以得到无重复的排名。这里要含义一下，$加在C2中是阻止向下拖动复制时 C2向D2,E2,F2变 化。8.0R函数OR仅当所有参数值均为逻辑“假（FALSE ”时返回结果逻辑“假（FALSE ”，否则都 返回逻辑“真（TRUE”，即如果其中一个条件为真，则返回结果为真。三、体育成绩统计、查询模板设计下面以小学六年级的立定跳远和 50mX8为例，制作一个体育成绩管理模板。1、新建工作表打开EXCEL新建一 EXCELS档,在工作表区分别建立以下工作表1.1在成绩工作表里输入相关的各指标数值，如图下所示。当然我们根据需要可以输入更多的 项目。A

18、E匚DEFLG1Hr1J1性刊空定跳远綽分50井冲B特分AB(C01毗3 .45b791011La13141516L?19其中性别一列采用数据有效性进行设置EF唱H_!具体方法：选取D2:D40单元格区域一单击“数据”菜单一 “有效性”一在打开的“数据有效性”对话框中作如下图所示的设置 ：选取“设置”选 项卡,在“允许”下拉列表框中选择“序列”，在“来源”文本框中输入“男，女”（注意中间 的逗号是在英文状态下输入的）,单击“确定”即可。1.2根据国家体质健康标准在标准工作表中输入如下成绩六年级体育成绩标准2 0 2 7 7 5 5 6 6 " 亠 1 ai L 1* i&汛氐

19、T.T,匸（为了方便时间的录入，我们以“ 表示分，“ 2.11”，即2分11秒。也可以将录入区改 为标准时间格式，比如“ 2: 11”）2. 各项成绩计算2.1计算“立定跳远”得分由于六年级立定跳远的男、女生成绩标准不同，所以我们用上了IF函数。在F2中输入：=IF（D2="女"丄00KUP（E2标准!D$4:D$21,标准!E$4:E$21）,LOOKUP（E2,标准!C$4:C$21,标 准!E$4:E$21）函数解释：如果D2为女，则执行LOOKUP（E标准!D$4:D$21,标准!E$4:E$21），否则执行 L00KUP（E2标准!C$4:C$21,标准!E$4:

20、E$21）。其中 LOOKUP（E2标准!C$4:C$21,标准!E$4:E$21） 的意思是：在标准工作表中的 D3至D21列，查找与E2对应的数。这里“标准！ ”是指向某工 作表的意思。结果如图：pa立離山饨:堺爼毗人30也？血"描崔Lb4：口船*掠進山肌盪柑力C£i1 1'E1K11班fitIt 111立wn迓-£00016.1K9l9*3 I160002600036. 1B. 11.T2la?2£.48.4Is IB0W46. 11.砺9. &百1£0003民J7.2T60006. 18. T9BOW?& 11.

21、53311SS&Kie-oixig6. 21.63呂.111600106.2E75T,312gNUl&甕I-S&注昌001眾E、2g, 1H6.31-S5e. 3166W14&DO136.2& zL55l.Tl7.25l_4K6TO166. ZL3i 1Libeo'ii?&t2根据此公式我们可以轻松得出不同性别的学生的立定跳远成绩。如果成绩为空，会自动“缺考”。为了快速录入，可以将 E2:E40表格选中，右键一一设置单元格格式一一数字一一自定 义一一类型中输入00” . ” 00,确定。以后在E2: E40单元格中，只要输入211,表格就

22、会自 动将其“变”为“ 02.11”，效果不等于“ 2.11”。所以还得在标准工作表中，将标准也改为“ 02.11 ”的格式。如下图：总帆丄監li彌 IDEF性别立定跳远得分02.111001.456. 301. 556. 3生HIT城匹g 00OCX 1000. 1001. 5101. 39-01+ 6201* to*301, 5 目01.6601. £201_ 50.勺Ok 6701.凱OK WQk 577. S01. 74叫坯匸旦01- T901-£7B.101. B3叽.T1B. <g B7叫”虽T<H. B9&1. T7961. 9101. T

23、&&. 201,芻01. 81B. 401, B6弘B3&. 601. 9701. B5&.SOL抽ftl.甜162.2计算“ 50mX8得分由于六年级50mX8男女成绩标准相同。所以我们只需要在 H2中编辑以下公式： =LOOKUP（-G2标准!A$3:A$21,标准!B$3:B$21）含义：由于我们录入成绩是以正数录入，而“标准”中我们设置的是负数，为了正确对应 数据，所以在G2前加入“-”号。结果如图:ECZ曄号&3toi乩1nsB&3M2d. 14608轧IL6&3K4 sotas吐1d.lT的切昔氐L&eocar乳1ea

24、cos吐1丄0ftOM乳Z11胡"0& 2&Q011& 213&3012g-2RD订1&60014171&6Q015&3016 頼MT氐26.26,22.3总成绩的计算总成绩，就是立定跳远和50mX8寻分的总合，所以在12中输入公式: =IF(F2="缺考","缺考",IF(H2="缺考","缺考",F2+H2)结果如图： fi =TT<P2="*<!*% "IF>K2)IBgMMt6.1&0C02fiB

25、l60C03&1&OCI0-16.1&o<!056MKG爲1firwiQ?氐1&刚日6(xiog& 2&OCHO-.1.A£立丄认远1.91,12172LS58- 41冲B.1S.41.438. 4匕卜L30B. T7.2L337.5”賢担IB得欝7516.1+39B. T1L 4L 527.513. B£阳& 912.91.57.315. &Q-fiuwlL AMI 2 6W13 60CI11&0CH5 &n<ii6MLF島-二 氐壬 民26.2-. ：_6.2b.2r sqi.h

26、47.5ni.eEJL EE7.515. hLS5二2.0?G. 313.61.557.2l.M7.51.T1&.4L47B. 116r5j.s£.11.21018. 1ffe笔2.4名次统计在名次统计中，我们将用到 RANI函数。我们在J2中录入： =IF(I2="缺考","缺考",RANK(l2,l$2:l$40,0)含义：降序，有重名排名；I40可以根据学生人数改成1100,1400等。 结果如图：“* mIJ .cf住罪即* |林冷iMKia- 口出曲A J B LC十SDC-M& ¥MJOMiM氐160056

27、. 1304£.1域妙孔1&DCOS& 1弭咖乱2126AP11讥640L2&0GL3 &D0L<“G. 24l2BDOL EH 260D1T氐2&QQLIILI氏3蓟斯X&0Ci2Z&. 3woa&OOZ<<60354.1丸片;ft氯真ST慝F务之VAtrS艾TFI女M5向丸LB.1.72鳥冬1_ 141隹丸a1.431.73S佻*1. SB721. 521.TB&T1. 391-53札$1.39EZ. AZirdti.sP701.5T.Bh 391.54)应1.紀J 阳£3$ 丁1

28、.&t.z1. SdJr 71出心1-171, E岳1lP2乳酯l.D1.5?&-S2. IBJ-7SS.41.軸J01-131.793.11_ g1卫L6LMPT9乳2!1. 4.-B1. B3.1In IB3 成绩统计打开成绩统计工作表，录入如图内容:JLZUXi6. 1班C. 2班6+ 3班6. 4班16n总分 平均分13刘分人数旧18分至妙9分人数16分至9分人数1巧12分15.9人数不及格缺考3.1统计立定跳远的具体分数在 B4 中录入：=COUNTIF成绩!F$2:F$40,"=10")；B5 中录入：=COUNTIF成绩!F$2:F$40,&q

29、uot;>=9.5")-B4；B6 中录入：=COUNTIF成绩!F$2:F$40,">=9")-B5-B4B7-B12 中录入：=COUNTIF成绩!F$2:F$40,"缺考")含义：B5中的函数后面“ -B4”，意思是统计了成绩大于等于9.5分的人数减去等于成绩为10 分的人的人数50mX8的具体分数，以及总成绩情况的统计和立定跳远具体分数的统计想同，这里不再多举 例。结果如图：成绩分析&£1口5氏5分矗以上104g9并臭戌上A：014吩贯U上8, 5最必上141器良ULhfl&甘丑以上1391却下d*

30、话湮址上1219*1出我310寒分憂以上6411弓守#下0012nX23.2各班具体分数情况统计各班的总分统计，首先得把各班分出来，然后再相加。用“筛选”功能的话，如果单元格 不连贯的话，也不好用公式统计。怎么办呢？还好上面有“SUMIF函数。各班部分统计我们在B16中录入：=SUMIF成绩!B$2:B$40,6.1,成绩!1$2:1$40）含义：在成绩工作表的B2:B40中查找和本工作表“6.1 ”相同的数据，然后把它们对应12:140 的数据相加。uBl 6*3A6.1,11403rABCt)e1314t 5& i班6” 2M G* 3班& 4班IB总分I 110. 4 j

31、138. 7192.4124. &各班平均分统计由于上一步已经统计了各班的总分，所以现在只要用COUNTI函数在成绩工作表中统计各班人数即可。在B17中录入函数：=B16/COUNTIF 成绩!$B$2:$B$40,"6.1")含义：用 B16 除以“ COUNTIF成绩!$B$2:$B$40,"6.1")BIT-Jt| =ni6?Mri»<JCJEB$2：EBf4O,'fi.rjAftc13讥156. 16.26, 36. 4IALu总分10. J138.7192. 4121 5平均分13. SO13, 87ie.03

32、I3r S3各班分数段成绩统计各班分数段成绩统计要在成绩工作表中找出，班级、分数段、人数，所以就涉及到条件求 和，如图，在B18中录入=SUM（成绩!I$2:I$40=20）*（成绩!B$2:B$40=6.1）按 CTRL+SHIFT+ENTE结束，得至U=SUM（成绩!I$2:I$40=20）*（ 成绩!B$2:B$40=6.1） 的数组。IB10A 11= HJJI :IE （2213=30 >TI Bl：1） ） 1一 ABcDE14156 1班氐2班氐3班氐4班16总分110. 4138. 7192. 4124” 517平均分13. SO13.8716. 0313. S31320

33、分人数0含义：统计成绩工作表中，12:140中“ =20” ,并且B2:B40中“ =6.1”的单元格个数其它分数段也可以照例录入函数，如下图：=SUM（成绩!B$2:B$40=6.1）*（成绩!1$2:1$40>=18）*（ 成绩!1$2:1$40<>" 缺考"）-B18 IIBig血缄绩!E贮忸昶（如！冷工飙0>=丄&）叭粛傷蛇昶朋汀缺爭ApEFL4：156, 1班6. 2班6. 3班& 4班丄6总分110. 4138. 71&2.4124.517平均分IN B013. 8716. 0313. S31820分人数0Kg怡分

34、至9分人数二1图表根据各项数据，我们可以制作各种相关的数据图表，比如:这是以全年级的立定跳远和50Mx8各分数段的数据图等级评定根据学生总分，还可以为学生自动打出成绩等级。打开成绩工作表一在K1中输入“等级”一在K2编辑栏中输入“ =IF（OR（I2="缺考",F2="缺考",H2="缺考"）,"缺考",IF（OR（l2v12,F2<6,H2<6,）," 不及格",IF（I2<14," 及格 ",IF（I2<8,"良","优"）如图：立定昭1.9501