摘 要：文章首先介紹了電子表格在成績分析管理中的必要性;然后從實(shí)際需求出發(fā)分析了中學(xué)成績分析管理系統(tǒng)的功能特點(diǎn);最后詳細(xì)闡述了如何巧妙利用WPS內(nèi)置的公式和單元格自動(dòng)填充實(shí)現(xiàn)這些功能的方法，并給出了關(guān)鍵公式。

關(guān)鍵詞：電子表格 ;成績分析 ;教學(xué)管理

引言：雖然很多學(xué)校已經(jīng)購買專業(yè)的成績管理系統(tǒng)，但是仍不能滿足不同學(xué)校不同教師個(gè)性化的需求，因此很多教師還需要用電子表格分析自己需要的數(shù)據(jù)。電子表格以其靈活易用的特點(diǎn)在數(shù)據(jù)分析處理中仍占有重要的地位。

筆者在多年利用電子表格分析處理學(xué)生成績的過程中不斷改進(jìn)完善系統(tǒng)的基本功能，逐漸形成了一套高效易用的成績分析管理系統(tǒng)。該系統(tǒng)巧妙運(yùn)用電子表格提供的基本公式，不用專業(yè)的VBA編寫代碼，因此普通教育工作者可依葫蘆畫瓢構(gòu)建出適合自己學(xué)校的成績分析系統(tǒng)。本文是筆者多年利用電子表格分析學(xué)生考試成績的經(jīng)驗(yàn)總結(jié)，希望能起到拋磚引玉的作用。

一、成績分析管理系統(tǒng)的功能模塊

中學(xué)的成績分析流程如下：

1.以備課組為單位進(jìn)行成績錄入分析打印;

2.備課組上報(bào)成績給年級長，年級長進(jìn)行總成績的匯總分析;

3.級長上報(bào)年級成績給學(xué)校教務(wù)處，教務(wù)處進(jìn)行成績存檔。

因此，成績分析管理系統(tǒng)的功能模塊如下：

1.備課組成績分析模塊

該模塊主要功能是：

（1）成績錄入功能：a、客觀題成績導(dǎo)入功能，方便導(dǎo)入機(jī)器改答題卡的成績;b、主觀題成績錄入功能。

（2）學(xué)生成績單功能：分別產(chǎn)生按學(xué)號排序和按成績名次排序兩種成績單。

（3）成績分析功能：計(jì)算班級平均分、分?jǐn)?shù)段人數(shù)、最高分、最低分、優(yōu)秀率、及格率等統(tǒng)計(jì)數(shù)據(jù)。

（4）其他個(gè)性化需求功能：比如產(chǎn)生教師和備課組長要上交到學(xué)校存檔的考試質(zhì)量分析報(bào)表。

2.年級總成績匯總分析模塊

該模塊主要功能是：

（1）成績匯總功能：自動(dòng)匯總各備課組上報(bào)的各科成績，產(chǎn)生成績總表。

（2）學(xué)生總成績單功能：分別產(chǎn)生按學(xué)號排序和按總成績名次排序的成績單。

（3）總成績分析功能：計(jì)算班級平均分、分?jǐn)?shù)段人數(shù)、最高分、最低分、優(yōu)秀率、及格率等統(tǒng)計(jì)數(shù)據(jù)。

（4）上線情況分析功能：上線指的是估算上重點(diǎn)線、本科線的人數(shù)，計(jì)算各班、各科單上線與雙上線情況，計(jì)算各班、各科的貢獻(xiàn)率、命中率、完成率等數(shù)據(jù)。

（5）其他個(gè)性化需求功能：比如產(chǎn)生要上交到學(xué)校存檔的考試質(zhì)量分析報(bào)表。

3.歷次考試成績匯總分析模塊

該模塊主要功能是：

（1）歷次成績匯總功能：自動(dòng)匯總歷次考試的成績，產(chǎn)生學(xué)年成績總表。

（2）學(xué)生個(gè)人歷次成績單功能：能自動(dòng)生成帶表頭的學(xué)生個(gè)人歷次成績單，方便班主任在家長會、期末向家長發(fā)放學(xué)生歷次成績單。

（3）歷次成績對比分析功能：能產(chǎn)生任意兩次考試成績的對比分析數(shù)據(jù)，學(xué)生成績的進(jìn)退一目了然。

