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

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