利用excel 輕松實(shí)現(xiàn)記分冊與試卷分析聯(lián)動

張東倉

臨近期末，各類學(xué)校需要對教學(xué)效果做出評定，需要給學(xué)生一學(xué)期各個科目的學(xué)習(xí)給出一個評定，記分冊和試卷分析就是這一方面的重要教學(xué)資料，也是反映教學(xué)水平和指引教學(xué)改革的重要參照。購買這一方面的軟件不一定適合本學(xué)校的具體情況，也不便于對其進(jìn)行其它方面功能的拓展。能不能制作一個既方便廣大教師平時操作，又適合學(xué)校的具體情況，也便于后續(xù)維護(hù)和拓展的這一方面軟件或模版呢？

考慮到我院廣大教師對辦公軟件一般都比較熟悉，excel 又具有強(qiáng)大的計(jì)算和數(shù)據(jù)處理能力。筆者利用空閑時間，結(jié)合我院的實(shí)際，2012年制作了一個試卷分析模版，受到廣大教師的好評，今年由于教務(wù)對學(xué)生期末總評成績的計(jì)算辦法作出了調(diào)整：考察課、考試課等記分冊中平時占40%，且包含四個子項(xiàng)：作業(yè)（占15%）、考勤（占5%）、課堂表現(xiàn)（10%）、平時測試（占10%），期末占60%，且總評成績要求考察課五檔制：優(yōu)秀、良好、中等、及格、不及格。每個教師大都有多個任課班級，每班人數(shù)都在四五十人左右，這樣數(shù)據(jù)處理的工作量很大，如何利用excel，結(jié)合以前的試卷分析實(shí)現(xiàn)同時處理數(shù)據(jù)，就顯得尤為必要。經(jīng)過筆者嘗試，結(jié)合本學(xué)期期末自己的使用，自己編寫的聯(lián)動模版覺得很方便，現(xiàn)與大家進(jìn)行分享。

1 記分冊的設(shè)置與編寫

（1）首先，平時成績在教學(xué)接近尾聲時就應(yīng)處理好。因此，在excel 中制作記分冊表格，由于g2 單元格未輸入作業(yè)最高次數(shù)，m，j，k，s，u，v 等列數(shù)據(jù)均顯示“#DIV/0!”，一旦輸入次數(shù)，則會顯示相應(yīng)的數(shù)據(jù)。

（2）設(shè)置好該表格的頁面，便于后面成績處理完畢時打印?？紤]到我院實(shí)際，記分冊每面20 人，兩面40 人，超過40 人，則在此表格的下方繼續(xù)復(fù)制即可。

（3）默認(rèn)每班不超過70 人，如超過，可對記分冊模版和試卷分析模版同時修改，擴(kuò)充人數(shù)即可。設(shè)置好記分冊頁面及表頭后，準(zhǔn)備學(xué)生名單。由于我院教務(wù)已在學(xué)籍管理系統(tǒng)輸入了任課班級的學(xué)生名單，只需要導(dǎo)入即可，為了不對記分冊造成干擾，在該電子表格下方，新建班級名單工作表.在將學(xué)籍系統(tǒng)中該班的學(xué)生名單直接粘貼至C 列。

（4）再在上面的工作表中的b3：b22 單元格做如下設(shè)置：將鼠標(biāo)移到b3 單元格，在編輯蘭輸入“= 班級名單!$C$1”，將鼠標(biāo)移到b4 單元格，在編輯蘭輸入“= 班級名單!$C$2”，……，同樣依次設(shè)置b5:b22，以及m3:m22，b25:b44，m25:m34（注意：模版默認(rèn)人數(shù)是70 人，班級名單每20 人占記分冊一面）。這樣，班級名單將快速進(jìn)入記分冊。

