用EXCEL制作模擬考試系統(tǒng)
2018-01-31 6頁 5.0分
1.建立題庫
打開Excel 2010，建立“模擬考試系統(tǒng)-出題測試版”。打開此工作簿，建立“單選題”工作表(如圖1)，在A2輸入公式=ROW()-1&"."以自動生成序號，在D2輸入=IF(C2="","",RAND())，選中A2:D2拖動向下填充幾百行。按住Ctrl鍵單擊 “單選題”工作表標簽拖動復制出兩份，分別重命名為“多選題”（如圖2）、“判斷題”（如圖3），分別輸入題目和答案，按Alt+回車鍵換行。請注意以下提示：
1、以上3個工作表，在B列輸入單選題題目（樣式隨意，但一道題只能占一個單元格），在C列輸入對應考題的答案，答案輸入時注意不要有多余字符。
2、“多選題”工作表的答案必須按ABCD的順序填寫，例如答案ABD，不能寫ADB、BAD、BDA、DAB、DBA，答錯、少答和多答均不得分；“判斷題”工作表的答案只能有兩種，本題按“正確”和“錯誤”作為答案，其他形式如“對”和“錯”、“√”和“×”也可以。
3、題庫中題目數(shù)量根據(jù)實際情況而定，多多益善。本例中題庫數(shù)目為單選題60道、多選題50道、判斷題45道。
?
圖1
?
圖2
?
圖3
2.建立考生名單
新建“考生名單”工作表，在A、B、C列分別輸入序號、準考證號、考生姓名，選中B列在名稱框中輸入“考號”，回車確定把B列命名為考號(如圖4)。完成后可在名稱管理器內(nèi)看到名字為“考號”的名稱，引用位置為“考生名單”工作表的B列。
?
圖4
3.設計試卷
建立“試卷”工作表，按需要設置試卷，序號生成方式如題庫序號。單選題30題(A5:C34)，多選題40(A36:C75)，判斷題30題(A77:C106)。在B4輸入“單選題（每題1分）”，在B35輸入“多選題（每題1分，錯、漏、多選均不得分）”，在B76輸入“判斷題（每題1分）”。
用“自選圖形/基本形狀”中的棱臺畫一個按鈕，選中在名稱框中輸入“出題按鈕”4字回車，把它命名為“出題按鈕”。再右擊此按鈕選擇“添加文字”，在按鈕上輸入“出題”2字。同樣再畫一個按鈕，把它命名為“評分按鈕”，添加文字“評分”。(如圖5)
?
圖5
4.顯示題目（如圖6）
在B5輸入公式=INDIRECT("單選題!R"&ROW()-3&"C"&COLUMN(),0)，復制到B5:C34（可拖動A5填充）。
在B36輸入公式=INDIRECT("多選題!R"&ROW()-34&"C"&COLUMN(),0)并復制到B36:C75。
在B77輸入公式=INDIRECT("判斷題!R"&ROW()-75&"C"&COLUMN(),0)并復制到B77:C106。
在E5單元格輸入公式=IF(D5="",0,IF(D5=C5,$G$2,0))，向下填充到E34，這公式用來判斷如果D5中的答案與C4的標準答案一致就得1分否則為0分，其中G2單元格內(nèi)數(shù)值為1。同理，在E36單元格輸入公式=IF(D36="",0,IF(D36=C36,$H$2,0))，向下填充到E75；在E77單元格輸入公式=IF(D77="",0,IF(D77=C77,$I$2,0))，向下填充到E106?？赏ㄟ^更改G2、H2、I2單元格的數(shù)值來更改分值。
B5的公式表示對單選題工作表中同列、行數(shù)差3的單元格(B2)的引用，公式中使用了R1C1格式的單元格編號，其中3、34、75是由三種題型的第一題所在的行號減2得來。這公式實現(xiàn)的是絕對位置引用，即不管在單選題工作表中進行插入、刪除行或排序，B5顯示的始終都是你從單選題B2中直接看到的內(nèi)容。因此B5:C34、B36:C75、B77:C106三個區(qū)域將分別與相應題庫中最前面的30(或40)題的內(nèi)容保持一致。
?
圖6
5.答案限制
選中單選題答題區(qū)D5:D34單擊“數(shù)據(jù)/有效性”（快捷鍵ALT+D→L），在“允許”中選擇“序列”，選中“提供下拉箭頭”復選項，在“來源”中輸入“A,B,C,D”，不含引號且其中逗號為半角格式(如圖7)。切換到“出錯警告”選項卡，輸入錯誤信息為“只能輸入A、B、C、D中的一項，其他任何符號或空格均為非法?！保x中“輸入無效數(shù)據(jù)時顯示警告”復選項，選擇樣式為“停止”(如圖8)，確定完成設置。
?
圖7
?
圖8
同樣對多選題答題區(qū)D36:D75，判斷題答題區(qū)D77:D106設置數(shù)據(jù)有效性，只是“來源”要分別換成“A,B,C,D,B,AC,AD,BC,BD,CD,ABC,ACD,BCD,ABCD”和“正確,錯誤”。 多選題的“出錯警告”一定要改成“ 必須按ABCD的順序輸入多選項，否則無法正確評分”。（如圖9到如圖11）
?
圖9
?
圖10
?
圖11
6.準考證號驗證
選中“試卷”工作表中要輸入準考證號的D2單元格，單擊“數(shù)據(jù)/有效性”，在“允許”中選擇“系列”，取消“提供下拉箭頭”復選項，在“來源”中輸入=INDIRECT("考號")（如圖12）。切換到“出錯警告”選項卡，輸入錯誤信息為“你輸入的準考證號并不存在，請重新輸入”，選中“輸入無效數(shù)據(jù)時顯示警告”，并設置樣式為“停止”（如圖13）。
?
圖12
?
圖13
在F2單元格輸入公式=IF(D2="","",VLOOKUP(D2,考生名單! $B:$C,2,FALSE))，在D2單元格輸入準考證號后會自動顯示考生姓名。（如圖14）
?
圖14
7.隨機抽題
右擊“出題”按鈕，選擇“指定宏”，在彈出窗口中，輸入宏名為“出題”，位置選擇當前工作簿，單擊“新建”按鈕，復制如下代碼粘貼到框內(nèi)，結果如圖15，保存時將文件另存為啟用宏的.xlsm格式。
Sub 出題()
If Range("D2").Value = 0 Then
提示 = MsgBox("請先輸入準考證號")
GoTo 重來
End If
'如果D2為空則彈出提示要求輸入準考證號并直接跳到后面的"重來:"處。
Sheets("單選題").Range("A:D").Sort Key1:=Sheets("單選題").Range("D2"), Order1:=xlAscending, Header:=xlGuess
Sheets("多選題").Range("A:D").Sort Key1:=Sheets("多選題").Range("D2"), Order1:=xlAscending, Header:=xlGuess
Sheets("判斷題").Range("A:D").Sort Key1:=Sheets("判斷題").Range("D2"), Order1:=xlAscending, Header:=xlGuess
'這三行分別是對單選、多選和判斷題工作表的A:D區(qū)域按D列隨機數(shù)升序排序。
Columns("B:B").Rows.AutoFit
'對B列設置最適合行高，以完整顯示題目。
Range("D5").Select
'定位到D5以便輸入答案
ActiveSheet.Shapes("出題按鈕").Visible = False
'隱藏出題按鈕以防重復選題
ActiveSheet.Shapes("評分按鈕").Visible = True
'顯示評分按鈕
重來:
End Sub
?
圖15
試卷中的題目總是顯示各題庫最前面的N題，而在此對各題庫按隨機數(shù)進行隨機排序后最前面的N題自然是隨機的，也就相當于是隨機抽題了。
8.成績記錄表
新建 “成績記錄”工作表(如圖16)，在A2輸入公式=NOW()，并選中A列右擊從“設置單元格格式”中設置為日期格式以顯示當前時間。在B2輸入公式=試卷!D2和C2輸入公式=試卷!F2以顯示相應數(shù)據(jù)。D2輸入公式=SUM(E2:G2)對后面三個得分進行合計，E2、F2、G2則分別輸入公式=SUM(試卷!E5:E34)、=SUM(試卷!E36:E75)、=SUM(試卷!E77:E106)分別對相應題型得分進行合計。
?
圖16
9.評分和記錄保存
右擊“評分”按鈕，選擇“指定宏”，輸入宏名“評分”，位置選擇當前工作簿，單擊“新建”按鈕，復制如下代碼粘貼到框內(nèi)，結果如圖17:
Sub 評分()
Sheets("成績記錄").Rows("3:3").Insert Shift:=xlDown
'在成績記錄工作表第3行前插入一行
Sheets("成績記錄").Rows("2:2").Copy
'復制成績記錄工作表第2行
Sheets("成績記錄").Range("A3").PasteSpecial Paste:=xlPasteValues
'以數(shù)值方式粘貼到成績記錄工作表A3
得分 = MsgBox("最終得分" & Range("成績記錄!D2").Value & " 其中:單選題" & Range("成績記錄!E2").Value & "、多選題" & Range("成績記錄!F2").Value & "、判斷題" & Range("成績記錄!G2").Value, , "評分結果")
'彈出信息框顯示成績記錄!D2、E2、F2、G2的數(shù)據(jù)，標題為"評分結果"
Range("D2,D5:D34,D36:D75,D77:D106").ClearContents
'清除試卷工作表中輸入的內(nèi)容
Sheets("試卷").Rows("3:500").RowHeight = 0
Sheets("成績記錄").Rows("2:2").RowHeight = 0
'設置題目區(qū)行高為0，以隱藏所有題目
ActiveSheet.Shapes("出題按鈕").Visible = True
ActiveSheet.Shapes("評分按鈕").Visible = False
'顯示出題按鈕,隱藏評分按鈕
ActiveWorkbook.Save
'保存
End Sub
?
圖17
10.其他設置
選中A4單擊“窗口/凍結空格”以始終顯示1:3行的標題和按鈕。右擊C、E列選擇隱藏。為答題區(qū)以外單元格設置填充色以示區(qū)別，把評分按鈕重疊到出題按鈕上(如圖18)。
?
圖18
選中D5:D34右擊選擇“設置單元格格式”，在“保護”選項卡中單擊取消“鎖定”選項，對于同樣需要添寫的D2,D36:D75,D77:D106區(qū)也同樣取消鎖定設置（如圖19）。然后單擊“工具/保護/保護工作表”，在中間“允許此工作表所有用戶進行”列表中只選中“選定未鎖定單元格”和“設置行格式”兩個選項，輸入密碼(如圖20)，單擊確定把工作表保護起來，這樣考生就無法修改試題等相應單元格了。單擊評分以隱藏所有題目，同時顯示出題按鈕，把文件另存為“模擬考試系統(tǒng)-考生測試版.xlsm”，退出。
?
如圖19
?
如圖20
系統(tǒng)維護
1.隱藏工作表
選中除“試卷”工作表除外的其他工作表，右擊選擇隱藏，以免考生看到。（如圖21）
?
圖21
2.保護VBA代碼
右擊 “試卷”工作表，選擇“查看代碼”，選中VBAProject(模擬考試系統(tǒng)-考生測試版.xlsm)，右擊，選擇“VBAProject 屬性”，在“保護”選項卡中選中“查看時鎖定工程”，并輸入密碼(如圖22)，確定，保存文檔后退出。關閉文檔后再重新打開，查看代碼需輸入密碼，這樣任何人沒有密碼就別想修改這些代碼了。
?
圖22
3.保護工作簿
為防止考生修改，在“審閱”選項卡中選中“保護工作簿”，勾選“結構”和“窗口”選項，輸入密碼后確定(如圖23)。
完成以上三步后，在沒有密碼的前提下，考生只能輸入準考證號和進行答題，不能取消隱藏工作表、取消隱藏某列或某行、查看和修改VBA代碼。
?
圖23
如何考試
打開“模擬考試系統(tǒng)-考生測試版.xlsm”就只有一張“試卷”工作表，先輸入準考證號回車確認，正確的話就顯示姓名，否則會要求你重輸。單擊出題按鈕即可顯示題目，同時出題按鈕會變成評分按鈕。考生可在D列選中相應單元格直接輸入答案或單擊下拉按鈕從列表中選擇輸入(如圖24到圖26)。題目做完后，單擊“評分”按鈕即可顯示最后得分，確定后自動保存，并回復到最初狀態(tài)。
?
圖24
?
圖25
?
圖26
系統(tǒng)修改
在電腦上能直接處理的題型就只有這三種，所以能改的也只有各種題的數(shù)量而已。步驟如下:
1.撤消工作表保護，在相應題目中選中N行，右擊選擇“復制”再選擇“插入復制單元格”，如果要減少則右擊選擇“刪除”就OK。
2.選中“試卷”工作表B:D列，右擊選擇“取消隱藏”，按之前講的公式修改復制各題型區(qū)域的公式。
3.然后重新隱藏C列和保護工作表，分數(shù)記錄中的公式會自動調(diào)整可不必修改
