巧用WPS構建中學高效易用的成績分析管理系統

摘 要：文章首先介紹了電子表格在成績分析管理中的必要性;然后從實際需求出發分析了中學成績分析管理系統的功能特點;最后詳細闡述了如何巧妙利用WPS內置的公式和單元格自動填充實現這些功能的方法，并給出了關鍵公式。

關鍵詞：電子表格 ;成績分析 ;教學管理

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

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

一、成績分析管理系統的功能模塊

中學的成績分析流程如下：

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

2.備課組上報成績給年級長，年級長進行總成績的匯總分析;

3.級長上報年級成績給學校教務處，教務處進行成績存檔。

因此，成績分析管理系統的功能模塊如下：

1.備課組成績分析模塊

該模塊主要功能是：

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

（2）學生成績單功能：分別產生按學號排序和按成績名次排序兩種成績單。

（3）成績分析功能：計算班級平均分、分數段人數、最高分、最低分、優秀率、及格率等統計數據。

（4）其他個性化需求功能：比如產生教師和備課組長要上交到學校存檔的考試質量分析報表。

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

該模塊主要功能是：

（1）成績匯總功能：自動匯總各備課組上報的各科成績，產生成績總表。

（2）學生總成績單功能：分別產生按學號排序和按總成績名次排序的成績單。

（3）總成績分析功能：計算班級平均分、分數段人數、最高分、最低分、優秀率、及格率等統計數據。

（4）上線情況分析功能：上線指的是估算上重點線、本科線的人數，計算各班、各科單上線與雙上線情況，計算各班、各科的貢獻率、命中率、完成率等數據。

（5）其他個性化需求功能：比如產生要上交到學校存檔的考試質量分析報表。

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

該模塊主要功能是：

（1）歷次成績匯總功能：自動匯總歷次考試的成績，產生學年成績總表。

（2）學生個人歷次成績單功能：能自動生成帶表頭的學生個人歷次成績單，方便班主任在家長會、期末向家長發放學生歷次成績單。

（3）歷次成績對比分析功能：能產生任意兩次考試成績的對比分析數據，學生成績的進退一目了然。

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

二、成績分析管理系統功能模塊實現

通常使用電子表格構建的成績分析系統不易于擴展、移植、復用，需求稍微有一點變化，則無法使用，需要重新修改。我們在實現各功能模塊時要充分利用電子表格自動填充的功能盡量使公式可擴展可移植可復用。

由于很多地方反復要使用學生的學號和姓名等信息，因此把學生的基本信息單獨做一個表格供其他地方引用。當重新分班或者學生轉學等發生學生變動情況，只需修改學生基本信息表，其他地方無需修改。

1.備課組成績分析模塊

（1）成績錄入功能

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

HLOOKUP（“總分”，選擇題錄入！$A$1：$Z$997，MATCH（A2，選擇題錄入！$E$1：$E$997，0），FALSE）

該公式利用了電子表格的自動填充功能，下拉填充即可。使用時只需要把從改卷機導出來的選擇題成績的學生考號和成績兩列復制到“選擇題”工作表，無需考慮排序問題，則“原始分輸入”表將自動找出對應學生的選擇題分數。

（2）學生成績單功能

為了方便生成按學號排序和按成績名次的成績單，要在“原始分輸入”工作表增加幾列并分別用如下公式生成學號序號：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（ ）生成的級排名，之所以還要額外生成級名序號列，目的是要產生唯一的級名，以方便產生按成績高低排序的成績單。

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

{HLOOKUP（“學號”，原始分輸入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分輸入！$T$1：$T$911&原始分輸入！$S$1：$S$911，0），FALSE）}

然后使用如下公式從“原始分輸入”表查找對應學號的成績：

VLOOKUP（$A3，原始分輸入！$A$2：$S$911，COLUMN（D$54），FALSE）

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

{HLOOKUP（“學號”，原始分輸入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分輸入！$T$1：$T$911&原始分輸入！$R$1：$R$911，0），FALSE）}

然后使用如下公式從“原始分輸入”表查找對應學號的成績：

VLOOKUP（$A3，原始分輸入！$A$2：$S$911，COLUMN（D$54），FALSE）

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

（3）成績分析功能

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

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

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

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

計算各班最低分類似

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

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

各科將成績上報給級長后只需要運行年級總成績匯總分析模塊即可自動匯總出總成績，不用任何復制粘貼操作。

（1）成績匯總功能

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

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

（2）其余功能實現如前所述，大同小異，不再重復。

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

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

（2）使用參數生成歷次成績表

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

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

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

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

VLOOKUP（$C2，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全級成績統計.xls]原始分輸入’！$c$1：$ak$1000”），MATCH（D$1，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全級成績統計.xls]原始分輸入’！$c$1：$ak$1”），0），FALSE）

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

IF（MOD（ROW（$C1），ShowNum+1）=0，C$2，OFFSET（數據導入！$A$1，ROW（$C1）-INT（ROW（$C1）/（ShowNum+1）），COLUMN（C$2）-1））

三、結語

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

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