基于Excel的隨機信息抽取工具

張亮

摘要：在日常工作中，我們需要抽取一些隨機的信息，比如選擇招投標代理機構、內部專家、供應商等。通過在Excel中建立這些信息庫，利用VBA和單元格輸入抽取的參數即可實現信息的快速隨機抽取。

關鍵詞：Excel信息庫；隨機；抽??；

中圖分類號：TP311 文獻標識碼：A 文章編號：1009-3044（2019）05-0208-01

在日常工作中，我們需要抽取一些隨機的信息，如招投標代理機構、內部專家、供應商等，為了盡可能的公平，本人在Excel中建立了相關的信息庫，并通過選擇相關的信息分類，設置抽取的數量，實現了基于Excel的抽取工具。

1 建立信息庫

下面以內部專家為例，建立一個內部專家的工作表，并通過透視表或去重的方式將類別再獨立放在一列。這列將用于數據有效性中的序列，用來選擇相關類別。專家表如圖1所示。

2 實現抽取

抽取的效果如圖2所示，左邊是抽取的結果，右邊抽取的設置和按鍵。在左邊選好類別后，庫內的數量會自動計算出來，如果輸入的抽取數量大于庫內數量，則會提示重新輸入。抽取數量輸好后，點擊抽取，后臺函數會將選擇類別的所有專家查詢出來，在右邊的現實區域輪轉，直到點擊停止后，系統按照隨機的規則抽出選中數量的專家數，這樣就完成了數據的抽取工作。以下是實現抽取的具體介紹。

左邊的抽取類別中，采用數據有效性中的序列，將內部專家表中的類別列設置成序列，即可進行類別的選擇。庫內數量使用公式：COUNTIF（信息庫！B：B，抽??！I2）可自動計算專家庫中相應類別的專家數量。抽取數量依然采用數據有效性進行限制：限制其區間為1至庫內數量即可。具體的抽取，則采用了Excel的隨機函數。在Excel中有2個隨機數生成的函數Rand和Randbetween，Rand函數可以生成0～1之間的隨機小數，RANDBETWEEN函數可以隨機生成指定范圍的隨機整數。通過Excel函數功能也可以實現抽取功能，但需每次手工操作，這里我們采用了VBA函數實現了抽取效果。如果程序中如果只用Rand取隨機數時，每次執行都會產生相同的隨機數序列。在VBA中，可以通過Randomize語句，讓每次執行程序產生不同的序列，這樣最大可能地避免重復抽取到一個人。代碼中通過循環將獲取到的隨機值與專家序號進行組合并排序，在右邊區域顯示，這樣得到了一個滾動顯示的效果，直到按下停止按鈕，抽取的一定數量專家顯示在右邊區域。抽取功能的主要VBA代碼如下所示：

Set cnn = CreateObject（"adodb.connection"）

mybook = ThisWorkbook.FullName

Range（"a3：g200"）.ClearContents

sqlc1 = Sheets（"抽取"）.Range（"I2"）

p = 0

cnn.Open "Provider = Microsoft.Jet.Oledb.4.0；Extended Properties =Excel 8.0；Data Source =" & mybook

Dim r

Dim i

For i = 1 To 10000

Randomize

r = Rnd

sql = "select * from [信息庫$a1：g100] where 類別='" & sqlc1 & "'and 序號 is not null order by rnd（" & r & "-序號）"

Range（"A3"）.CopyFromRecordset cnn.Execute（sql）

DoEvents

If p = 1 Then Set cnn = Nothing： Exit Sub

Next

3 結束語

經過多次測試和使用，該工具運行正常，操作簡單，而且信息庫維護方便，提高了日常信息抽取的工作效率，又確保了各項抽取工作的公平、公正、公開的目的。

參考文獻：

[1] Excel Home. 別怕， Excel VBA其實很簡單[M]. 北京：人民郵電出版社， 2014.

[2] Excel Home. Excel 2016函數與公式應用大全[M]. 北京： 北京大學出版社， 2018.

【通聯編輯：張薇】