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

趙亞特

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

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

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

1.1 成績(jī)登分表的建立

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

1.2 成績(jī)分析表的建立

1.2.1 表頭的建立

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

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

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

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

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

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

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

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

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

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

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

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

2.2 自動(dòng)化成績(jī)統(tǒng)計(jì)分析

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

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

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

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

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

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

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