Excel處理《國家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》數(shù)據(jù)的應(yīng)用研究

劉月紅

摘要：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.