摘要:題庫系統(tǒng)是教師智慧的結(jié)晶，對教學質(zhì)量和管理的提高有著重要作用;而Excel作為數(shù)據(jù)處理與分析的常用辦公軟件，具有界面直觀、操作簡潔的優(yōu)點。通過直接利用Excel公式，實現(xiàn)備題、組題、判題等核心功能，可形成一個直觀、簡潔，可自行組織、管理的選擇題題庫和考試系統(tǒng)。

關(guān)鍵詞:Excel;公式;題庫;選擇題;考試系統(tǒng)

中圖分類號:G434文獻標識碼:B文章編號:1673-8454(2010)04-0075-04

在日常教學中，經(jīng)常要為各階段性考試準備選擇題，雖有現(xiàn)成的題目，但是為了更好地針對不同班級的實際情況和教學進度，仍然需要對題目進行再處理。而且隨著題目不斷積累增多，在選取和編排上花費的精力也隨之越來越多，試題的管理成為一個重要問題。Excel作為常用辦公軟件MSOffice中的一員，不僅是強大的數(shù)據(jù)處理與分析軟件，也是一個微型數(shù)據(jù)庫。雖然在數(shù)據(jù)的存儲和管理上不及真正的數(shù)據(jù)庫系統(tǒng)，但對于小型的應(yīng)用，完全能夠滿足需求。其簡捷直觀的界面與靈活強大的處理能力，體現(xiàn)出“簡單就是美”的觀念。本著簡捷、直觀、實用、好用的原則，充分利用Excel，完全可以將其用于構(gòu)建一個選擇題考試系統(tǒng)，實現(xiàn)從備題、組題到判題等各個關(guān)鍵環(huán)節(jié)。

一、備選答案組題與判題的實現(xiàn)

從選擇題的題型上看，可分為單選和多選;而從備選答案的數(shù)量上看，一種僅儲備和出題要求數(shù)量相同的備選答案，暫且稱之為“恰備選”;另一種則儲備更多的備選答案，組題時根據(jù)需要，從中選取一定數(shù)量完成組題，暫且稱之為“多備選”。

1.單選題與“偽”多選題

單選題只有一個正選，實現(xiàn)相對簡單。先看“恰備選”的情況。由于所有備選答案都必須提供，因此只需將備選亂序排列:先用隨機數(shù)函數(shù)RAND()給備選答案編號，再結(jié)合MAX()和LARGE()函數(shù)，按隨機數(shù)的大小，通過查找函數(shù)VLOOKUP()取出備選答案即可。為方便后續(xù)判題，我們將備選答案的正確性同時輸入和參與亂序，答題的判斷同樣利用VLOOKUP()函數(shù)即可完成。公式如圖1。

圖中為便于查看和減小圖片寬度，對公式進行了字體加粗和換行處理，實際輸入時不需換行。實際效果如圖2。

對于其中的答題選擇單元格D3，設(shè)置了數(shù)據(jù)有效性，限制其只能輸入A、B、C、D四個選項之一(如圖3)。

以上方法的核心，就是Excel中強大的VLOOKUP()函數(shù)，利用其從數(shù)組的首列中查找指定值，而從返回數(shù)組其他列中查找對應(yīng)位置數(shù)值的能力，該函數(shù)也是后續(xù)所有組題的核心構(gòu)建公式。

當然，這只是一個最基本的雛形。通常情況下會儲備多個備選答案，即前述的“多備選”，比如儲備6個備選答案，出題時選取4個。對于單選題，只能有一個正選，因此只需從其他備選答案中隨機選取后再與正選組合即可。將前述“恰備選”進行擴充，增加一個“選取”列即可完成。公式和實際效果如圖4、圖5。

這種方式還可用于產(chǎn)生不完全隨機的多選題組題:將其中一個正選當作是單選中的唯一正選，然后從其他備選中選取3個組合即可。當然，由于是多選題，答題方式也需修改:答題選擇改為E4到E7四個單元格，數(shù)據(jù)有效性的來源設(shè)置為“√”。判題則只需判斷所有的答題選擇是否與正確答案相同。公式和實際效果如圖6、圖7。

其中H4:H7單元格中的T()函數(shù)，是為了將VLOOKUP()函數(shù)對于空值返回的0再次轉(zhuǎn)換為空值。由于這種方式會使某一個正選(圖中即“備選1”)必定出現(xiàn)，所以稱之為“偽”多選題。

2.多選題

