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