生物教學(xué)中巧用Excel 2003實現(xiàn)成績統(tǒng)計自動化

趙亞特

中學(xué)生物教育教學(xué)評價中經(jīng)常會涉及到眾多的考試，如周考、月考、期中考、期末考等。面對海量的成績數(shù)據(jù)，生物老師如何準(zhǔn)確快速地進(jìn)行分析，從而找出教學(xué)中和班級管理中的問題，是一線生物教師比較苦惱的事情。Excel 2003作為中學(xué)老師比較熟悉的辦公軟件，雖經(jīng)常接觸，但很少會使用其函數(shù)統(tǒng)計功能。下面通過筆者在年級生物成績統(tǒng)計分析中的摸索和經(jīng)驗，以一個年級（包含4個班）成績數(shù)據(jù)分析為例來介紹如何使用Excel 2003做好成績分析模板，實現(xiàn)對歷次生物考試成績輕松進(jìn)行分析，利于生物教師找出各班的差異以及幾次考試成績的相對情況。

1 成績統(tǒng)計分析工作簿模板的建立

建立成績統(tǒng)計分析工作簿模板，命名為成績統(tǒng)計分析模板，其中包含兩張工作表，一張重命名為成績登分表，另一張重命名為成績分析表。

1.1 成績登分表的建立

切換工作表至成績登分表，在第一行建立列名，依次為班級、姓名、生物1、生物1排名、生物2、生物2排名等?？颇繉?yīng)列中為學(xué)生的原始成績，如果缺考就在相應(yīng)的單元格中輸入“缺考”?？颇颗琶兄惺褂门判蚝瘮?shù)=rank（），如生物1成績在c列，生物1排名在d列，在d2單元格中則輸入函數(shù)=rank（c2，c：c），如圖1所示。然后將鼠標(biāo)移至單元格右下角出現(xiàn)“+”時向下拖曳即可得到生物1成績在全年級相應(yīng)的排名，其他依次類推，至此成績登分表就建立完畢。為了方便對成績列和成績排名列的引用，如對生物1成績列進(jìn)行定義，單擊插入菜單，依次選擇“名稱”—“定義”，彈出對話框如圖2所示。在“在當(dāng)前工作簿中的名稱”下面的框里鍵入生物1，在“引用位置”下面的框中點選框右端的選擇按鈕，然后點選生物1成績所在列C，在空白框中顯示“=成績登分表！$C：$C”。由于第一行為標(biāo)題行，所以將“=成績登分表！$C：$C”修改為“=成績登分表！$C2：$C20 000”，根據(jù)學(xué)生人數(shù)可以相應(yīng)調(diào)整其中20 000這個數(shù)值。當(dāng)然值越小，分析速度越快，可以將其設(shè)置為稍大于學(xué)生人數(shù)即可。

1.2 成績分析表的建立

1.2.1 表頭的建立

切換工作表至成績分析表，首先在第一行鍵入表頭，如“高2016級生物歷次成績分析表”，接下來在鍵入成績分析指標(biāo)，在這里建立分析指標(biāo)如圖3所示。其中的上線人數(shù)1和下面的30指的是取前30名，上線人數(shù)2和下面的112指的是取前112名，上線率則是用各班上線人數(shù)除以各班參考總?cè)藬?shù)。30和112可以根據(jù)要求手動進(jìn)行更改，變異系數(shù)=標(biāo)準(zhǔn)差/平均數(shù)，變異系數(shù)越大，表明個體差異越大。為了制作一張表中根據(jù)需要選擇顯示歷次生物成績，需要建立下拉單元格，具體操作：① 在一定區(qū)域如圖4輸入科目和科目排名；② 點選圖3中生物1所在的單元格點擊菜單數(shù)據(jù)選擇有效性，在允許中選擇序列，在來源下面的框右端點擊選擇圖4中的左列中生物數(shù)據(jù)所在區(qū)域，即可制作生物歷次考試下拉列表，同理制作生物排名下拉列表。

1.2.2 統(tǒng)計指標(biāo)中用到的函數(shù)

在多條件統(tǒng)計人數(shù)時常用到的函數(shù)“=SUMPRODUCT（）”。多個條件時，條件之間用*連接，如滿足兩個條件的計數(shù)函數(shù)“=SUMPRODUCT（（ ）*（ ））”，其中的條件為真時返回值為1，為假時返回值為0，所以只要有一個條件不滿足，結(jié)果就為0。最后將所有記錄的所有的結(jié)果相加，即可求出滿足條件的所有記錄的個數(shù)。如在B5單元格中輸入函數(shù)“=SUMPRODUCT（（成績登分表！$A$2：$A$20000=$A5）*（INDIRECT（$C$2）<=B$4））”，下拉填充即可得到其他班級的數(shù)據(jù)，上線人數(shù)2的公式編輯同理。

