|
宏代码
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
|