Excel處理《國家學生體質健康標準》數據的應用研究

劉月紅

摘要：Excel是大家非常熟悉的應用軟件，它強大的函數功能，可以幫助我們完成許多復雜的數據統計和操作。本文主要利用Excel 函數及郵件合并功能，對《國家學生體質健康標準》的數據進行自動計算、統計及《〈國家學生體質健康標準〉登記卡》的輸出，實現了《國家學生體質健康標準》計算及統計，自動輸出了《〈國家學生體質健康標準〉登記卡》，以方便學生體測數據管理，減輕體育老師的工作量。

關鍵詞：學生體質健康標準;函數;郵件合并

一、前言

《國家學生體質健康標準》（以下簡稱《標準》）是對學生體質健康狀態和鍛煉效果進行評估的一種標準。

目前，國內學校每年將體測資料上傳到“國家學生體質健康標準數據管理系統”軟件（以下簡稱《體測軟件》）。但是體育教師在軟件的實際軟件操作中，軟件操作和易用性等方面存在著諸多問題。如果可以通過一種常用的軟件，也可以實現《標準》數據的全部功能，且生成《〈國家學生體質健康標準〉登記卡》（以下簡稱《登記卡》），以方便體育教師上傳，從而大大減輕體育老師的工作量。

在教學中也會遇到許多報表的制作，其本質主要是對原始數據進行符合多個條件的統計。本文根據我校近年來的實際使用經驗，闡述利用Excel公式進行自動計算、評價、統計、輸出，實現《體測軟件》的所有功能，并加以擴展，解決實際工作中的問題。

二、目標設計

首要任務是自動計算和評價，即體育教師將數據輸入電腦之后，馬上能對數據進行相應分數的計算，并進行相應的評價;其次，實現《登記卡》一鍵自動生成的功能，方便畢業班級打印登記卡;再次，對所得的數據實現自動統計，自動統計出不及格率、及格率、良好率、優秀率、平均分等;最后，生成與《體測軟件》對應的數據，方便體育教師將本校體測數據上傳。

三、Excel自動計算、評價、統計、輸出設計

（一）前期數據準備

如表1、表2、表3所示：

以中學為例，將《標準》評分表錄入到一張工作表，將其命名為“標準”，按上表所示，分別將各項評分標準錄入在一張工作表中，以備后續函數運用時的參數調用。

其中“50米”和“1000/800米”項目的評分表，需要在評分表中的評分值加“0.001”，如表3所示。

（二）基本函數運用詳解

為實現自動評分和自動評價，我們用LOOKUP函數。

例如在進行肺活量（表2）評分，當學生的肺活量數據是“2300”時，函數會在評分表自動找到“2300”對應的分數是“70”分，從而返回值“70”，當實際數據小于“2300”，如“2290”時，此時函數找不到“2290”對應的分數值，函數會將小于“2290”對應的值“2180”自動匹配“68”的分數，從而返回值“68”（見表4）。

但是50米和1000/800米評分時，成績越小，分數越高，如果按照LOOKUP 函數的規則會出現問題。如表，當學生50米在表中對應的值“7.8”，則返回值是“100”，當學生成績在表中沒有對應的值如“8.3”，返回的值是“80”，而實際分數應該是“78”。為解決這個問題對評分表做適當調整，如表5右側，將標準值后面加上0.01，這樣當學生成績沒有對應的值的時候如“8.3”，函數會將小于“8.3”對應的值“8.21”自動匹配“78”，這樣就完美解決“成績越小，分數越好”的自動評分的問題。

（三）各數據自動評分、評價的運用

如表6所示：

清楚了LOOKUP函數的涵義，下一步目標是對學生的所有體測數據進行自動計算和自動評價。

以初一年級為例，新建工作表，命名為“初一”（初二、初三以此類推），當教師將學生各項數據輸入時，函數會自動計算出其對應的成績和評價。

其公式如下：

體重指數對應的公式：= F2/（E2/100 ×E2/100）;

體重指數成績對應的公式：

=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"））））;

肺活量成績對應的公式：

=LOOKUP（G2，標準！$B$23：$B$42，標準！$A$23：$A$42）;

以此類推，設置相應的公式，就可以對其他各項體測數據進行自動計算和評價。

（四）數據優化

在實際成績輸入數據過程中會出現幾個問題：

（1）男生體測和女生體測的標準不一致，項目不一致，如何對輸入的成績自動判斷并根據判斷選擇男生或女生的項目和評價標準;

（2）如果學生出現某個項目缺考，如何讓這個單項成績為0分，其他項目都正常顯示，以利最后的總評;

（3）《標準》中耐久跑和引體向上/仰臥起坐附加分，如何將這個分數自動加入。

通過對IF函數和LOOKUP函數的深度嘗試，可以完美解決以上出現的問題。

以50米評分為例（表6），公式如下：

=IF（D2="男"，（IF（C2=""，""，IF（OR（H2=""，H2=0），0，IF（H2>

標準！$B$91，0，（IF（H2<=標準！

$B$72，100，（LOOKUP（H2，標準！

$B$71：$B$91，標準！$A$71：$A$91）