（4）歷次考試光榮榜功能：產(chǎn)生各次考試的光榮榜，年級前50名名單、班級前10名名單、各科前5名名單、進(jìn)步最大的前30名名單等數(shù)據(jù)。

二、成績分析管理系統(tǒng)功能模塊實(shí)現(xiàn)

通常使用電子表格構(gòu)建的成績分析系統(tǒng)不易于擴(kuò)展、移植、復(fù)用，需求稍微有一點(diǎn)變化，則無法使用，需要重新修改。我們在實(shí)現(xiàn)各功能模塊時(shí)要充分利用電子表格自動(dòng)填充的功能盡量使公式可擴(kuò)展可移植可復(fù)用。

由于很多地方反復(fù)要使用學(xué)生的學(xué)號和姓名等信息，因此把學(xué)生的基本信息單獨(dú)做一個(gè)表格供其他地方引用。當(dāng)重新分班或者學(xué)生轉(zhuǎn)學(xué)等發(fā)生學(xué)生變動(dòng)情況，只需修改學(xué)生基本信息表，其他地方無需修改。

1.備課組成績分析模塊

（1）成績錄入功能

成績一般是兩部分：選擇題和主觀題。選擇題使用改卷機(jī)改卷，由于學(xué)生沒正確填涂考號或缺考等原因造成個(gè)別學(xué)生沒有選擇題成績，因此使用復(fù)制粘貼的方法往往發(fā)生錯(cuò)位。我們的解決方案是新建“選擇題錄入”工作表，在“原始分輸入”表中利用如下公式將對應(yīng)學(xué)生的選擇題分?jǐn)?shù)找出來。

HLOOKUP（“總分”，選擇題錄入！$A$1：$Z$997，MATCH（A2，選擇題錄入！$E$1：$E$997，0），F(xiàn)ALSE）

該公式利用了電子表格的自動(dòng)填充功能，下拉填充即可。使用時(shí)只需要把從改卷機(jī)導(dǎo)出來的選擇題成績的學(xué)生考號和成績兩列復(fù)制到“選擇題”工作表，無需考慮排序問題，則“原始分輸入”表將自動(dòng)找出對應(yīng)學(xué)生的選擇題分?jǐn)?shù)。

（2）學(xué)生成績單功能

為了方便生成按學(xué)號排序和按成績名次的成績單，要在“原始分輸入”工作表增加幾列并分別用如下公式生成學(xué)號序號：COUNTIFS（A$2：A$911，”<”&A2，$B$2：$B$911，$B2）+1

生成班名序號：COUNTIFS（Q$2：Q$911，”<”&Q2，B$2：B$911，B2）+1

生成級名序號：P2+COUNTIF（P$1：P1，P2）

其中P列為用公式RANK（ ）生成的級排名，之所以還要額外生成級名序號列，目的是要產(chǎn)生唯一的級名，以方便產(chǎn)生按成績高低排序的成績單。

新建“成績打印1”和“成績打印2”兩個(gè)工作表分別用于產(chǎn)生按學(xué)號排序和按成績名次的成績單。在“成績打印1”工作表先用如下公式產(chǎn)生按學(xué)號排序的學(xué)號：

{HLOOKUP（“學(xué)號”，原始分輸入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分輸入！$T$1：$T$911&原始分輸入！$S$1：$S$911，0），F(xiàn)ALSE）}

然后使用如下公式從“原始分輸入”表查找對應(yīng)學(xué)號的成績：

VLOOKUP（$A3，原始分輸入！$A$2：$S$911，COLUMN（D$54），F(xiàn)ALSE）

在“成績打印2”工作表先用如下公式產(chǎn)生按成績名次排序的學(xué)號：

{HLOOKUP（“學(xué)號”，原始分輸入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分輸入！$T$1：$T$911&原始分輸入！$R$1：$R$911，0），F(xiàn)ALSE）}

然后使用如下公式從“原始分輸入”表查找對應(yīng)學(xué)號的成績：

VLOOKUP（$A3，原始分輸入！$A$2：$S$911，COLUMN（D$54），F(xiàn)ALSE）

以上公式均充分利用電子表格自動(dòng)填充功能，下拉或左右拉填充即可。

（3）成績分析功能

用如下公式生成各班均分，下拉填充即可自動(dòng)生成1、2、……班的均分。

AVERAGEIF（原始分輸入！$B$2：$B$912，ROW（$T1），原始分輸入！E$2：E$912）

