EXCEL公式 VBA 随机生成不重复值实现方法详解

来源:转载

宏代码

Sub 不重复的值()

Dim arr(1 To 100000, 1 To 1), dic

Set dic = CreateObject("scripting.dictionary")

Do Until i = 100000

tmp = Format(Int(Rnd() * 100000) + 1, 0)

If Not dic.exists(tmp) Then

i = i + 1

dic.Add tmp, i

arr(i, 1) = tmp

End If

Loop

[a1].Resize(i, 1) = arr

End Sub

自定义函数

Function NoSame(t As Long) As Variant

'需要几个数字,t就为几

'比如 =NoSame(100)

'但是要选择100个单元格,而且是一列的

'比如选择A1:A100,输入 =NoSame(100),然后按CTRL+SHIFT+ENTER完成公式

Dim dic, arr

ReDim arr(1 To t, 1 To 1)

Set dic = CreateObject("scripting.dictionary")

Do Until i = t

'Randomize

tmp = Format(Int(Rnd() * t) + 1, 0)

If Not dic.exists(tmp) Then

i = i + 1

dic.Add tmp, i

arr(i, 1) = tmp

End If

Loop

NoSame = arr

End Function

分享给朋友:
您可能感兴趣的文章:
随机阅读: