用EXCEL做學(xué)生體育鍛煉達(dá)標(biāo)情況統(tǒng)計

1、用EXCEL做學(xué)生體育鍛煉達(dá)標(biāo)情況統(tǒng)計你知道體育老師最惱火的工作是什么嗎？就我的經(jīng)驗，體棄老師最惱火的工作是給學(xué)生作 體冇鍛煉達(dá)標(biāo)成績的換算工作。他們要根據(jù)國家體育鍛煉標(biāo)準(zhǔn)成績評分表將學(xué)生的測試成績 換算成100分制來作統(tǒng)訃和評優(yōu)。但他們用人工對照換算非常麻煩，工作量也很大。有些老師對 數(shù)據(jù)的敏感度很高，于是他們能將評分標(biāo)準(zhǔn)背卜來，這樣當(dāng)然能加快工作速度。但并不是每個體 棄老師都擁有這一背功”，所以我給我周用的體育老師搞了這樣一個自動 統(tǒng)訃的模板。你可將 這一方法介紹給你們的體ff老師。（一）測試成績與100分制的自動套算以國家體冇鍛煉標(biāo)準(zhǔn)測驗成績大學(xué)男生評分表之跳遠(yuǎn)項目為例。首先，將某一班級

2、學(xué) 生測試成績錄入Excel工作表中（圖3-18）ABCD1學(xué)生體育鍛煉達(dá)標(biāo)統(tǒng)計表2跳遠(yuǎn)3測試成績冰）換算成100分制415. 61524. 25634. 05圖3-18再利用可嵌套的IF函數(shù)，在D4單元格中連續(xù)輸入=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)回車確認(rèn)后將會在DI單元格中自動得到與5. 61米”對應(yīng)的100分制成績“100”，再利用“單元格的“引用”功能， 將D4中的換算公 式用鼠標(biāo)“填充柄”方式（即將鼠標(biāo)移至D4單 元格右下方使之變?yōu)楹凇笆弊郑┮来瓮戏诺紻5,D6等單元格，相應(yīng)地在D3, D6等單元格 將得到與C5,C6等單元格相對應(yīng)的100分制成 績，見圖3-19。相應(yīng)地，可在后而單元格中輸 入苴它項目的測試成績，并輸 入相應(yīng)的換算公 式，即可得到其它項目的100制成績。ABCD1學(xué)空體育鍛煉達(dá)標(biāo)統(tǒng)計表2跳遠(yuǎn)3測試成績換算成100

4、分制415. 61100524 2545634 0530743 983A圖3-19說明：IF函數(shù)的一般格式是IF（條件,值1,值2）功能：當(dāng)條件為真時取值1否則取值2;并允許在值1或值2位置嵌套下一個IF條件，嵌套 層數(shù)最多不超過7層。在國家體育鍛煉標(biāo)準(zhǔn)評分表中，從30分到100分每5分一個段，于是 可分7個條件到“值1”中，另分7個條件到“值2”中。假設(shè)需要，還可在“值1”條件中的 “值1”中再嵌套條件，因此理論上說，可以嵌套足夠多的條件。所以再復(fù)雜的條件也是可以在 這個表格中得到解決的（詳細(xì)使用規(guī)則可參看相關(guān)書籍或從Excel的使用幫助中得到，此處不再 贊述）。話說回來，除在體育鍛煉達(dá)標(biāo)成

5、績換算中需要這樣多的嵌套條件外，在其他問題中尚未 遇到需要嵌套這么多條件的問題。（-）學(xué)生成績分布情況的自動統(tǒng)計現(xiàn)在我們要對學(xué)生達(dá)標(biāo)情況進(jìn)行統(tǒng)汁。 假設(shè)將對一個班30名學(xué)生的跳遠(yuǎn)測試成績進(jìn)行統(tǒng)訃。先要在前一步中將原始數(shù)據(jù)輸入到工作表的C4C33單元格中，再在前一步中將對應(yīng)100分制成績換 算到單元格D4-D33中。然后在G2-G5單元格分別輸入“優(yōu)秀”、“良好”、“及格”、“不及 格”。假設(shè)優(yōu)秀的條件是成績不低于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的數(shù)據(jù)個數(shù)記入H2單元格，將大于或等于75小于85的數(shù)據(jù) 個數(shù)記入H3單元格，將大于或等于60小于75的數(shù)據(jù)個數(shù)記入H4單元格，將小于60的數(shù)據(jù)個數(shù) 記入H5單元格，即可分別得到各分?jǐn)?shù)段的學(xué)生人數(shù)（圖3-20）a（三）繪制統(tǒng)計圖利用Excel軟件可以很方便的繪制統(tǒng)計圖。首先用鼠標(biāo)選中需要繪制統(tǒng)計圖的數(shù)拯源，此例中為單元格G2-H5,然后在“插入”菜單中選擇“ 即因表

