excel試卷分析系統：基於Excel的自動閱卷評分系統
基於Excel的自動閱卷評分系統
xxx
雅安職業技術學院 四川 雅安 625000
摘要：在教學中，教師一直在尋找一種環保、快速的考核評價方式來考察學生的成績，但由於對程式設計的恐懼，只好選擇傳統的紙質考試方式。用Excel來製作自動閱卷評分系統，能大大的節約教師的閱卷、評分、統分時間，有效地提高工作效率和準確率。
關鍵字：計算機應用技術； Excel函式； 自動閱卷 Auto scoring system based on Excel
Luo Cuiqiong
Ya'an Vocational College Sichuan Ya'an 625000
Abstract: in the process of teaching, teachers are always looking for an environmental protection, rapid evaluation method to examine student achievement, but due to the programming of the fear, had to choose the traditional paper examination. To make use of Excel automatic marking system, can save teachers greatly marking, score, the time, effectively improve the work efficiency and accuracy. Keywords: computer application technology; Excel function; automatic marking
1 設計理由
作為一位高職院校的公共課教師，在每學期期末面對閱卷、分數統計、成績分析時的龐大工作量，很多老師都感覺是一項既浪費時間又消耗腦力的工作，不僅工作效率低而且容易出錯。運用電子表格把傳統紙質考試的閱卷、統計分數和成績分析變成可以自動閱卷、統計分數和成績分析的方式，可以大大減輕老師們的工作量，從而極大的提高工作效率和準確率；同時也可以大大減輕學校教務處的印刷工作量，節約大量紙張，這樣既經濟又環保，還可以省掉保管試卷的檔案室和相應的精力，降低辦學成本。
2 系統設計思路
在Excel中製作好試卷，在指定單元格輸入學生基本資訊、作答情況，系統透過IF函式讀取標準答案並判斷正誤和計分，學生試卷儲存後交給老師，最後在成績統計檔案中讀出所有學生得分或答題情況，計算總分、平均分，並可做成績分析。
在設計自動閱卷評分系統的時候，在同一個Excel工作薄中新建兩張工作表，一張工作表按照試卷的格式設計試卷，學生可以在試卷上答題，但不能改動試題；另一張工作表用來自動判斷、統計學生答題的情況和分數、統計總分，這張工作表應隱藏，不能讓學生看見。
2.1 試卷設計
（1）開啟Excel文件，建立“試卷”工作表，合併A1：G1單元格，在A1單元格中輸入試卷名稱“雅安職業技術學院《計算機應用基礎》普通專科公共必修課2013年期末測試試卷”並居中。在A2單元格輸入“班級”，D2單元格輸入“學號”，F2單元格輸入“姓名” ( 合併B2：C2單元格，將B2，E2，G2單元格設定成藍色底紋。)。在A3：G6單元格區域設定。
（2）填空題的設計，合併A7：G7，在單元格中輸入“一、填空題（每空1分，共20分，請將正確答案填寫在相應小題下一行紅色文字“答題處”所在單元格內）”，合併A8：G8，在單元格中輸入題目，例如：“1、在WINDOWS XP中，若系統長時間不響應使用者的要求，為了結束該任務，應使用的快捷鍵組合是CTRL+ALT+（ ）。”在A9單元格中輸入“答題”，並將單元格設定成藍色底紋，按此方式設計10題。
（3）選擇題的設計，合併A29：G29，在單元格中輸入“二、選擇題（單選題，每題2分，共50分。請在每小題右邊的“選擇答案處”內選擇正確答案對應的大寫字母）”合併A31：G31，在單元格中輸入題目，例如：“2. 要在Excel2003單元格中輸入任何分數並保留輸入時的數字和數位，方法是：。”合併A32：C32，D32：G32，A33：C33，D33：G33，分別在這四個單元格中輸入A、B、C、D四個選項。單擊H列設定資料有效性，開啟資料有效性對話方塊，在“設定”選項卡中，有效性條件允許中選擇“序列”，來源中輸入“選擇答案處,A,B,C,D”（不含引號且其中逗號為半形格式），勾選“提供下拉箭頭”，單擊“確定”按鈕完成設定。按此方法共設計25個選擇題。
2.2 評分表的設計
（1）在同一個工作薄中建一個工作表命名為“評分表”，合併A1：F1單元格，在單元格中輸入“雅安職業技術學院公共必修課《計算機應用基礎》評分表”，分別在A2，E2單元格中輸入“學號”、“姓名”，為了在評分表中能夠自動獲取考生在試卷中填寫的資訊，在B2單元格中輸入“=試卷!E4”，在F2單元格中輸入“=試卷!G4”。然後在A3：F4單元格區域中設定一個得分統計表，用於統計各題的分數和總分。
（2）填空題得分表設計，由於每一道題中需要考生作答的空不一樣，有的題可能只有一個空需要作答，有的`題需要作答的空不止一個，這就要求我們在設計填空題得分表的時候需要考慮到有多個空需要作答的情況，因此在設計得分表時，按照最多空數來設計表格。合併A5：F5單元格，在單元格中輸入“一、填空題（每空1分，共20分）”並設定該單元格格式為字型：仿宋、字號：16號、對齊方式：左端對齊。在A6單元格中輸入“題號”，在B6：F6中分別輸入“分值一、分值二、分值三、分值四、分值五”，在A7：A21中填充序列1到15。
（3）選擇題得分表設計，選擇題相對於填空題來說就非常簡單了，每道題只有A、B、C、D四個選項，其中只有一個正確答案，選擇正確得2分，不正確就得0分。
3 自動閱卷評分的實現
3.1 填空題
由於填空題的答案有的不是唯一的，有的順序是可以調換的，因此在評分設計上需要綜合考慮到各種因素的存在。這裡以第一個題目的設計為例進行詳細的介紹。在“評分表”B7單元格中輸入：“=IF(OR(試卷!A9="DELETE",試卷!A9="",試卷!A9="Delete",試卷!A9="DElete",試卷!A9="DELete",試卷!A9="DELEte",試卷!A9="DELETe",試卷!A9="deletE",試卷!A9="deleTE",試卷!A9="deletE",試卷!A9="delETE",試卷!A9="deLRTE",試卷!A9="dELETE"),1,0)”，應用了IF函式和OR函式的巢狀，意思是隻要“試卷”工作表中A9單元格中的答案與他們中的其中一個相同，“評分表”工作表中B7單元格中的值就為“1”，否則為“0”。
3.2 選擇題
在選擇題的評分設計上，為了能夠適用於不同的題目。我們可以透過如下幾步來完成：
（1）在“評分表”工作表中任意的空白單元格區域設計一個“標準答案表”，我們在H16：I42單元格區域設定“標準答案表”。
（2）在“評分表”B18單元格中輸入簡單的IF函式“=if(試卷！H32=I18，2，0)”，意思是如果“試卷”工作表中H32單元格的內容與“評分表”I18單元格中內容相同，“評分表”B18單元格的值顯示為“2”，否則顯示為“0”。同理完成其他選擇題的評分。
3.3 成績的統計與顯示
B4單元格用於統計顯示填空題的分值，我們在B4單元格中輸：“=SUM(B7:F14)”，該函式求和的結果就是填空題所得的分值。C4單元格用於統計顯示選擇題的分值，與填空題的統計方式相同，在C4單元格中輸入：“=SUM(B18:B30,D18:D30,E18:E30)”。接下來就是要算出總分，在E4單元格中輸入：“=SUM(B4:D4)”。這樣就完成了試卷的自動閱卷和自動統分，極大的提高了資料處理的效率。
4 許可權的設定
學生對試卷只有答題的許可權，對工作表沒有修改和設定的許可權，還不能讓學生看到“評分表”，因此需要對工作表進行保護設定。
（1）將“評分表”工作表中不需要學生填寫作答的所有單元格選中，點選滑鼠右鍵開啟“設定單元格格式”對話方塊，選擇“保護”選項卡，勾選“鎖定”，然後確定。
（2）在選單欄中單擊“審閱”，單擊“保護工作表”開啟對話方塊，設定保護密碼和所有使用者的操作許可權。
（3）首先選擇“評分表”工作表單擊滑鼠右鍵，單擊“隱藏”，然後，在選單欄中單擊“審閱”，單擊“保護工作薄”選擇“保護結構和窗體”，開啟“保護結構和窗體”對話方塊，勾選“結構”和“窗體”，輸入密碼。
在本案例中只對填空題和選擇題做了說明，當然對於判斷題、簡答題等題型也可以進行相應的設定。對簡答題的評分可以採用模糊數學中的單項貼近度作為一個度量依據，有效的減少誤判，以答案中的關鍵字、核心字詞句作為參考標準來判斷。
5 結束語
考試是教學評價的一個非常重要的組成部分，是一種非常重要的教學檢驗手段，主要是為了考察教師的教學質量和學生的學習效果。基於Excel的自動閱卷評分系統與傳統的考試方式相比，節約了教育資源，減少了人力、物力、財力的消耗；實現了無紙化考試，自動閱卷評分，減輕老師負擔。本系統在設計中只用到了一些簡單的常用函式，整體設計很簡單。最後，希望本文能夠為大家在探索自動閱卷評分系統以及Excel的功能等起到一點點拋磚引玉的作用。
參考文獻
[1]賁黎明.Excel操作題自動閱卷系統的設計與實現[J].常熟理工學院學報，2008年08期
[2]劉玉英.Excel公式和函式在成績統計表中的應用[J].科技論壇，2008
[3]陳京.淺談如何利用Excel提高教學管理的辦公效率[J].中央民族大學學報，2005.03
【excel試卷分析系統：基於Excel的自動閱卷評分系統】相關文章：