劉月紅
摘要:Excel是大家非常熟悉的應(yīng)用軟件,它強大的函數(shù)功能,可以幫助我們完成許多復(fù)雜的數(shù)據(jù)統(tǒng)計和操作。本文主要利用Excel 函數(shù)及郵件合并功能,對《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》的數(shù)據(jù)進行自動計算、統(tǒng)計及《〈國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)〉登記卡》的輸出,實現(xiàn)了《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》計算及統(tǒng)計,自動輸出了《〈國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)〉登記卡》,以方便學(xué)生體測數(shù)據(jù)管理,減輕體育老師的工作量。
關(guān)鍵詞:學(xué)生體質(zhì)健康標(biāo)準(zhǔn);函數(shù);郵件合并
一、前言
《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》(以下簡稱《標(biāo)準(zhǔn)》)是對學(xué)生體質(zhì)健康狀態(tài)和鍛煉效果進行評估的一種標(biāo)準(zhǔn)。
目前,國內(nèi)學(xué)校每年將體測資料上傳到“國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)數(shù)據(jù)管理系統(tǒng)”軟件(以下簡稱《體測軟件》)。但是體育教師在軟件的實際軟件操作中,軟件操作和易用性等方面存在著諸多問題。如果可以通過一種常用的軟件,也可以實現(xiàn)《標(biāo)準(zhǔn)》數(shù)據(jù)的全部功能,且生成《〈國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)〉登記卡》(以下簡稱《登記卡》),以方便體育教師上傳,從而大大減輕體育老師的工作量。
在教學(xué)中也會遇到許多報表的制作,其本質(zhì)主要是對原始數(shù)據(jù)進行符合多個條件的統(tǒng)計。本文根據(jù)我校近年來的實際使用經(jīng)驗,闡述利用Excel公式進行自動計算、評價、統(tǒng)計、輸出,實現(xiàn)《體測軟件》的所有功能,并加以擴展,解決實際工作中的問題。
二、目標(biāo)設(shè)計
首要任務(wù)是自動計算和評價,即體育教師將數(shù)據(jù)輸入電腦之后,馬上能對數(shù)據(jù)進行相應(yīng)分數(shù)的計算,并進行相應(yīng)的評價;其次,實現(xiàn)《登記卡》一鍵自動生成的功能,方便畢業(yè)班級打印登記卡;再次,對所得的數(shù)據(jù)實現(xiàn)自動統(tǒng)計,自動統(tǒng)計出不及格率、及格率、良好率、優(yōu)秀率、平均分等;最后,生成與《體測軟件》對應(yīng)的數(shù)據(jù),方便體育教師將本校體測數(shù)據(jù)上傳。
三、Excel自動計算、評價、統(tǒng)計、輸出設(shè)計
(一)前期數(shù)據(jù)準(zhǔn)備
如表1、表2、表3所示:
以中學(xué)為例,將《標(biāo)準(zhǔn)》評分表錄入到一張工作表,將其命名為“標(biāo)準(zhǔn)”,按上表所示,分別將各項評分標(biāo)準(zhǔn)錄入在一張工作表中,以備后續(xù)函數(shù)運用時的參數(shù)調(diào)用。
其中“50米”和“1000/800米”項目的評分表,需要在評分表中的評分值加“0.001”,如表3所示。
(二)基本函數(shù)運用詳解
為實現(xiàn)自動評分和自動評價,我們用LOOKUP函數(shù)。
例如在進行肺活量(表2)評分,當(dāng)學(xué)生的肺活量數(shù)據(jù)是“2300”時,函數(shù)會在評分表自動找到“2300”對應(yīng)的分數(shù)是“70”分,從而返回值“70”,當(dāng)實際數(shù)據(jù)小于“2300”,如“2290”時,此時函數(shù)找不到“2290”對應(yīng)的分數(shù)值,函數(shù)會將小于“2290”對應(yīng)的值“2180”自動匹配“68”的分數(shù),從而返回值“68”(見表4)。
但是50米和1000/800米評分時,成績越小,分數(shù)越高,如果按照LOOKUP 函數(shù)的規(guī)則會出現(xiàn)問題。如表,當(dāng)學(xué)生50米在表中對應(yīng)的值“7.8”,則返回值是“100”,當(dāng)學(xué)生成績在表中沒有對應(yīng)的值如“8.3”,返回的值是“80”,而實際分數(shù)應(yīng)該是“78”。為解決這個問題對評分表做適當(dāng)調(diào)整,如表5右側(cè),將標(biāo)準(zhǔn)值后面加上0.01,這樣當(dāng)學(xué)生成績沒有對應(yīng)的值的時候如“8.3”,函數(shù)會將小于“8.3”對應(yīng)的值“8.21”自動匹配“78”,這樣就完美解決“成績越小,分數(shù)越好”的自動評分的問題。
(三)各數(shù)據(jù)自動評分、評價的運用
如表6所示:
清楚了LOOKUP函數(shù)的涵義,下一步目標(biāo)是對學(xué)生的所有體測數(shù)據(jù)進行自動計算和自動評價。
以初一年級為例,新建工作表,命名為“初一”(初二、初三以此類推),當(dāng)教師將學(xué)生各項數(shù)據(jù)輸入時,函數(shù)會自動計算出其對應(yīng)的成績和評價。
其公式如下:
體重指數(shù)對應(yīng)的公式:= F2/(E2/100 ×E2/100);
體重指數(shù)成績對應(yīng)的公式:
=IF(M2>=25,"60",IF(AND(M2>=22.2,M2<25),"80",IF(AND(M2<22.2,M2>15.4),"100",IF(M2<=15.4,"80"))));
肺活量成績對應(yīng)的公式:
=LOOKUP(G2,標(biāo)準(zhǔn)!$B$23:$B$42,標(biāo)準(zhǔn)!$A$23:$A$42);
以此類推,設(shè)置相應(yīng)的公式,就可以對其他各項體測數(shù)據(jù)進行自動計算和評價。
(四)數(shù)據(jù)優(yōu)化
在實際成績輸入數(shù)據(jù)過程中會出現(xiàn)幾個問題:
(1)男生體測和女生體測的標(biāo)準(zhǔn)不一致,項目不一致,如何對輸入的成績自動判斷并根據(jù)判斷選擇男生或女生的項目和評價標(biāo)準(zhǔn);
(2)如果學(xué)生出現(xiàn)某個項目缺考,如何讓這個單項成績?yōu)?分,其他項目都正常顯示,以利最后的總評;
(3)《標(biāo)準(zhǔn)》中耐久跑和引體向上/仰臥起坐附加分,如何將這個分數(shù)自動加入。
通過對IF函數(shù)和LOOKUP函數(shù)的深度嘗試,可以完美解決以上出現(xiàn)的問題。
以50米評分為例(表6),公式如下:
=IF(D2="男",(IF(C2="","",IF(OR(H2="",H2=0),0,IF(H2>
標(biāo)準(zhǔn)!$B$91,0,(IF(H2<=標(biāo)準(zhǔn)!
$B$72,100,(LOOKUP(H2,標(biāo)準(zhǔn)!
$B$71:$B$91,標(biāo)準(zhǔn)!$A$71:$A$91)
))))))),(IF(C2="","",IF(OR
(H2="",H2=0),0,IF(H2>標(biāo)準(zhǔn)!
$B$115,0,(IF(H2<=標(biāo)準(zhǔn)!$B$96,
100,(LOOKUP(H2,標(biāo)準(zhǔn)!$B$95:
$B$115,標(biāo)準(zhǔn)!$A$95:$A$115))))
)))))。
此公式的涵義為:首先判斷這名學(xué)生是否是“男”,如果是“男”,則執(zhí)行后面“男生評價標(biāo)準(zhǔn)”的函數(shù),否則執(zhí)行“女生評價標(biāo)準(zhǔn)”的函數(shù);再次判斷學(xué)生姓名有沒有輸入,如果沒有姓名,則50米成績顯示為空白,否則繼續(xù)執(zhí)行后面的函數(shù);最后還要判斷,50米成績是“空白(無成績)”的還是“0”,當(dāng)50米成績是“空白(無成績)”或者是“0”時,其成績自動判斷為“0”分,否則執(zhí)行對應(yīng)的評分標(biāo)準(zhǔn),并自動計算成績。這是一個多重嵌套的IF函數(shù),完美解決第(1)和(2)的問題。
對于第(3)個問題,在引體向上/仰臥起坐和1000/800米評分時,可以對評分表格做出適當(dāng)調(diào)整,見表7。通過對附加分評分標(biāo)準(zhǔn)的調(diào)整,可以完美解決附加分自動評分的問題,其公式與上述“50米評分”類似。
如表7所示:
最后“總評”成績,可以通過如下公式自動算出:
=IF(C2="","",(N2×0.15+P2×0.15+R2×0.2+T2×0.1+V2×0.1+X2×0.1+Z2×0.2+AB2+AC2))。
(五)《登記卡》自動生成
《登記卡》是每學(xué)年畢業(yè)班級每個學(xué)生通過三年的體測數(shù)據(jù)自動生成的,其數(shù)據(jù)來源就是每學(xué)年的體測數(shù)據(jù)。當(dāng)一個學(xué)生三年當(dāng)中每年的體測數(shù)據(jù)都輸入完成,其對應(yīng)的成績、評價都可以通過上述方法自動生成,并用郵件合并。
作為數(shù)據(jù)源,其數(shù)據(jù)需要做適當(dāng)處理。根據(jù)Excel郵件合并的規(guī)則,郵件合并的數(shù)據(jù)源的所有數(shù)據(jù)必須在一個工作表。
新建一個工作表,命名為“初三畢業(yè)登記卡數(shù)據(jù)”(如表6)。
用公式調(diào)用“初一”“初二”“初三”中的所有數(shù)據(jù)到“初三畢業(yè)登記卡數(shù)據(jù)”工作表,實現(xiàn)“初三畢業(yè)登記卡數(shù)據(jù)”的自動填充,公式如下:
=IF(初一!E2="","",初一!E2)
此公式涵義是:“初一”工作表“E2”為空時,“初三畢業(yè)登記卡數(shù)據(jù)”對應(yīng)的“E2”也為空,如果有數(shù)據(jù),就調(diào)用“初一”工作表“E2”的數(shù)據(jù),以此類推,可以將“初一”“初二”“初三”所有數(shù)據(jù)調(diào)用到一張工作表,這樣郵件合并的數(shù)據(jù)源就完成。
用Word調(diào)用“登記卡數(shù)據(jù)”工作本中的數(shù)據(jù),《登記卡》就自動生成完成,生成后的登記卡如表8所示。
(六)數(shù)據(jù)結(jié)果統(tǒng)計與分析
新建一張統(tǒng)計表,如表9所示。
在對應(yīng)的表格中輸入如下公式:
不及格人數(shù):=COUNTIF(初一!AF:
AF,“不及格”),自動統(tǒng)計“初一”工作表中“不及格”的學(xué)生人數(shù);
不及格比率:=B4/K4,自動統(tǒng)計“初一”工作表中“不及格”學(xué)生占總?cè)藬?shù)的比率百分比;
平均分:=AVERAGE(初一!AE:AE),自動統(tǒng)計“初一”工作表中所有學(xué)生的總評平均分。
其他年級的統(tǒng)計公式依此類推。如果需要其他統(tǒng)計信息,也可以在此工作表中調(diào)用前面的所有數(shù)據(jù)。
(七)上報數(shù)據(jù)的生成
各個學(xué)校每年將體測數(shù)據(jù)上傳至《體測軟件》,在完成數(shù)據(jù)錄入之后,如果數(shù)據(jù)不能與《體測軟件》匹配,上傳會出現(xiàn)錯誤,需要體育教師在軟件上重復(fù)輸入。但是通過下面的操作,可以自動完成數(shù)據(jù)的轉(zhuǎn)化?!扼w測軟件》中最主要的數(shù)據(jù)變化是1000米由“#.##”分轉(zhuǎn)化成“###”秒,如1000米成績是“3.55”,則在軟件中需要輸入“235”,可以通過下面的方法轉(zhuǎn)化。
在“標(biāo)準(zhǔn)”工作表中建立一個“分鐘”與“秒”對應(yīng)的一個標(biāo)準(zhǔn),作為函數(shù)調(diào)用的參數(shù)(如表10所示)。
表10 分鐘與秒的對應(yīng)表
新建一個工作表,將其命名為“初一數(shù)據(jù)”(初二、初三以此類推),用公式“=IF(初一!A2="","",初一!A2)”
調(diào)用“初一”工作表中除“1000/800米”外的所有數(shù)據(jù),調(diào)用“1000/800米”數(shù)據(jù)時用公式“=IF(初一!L2="","",(LOOKUP(初一!L2,標(biāo)準(zhǔn)!$A$366:
$A$726,標(biāo)準(zhǔn)!$B$366:$B$726)))”,
這樣初一年級的所有上傳數(shù)據(jù)自動調(diào)用完成,只需要將此工作表上傳到《體測軟件》,即可完成數(shù)據(jù)的上傳,不需再重復(fù)輸入。
四、總結(jié)
通過Excel函數(shù)和郵件合并功能的實際運用,Excel完全可以實現(xiàn)《標(biāo)準(zhǔn)》的數(shù)據(jù)自動計算、評價、統(tǒng)計及輸出《登記卡》的功能。開始制作工作表時,看起來公式運用比較煩瑣和復(fù)雜,但是理解LOOKUP和IF函數(shù)的具體涵義之后,操作起來就非常簡便,一個項目公式完成以后,其他項目只需要修改個別參數(shù)就可以完成,包括其他年級,也包括大學(xué)的體測數(shù)據(jù),都可以全部參照此公式。當(dāng)幾個工作表都完成之后,體育教師只需要每年按體測的各項數(shù)據(jù)輸入,學(xué)生的成績、評價、各項統(tǒng)計、登記表、數(shù)據(jù)上報等所有工作全部自動完成,將大大減輕體育教師的工作量。
參考文獻
[1]中華人民共和國教育部.教育部關(guān)于印發(fā)《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)(2014年修訂)》的通知:教體藝〔2014〕5號[EB/OL].
[2014–07–07].http://old.moe.gov.cn//publicfiles/business/htmlfiles/moe/s3273/201407/171692.html
[2]徐德寶.芻議Lookup函數(shù)在體育成績評分上的應(yīng)用[J].運動,2013(4):121–122.