真正的多選組題應(yīng)該是從備選答案中隨機選取一個正選，再與其他的備選進行組合。具體實現(xiàn)通過以下步驟完成:①將正選的隨機數(shù)加1，區(qū)分正選與錯選;②從調(diào)整后的隨機數(shù)中取最大值，實現(xiàn)隨機選取一個正選;同時，再將該正選取值設(shè)置為0，其他正選隨機數(shù)還原(相應(yīng)減1)，使得該正選不會進入下一步的隨機選取;③從還原后的隨機數(shù)中取出前三個，與0值所對應(yīng)的原始隨機數(shù)組合。公式如圖8(因圖片大小原因，只截取了備題部分)。

其中F4單元格:INDEX(A4:A9，MATCH(0，E4:E9，0))的作用，是先用MATCH()函數(shù)從置0并還原后的隨機數(shù)E4:E9中，找到0所在的位置(0對應(yīng)的就是隨機選取的第一個正選);然后再用INDEX()函數(shù)，在原隨機數(shù)A4:A9中，根據(jù)MATCH()函數(shù)找到位置，返回其對應(yīng)的原隨機數(shù)。出題和判題部分，則和前述的“偽”多選題一致。實際效果如圖9:

通過以上的簡單公式和設(shè)置，就實現(xiàn)了隨機組合備選答案和判題的核心功能。沒有任何VBA代碼，完全用公式實現(xiàn)，公式中使用的都是相對引用，可以直接復(fù)制到新行中構(gòu)造更多的題目，由Excel自動完成引用位置的調(diào)整。在實際使用時需要手工完成的，就是通過選擇性粘貼來固定隨機數(shù)或產(chǎn)生的組題結(jié)果(也可以將工作表設(shè)置為手工重算)。如果條件允許直接上機考試，則可以利用Excel的保護功能，限制某些操作和隱藏相關(guān)內(nèi)容即可。如果仍然需要紙質(zhì)試卷，則可以將Excel工作簿作為數(shù)據(jù)源，利用Word的郵件合并功能，設(shè)計好相應(yīng)的試卷模板和答案模板后合并數(shù)據(jù)。這些操作，都可以先利用Excel的錄制宏，將操作步驟轉(zhuǎn)化為VBA代碼，再根據(jù)實際需要加以修改，實現(xiàn)自動操作。

二、隨機取題的實現(xiàn)

不過，上述方法還只是實現(xiàn)了題內(nèi)的備選答案組題，而每次考試還需要綜合以前學習過的知識點加以聯(lián)系和強化。那么，是否能夠在上述方法的基礎(chǔ)上更進一步，在積累的題目中，針對每次考試的要求實現(xiàn)隨機取題，甚至一人一套題目呢?利用Excel強大的公式，我們還可以繼續(xù)加以改進。

1.組合題干和備選答案

在實現(xiàn)選題之前，先要將題干和備選答案“組合”起來，避免在后續(xù)的操作中不同的題干與備選答案之間發(fā)生錯位。這可以利用“組和分級顯示”來完成，因為Excel在排序時針對整個組進行，組內(nèi)的數(shù)據(jù)不會變動。通常情況下，題干在上方輸入，備選答案在下方，所以就先要修改默認的分級顯示設(shè)置。通過菜單“數(shù)據(jù)→組及分級顯示→設(shè)置…”，取消“明細數(shù)據(jù)的下方”復(fù)選框，再點擊“確定”即可(如圖10)。

完成設(shè)置后，選擇備選答案所在的各行，通過菜單“數(shù)據(jù)→組及分級顯示→組合…”，即完成題干和對應(yīng)備選的組合。同時，組合的設(shè)置情況也可以復(fù)制，通過復(fù)制的方式進行修改，可大量減少操作。組合后的題干和備選，還可以折疊，有利于查看與定位。同樣可以先制作一個相關(guān)公式都設(shè)置好的空白題干和備選行，作為“模板”，通過復(fù)制、修改的方式添加新的內(nèi)容。

2.按知識點和難度進行分類

作為Excel應(yīng)用，需要的就是它的直觀，因此直接將各知識點及難度的標示內(nèi)容單獨作為一行，在后續(xù)行中輸入所需的題干和備選，相同知識點/難度的題目相鄰排列。為了方便操作，還可以將各知識點下的題目再次進行組合，方便查看與編輯。

3.設(shè)置分組選題公式

為方便處理和減少不必要的數(shù)據(jù)列，將分組選題的公式設(shè)置與前述內(nèi)容安排到一起。實際的效果如圖11(以多選題為例)。

