用EXCEL制作模擬考試系統(tǒng)

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流用EXCEL制作模擬考試系統(tǒng).精品文檔.1.建立題庫打開Excel 2010，建立“模擬考試系統(tǒng)-出題測試版”。打開此工作簿，建立“單選題”工作表(如圖1)，在A2輸入公式=ROW()-1&.以自動生成序號，在D2輸入=IF(C2=,RAND()，選中A2:D2拖動向下填充幾百行。按住Ctrl鍵單擊 “單選題”工作表標(biāo)簽拖動復(fù)制出兩份，分別重命名為“多選題”（如圖2）、“判斷題”（如圖3），分別輸入題目和答案，按Alt+回車鍵換行。請注意以下提示：1、以上3個工作表，在B列輸入單選題題目（樣式隨意，但一道題只能占一個單元格），在C列輸入對應(yīng)考

2、題的答案，答案輸入時注意不要有多余字符。2、“多選題”工作表的答案必須按ABCD的順序填寫，例如答案ABD，不能寫ADB、BAD、BDA、DAB、DBA，答錯、少答和多答均不得分；“判斷題”工作表的答案只能有兩種，本題按“正確”和“錯誤”作為答案，其他形式如“對”和“錯”、“”和“”也可以。3、題庫中題目數(shù)量根據(jù)實際情況而定，多多益善。本例中題庫數(shù)目為單選題60道、多選題50道、判斷題45道。圖1圖2圖32.建立考生名單新建“考生名單”工作表，在A、B、C列分別輸入序號、準(zhǔn)考證號、考生姓名，選中B列在名稱框中輸入“考號”，回車確定把B列命名為考號(如圖4)。完成后可在名稱管理器內(nèi)看到名字為“考

3、號”的名稱，引用位置為“考生名單”工作表的B列。圖43.設(shè)計試卷建立“試卷”工作表，按需要設(shè)置試卷，序號生成方式如題庫序號。單選題30題(A5:C34)，多選題40(A36:C75)，判斷題30題(A77:C106)。在B4輸入“單選題（每題1分）”，在B35輸入“多選題（每題1分，錯、漏、多選均不得分）”，在B76輸入“判斷題（每題1分）”。用“自選圖形/基本形狀”中的棱臺畫一個按鈕，選中在名稱框中輸入“出題按鈕”4字回車，把它命名為“出題按鈕”。再右擊此按鈕選擇“添加文字”，在按鈕上輸入“出題”2字。同樣再畫一個按鈕，把它命名為“評分按鈕”，添加文字“評分”。(如圖5)圖54.顯示題目（如

4、圖6）在B5輸入公式=INDIRECT(單選題!R&ROW()-3&C&COLUMN(),0)，復(fù)制到B5:C34（可拖動A5填充）。在B36輸入公式=INDIRECT(多選題!R&ROW()-34&C&COLUMN(),0)并復(fù)制到B36:C75。在B77輸入公式=INDIRECT(判斷題!R&ROW()-75&C&COLUMN(),0)并復(fù)制到B77:C106。在E5單元格輸入公式=IF(D5=,0,IF(D5=C5,$G$2,0)，向下填充到E34，這公式用來判斷如果D5中的答案與C4的標(biāo)準(zhǔn)答案一致就得1分否則為0分，其中G2單元格內(nèi)數(shù)值為1。同理，在E36單元格輸入公式=IF(D36=

5、,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)的是絕對位置引用，即不管在單選題工作表中進(jìn)行插入、刪除行或排序，B5顯示的始終都是你從單選題B2中直接看到的內(nèi)容。因此B5:C34、B36:C75、B77:C106三個區(qū)域?qū)⒎謩e與相應(yīng)題庫中最前面的30(或40)

6、題的內(nèi)容保持一致。圖65.答案限制選中單選題答題區(qū)D5:D34單擊“數(shù)據(jù)/有效性”（快捷鍵ALT+DL），在“允許”中選擇“序列”，選中“提供下拉箭頭”復(fù)選項，在“來源”中輸入“A,B,C,D”，不含引號且其中逗號為半角格式(如圖7)。切換到“出錯警告”選項卡，輸入錯誤信息為“只能輸入A、B、C、D中的一項，其他任何符號或空格均為非法?！保x中“輸入無效數(shù)據(jù)時顯示警告”復(fù)選項，選擇樣式為“停止”(如圖8)，確定完成設(shè)置。圖7圖8同樣對多選題答題區(qū)D36:D75，判斷題答題區(qū)D77:D106設(shè)置數(shù)據(jù)有效性，只是“來源”要分別換成“A,B,C,D,B,AC,AD,BC,BD,CD,ABC,ACD,

7、BCD,ABCD”和“正確,錯誤”。 多選題的“出錯警告”一定要改成“ 必須按ABCD的順序輸入多選項，否則無法正確評分”。（如圖9到如圖11）圖9圖10圖116.準(zhǔn)考證號驗證 選中“試卷”工作表中要輸入準(zhǔn)考證號的D2單元格，單擊“數(shù)據(jù)/有效性”，在“允許”中選擇“系列”，取消“提供下拉箭頭”復(fù)選項，在“來源”中輸入=INDIRECT(考號)（如圖12）。切換到“出錯警告”選項卡，輸入錯誤信息為“你輸入的準(zhǔn)考證號并不存在，請重新輸入”，選中“輸入無效數(shù)據(jù)時顯示警告”，并設(shè)置樣式為“停止”（如圖13）。圖12圖13在F2單元格輸入公式=IF(D2=,VLOOKUP(D2,考生名單! $B:$C,

8、2,FALSE)，在D2單元格輸入準(zhǔn)考證號后會自動顯示考生姓名。（如圖14）圖147.隨機(jī)抽題右擊“出題”按鈕，選擇“指定宏”，在彈出窗口中，輸入宏名為“出題”，位置選擇當(dāng)前工作簿，單擊“新建”按鈕，復(fù)制如下代碼粘貼到框內(nèi)，結(jié)果如圖15，保存時將文件另存為啟用宏的.xlsm格式。Sub 出題() If Range(D2).Value = 0 Then 提示 = MsgBox(請先輸入準(zhǔn)考證號) GoTo 重來 End If 如果D2為空則彈出提示要求輸入準(zhǔn)考證號并直接跳到后面的重來:處。 Sheets(單選題).Range(A:D).Sort Key1:=Sheets(單選題).Range(D

9、2), 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列隨機(jī)數(shù)升序排序。 Columns(B:B).Rows.AutoFit 對B列設(shè)置最適合

10、行高，以完整顯示題目。 Range(D5).Select 定位到D5以便輸入答案 ActiveSheet.Shapes(出題按鈕).Visible = False 隱藏出題按鈕以防重復(fù)選題 ActiveSheet.Shapes(評分按鈕).Visible = True 顯示評分按鈕重來: End Sub圖15試卷中的題目總是顯示各題庫最前面的N題，而在此對各題庫按隨機(jī)數(shù)進(jìn)行隨機(jī)排序后最前面的N題自然是隨機(jī)的，也就相當(dāng)于是隨機(jī)抽題了。8.成績記錄表新建 “成績記錄”工作表(如圖16)，在A2輸入公式=NOW()，并選中A列右擊從“設(shè)置單元格格式”中設(shè)置為日期格式以顯示當(dāng)前時間。在B2輸入公式=試

11、卷!D2和C2輸入公式=試卷!F2以顯示相應(yīng)數(shù)據(jù)。D2輸入公式=SUM(E2:G2)對后面三個得分進(jìn)行合計，E2、F2、G2則分別輸入公式=SUM(試卷!E5:E34)、=SUM(試卷!E36:E75)、=SUM(試卷!E77:E106)分別對相應(yīng)題型得分進(jìn)行合計。圖169.評分和記錄保存右擊“評分”按鈕，選擇“指定宏”，輸入宏名“評分”，位置選擇當(dāng)前工作簿，單擊“新建”按鈕，復(fù)制如下代碼粘貼到框內(nèi)，結(jié)果如圖17:Sub 評分() Sheets(成績記錄).Rows(3:3).Insert Shift:=xlDown 在成績記錄工作表第3行前插入一行 Sheets(成績記錄).Rows(2:2

12、).Copy 復(fù)制成績記錄工作表第2行 Sheets(成績記錄).Range(A3).PasteSpecial Paste:=xlPasteValues 以數(shù)值方式粘貼到成績記錄工作表A3 得分 = MsgBox(最終得分 & Range(成績記錄!D2).Value & 其中:單選題 & Range(成績記錄!E2).Value & 、多選題 & Range(成績記錄!F2).Value & 、判斷題 & Range(成績記錄!G2).Value, , 評分結(jié)果) 彈出信息框顯示成績記錄!D2、E2、F2、G2的數(shù)據(jù)，標(biāo)題為評分結(jié)果 Range(D2,D5:D34,D36:D75,D77:D

13、106).ClearContents 清除試卷工作表中輸入的內(nèi)容 Sheets(試卷).Rows(3:500).RowHeight = 0 Sheets(成績記錄).Rows(2:2).RowHeight = 0 設(shè)置題目區(qū)行高為0，以隱藏所有題目 ActiveSheet.Shapes(出題按鈕).Visible = True ActiveSheet.Shapes(評分按鈕).Visible = False 顯示出題按鈕,隱藏評分按鈕 ActiveWorkbook.Save 保存 End Sub圖1710.其他設(shè)置選中A4單擊“窗口/凍結(jié)空格”以始終顯示1:3行的標(biāo)題和按鈕。右擊C、E列選擇隱

14、藏。為答題區(qū)以外單元格設(shè)置填充色以示區(qū)別，把評分按鈕重疊到出題按鈕上(如圖18)。圖18選中D5:D34右擊選擇“設(shè)置單元格格式”，在“保護(hù)”選項卡中單擊取消“鎖定”選項，對于同樣需要添寫的D2,D36:D75,D77:D106區(qū)也同樣取消鎖定設(shè)置（如圖19）。然后單擊“工具/保護(hù)/保護(hù)工作表”，在中間“允許此工作表所有用戶進(jìn)行”列表中只選中“選定未鎖定單元格”和“設(shè)置行格式”兩個選項，輸入密碼(如圖20)，單擊確定把工作表保護(hù)起來，這樣考生就無法修改試題等相應(yīng)單元格了。單擊評分以隱藏所有題目，同時顯示出題按鈕，把文件另存為“模擬考試系統(tǒng)-考生測試版.xlsm”，退出。如圖19如圖20系統(tǒng)維護(hù)

15、1.隱藏工作表選中除“試卷”工作表除外的其他工作表，右擊選擇隱藏，以免考生看到。（如圖21）圖212.保護(hù)VBA代碼右擊 “試卷”工作表，選擇“查看代碼”，選中VBAProject(模擬考試系統(tǒng)-考生測試版.xlsm)，右擊，選擇“VBAProject 屬性”，在“保護(hù)”選項卡中選中“查看時鎖定工程”，并輸入密碼(如圖22)，確定，保存文檔后退出。關(guān)閉文檔后再重新打開，查看代碼需輸入密碼，這樣任何人沒有密碼就別想修改這些代碼了。圖223.保護(hù)工作簿為防止考生修改，在“審閱”選項卡中選中“保護(hù)工作簿”，勾選“結(jié)構(gòu)”和“窗口”選項，輸入密碼后確定(如圖23)。完成以上三步后，在沒有密碼的前提下，考生只能輸入準(zhǔn)考證號和進(jìn)行答題，不能取消隱藏工作表、取消隱藏某列或某行、查看和修改VBA代碼。圖23如何考試打開“模擬考試系統(tǒng)-考生測試版.xlsm”就只有一張“試卷”工作表，先輸入準(zhǔn)考證號回車確認(rèn)，正確的話就顯示姓名，否則會要求你重輸。單擊出題按鈕即可顯示題目，同時出題按鈕會變成評分按鈕?？忌稍贒列選中相應(yīng)單元格直接輸入答案或單擊下拉按鈕從列表中選擇輸入(如