））））））），（IF（C2=""，""，IF（OR

（H2=""，H2=0），0，IF（H2>標準！

$B$115，0，（IF（H2<=標準！$B$96，

100，（LOOKUP（H2，標準！$B$95：

$B$115，標準！$A$95：$A$115））））

）））））。

此公式的涵義為：首先判斷這名學生是否是“男”，如果是“男”，則執行后面“男生評價標準”的函數，否則執行“女生評價標準”的函數;再次判斷學生姓名有沒有輸入，如果沒有姓名，則50米成績顯示為空白，否則繼續執行后面的函數;最后還要判斷，50米成績是“空白（無成績）”的還是“0”，當50米成績是“空白（無成績）”或者是“0”時，其成績自動判斷為“0”分，否則執行對應的評分標準，并自動計算成績。這是一個多重嵌套的IF函數，完美解決第（1）和（2）的問題。

對于第（3）個問題，在引體向上/仰臥起坐和1000/800米評分時，可以對評分表格做出適當調整，見表7。通過對附加分評分標準的調整，可以完美解決附加分自動評分的問題，其公式與上述“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））。

（五）《登記卡》自動生成

《登記卡》是每學年畢業班級每個學生通過三年的體測數據自動生成的，其數據來源就是每學年的體測數據。當一個學生三年當中每年的體測數據都輸入完成，其對應的成績、評價都可以通過上述方法自動生成，并用郵件合并。

作為數據源，其數據需要做適當處理。根據Excel郵件合并的規則，郵件合并的數據源的所有數據必須在一個工作表。

新建一個工作表，命名為“初三畢業登記卡數據”（如表6）。

用公式調用“初一”“初二”“初三”中的所有數據到“初三畢業登記卡數據”工作表，實現“初三畢業登記卡數據”的自動填充，公式如下：

=IF（初一！E2=""，""，初一！E2）

此公式涵義是：“初一”工作表“E2”為空時，“初三畢業登記卡數據”對應的“E2”也為空，如果有數據，就調用“初一”工作表“E2”的數據，以此類推，可以將“初一”“初二”“初三”所有數據調用到一張工作表，這樣郵件合并的數據源就完成。

用Word調用“登記卡數據”工作本中的數據，《登記卡》就自動生成完成，生成后的登記卡如表8所示。

（六）數據結果統計與分析

新建一張統計表，如表9所示。

在對應的表格中輸入如下公式：

不及格人數：=COUNTIF（初一！AF：

AF，“不及格”），自動統計“初一”工作表中“不及格”的學生人數;

不及格比率：=B4/K4，自動統計“初一”工作表中“不及格”學生占總人數的比率百分比;

平均分：=AVERAGE（初一！AE：AE），自動統計“初一”工作表中所有學生的總評平均分。

其他年級的統計公式依此類推。如果需要其他統計信息，也可以在此工作表中調用前面的所有數據。

（七）上報數據的生成

各個學校每年將體測數據上傳至《體測軟件》，在完成數據錄入之后，如果數據不能與《體測軟件》匹配，上傳會出現錯誤，需要體育教師在軟件上重復輸入。但是通過下面的操作，可以自動完成數據的轉化。《體測軟件》中最主要的數據變化是1000米由“#.##”分轉化成“###”秒，如1000米成績是“3.55”，則在軟件中需要輸入“235”，可以通過下面的方法轉化。

在“標準”工作表中建立一個“分鐘”與“秒”對應的一個標準，作為函數調用的參數（如表10所示）。

表10 分鐘與秒的對應表

新建一個工作表，將其命名為“初一數據”（初二、初三以此類推），用公式“=IF（初一！A2=""，""，初一！A2）”

調用“初一”工作表中除“1000/800米”外的所有數據，調用“1000/800米”數據時用公式“=IF（初一！L2=""，""，（LOOKUP（初一！L2，標準！$A$366：

$A$726，標準！$B$366：$B$726）））”，

這樣初一年級的所有上傳數據自動調用完成，只需要將此工作表上傳到《體測軟件》，即可完成數據的上傳，不需再重復輸入。

四、總結

通過Excel函數和郵件合并功能的實際運用，Excel完全可以實現《標準》的數據自動計算、評價、統計及輸出《登記卡》的功能。開始制作工作表時，看起來公式運用比較煩瑣和復雜，但是理解LOOKUP和IF函數的具體涵義之后，操作起來就非常簡便，一個項目公式完成以后，其他項目只需要修改個別參數就可以完成，包括其他年級，也包括大學的體測數據，都可以全部參照此公式。當幾個工作表都完成之后，體育教師只需要每年按體測的各項數據輸入，學生的成績、評價、各項統計、登記表、數據上報等所有工作全部自動完成，將大大減輕體育教師的工作量。

參考文獻

[1]中華人民共和國教育部.教育部關于印發《國家學生體質健康標準（2014年修訂）》的通知：教體藝〔2014〕5號[EB/OL].

[2014–07–07].http：//old.moe.gov.cn//publicfiles/business/htmlfiles/moe/s3273/201407/171692.html

[2]徐德寶.芻議Lookup函數在體育成績評分上的應用[J].運動，2013（4）：121–122.