EXCEL在體育成績(jī)統(tǒng)計(jì)上的運(yùn)用

./EXCEL在體育成績(jī)統(tǒng)計(jì)上的運(yùn)用EXCEL在體育成績(jī)統(tǒng)計(jì)上的運(yùn)用[摘要]EXCEL是我們熟悉的"表格"處理軟件,它支持強(qiáng)大的數(shù)據(jù)分析和數(shù)據(jù)處理?；贓XCEL的體育成績(jī)管理、統(tǒng)計(jì)、分析,彌補(bǔ)了手工統(tǒng)計(jì)的不足,提高體育成績(jī)統(tǒng)計(jì)的作用和效率。[關(guān)鍵詞]EXCEL體育成績(jī)統(tǒng)計(jì)一、引言體育學(xué)科在成績(jī)的計(jì)算、統(tǒng)計(jì)、查詢上與其它學(xué)科相比有著其特殊性。因?yàn)轶w育成績(jī)的計(jì)量單位非常多,如"秒"、"米"、"個(gè)"等,但是這些具體的成績(jī)又要折合為分?jǐn)?shù),才能評(píng)判學(xué)生的成績(jī)。隨著科學(xué)的發(fā)展,以往要用人工完成的事情,很多可以用計(jì)算機(jī)來完成了。比如像上面提到的體育成績(jī)計(jì)算、統(tǒng)計(jì)、查詢,我們可以用C語言,FOXPOR等編寫一個(gè)程序來完成,但是這些軟件涉與到復(fù)雜的程序編寫,我們普通的老師沒法做到。EXCEL的出現(xiàn),為我們?cè)隗w育成績(jī)的統(tǒng)計(jì)上開辟了另一扇窗。二、EXCEL與常用函數(shù)Excel是微軟公司的辦公軟件Microsoftoffice的組件之一,是由Microsoft為Windows和AppleMacintosh操作系統(tǒng)的電腦而編寫和運(yùn)行的一款試算表軟件。Excel是微軟辦公套裝軟件的一個(gè)重要的組成部分,它可以進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和輔助決策操作,廣泛地應(yīng)用于管理、統(tǒng)計(jì)財(cái)經(jīng)、金融等眾多領(lǐng)域。Excel中大量的公式函數(shù)可以應(yīng)用選擇,可以實(shí)現(xiàn)許多方便的功能,給使用者方便。我們?cè)隗w育成績(jī)的計(jì)算、統(tǒng)計(jì)、查詢上常用的函數(shù)有：LOOKUP,SUMIF,COUNTIF,SUM,IF,ROUND等。下面以EXCEL2007為例,舉例來介紹這幾個(gè)函數(shù)的用法。1.ROUND函數(shù)ROUND<number,num_digits>,它的功能就是根據(jù)指定的位數(shù),將數(shù)字四舍五入。這個(gè)函數(shù)有兩個(gè)參數(shù),分別是number和num_digits。其中number就是將要進(jìn)行四舍五入的數(shù)字；num_digits則是希望得到的數(shù)字的小數(shù)點(diǎn)后的位數(shù)。例：公式結(jié)果含義=ROUND<74.58,-1>70.00〔向十位四舍五入〕=ROUND<74.58,1>74.6〔小數(shù)點(diǎn)后一位四舍五入〕=ROUND<74.58,-2>100.00〔向百位四舍五入〕=ROUND<74.583,2>74.58〔小數(shù)點(diǎn)后兩位四舍五入〕2.SUM和SUMIF函數(shù)SUM函數(shù)這里我不再細(xì)說了,相信大家對(duì)于這個(gè)求和函數(shù)都能運(yùn)用。這里我要說一下SUMIF函數(shù),這個(gè)函數(shù)主要用于成績(jī)統(tǒng)計(jì)、分析等。SUMIF<range,criteria,sum_range>,它的功能是根據(jù)指定條件對(duì)若干單元格求和。Range為用于條件判斷的單元格區(qū)域。Criteria為確定哪些單元格將被相加求和的條件,其形式可以為數(shù)字、表達(dá)式或文本。例如,條件可以表示為32、"32"、">32"或"apples"。Sum_range是需要求和的實(shí)際單元格。含義:只有在區(qū)域中相應(yīng)的單元格符合條件的情況下,sum_range中的單元格才求和。如果忽略了sum_range,則對(duì)區(qū)域中的單元格求和。例：ABC1XX班級(jí)得分2乙6.1923丙6.1904丁6.2895戊6.395公式〔結(jié)果〕=SUMIF<B2:B5,"=6.1",C2:C5>含義：班級(jí)為6.1班的學(xué)生的成績(jī)和為<182>3.COUNTIF函數(shù)COUNTIF<range,criteria>Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件,其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。例如,條件可以表示為89、"89"、"<89"、"缺考"或B2。例：ABC1XX班級(jí)得分2乙6.1923丙6.1904丁6.2895戊6.3956甲6.3缺考公式含義〔結(jié)果〕=COUNTIF<B2:B6,6.1>計(jì)算第二列中6.1班所在單元格的個(gè)數(shù)<2>=COUNTIF<B2:B6,B5>計(jì)算第二列中與B5〔即6.3〕相同的所在單元格的個(gè)數(shù)<2>=COUNTIF<C2:C6,"缺考">計(jì)算第三列中缺考的所在單元格的個(gè)數(shù)<<1>注意必須使用引號(hào)>這個(gè)公式也可寫成=COUNTIF<C2:C6,C6>=COUNTIF<C2:C6,">=90">-COUNTIF<C2:C6,">=95">計(jì)算第三列中值大于或等于90且小于95的單元格個(gè)數(shù)<2>此公式可以解釋為,計(jì)算第三列中大于90的人數(shù)減去大于且等于95的人數(shù)4.IF函數(shù)IF函數(shù)是較為常用的邏輯函數(shù)之一,它執(zhí)行真假值判斷,根據(jù)邏輯計(jì)算的真假值,返回不同結(jié)果?？梢允褂煤瘮?shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。函數(shù)語法:IF<logical_test,value_if_true,value_if_false>Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。Value_if_truelogical_test為TRUE時(shí)返回的值。例如,如果本參數(shù)為文本字符串"缺考"而且logical_test參數(shù)值為TRUE,則IF函數(shù)將顯示文本"缺考"。如果logical_test為TRUE而value_if_true為空,則本參數(shù)返回0<零>。如果要顯示TRUE,則應(yīng)為本參數(shù)使用邏輯值TRUE。Value_if_true也可以是其他公式。例如,=IF<A1=89,"良好","合格">A1=89就是一個(gè)邏輯表達(dá)式,如果單元格A1中的值等于89,表達(dá)式即為TRUE<這里就是"良好">,否則為FALSE〔這里就是"合格"〕。本參數(shù)可使用任何比較運(yùn)算符。函數(shù)IF可以嵌套七層,用value_if_false與value_if_true參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。含義：IF<logical_test,value_if_true,value_if_false>函數(shù)可以解釋為,如果TEST成立,則執(zhí)行TRUE,否則執(zhí)行FALSE。5.LOOKUP函數(shù)LOOKUP<lookup_value,lookup_vector,result_vector>Lookup_value為函數(shù)LOOKUP在第一個(gè)向量中所要查找的數(shù)值。Lookup_value可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。Lookup_vector為只包含一行或一列的區(qū)域。Lookup_vector的數(shù)值可以為文本、數(shù)字或邏輯值。要點(diǎn)：Lookup_vector的數(shù)值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則,函數(shù)LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫。Result_vector只包含一行或一列的區(qū)域,其大小必須與lookup_vector相同。含義：如果函數(shù)LOOKUP找不到lookup_value,則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。如果lookup_value小于lookup_vector中的最小值,函數(shù)LOOKUP返回錯(cuò)誤值#N/A。例：立定跳遠(yuǎn)成績(jī)計(jì)算制作上表,由于立定跳遠(yuǎn)男女標(biāo)準(zhǔn)不同,所以我們加入IF函數(shù),在E2中輸入：=IF<B2="女",LOOKUP<D2,L$3:L$21,M$3:M$21>,LOOKUP<D2,K$3:K$21,M$3:M$21>>結(jié)果可以得出甲立定跳遠(yuǎn)2.23米的得分是10分。拖動(dòng)E2公式,可以相應(yīng)得出其它學(xué)生的立定跳遠(yuǎn)得分。公式解釋：如果B2是"女",則在L3到L21列查找D2對(duì)應(yīng)的位置,然后返回其對(duì)應(yīng)行的M列的數(shù)；否則在K3至K21列查找D2對(duì)應(yīng)的位置,然后返回其對(duì)應(yīng)的行的M列的數(shù)。從上圖可以看出,B2="女",D2=2.23,對(duì)應(yīng)的L3至L21列的數(shù)沒有,所以D2則對(duì)應(yīng)比2.23小且最接近2.23的數(shù),那就是1.86,所以公式計(jì)算結(jié)果為10。例2：50mX8成績(jī)計(jì)算根據(jù)如果函數(shù)LOOKUP找不到與學(xué)生所得成績(jī)相對(duì)應(yīng)的lookup_value,則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。而通常情況下在評(píng)徑賽成績(jī)時(shí)卻與之相反,如上圖,表2中的50mx8成績(jī)中,10分為1′24″和9.8分為1′26″,如果你跑了1′25″就打10分了,這肯定是不行的。所以在徑賽中我們要將成績(jī)作負(fù)數(shù)處理〔如上圖,表3〕這樣保證了輸入1′25時(shí)不是打10分,而是打9.8分由于lookup_value小于lookup_vector中的最小值,函數(shù)LOOKUP會(huì)返回錯(cuò)誤信息,所以在上表3中第一行<即最小值行>前插了一個(gè)非常小的數(shù)值,如"-50",以保證函數(shù)返回值的正確。6.RANK函數(shù)RANK<number,ref,order>umber為需要找到排位的數(shù)字。Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order為一數(shù)字,指明排位的方式。如果order為0〔零〕或省略,MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照降序排列的列表。如果order不為零,MicrosoftExcel對(duì)數(shù)字的排位是基于ref為按照升序排列的列表。例：1XX班級(jí)得分排名2乙6.1923丙6.1904丁6.2895戊6.39516甲6.3缺考公式含義結(jié)果=RANK<C3,C2:C6,1>C3即90分在C2至C6中的升序排位<2>=RANK<C3,C2:C6,0>C3即90分在C2至C6中的降序排位<3>含義：函數(shù)RANK對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如,在一列按升序排列的整數(shù)中,如果整數(shù)10出現(xiàn)兩次,其排位為5,則11的排位為7〔沒有排位為6的數(shù)值〕。由于某些原因,用戶可能使用考慮重復(fù)數(shù)字的排位定義。在前面的示例中,用戶可能要將整數(shù)10的排位改為5.5。這可通過將下列修正因素添加到按排位返回的值來實(shí)現(xiàn)。該修正因素對(duì)于按照升序計(jì)算排位〔順序=非零值〕或按照降序計(jì)算排位〔順序=0或被忽略〕的情況都是正確的。消除重復(fù)數(shù)排位的公式例：ABCDE1XX班級(jí)得分重復(fù)排名無重復(fù)排名2乙6.192233丙6.190444丁6.289555戊6.395116甲6.39222我們?cè)贓2中輸入=RANK<C2,C$2:C$6,0>+COUNTIF<C$2:C2,C2>-1,向下拖動(dòng)復(fù)制,可以得到無重復(fù)的排名。這里要含義一下,$加在C2中是阻止向下拖動(dòng)復(fù)制時(shí)C2向D2,E2,F2…變化。8.OR函數(shù)OR僅當(dāng)所有參數(shù)值均為邏輯"假〔FALSE〕"時(shí)返回結(jié)果邏輯"假〔FALSE〕",否則都返回邏輯"真〔TRUE〕",即如果其中一個(gè)條件為真,則返回結(jié)果為真。三、體育成績(jī)統(tǒng)計(jì)、查詢模板設(shè)計(jì)下面以小學(xué)六年級(jí)的立定跳遠(yuǎn)和50mX8為例,制作一個(gè)體育成績(jī)管理模板。新建工作表打開EXCEL,新建一EXCEL文檔,在工作表區(qū)分別建立以下工作表1.1在成績(jī)工作表里輸入相關(guān)的各指標(biāo)數(shù)值,如圖下所示。當(dāng)然我們根據(jù)需要可以輸入更多的項(xiàng)目。其中性別一列采用數(shù)據(jù)有效性進(jìn)行設(shè)置。具體方法:選取D2:D40單元格區(qū)域→單擊"數(shù)據(jù)"菜單→"有效性"→在打開的"數(shù)據(jù)有效性"對(duì)話框中作如下圖所示的設(shè)置:選取"設(shè)置"選項(xiàng)卡,在"允許"下拉列表框中選擇"序列",在"來源"文本框中輸入"男,女"<注意中間的逗號(hào)是在英文狀態(tài)下輸入的>,單擊"確定"即可。1.2根據(jù)《國(guó)家體質(zhì)健康標(biāo)準(zhǔn)》在標(biāo)準(zhǔn)工作表中輸入如下成績(jī)〔為了方便時(shí)間的錄入,我們以"."表示分,"2.11",即2分11秒。也可以將錄入?yún)^(qū)改為標(biāo)準(zhǔn)時(shí)間格式,比如"2：11"〕2.各項(xiàng)成績(jī)計(jì)算2.1計(jì)算"立定跳遠(yuǎn)"得分由于六年級(jí)立定跳遠(yuǎn)的男、女生成績(jī)標(biāo)準(zhǔn)不同,所以我們用上了IF函數(shù)。在F2中輸入：=IF<D2="女",LOOKUP<E2,標(biāo)準(zhǔn)!D$4:D$21,標(biāo)準(zhǔn)!E$4:E$21>,LOOKUP<E2,標(biāo)準(zhǔn)!C$4:C$21,標(biāo)準(zhǔn)!E$4:E$21>>函數(shù)解釋：如果D2為女,則執(zhí)行LOOKUP<E2,標(biāo)準(zhǔn)!D$4:D$21,標(biāo)準(zhǔn)!E$4:E$21>,否則執(zhí)行LOOKUP<E2,標(biāo)準(zhǔn)!C$4:C$21,標(biāo)準(zhǔn)!E$4:E$21>。其中LOOKUP<E2,標(biāo)準(zhǔn)!C$4:C$21,標(biāo)準(zhǔn)!E$4:E$21>的意思是：在標(biāo)準(zhǔn)工作表中的D3至D21列,查找與E2對(duì)應(yīng)的數(shù)。這里"標(biāo)準(zhǔn)！"是指向某工作表的意思。結(jié)果如圖：根據(jù)此公式我們可以輕松得出不同性別的學(xué)生的立定跳遠(yuǎn)成績(jī)。如果成績(jī)?yōu)榭?會(huì)自動(dòng)"缺考"。為了快速錄入,可以將E2:E40表格選中,右鍵——設(shè)置單元格格式——數(shù)字——自定義——類型中輸入00"."00,確定。以后在E2：E40單元格中,只要輸入211,表格就會(huì)自動(dòng)將其"變"為"02.11",效果不等于"2.11"。所以還得在標(biāo)準(zhǔn)工作表中,將標(biāo)準(zhǔn)也改為"02.11"的格式。如下圖：2.2計(jì)算"50mX8"得分由于六年級(jí)50mX8男女成績(jī)標(biāo)準(zhǔn)相同。所以我們只需要在H2中編輯以下公式：=LOOKUP<-G2,標(biāo)準(zhǔn)!A$3:A$21,標(biāo)準(zhǔn)!B$3:B$21>含義：由于我們錄入成績(jī)是以正數(shù)錄入,而"標(biāo)準(zhǔn)"中我們?cè)O(shè)置的是負(fù)數(shù),為了正確對(duì)應(yīng)數(shù)據(jù),所以在G2前加入"-"號(hào)。結(jié)果如圖：2.3總成績(jī)的計(jì)算總成績(jī),就是立定跳遠(yuǎn)和50mX8得分的總合,所以在I2中輸入公式：=IF<F2="缺考","缺考",IF<H2="缺考","缺考",F2+H2>>結(jié)果如圖：2.4名次統(tǒng)計(jì)在名次統(tǒng)計(jì)中,我們將用到RANK函數(shù)。我們?cè)贘2中錄入：=IF<I2="缺考","缺考",RANK<I2,I$2:I$40,0>>含義：降序,有重名排名；I40可以根據(jù)學(xué)生人數(shù)改成I100,I400等。結(jié)果如圖:3．成績(jī)統(tǒng)計(jì)打開成績(jī)統(tǒng)計(jì)工作表,錄入如圖內(nèi)容：3.1統(tǒng)計(jì)立定跳遠(yuǎn)的具體分?jǐn)?shù)在B4中錄入：=COUNTIF<成績(jī)!F$2:F$40,"=10">；B5中錄入：=COUNTIF<成績(jī)!F$2:F$40,">=9.5">-B4；B6中錄入：=COUNTIF<成績(jī)!F$2:F$40,">=9">-B5-B4B7…B12中錄入：=COUNTIF<成績(jī)!F$2:F$40,"缺考">含義：B5中的函數(shù)后面"-B4",意思是統(tǒng)計(jì)了成績(jī)大于等于9.5分的人數(shù)減去等于成績(jī)?yōu)?0分的人的人數(shù)…50mX8的具體分?jǐn)?shù),以與總成績(jī)情況的統(tǒng)計(jì)和立定跳遠(yuǎn)具體分?jǐn)?shù)的統(tǒng)計(jì)想同,這里不再多舉例。結(jié)果如圖：3.2各班具體分?jǐn)?shù)情況統(tǒng)計(jì)各班的總分統(tǒng)計(jì),首先得把各班分出來,然后再相加。用"篩選"功能的話,如果單元格不連貫的話,也不好用公式統(tǒng)計(jì)。怎么辦呢？還好上面有"SUMIF"函數(shù)。3.2.1各班部分統(tǒng)計(jì)我們?cè)贐16中錄入：=SUMIF<成績(jī)!B$2:B$40,6.1,成績(jī)!I$2:I$40>含義：在成績(jī)工作表的B2:B40中查找和本工作表"6.1"相同的數(shù)據(jù),然后把它們對(duì)應(yīng)I2:I40的數(shù)據(jù)相加。3.2.2各班平均分統(tǒng)計(jì)由于上一步已經(jīng)統(tǒng)計(jì)了各班的總分,所以現(xiàn)在只要用COUNTIF函數(shù)在成績(jī)工作表中統(tǒng)計(jì)各班人數(shù)即可。在B17中錄入函數(shù)：=B16/COUNTIF<成績(jī)!$B$2:$B$40,"6.1">含義：用B16除以"COUNTIF<成績(jī)!$B$2:$B$40,"6.1">"3.3.3各班分?jǐn)?shù)段成績(jī)統(tǒng)計(jì)各班分?jǐn)?shù)段成績(jī)統(tǒng)計(jì)要在成績(jī)工作表中找出,班級(jí)、分?jǐn)?shù)段、人數(shù),所以就涉與到條件求和,如圖,在B18中錄入=SUM<<成績(jī)!I$2:I$40=20>*<成績(jī)!B$2:B$40=6.1>>按CTRL+SHIFT+ENTER結(jié)束,得到{=SUM<<成績(jī)!I$2:I$40=20>*<成績(jī)!B$2:B$40=6.1>>}的數(shù)組。含義：統(tǒng)計(jì)成績(jī)工作表中,I2:I40中"=20",并且B2:B40中"=6.1"的單元格個(gè)數(shù)。其它分?jǐn)?shù)段也可以照例錄入函數(shù),如下圖：=SUM<<成績(jī)!B$2:B$40=6.1>*<成績(jī)!I$2:I$40>=18>*<成績(jī)!I$2:I$40<>"缺考">>-B18EXCEL圖表根據(jù)各項(xiàng)數(shù)據(jù),我們可以制作各種相關(guān)的數(shù)據(jù)圖表,比如：這是以全年級(jí)的立定跳遠(yuǎn)和50Mx8各分?jǐn)?shù)段的數(shù)據(jù)圖。3.3.5等級(jí)評(píng)定根據(jù)學(xué)生總分,還可以為學(xué)生自動(dòng)打出成