利用excel 輕松實現記分冊與試卷分析聯動

張東倉

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

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

1 記分冊的設置與編寫

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

（2）設置好該表格的頁面，便于后面成績處理完畢時打印。考慮到我院實際，記分冊每面20 人，兩面40 人，超過40 人，則在此表格的下方繼續復制即可。

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

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

（5）接下來，在工作表的g2 單元格輸入本學期作業的最高次數，將鼠標分別移到r2、g24、r24 單元，在編輯蘭均輸入“=$G$2”。在c3:c22、c25:c44、n3:n22、n25:n34 各列輸入作業次數?？记?、課堂表現、平時測試三個子項均按百分制輸入分數。以1號學生的平時成績為例，將鼠標移到h3 在編輯蘭輸入“=(C3/$G$2*100*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”，同樣設置其余學生對應的平時成績，（當然，如果作業成績直接就是百分制，則可以將鼠標移到h3 單元格，修改編輯蘭為“=(C3*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”）。

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

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

注意：如果學生休學或轉專業則平時成績可空缺。

2 試卷成績工作表的設置與編寫

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

（2）以上只是我院試卷分析的前兩項，后兩項主要是分析教學中存在的問題和經驗教訓以及教學建議，這里并未顯示。根據上面的表頭的要求，自己設計了下面的試卷成績模版，如圖1。

圖1

圖2

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

其中對應的各單元格的設置如下：

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

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

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

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

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

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

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

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

L80 設置為“=D79+E79+F79”，計算及格率；

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

B82 設置為“=MAX(B7:B76)”，C82:K82 做類似的設置，統計每題考試最高分數；

B83 設置為“=COUNTIF(B7:B76，B82)”，C83:K83 做類似的設置，統計每題最高分人數；

B84 設置為“=MIN(B7:B76)”，C84:K84 做類似的設置，統計每題考試最低分數；

B85 設置為“=COUNTIF(B7:B76，B84)”，C85:K85 做類似的設置，統計每題最低分人數；

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

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

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

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

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

圖3

這時即可得到相應的記分冊，當然，記分冊的封面亦可以通過單元格引用進行自動填寫。

3 結束語

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