體質(zhì)測試數(shù)據(jù)Excel自動統(tǒng)計模板的研制

摘 要：由于設(shè)備維修等原因，學(xué)生健康體質(zhì)測試的各項數(shù)據(jù)往往需要人工記錄與統(tǒng)計，為了提高工作效率和數(shù)據(jù)的準(zhǔn)確性、通過在excel相應(yīng)單元插入公式的方式制作了該模板文件，以實現(xiàn)體質(zhì)測試成績的自動統(tǒng)計與等級評定，對減輕體育教師的工作負荷，從巨大而繁瑣的低級重復(fù)工作中解脫出來，具有一定的現(xiàn)實意義。此外，學(xué)生或其他人士也可以應(yīng)用該模板及時了解自己的體質(zhì)狀況，有利于體質(zhì)自我監(jiān)測、自我評估，對國民綜合體質(zhì)水平的提高具有一定的推動作用。

關(guān)鍵詞：體質(zhì)測試；自動統(tǒng)計；模板

中圖分類號：TP391.13

盡管我國大多數(shù)學(xué)校均采用專業(yè)的自動化設(shè)備，進行學(xué)生健康體質(zhì)測試數(shù)據(jù)的采集與統(tǒng)計，但由于設(shè)備價格、配套測試場地等開支龐大，設(shè)備采購量一般比較少，導(dǎo)致設(shè)備損耗大，故障率高，在一定程度上影響了學(xué)生健康體質(zhì)測試工作的正常進行。特別是條件相對艱苦的學(xué)校，一直都是人工測試、記錄和統(tǒng)計，每個學(xué)生的每項成績都需經(jīng)過多次計算和查表對照，工作量十分巨大，即耗費了大量的時間和精力，又因為疲勞而可能造成計算差錯，致使統(tǒng)計數(shù)據(jù)的準(zhǔn)確性不高。另外，通過儀器測試的數(shù)據(jù)存貯在儀器內(nèi)部的存儲器中，數(shù)據(jù)和成績反饋相對滯后。顯然，在人工計算、統(tǒng)計的前提下，和追求測試數(shù)據(jù)時效性的研究者來說，找到一種簡便有效的方法十分必要。Excel在數(shù)據(jù)報表方面具有強大的功能，“是一種先進的多功能集成軟件，具有強大的數(shù)據(jù)處理、分析、統(tǒng)計等功能”[1]，“可以使教師的成績分析工作事半功倍”[2]，但大多數(shù)體育教師和其他普通民眾并不擅長公式編輯。因此，該模板的應(yīng)用具有“解放生產(chǎn)力”的實際意義。

1 兩個基礎(chǔ)指數(shù)表

基礎(chǔ)表單是經(jīng)過研究人員依據(jù)我國青少年體質(zhì)的綜合情況，在充分調(diào)研、測試和論證的前提下研制而成，并經(jīng)教育主管部門核發(fā)下來，作為體質(zhì)測試成績計算、統(tǒng)計的標(biāo)準(zhǔn)和依據(jù)。分別是“臺階測試指數(shù)表”和“身高體重指數(shù)表”。

1.1 臺階測試指數(shù)表

臺階測試指數(shù)表的左邊第1列為3次心率測試之和的百位和十位值，個位以“0”代替，心率的個位值是最上邊1行的0-9（第1行第1列原表為空白，為避免公式在計算時出現(xiàn)非數(shù)值而產(chǎn)生錯誤，故本模板在此填充為“-1”），其它則為心率對應(yīng)的指數(shù)。如心率100對應(yīng)的指數(shù)為90，心率101對應(yīng)的指數(shù)為89.1等等，但指數(shù)并不體現(xiàn)為學(xué)生該項的成績（分?jǐn)?shù)）。

表1 大學(xué)生臺階測試指數(shù)對照表

1.2 身高體重指數(shù)表

身高體重指數(shù)對照表左邊第1列為身高值，第一行為分值（分?jǐn)?shù)）（第1行第1列的“0”與臺階對照表類似），其它則為體重標(biāo)準(zhǔn)值。

表2 大學(xué)生身高體重指數(shù)對照表

2 函數(shù)與公式

2.1 相關(guān)函數(shù)

（1）MATCH（）

即在指定的目標(biāo)區(qū)域內(nèi)查找指定的內(nèi)容，如：MATCH（D6，身高體重！$A$1：$A$52，1）。數(shù)據(jù)錄入表的D列是學(xué)生身高值，“身高體重！$A$1：$A$52”實際是身高體重表的整個A列（第1列），該列的身高值從140.9開始至190.9止，共51行。如身高175cm，反饋回來的結(jié)果是174.9行坐標(biāo)，如果身高的小數(shù)位在4以上時（如175.5cm），因遵循“小于或等于”的原則，實際返回的仍然是174.9的行坐標(biāo)。鑒于對照表中的身高的小數(shù)位都是“9”，因此，通過在原公式內(nèi)加上if（）語句判斷小數(shù)位的大小后，對D6的值作相應(yīng)調(diào)整，以誘使公式在計算時，指向預(yù)期記錄。

（2）CONCATENATE（）