用如下公式算出各班最高分，下拉填充即可自動(dòng)算出1、2、……班的最高分。

DMAX（原始分輸入！$B$1：$P$555，”總分”，EVALUATE（“A”&17+ROW（$A1）*2&”：A”&18+ROW（$A1）*2））

計(jì)算各班最低分類似

DMIN（原始分輸入！$B$1：$P$555，”總分”，EVALUATE（“A”&17+ROW（$A1）*2&”：A”&18+ROW（$A1）*2））

2.年級總成績匯總分析模塊

各科將成績上報(bào)給級長后只需要運(yùn)行年級總成績匯總分析模塊即可自動(dòng)匯總出總成績，不用任何復(fù)制粘貼操作。

（1）成績匯總功能

為方便分別統(tǒng)計(jì)含借讀生和不含借讀生的成績，設(shè)置“考生范圍”參數(shù)，當(dāng)該參數(shù)為0時(shí)只統(tǒng)計(jì)錄取生的成績，不統(tǒng)計(jì)借讀生的成績;當(dāng)該參數(shù)為1時(shí)統(tǒng)計(jì)所有考生的成績。在成績匯總表用如下公式可以實(shí)現(xiàn)上述成績匯總功能，該公式也充分利用電子表格自動(dòng)填充功能，往下拉和往右拉單元格即可自動(dòng)填充。

IF（OR（考生范圍=1，$AF2=”錄取生”），VLOOKUP（$A2，EVALUATE（“[“&D$1&”.xls]原始分輸入！$A$1：$S$1000”），MATCH（“總分”，EVALUATE（“[“&D$1&”.xls]原始分輸入！$A$1：$S$1”），0），F(xiàn)ALSE），””）

（2）其余功能實(shí)現(xiàn)如前所述，大同小異，不再重復(fù)。

3.歷次考試成績匯總分析模塊

（1）文件命名規(guī)則：為方便利用公式產(chǎn)生歷次成績，需要將歷次成績分別放入文件夾里并依次命名為1、2、3……

（2）使用參數(shù)生成歷次成績表

a、先用如下公式生成學(xué)號和學(xué)生姓名。

IF（INT（（ROW（$A1）-1）/ShowNum）<COUNTIF（學(xué)生基本信息！$A$1：$B$812，顯示班級），OFFSET（學(xué)生基本信息！$A$1，MATCH（顯示班級，學(xué)生基本信息！$B$2：$B$812，0）+INT（（ROW（$A1）-1）/ShowNum），COLUMN（A$2）-1），””）

公式中參數(shù)“ShowNum”為顯示幾次考試數(shù)據(jù)，參數(shù)“顯示班級”為需要顯示的班級，這樣做的目的是盡量減少電子表格的計(jì)算量，提高表格顯示速度。

b、再使用以下公式生成如圖1的歷次成績表。

VLOOKUP（$C2，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全級成績統(tǒng)計(jì).xls]原始分輸入’！$c$1：$ak$1000”），MATCH（D$1，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全級成績統(tǒng)計(jì).xls]原始分輸入’！$c$1：$ak$1”），0），F(xiàn)ALSE）

c、再新建一工作表用以下公式生成帶表頭的個(gè)人歷次成績表

IF（MOD（ROW（$C1），ShowNum+1）=0，C$2，OFFSET（數(shù)據(jù)導(dǎo)入！$A$1，ROW（$C1）-INT（ROW（$C1）/（ShowNum+1）），COLUMN（C$2）-1））

三、結(jié)語

本文闡述了如何巧妙利用WPS電子表格自帶的基本公式和自動(dòng)填充功能構(gòu)建高效易用的成績分析管理系統(tǒng)，該系統(tǒng)易于擴(kuò)展、移植、復(fù)用，不同學(xué)校只需導(dǎo)入本校學(xué)生信息即可使用，所有公式無需更改。教師只要錄入考試分?jǐn)?shù)，所有考試分析數(shù)據(jù)自動(dòng)生成，可直接打印，省時(shí)省力。有別于利用VBA編寫代碼構(gòu)建的系統(tǒng)，該系統(tǒng)構(gòu)建門檻低適合在廣大教育工作者中推廣應(yīng)用。

作者簡介：吳校玉，男（1977.12—），侗族，籍貫 廣西柳州，本科，中級，物理