基于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.

【通联编辑：张薇】