7、”功能，系統(tǒng)將彈岀建立圖表的向?qū)В凑障驅(qū)崾究稍谒牟街畠?nèi)建好自己所需要的統(tǒng)計圖，如圖3-21（柱形圖） 和圖3-22（餅圖）。ABCDFGHI1學(xué)生體育鍛煉達(dá)標(biāo)統(tǒng)計表成績分布情況統(tǒng)計2MH:遠(yuǎn)85及以上優(yōu)秀143J-P測試成績換算成10D分乃期良好4415.6110060-74及格5524. 254560以下 不及格7圖3-205學(xué)生休育鍛煉測試達(dá)標(biāo)柱形統(tǒng)計圖3-217生體育鍛煉測試達(dá)標(biāo)餅形統(tǒng)計圖不及格23%廠、優(yōu)秀良好圖3-22（四）VL00KUP函數(shù)11、 此工作之成績換算中還有一種町自動查找并匹配成績的方法， 那就是HL00KUP和VL00KUP函數(shù)。（1）功能：在表格或數(shù)值數(shù)組的首列

8、査找指左的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中 指立列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表首列時，可以使用函數(shù)VL00KUP代替函數(shù)HLOOKUPo在VTOOKUP中的V代表垂直。（2）語法：VLOOKUP （lookiip*-alue, table_array, col_mdex_num, range_lookiip）（3）參數(shù)說明：Lookup_wlue為需要在數(shù)組（數(shù)組：用于建立可生成多個結(jié)果或可對在行和列中排列的一組 參數(shù)進(jìn)行運算的單個公式。數(shù)組區(qū)域共用一個公式：數(shù)組常量是用作參數(shù)的一組常量。）第一 列中查找的數(shù)值。Lookiip. value nf以為數(shù)值、引用或文本字符串。Table.ami

9、y為需要在英中查找數(shù)拯的數(shù)據(jù)表。町以使用對區(qū)域或區(qū)域名稱的引用，例如數(shù) 據(jù)庫或列表。如果range_lookup為TRUE,則table_array的第一列中的數(shù)值必須按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE：否則，函數(shù)VLOOKUP不能返回正確的數(shù)值。如果rangelookup01優(yōu)秀良好及格不及幣13% *5為FALSE, table array不必進(jìn)行排序。I文字來源于0伍ce2003之幫助通過在“數(shù)據(jù)”菜單中的“排序”中選擇“升序S可將數(shù)值按升序排列。Table_anay的第一列中的數(shù)值可以為文本、數(shù)字或邏借值。文本不區(qū)分大小寫。Col_mdex_niim為ta

10、ble_array中待返回的匹配值的列序號。Col_index_num為1時，返回table_array第一列中的數(shù)值：col_mdex_num為2,返回tab 1 e_array第二列中的數(shù)值， 以此類推。如果col_mdex_num小于1,函數(shù)TOOKUP返回錯誤值如果col_index_num人于table_array的列數(shù)， 函數(shù)VLOOKUP返回錯誤值#REF!。Rangejookiip為一邏輯值， 指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。 如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookupjalue的最 大數(shù)值：如果iaiig

11、e_value為FALSE,函數(shù)T00KUP將返回精確匹配值。如果找不到，則返回錯誤 值#N/A.如果函數(shù)TOOKUP找不到lookiip_value.且rangeJookiip為TRUE,則使用小于 等于lookup_valu已的最大值。如果1 ookup_value小于table_array第一列中的最小數(shù)值，函數(shù)VTOOKUP返回錯誤 值#N/A.如果函數(shù)XTL00KUP找不到lookup J alue且range.lookiip為FALSE,函數(shù)VLOOKUP返回錯 誤值#N/Ao(4)示例：首先，我們需要一張成績對照表，可放在sheet?工作表中(也町與要處理的數(shù)據(jù)在同表中)，如圖3-23：ABC1跳遠(yuǎn)（米）分?jǐn)?shù)（100分制）23. 923031063544. 141054.224564. 305074.385584. 466094. 5465104.6270114.7075124.7880134.8685144. 9490155. 0295165.1010017w M Sheetl 5heet2 /5heet3 /圖3。3所要處理數(shù)據(jù)同前圖3-18,仍在sheetl工作表中。在D4單元格中輸入=TOOKUP （C4, Sheet2SA:$B. 2）回車確認(rèn)后將會在D4單元格中自動得到與C4單元格中“5.61