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

張亮

摘要：在日常工作中，我們需要抽取一些隨機的信息，比如選擇招投標(biāo)代理機構(gòu)、內(nèi)部專家、供應(yīng)商等。通過在Excel中建立這些信息庫，利用VBA和單元格輸入抽取的參數(shù)即可實現(xiàn)信息的快速隨機抽取。

關(guān)鍵詞：Excel信息庫；隨機；抽取；

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2019）05-0208-01

在日常工作中，我們需要抽取一些隨機的信息，如招投標(biāo)代理機構(gòu)、內(nèi)部專家、供應(yīng)商等，為了盡可能的公平，本人在Excel中建立了相關(guān)的信息庫，并通過選擇相關(guān)的信息分類，設(shè)置抽取的數(shù)量，實現(xiàn)了基于Excel的抽取工具。

1 建立信息庫

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

2 實現(xiàn)抽取

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

左邊的抽取類別中，采用數(shù)據(jù)有效性中的序列，將內(nèi)部專家表中的類別列設(shè)置成序列，即可進(jìn)行類別的選擇。庫內(nèi)數(shù)量使用公式：COUNTIF（信息庫！B：B，抽?。2）可自動計算專家?guī)熘邢鄳?yīng)類別的專家數(shù)量。抽取數(shù)量依然采用數(shù)據(jù)有效性進(jìn)行限制：限制其區(qū)間為1至庫內(nèi)數(shù)量即可。具體的抽取，則采用了Excel的隨機函數(shù)。在Excel中有2個隨機數(shù)生成的函數(shù)Rand和Randbetween，Rand函數(shù)可以生成0～1之間的隨機小數(shù)，RANDBETWEEN函數(shù)可以隨機生成指定范圍的隨機整數(shù)。通過Excel函數(shù)功能也可以實現(xiàn)抽取功能，但需每次手工操作，這里我們采用了VBA函數(shù)實現(xiàn)了抽取效果。如果程序中如果只用Rand取隨機數(shù)時，每次執(zhí)行都會產(chǎn)生相同的隨機數(shù)序列。在VBA中，可以通過Randomize語句，讓每次執(zhí)行程序產(chǎn)生不同的序列，這樣最大可能地避免重復(fù)抽取到一個人。代碼中通過循環(huán)將獲取到的隨機值與專家序號進(jìn)行組合并排序，在右邊區(qū)域顯示，這樣得到了一個滾動顯示的效果，直到按下停止按鈕，抽取的一定數(shù)量專家顯示在右邊區(qū)域。抽取功能的主要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 結(jié)束語

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

參考文獻(xiàn)：

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

[2] Excel Home. Excel 2016函數(shù)與公式應(yīng)用大全[M]. 北京： 北京大學(xué)出版社， 2018.

【通聯(lián)編輯：張薇】