如何建立隨機從題庫選題的試卷

本文共計字數：2123 ，預計閱讀時間：10分鐘

關鍵字：題庫（公眾號回復關鍵字可獲取相關分享）

起因：一個SQE，為什麼要去做隨機出題這件事？

    部門領導為了提升巡檢、質檢人員的積極性，制定了一套激勵制度——通過對員工進行實操與理論培訓、考核，予以相對應的績效獎金係數。

    考核是與績效考核同周期的，所以需要從建立的題庫隨機選題組成筆試試卷。

    起初小羊還擔心，萬一員工把整個題庫都掌握了，豈不是考試就失去意義了，領導的批示是：「如果員工掌握了題庫的所有知識點，那我們考核的目的不就已經達到了嗎！」 。

方法：用Excel表格實現隨機出題功能！

    以下操作,只需要在辦公軟體Excel中操作即可(先以考核常(zui)用(nan)題型填空題為例）:

1. 建立題庫

    1.1 在新建的excel表格中，建立一個題庫工作表，取名 TK，之所以用首字母代替，主要是為了後面公式引用的時候方(sheng)便(lan)。

    1.2 依 序號、輔助列、題幹、答案，將收集的題目和答案整理好。輔助列是為後面實現隨機出題做準備。這樣題庫的雛形就完成了。

2.1 同一個Excel中，建一個試卷工作表，取名SJ。

2.2 編輯好試卷名稱、信息欄等內容

2.3 預留出題號、輔助列(作用同上)、題幹、答案(批改試卷用，列印試卷時不在列印範圍)四列，只填寫題號，其他內容稍後以公式形式自動帶出。

3.  選題

    完成題庫和試卷後，就需要從題庫中，隨機將題目複製到試卷工作表中。

    我的思路是，當題庫有5道題，試卷需要3道。則給題庫的每道題，賦予一個不重複、隨機、且可刷新的數值，再在試卷工作表中抓取這些數值的前3大(試卷要3題)的值所對應的題幹和答案，最終實現「隨機選題」的效果。

    3.1 賦隨機數值：在題庫工作表的輔助列，利用隨機函數Rand ¹，給每一道題賦予一個隨機變化、不重複的數值。

   效果如下：

          3.2 隨機選題：在試卷工作表中的輔助列，對應題號1，通過Large²函數選取題庫工作表輔助列中的第 1 大、第 2 大 和第 3 大數值，這樣我們就會通過隨機數值，從題庫的5道題中選出了3道。

3.3 連結題幹與答案：現在要通過這些隨機的數值，將對應的題幹、答案，通過查找公式Vlookup ³ 連結過來。 

    其實到上一步，就已經完成了隨機出題的目的，因為輔助列裡的公式Rand有個特性，即你每次打開Excel、每次刷新Excel（按F9）、甚至每次編輯任一個單元格後，其Rand返回的數字都會變化，所以每個月需要考試時，只需打開此Excel檔，即可實現隨機出題。

     剩下就是對試卷的列印格式、範圍的設置，並對相關輔助列做隱藏。另外就是為了滿足列印的需求，題庫中題幹的長度需做匹配設計，比如題幹過長時，需要做單元格內強制換行（Alt+Enter），而不可以一味的按空格來實現換行。 

    當這份表格完成後，為了保護相關公式和設定，可以對表格進行加密，然後提供一個刷新按鈕的方式完成刷新。具體可參考excel的控制塊和宏命令功能，後面有機會也會把這些不常用的功能分享出來。

可以將密碼藏在表格的某個位置，避免自己遺忘密碼的煩惱（後面會介紹如何破解被加密的Excel，2019/9/16之後在公眾號回覆：表格破解）

 

寫在結尾：

    實現以上功能的方法肯定不止這一種，這只是小羊在接到領導任務後，通過自己掌握的有限知識，實現的一個解決方案。如果大家還有更有效率的方法，歡迎給我留言分享，也可以添加小羊微信，面對面溝通。長按識別下列二維碼，或者搜索微信號seanwu87。

感謝您的閱讀與分享！

小羊 2019/8/31

 

附錄1 所用公式說明：

① RAND

傳回大於等於 0 且小於 1 的均等分配隨機實數。每次計算工作表時，都會傳回新的隨機實數。

語法  =RAND()

 

② LARGE

傳回資料組中第 k 個最大的數值。您可以用這個函數來指定選取排在第幾位的值。例如，您可以使用 LARGE 傳回最高、第二高或第三高的分數。

語法 =LARGE(array,k)

Array   是您要決定第 k 個最大值的數值陣列或資料範圍。

K   是在陣列或資料的儲存格範圍中要傳回的位置 (由最大值算起)。

 

③VLOOKUP

在表格陣列的第一欄中搜尋某個數值，並傳回該表格陣列中同一列之其他欄中的數值。

語法 =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value  在表格陣列 (陣列：用來建立產生多個結果或運算一組以列及欄排列之自變量的單一公式。陣列範圍共享一個公式；一個陣列常數是用作一個自變量的一組常數。)的第一欄中搜尋的數值Lookup_value 可以是數值，也可以是參照位址。當 lookup_value 小於 table_array 第一欄中的最小值時，VLOOKUP 將會傳回錯誤值 #N/A。

Table_array  兩欄以上的資料。請使用參照位址來指向某個範圍或範圍名稱。table_array 第一欄中的值即為 lookup_value 所搜尋的值。這些值可以是文字、數字或邏輯值 (不分大小寫)。

Col_index_num  table_array 中的欄號；相符的值將從該欄中傳回。如果 col_index_num 自變量值為 1，則傳回 table_array 第一欄中的值；如果 col_index_num 自變量值為 2，則傳回 table_array 第二欄中的值，依此類推。如果 col_index_num：小於 1，則 VLOOKUP 會傳回錯誤值 #VALUE!。

大於 table_array 中的欄數，則 VLOOKUP 會傳回錯誤值 #REF!。

Range_lookup  一個邏輯值，用來指定 VLOOKUP 應該要尋找完全符合還是部分符合的值：

如果此自變量值為 TRUE 或被省略了，則傳回完全符合或部分符合的值。如果找不到完全符合的值，將會傳回僅次於 lookup_value 的值。

table_array 第一欄中的值必須以遞增順序排序；否則，VLOOKUP 可能無法提供正確的值。如需詳細信息，請參閱排序資料。

 如果此自變量值為 FALSE，則 VLOOKUP 函數只會尋找完全符合的值。在此情況下，table_array 第一欄中的值便不需要排序。如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符，將會使用第一個找到的值。如果找不到完全符合的值，則傳回錯誤值 #N/A。