為了使得分析數(shù)據(jù)隨著點選下拉列表的變化而同步變化，用到了一個間接引用函數(shù)“INDIRECT（ ）”，如“INDIRECT（$C$2）”的結(jié)果就是引用C2單元格中的值所代表的值，當(dāng)C2顯示為語文排名時，它執(zhí)行的結(jié)果就是引用成績登分表中語文排名中所定義的數(shù)據(jù)區(qū)域。

在F5單元格中輸入及格人數(shù)統(tǒng)計函數(shù)=SUMPRODUCT（（理科！$A$2：$A$20000=$A23）*（INDIRECT（$B$20）>=$H$22））-SUMPRODUCT（（理科！$A$2：$A$20000=$A23）*（INDIRECT（$B$20）="缺考"）），其中由于只用前半部分函數(shù)會將“缺考”單元格判斷為無窮大而包含在內(nèi)，所以用后半部分公式將缺考單元格去掉。

為了按照班級統(tǒng)計最高分、最低分和標(biāo)準(zhǔn)差，使用了數(shù)組函數(shù)，如求最高分在I5單元格輸入函數(shù)“=MAX（IF（成績登分表！$A$2：$A$20 000=$A5，INDIRECT（$B$2）））”之后，同時按下“Ctrl+Shift+Enter”三鍵，就會在原有公式首尾出現(xiàn)“{ }”。同理在J5和K5單元格分別輸入數(shù)組公式“=MIN（IF（成績登分表！$A$2：$A$20000=$A5，INDIRECT（$B$2）））”和“=STDEV（IF（成績登分表！$A$2：$A$20000=$A5，INDIRECT（$B$2）））”，分別求出最小值和標(biāo)準(zhǔn)差，鼠標(biāo)向下拖曳可求出其他3個班的數(shù)據(jù)。

在H5單元格中輸入平均分統(tǒng)計函數(shù)“=SUMIF（成績登分表！$A$2：$A$20 000，$A5，INDIRECT（$B$2））/M5”，其中M5為各班的參考人數(shù)，鼠標(biāo)向下拖曳可求出其他3個班的數(shù)據(jù)。

在M5單元格中輸入計數(shù)函數(shù)“=SUMPRODUCT（（成績登分表！$A$2：$A$20 000=$A5）*（INDIRECT（$B$2）<>“缺考”））”。

在N5單元格中輸入計數(shù)函數(shù)“=COUNTIF（成績登分表！$A：$A，$A5）”求出總?cè)藬?shù)，鼠標(biāo)向下拖曳可求出其他3個班的數(shù)據(jù)。

2 成績統(tǒng)計分析工作簿模板的使用

2.1 原始成績的錄入及缺考標(biāo)記

在成績登分表科目列中登入原始成績后，排名列會更新排名情況，缺考可能是全部科目都缺考，也可能只有某一門或幾門缺考，在缺考考生記錄相應(yīng)缺考科目及排名單元格中輸入“缺考”作為標(biāo)記。

2.2 自動化成績統(tǒng)計分析

在成績分析表中，可以根據(jù)需要更改名次，如取前20名，則將30更改為20，上線分?jǐn)?shù)2的修改同理。及格人數(shù)下面的90是針對150分滿分設(shè)置的，如果是100分滿分，則將其改為60即可。

比較4個班生物1成績情況，可以在B2單元格下拉列表中點選“生物1”，在C2單元格下拉列表中點選“生物1排名”，結(jié)果如圖5所示。要比較生物2則相應(yīng)點選“生物2”和“生物2排名”，稍等片刻會計算最終顯示結(jié)果如圖6。由于有重名次，所以取前30名，實際的結(jié)果有32人。

3 成績統(tǒng)計分析工作簿模板的注意事項

3.1 成績登分表的保護(hù)

在每次考試時，由于有的考生退學(xué)或者全部科目缺考，這時為了不顯示這些記錄只能選定相應(yīng)內(nèi)容進(jìn)行清除內(nèi)容操作，而不能刪除相應(yīng)行或列破壞表格，一旦表格被破壞，所有有關(guān)間接引用的函數(shù)計算結(jié)果都會報錯。

3.2 成績分析表的保護(hù)

成績分析表建立好以后，由于公式眾多，為了保證公式不被失誤操作而修改，可以點選相應(yīng)的公式區(qū)域，然后點擊格式菜單選擇單元格，切換到保護(hù)選項卡，點選“鎖定”和“隱藏”。確定后，點擊工具“菜單”選擇“保護(hù)”—“工作表保護(hù)”，即可保護(hù)公式的正確性，從而多次重復(fù)使用。