2018-01-31 6頁(yè) 5.0分 

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+回車(chē)鍵換行。請(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

?

圖3

2.建立考生名單

新建“考生名單”工作表，在A、B、C列分別輸入序號(hào)、準(zhǔn)考證號(hào)、考生姓名，選中B列在名稱框中輸入“考號(hào)”，回車(chē)確定把B列命名為考號(hào)(如圖4)。完成后可在名稱管理器內(nèi)看到名字為“考號(hào)”的名稱，引用位置為“考生名單”工作表的B列。

?

圖4

3.設(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字回車(chē)，把它命名為“出題按鈕”。再右擊此按鈕選擇“添加文字”，在按鈕上輸入“出題”2字。同樣再畫(huà)一個(gè)按鈕，把它命名為“評(píng)分按鈕”，添加文字“評(píng)分”。(如圖5)

?

圖5

4.顯示題目（如圖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)容保持一致。

?

圖6

5.答案限制

選中單選題答題區(qū)D5:D34單擊“數(shù)據(jù)/有效性”（快捷鍵ALT+D→L），在“允許”中選擇“序列”，選中“提供下拉箭頭”復(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)或空格均為非法?！保x中“輸入無(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

?

圖11

6.準(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）

?

圖14

7.隨機(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ì)。

?

圖16

9.評(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

?

圖17

10.其他設(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）

?

圖21

2.保護(hù)VBA代碼

右擊 “試卷”工作表，選擇“查看代碼”，選中VBAProject(模擬考試系統(tǒng)-考生測(cè)試版.xlsm)，右擊，選擇“VBAProject 屬性”，在“保護(hù)”選項(xiàng)卡中選中“查看時(shí)鎖定工程”，并輸入密碼(如圖22)，確定，保存文檔后退出。關(guān)閉文檔后再重新打開(kāi)，查看代碼需輸入密碼，這樣任何人沒(méi)有密碼就別想修改這些代碼了。

?

圖22

3.保護(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)回車(chē)確認(rèn)，正確的話就顯示姓名，否則會(huì)要求你重輸。單擊出題按鈕即可顯示題目，同時(shí)出題按鈕會(huì)變成評(píng)分按鈕?？忌稍贒列選中相應(yīng)單元格直接輸入答案或單擊下拉按鈕從列表中選擇輸入(如圖24到圖26)。題目做完后，單擊“評(píng)分”按鈕即可顯示最后得分，確定后自動(dòng)保存，并回復(fù)到最初狀態(tài)。

?

圖24

?

圖25

?

圖26

系統(tǒng)修改

在電腦上能直接處理的題型就只有這三種，所以能改的也只有各種題的數(shù)量而已。步驟如下:

1.撤消工作表保護(hù)，在相應(yīng)題目中選中N行，右擊選擇“復(fù)制”再選擇“插入復(fù)制單元格”，如果要減少則右擊選擇“刪除”就OK。

2.選中“試卷”工作表B:D列，右擊選擇“取消隱藏”，按之前講的公式修改復(fù)制各題型區(qū)域的公式。

3.然后重新隱藏C列和保護(hù)工作表，分?jǐn)?shù)記錄中的公式會(huì)自動(dòng)調(diào)整可不必修改