（5）接下來，在工作表的g2 單元格輸入本學(xué)期作業(yè)的最高次數(shù)，將鼠標(biāo)分別移到r2、g24、r24 單元，在編輯蘭均輸入“=$G$2”。在c3:c22、c25:c44、n3:n22、n25:n34 各列輸入作業(yè)次數(shù)。考勤、課堂表現(xiàn)、平時測試三個子項(xiàng)均按百分制輸入分?jǐn)?shù)。以1號學(xué)生的平時成績?yōu)槔瑢⑹髽?biāo)移到h3 在編輯蘭輸入“=(C3/$G$2*100*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”，同樣設(shè)置其余學(xué)生對應(yīng)的平時成績，（當(dāng)然，如果作業(yè)成績直接就是百分制，則可以將鼠標(biāo)移到h3 單元格，修改編輯蘭為“=(C3*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”）。

注意：此時期末成績由于還未進(jìn)行試卷分析，暫時顯示“0”。

（6）其次，設(shè)置總評成績，即j 列，以1 號學(xué)生為例，將鼠標(biāo)移到j(luò)3 單元格，在編輯蘭輸入“=H3*40%+I3*60%”，則總評成績此時顯示的期末為零分時的成績。將鼠標(biāo)移到i3 單元格，在編輯蘭輸入“= 試卷成績!L7”，等待試卷分析工作表期末數(shù)據(jù)自動調(diào)入；如果是五檔制，則需要設(shè)置k，v 兩列。以1 號學(xué)生為例，將鼠標(biāo)移到k3 單元格，在編輯蘭輸入“=IF(J3<60，"不及格"，IF(J3<=69，"及格"，IF(J3<=79，"中等"，IF(J3<=89，"良好"，"優(yōu)秀"))))”，則1 號學(xué)生的總評成績自動轉(zhuǎn)換成五檔制，只是現(xiàn)在還沒有進(jìn)行試卷分析，期末成績還未傳輸過來。依照i3、j3、k3單元格的設(shè)置，分別設(shè)置其它學(xué)生期末成績，總評成績、等次等對應(yīng)的單元格。

注意：如果學(xué)生休學(xué)或轉(zhuǎn)專業(yè)則平時成績可空缺。

2 試卷成績工作表的設(shè)置與編寫

（1）由于各個學(xué)?？荚嚨男再|(zhì)不同，試卷分析涉及的具體項(xiàng)目亦不同。以我院為例，試卷分析主要涉及以下項(xiàng)目，相對比較簡單，所以在該電子表格中創(chuàng)建試卷分析工作表。

（2）以上只是我院試卷分析的前兩項(xiàng)，后兩項(xiàng)主要是分析教學(xué)中存在的問題和經(jīng)驗(yàn)教訓(xùn)以及教學(xué)建議，這里并未顯示。根據(jù)上面的表頭的要求，自己設(shè)計(jì)了下面的試卷成績模版，如圖1。

圖1

圖2

其中g(shù)1、i1、k1、f4、h4、j4、l4 七個單元格等待輸入卷面信息。將鼠標(biāo)移到a7 單元格，在編輯蘭輸入“= 記分冊!$B$3”，則從記分冊工作表調(diào)取1 號學(xué)生的姓名，以此類推，設(shè)置剩余的a8:a76 單元格，這時考試班級的學(xué)生姓名將陸續(xù)調(diào)入該模版，將鼠標(biāo)移到b4 單元格，在編輯蘭輸入“=COUNTIF($A$7:$A$76，"<>0")”，則模版自動統(tǒng)計(jì)本班人數(shù)，這時輸入表頭信息，將d4 單元格設(shè)置為“=70- COUNTIF($B$7:$B$76，"")”，計(jì)算實(shí)際參加考試的人數(shù)，默認(rèn)的是缺考學(xué)生第一題得分為空，即不輸入成績，將鼠標(biāo)移到對應(yīng)的總分輸入“缺考”字樣。默認(rèn)考試共十道大題，修改模版進(jìn)行超過可追加。其中b6:k6 為每題的滿分，并將l6 單元格做如下設(shè)置：“=SUM(B6:K6)”，計(jì)算試卷總分。默認(rèn)是100 分，當(dāng)然可以設(shè)置的。然后，以1 號學(xué)生第一題得分為例，對b7 單元格作如下設(shè)置這樣設(shè)置的目的是防止教師在輸入每個學(xué)生的各題得分時超出該題滿分或?yàn)樨?fù)分，以此類推，設(shè)置1 號學(xué)生其它單元格對應(yīng)的各題得分，并在l7 單元格設(shè)置“=SUM(B7:K7)”，計(jì)算該學(xué)生此次考試的總分，最后對其它學(xué)生各題得分做類似的設(shè)置。為了得到試卷分析的表頭，在該工作表點(diǎn)的下方作了如下設(shè)計(jì)，如圖2。

其中對應(yīng)的各單元格的設(shè)置如下：

B78 設(shè)置為“=COUNTIF($L$7:$L$76，">0")- COUNTIF($L$7:$L$76，">39")”，計(jì)算成績在039 之間人數(shù)；