圖中1～3為標題行，4、19作為各知識點的標題和分隔，同時也作為選題時輸入位置。為了區(qū)分用于取題和取備選答案的隨機數(shù)，將取題隨機數(shù)設(shè)置為RAND()+1。隨后就要確定某一知識點的題目范圍以便選題。雖然可以直接指定一個具體區(qū)域，但范圍是固定的，而實際操作時經(jīng)常要進行的復(fù)制、刪除、移動等，會使公式中的范圍產(chǎn)生錯位。從圖示中的情況分析，只要能夠找到某一知識點的后續(xù)行中，隨機數(shù)列的第一個空白單元格，即可確定范圍。這時就需要利用Excel中更為強大、靈活的數(shù)組公式來實現(xiàn)。先看一個普通公式與數(shù)組公式取得下一空白行的簡單對比示意圖如圖12。

從圖中可以看出，使用普通公式對各行公式的結(jié)果求最小值(MIN()函數(shù)忽略邏輯值)，即可得到第一個非空單元格的行號。如果直接使用，不但每行都需要公式，而且也很難針對不同知識點分別設(shè)置。而用數(shù)組公式則可以在一個單元格內(nèi)將兩個步驟一次性完成。(注:數(shù)組公式在輸入后，應(yīng)按Ctrl+Shift+Enter組合鍵，而不是直接按回車;Excel會自動在公式外加“{}”以示區(qū)別，不必也不能手工輸入“{}”)

通過以上數(shù)組公式，即可得到相應(yīng)的范圍。當然，數(shù)組公式中的范圍，從邏輯上講，應(yīng)該是從當前知識點所在的下一行開始，一直到Excel所支持的最大行數(shù)65536為止，但這會使計算量過大，也沒有必要。我們只需設(shè)置一個足夠大的范圍即可。比如按照6個備選答案，包括題干在內(nèi)共7行，每個知識點最多有可能輸入20道儲備題，數(shù)組公式中的范圍就可以設(shè)置為150行。最終得到的公式設(shè)置如圖13。

E4/E19中為了方便后續(xù)操作，將結(jié)果再與字符“A”組合，得到代表下一非空單元格地址的字符型數(shù)據(jù);而D4/D19/F4/F19中的INDIRECT()函數(shù)，則是將字符型的地址轉(zhuǎn)換為實際的單元格引用。

得到下一非空單元格的地址后，相關(guān)公式的填寫就很簡單了，F(xiàn)4/F19根據(jù)取題數(shù)(C4/C19)，在該知識點范圍內(nèi)選取一個相應(yīng)值，作為取題分界點;隨后對取題隨機數(shù)(F5/F12/F20)進行調(diào)整，將其減去分界點的值。這樣，就根據(jù)取題數(shù)量將隨機數(shù)分成了大于0和小于等于0的兩部分，隨后依據(jù)該列(F列)排序或者篩選，選擇大于0部分的題目即可。其中的D4/D19則是為了避免輸入錯誤，對現(xiàn)有題數(shù)進行統(tǒng)計后給出相應(yīng)提示。

不過，以上公式中還存在一個小問題。對隨機數(shù)進行調(diào)整的公式(F5/F12/F20)中，對于分界點單元格(E4/E19)使用的是相對引用，在復(fù)制時Excel會自作聰明地進行修正，造成復(fù)制后公式錯誤。為避免該情況，可以通過Excel另一個強大而好用的功能:“命名區(qū)域”來解決:將各知識點所在的行(如圖13中的4/19行)命名，例如命名為“點1難”，那么分界點的值就是:INDEX(點1難，6)，即“點1難”各單元格構(gòu)成的數(shù)組中的第6個元素;F5單元格相應(yīng)變?yōu)?A5-INDEX(點1難，6)。修改后，在復(fù)制時，INDEX()函數(shù)的內(nèi)容不會變化，而“A5”則會由Excel按照正確的位置完成變換。

結(jié)束語

通過以上簡單的方法，就在Excel中實現(xiàn)了原本題庫軟件才具有的核心功能。如果有興趣，還可以在此基礎(chǔ)上繼續(xù)加以擴充，比如，針對考試結(jié)果對各知識點進行分析，或者生成考試情況分析表等，既可以讓學生知道自己在哪些方面存在不足，也可以讓教師檢查教學情況。不過這些后期的功能實現(xiàn)，就需要一定的VBA編程了。

(編輯:劉軒)