在獲取行坐標(biāo)后，在把學(xué)生“75kg”的體重到該行去搜尋與之匹配的值所在的列坐標(biāo)之前，需要CONCATENATE（）的“合并”功能。因為MATCH（）返回的行坐標(biāo)是個變量，而“身高體重！”是字符，公式自然把它當(dāng)作字符處理，而不是以此命名的工作表。因此，MATCH（E6，CONCATENATE（“身高體重！$A$”，MATCH（IF（RIGHT（D6，1）<5，D6，D6+1），身高體重！$A$1：$A$52，1），”：$F$”，MATCH（IF（RIGHT（D6，1）<5，D6，D6+1），身高體重！$A$1：$A$52，1）））的公式就躍然而出了。然而實際測試中發(fā)現(xiàn)，返回的列坐標(biāo)誤差較大，原因在于體重標(biāo)準(zhǔn)值不像身高標(biāo)準(zhǔn)值那么有規(guī)律，而MATCH（）的“1”和“-1”只能用一個，顯然這個看似名正言順而又最簡單的公式卻不能達到預(yù)期目的，最終用“E6”與“B6”至“F6”逐個相減，差的絕對值最小的當(dāng)然是最匹配的。

（3）INDIRECT（）

求差的絕對值自然是用單元格的數(shù)值去運算，而前面得到的卻是某個數(shù)值的單元格坐標(biāo)（行，列），INDIRECT（）的功能就是返回指定坐標(biāo)的單元格的數(shù)值。再通過IF（）條件判斷與其它幾個的差的絕對值最小者，獲得所匹配的標(biāo)準(zhǔn)體重值的列坐標(biāo)。盡管獲取了身高體重表中所匹配的體重標(biāo)準(zhǔn)值的單元格坐標(biāo)，但任務(wù)還沒有完成，因為最終目的是要獲得這個體重標(biāo)準(zhǔn)值所對應(yīng)的分?jǐn)?shù)，即身高體重表中第1行中“B”至“F”列其中一個單元格的數(shù)值。有利因素是分?jǐn)?shù)值是在第一行，即單元格行坐標(biāo)是已知的。至此，獲取身高體重的分?jǐn)?shù)的預(yù)期目標(biāo)順利達成。

（4）INDEX（）

本模板最后一個重要函數(shù)INDEX（row，col）的功能就是在指定區(qū)域內(nèi)搜索出匹配值。似乎與INDIRECT（）一樣，但兩者間還是有區(qū)別的，主要在于INDIRECT（）需要給定單元格的絕對坐標(biāo)，根據(jù)這個給定的坐標(biāo)返回單元格的值，公式中\(zhòng)"身高體重！b\"的“b”就是指定了絕對的單元格列坐標(biāo)。而index（）能夠在較大單元格區(qū)域內(nèi)搜索與返回動態(tài)坐標(biāo)（變量）單元格的值。正如即將獲取的臺階測試指數(shù)一樣，只要給定目標(biāo)區(qū)域內(nèi)最左邊（區(qū)域第1列）單元格的行坐標(biāo)和最頂端（區(qū)域第1行）單元格的列坐標(biāo)，index（）即可返回行向右延伸而列向下延伸兩者交匯點單元格的值。因此，臺階測試指數(shù)獲取的任務(wù)就非index（）莫屬了。公式如下：

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=59，\"20\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=54，\"17\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=50，\"16\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=46，\"15\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=43，\"13\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>=40，\"12\"，

IF（INDEX（臺階指標(biāo)！$A$1：$K$18，MATCH（LEFT（G5，2）*10，臺階指標(biāo)！$A$1：$A$18），RIGHT（G5）+2）>35，\"10\"，\"5\"）））））））

2.2 其它相關(guān)公式

肺活量評分、立定跳遠評分、坐位體前屈、總分和等級評定等分?jǐn)?shù)（等級）評定，是根據(jù)學(xué)生成績所在的分值段，給出相應(yīng)的成績，只需通過if（）條件判斷直接給定即可。如等級評定的公式為：IF（P6>=86，\"優(yōu)秀\"，IF（P6>=76，\"良好\"，IF（P6>=60，\"及格\"，\"不及格\"）））

肺活量成績需要與體重關(guān)聯(lián)計算，公式為：IF（I6/E6>=75，\"15\"，IF（I6/E6>=70，\"13\"，IF（I6/E6>=64，\"12\"，IF（I6/E6>=57，\"11\"，IF（I6/E6>=54，\"10\"，

IF（I6/E6>=44，\"9\"，IF（I6/E6>=34，\"8\"，IF（I6/E6>=24，\"6\"，IF（I6/E6>=14，\"3\"，IF（I6/F6<14，\"1\"））））））））））

其它不再贅述。

3 結(jié)束語

本模板的研制從“解放人力就是解放生產(chǎn)力”的角度看，具有實際的社會價值，秉著“勿以善小而不為”的原則研制的本模板，必會給需要的人士帶來便利。當(dāng)然，利用excel的Vba編程比在單元格插入公式的方式代碼效率更高、可讀性更強且功能更為強大，“它使得用Excel完成一個管理系統(tǒng)的工作變得完全可能”[3]。

參考文獻：

[1]陳玉林.Excel在大學(xué)物理實驗數(shù)據(jù)處理中的應(yīng)用[J].實驗室研究與探索，2007（10）：63-65.

[2]高世萍.基于Excel函數(shù)及Excel VAB的成績分析[J].電腦學(xué)習(xí)，2011（01）：18-19.

[3]李曉玫.Excel中的VBA程序設(shè)計[J].四川師范大學(xué)學(xué)報（自然科學(xué)版），2004（04）：423-426.

作者簡介：袁鐸峰（1968-），男，湖南邵東人，副教授，研究方向：體育教學(xué)；楊惠燕（1974-），女，湖南懷化人，碩士，講師，研究方向：體育課程與教學(xué)論。

作者單位：湖南師范大學(xué)體育學(xué)院，長沙 410012；湖南軟件職業(yè)學(xué)院體育教研室，湖南湘潭 411200