巧用WPS構(gòu)建中學(xué)高效易用的成績(jī)分析管理系統(tǒng)

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

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

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

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

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

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

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

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

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

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

1.備課組成績(jī)分析模塊

該模塊主要功能是：

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

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

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

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

2.年級(jí)總成績(jī)匯總分析模塊

該模塊主要功能是：

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

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

（3）總成績(jī)分析功能：計(jì)算班級(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.歷次考試成績(jī)匯總分析模塊

該模塊主要功能是：

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

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

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

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

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

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

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

1.備課組成績(jī)分析模塊

（1）成績(jī)錄入功能

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

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

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

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

生成級(jí)名序號(hào)：P2+COUNTIF（P$1：P1，P2）

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

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

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

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

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

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

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

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

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

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

（3）成績(jī)分析功能

用如下公式生成各班均分，下拉填充即可自動(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.年級(jí)總成績(jī)匯總分析模塊

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

（1）成績(jī)匯總功能

為方便分別統(tǒng)計(jì)含借讀生和不含借讀生的成績(jī)，設(shè)置“考生范圍”參數(shù)，當(dāng)該參數(shù)為0時(shí)只統(tǒng)計(jì)錄取生的成績(jī)，不統(tǒng)計(jì)借讀生的成績(jī);當(dāng)該參數(shù)為1時(shí)統(tǒng)計(jì)所有考生的成績(jī)。在成績(jī)匯總表用如下公式可以實(shí)現(xiàn)上述成績(jī)匯總功能，該公式也充分利用電子表格自動(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.歷次考試成績(jī)匯總分析模塊

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

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

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

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

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

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

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

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

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)建高效易用的成績(jī)分析管理系統(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)用。

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