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

張東倉(cāng)

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

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

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

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

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

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

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

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

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

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

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

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

（1）由于各個(gè)學(xué)校考試的性質(zhì)不同，試卷分析涉及的具體項(xiàng)目亦不同。以我院為例，試卷分析主要涉及以下項(xiàng)目，相對(duì)比較簡(jiǎn)單，所以在該電子表格中創(chuàng)建試卷分析工作表。

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

圖1

圖2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

圖3

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

3 結(jié)束語(yǔ)

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