|
也写了一个,请参考一下。- Sub Ax()
- Dim Arr, Brr, Dic, D1, Cq, Wz As Range, Col, Crr
- Arr = Range("a7:b" & Cells(Rows.Count, 1).End(3).Row)
- Cq = Application.InputBox("请指定抽取人数", "设置", 20, , , , , 1)
- Set Wz = Application.InputBox("请指定保存位置", "设置", , , , , , 8)
- Set Dic = CreateObject("Scripting.Dictionary")
- Set D1 = CreateObject("Scripting.Dictionary")
- ReDim Crr(1 To Cq, 1 To 2)
- Col = Columns.Count - 1
- t = Timer
- ''将原始数据,装进字典
- For i = 1 To UBound(Arr)
- Dic(Arr(i, 1)) = Arr(i, 2)
- Next
- If Cells(2, Col) <> "" Then
- Brr = Cells(2, Col).CurrentRegion
- For i = 1 To UBound(Brr)
- Dic.Remove (Brr(i, 1)) '去除已经抽取过的数据
- Next
- End If
- If Dic.Count = 0 Then '如果全抽空,清空缓冲区
- If MsgBox("数据已经抽空,请重新开始", vbYesNo, "提示") Then
- Cells(2, Col).CurrentRegion.ClearContents
- End If
- End If
- On Error Resume Next
- Randomize
- If Dic.Count > Cq Then '单组抽取数量小于可抽取数量时,随机抽取
- Do While js < Cq
- n = Int(Rnd() * (Dic.Count - 1)) + 1
- If Not D1.exists(Dic.keys()(n)) Then
- js = js + 1
- D1(Dic.keys()(n)) = Dic.items()(n)
- End If
- Loop
- For i = 0 To D1.Count - 1
- Crr(i + 1, 1) = D1.keys()(i)
- Crr(i + 1, 2) = D1.items()(i)
- Next
- Else
- For i = 0 To Dic.Count - 1 '单组抽取数量大于可用抽取数量时,
- Crr(i + 1, 1) = Dic.keys()(i)
- Crr(i + 1, 2) = Dic.items()(i)
- Next
- End If
- Wz.Resize(UBound(Crr), 2) = Crr
- Cells(1, Col).Resize(UBound(Crr), 2).Offset(Cells(Rows.Count, Col).End(3).Row) = Crr
- MsgBox Format(Timer - t, "0.00") & "秒"
- End Sub
复制代码
不放回抽取.zip
(33.45 KB, 下载次数: 5)
|
|