C78 設(shè)置為“=COUNTIF($L$7:$L$76，">=40")- COUNTIF($L$7:$L$76，">59")”；，計(jì)算成績在4059 人數(shù)；

D78 設(shè)置為“=COUNTIF($L$7:$L$76，">=60")- COUNTIF($L$7:$L$76，">79")”，計(jì)算成績在6079 人數(shù)；

E78 設(shè)置為“=COUNTIF($L$7:$L$76，">=80")- COUNTIF($L$7:$L$76，">89")”，計(jì)算成績在8089 人數(shù)；

F78 設(shè)置為“=COUNTIF($L$7:$L$76，">=90")- COUNTIF($L$7:$L$76，">100")”，計(jì)算成績在90100 人數(shù)；

L78 設(shè)置為“=SUM(L7:INDEX(L:L，D4+6))/D4”計(jì)算全班平均分，缺考考生不參與平均；

B79 設(shè)置為“=B78/INDIRECT("D"&4)”，C79:F79 做類似的設(shè)置，計(jì)算成績各檔百分比；

B80 設(shè)置為“=AVERAGE(B7:B76)”，C80:K80 做類似的設(shè)置，計(jì)算各題平均分；

L80 設(shè)置為“=D79+E79+F79”，計(jì)算及格率；

B81 設(shè)置為“=(B6- B80)/B6”，C81:K81 做類似的設(shè)置，計(jì)算各題失分率；

B82 設(shè)置為“=MAX(B7:B76)”，C82:K82 做類似的設(shè)置，統(tǒng)計(jì)每題考試最高分?jǐn)?shù)；

B83 設(shè)置為“=COUNTIF(B7:B76，B82)”，C83:K83 做類似的設(shè)置，統(tǒng)計(jì)每題最高分人數(shù)；

B84 設(shè)置為“=MIN(B7:B76)”，C84:K84 做類似的設(shè)置，統(tǒng)計(jì)每題考試最低分?jǐn)?shù)；

B85 設(shè)置為“=COUNTIF(B7:B76，B84)”，C85:K85 做類似的設(shè)置，統(tǒng)計(jì)每題最低分人數(shù)；

B86 設(shè)置為“=ROW(INDIRECT("A"&MATCH(試卷成績!B84，試卷成績!B7:B76，0)+6))- 6&"號"”，C86:K86 做類似的設(shè)置，統(tǒng)計(jì)每題最低分學(xué)生學(xué)號；（只要一個學(xué)生學(xué)號）

B87:K87 輸入考試題型；L82 設(shè)置為“=MAX(L7:L76)”，統(tǒng)計(jì)最高分；

L83 設(shè)置為“=INDIRECT("A"&MATCH(L82，L7:L76，0)+6)”，查找最高分學(xué)生姓名；

L85 設(shè)置為“=MIN(L7:INDEX(L:L，B4+6))”，統(tǒng)計(jì)最低分；L86 設(shè)置為“=INDIRECT("A"&MATCH(L85，L7:L76，0)+6)”，查找最低分學(xué)生姓名；

（3）試卷分析表的中單元格的設(shè)置。調(diào)取試卷成績工作表中的相應(yīng)數(shù)據(jù)填寫到試卷分析表的中相應(yīng)的單元格。以試卷分析表中全班人數(shù)為例，將鼠標(biāo)移到試卷分析表工作表的A11（A11 與A12 合并）單元格，在編輯蘭輸入“= 試卷成績!B4”，以此類推，將試卷分析表中的所有數(shù)據(jù)從試卷成績工作表中調(diào)取即可。最終完成的試卷分析表格如圖3：（考慮到個人隱私，對部分?jǐn)?shù)據(jù)進(jìn)行了處理）。

圖3

這時即可得到相應(yīng)的記分冊，當(dāng)然，記分冊的封面亦可以通過單元格引用進(jìn)行自動填寫。

3 結(jié)束語

以上試卷分析和記分冊模版能夠?qū)崿F(xiàn)快速的數(shù)據(jù)調(diào)用，提高了辦公的高效率，為任課教師節(jié)約了大量時間，而且數(shù)據(jù)運(yùn)算精度很高，能夠使廣大教師的從期末緊張的教學(xué)任務(wù)中解脫出來，真正實(shí)現(xiàn)信息化了辦公。希望筆者的模版能給廣大教師期末閱卷工作起到拋磚引玉的效果，不妥之處請批評指正。