[原创]不重复随机数

Kavis · 发表于 2010-11-24 10:06

精简一下，A1=MATCH(SMALL($B$1:$B$50,ROW()),$B$1:$B$50,0)

d_d_zip · 发表于 2011-1-16 01:38

理论上确实无法产生不重复的随机数，重复几率小，不等于不会重复。不过，10^15确实是个天文数字，远大于彩票中奖几率。除非做专门的研究，象是数学或者天文之类的，可以视同能够产生不重复的随机数。

楼主的公式还是有较大的实用性的。

灰袍法师 · 发表于 2011-1-16 19:45

原帖由 d_d_zip 于 2011-1-16 01:38 发表
理论上确实无法产生不重复的随机数，重复几率小，不等于不会重复。不过，10^15确实是个天文数字，远大于彩票中奖几率。除非做专门的研究，象是数学或者天文之类的，可以视同能够产生不重复的随机数。
楼主的公式还 ...

VBA rnd函数用什么算法不清楚，不过Excel工作表的RAND函数使用以下算法

随机小数重复的可能性是零其实也完全可以把随机小数看成是15位长的随机整数，反正就是小数点的位置不同而已。

首先，按下面的资料所言，rand()函数的周期是6.95E+12，而不是上面两帖所以为的10^15

其次，周期是6.95E+12，意味着除非你连续用超过6.95千亿个随机数，否则不会出现重复，而不是一般认为的"会有重复但是机会很低"

rand函数算法的三个整数生成函数，都是精心选择的，其周期都是满的
也就是说在 30269 30307 30323的范围内，每个生成的整数都不相同

而且我验证过，按rand()函数的算法把这三个整数转化为小数，每个小数都不相同
也就是说，这三个数的组合数接近 30269*30307*30323 = 27万亿种

当然，rand()函数的算法把这三个小数求和，然后只取小数部分
这个过程导致最终的周期只有6.95千亿，而不是27万亿。

最后，如果是用rand()配合其它公式，生成 1-100的随机整数100个，那么重复的可能性就绝对是100%了。

重复与否是跟你要生成的数字范围有关。

===============================================
rand()函数的VBA代码实现

Sub gen_rand_numbers(arr_rnd()) '本程序用0-1随机小数填满 arr_rnd 数组，arr_rnd是一个一行N列的变体类型数组

'使用以下算法 use algorithm as http://support.microsoft.com/kb/828795/zh-cn
'Wichman, B.A. 和 I.D. Hill，Algorithm AS 183:An Efficient and Portable Pseudo-Random Number Generator，《Applied Statistics》，31，188-190，1982。

Dim ix As Long, iy As Long, iz As Long, p As Long, r As Double
Const mod1 As Long = 30269: Const mod2 As Long = 30307: Const mod3 As Long = 30323
Randomize
ix = Rnd() * mod1 '这里用了VBA内置函数生成三个随机种子，实际还可以用随机时间，鼠标位移等等方法，不依赖VBA随机函数
iy = Rnd() * mod2
iz = Rnd() * mod3
For p = LBound(arr_rnd) To UBound(arr_rnd)
ix = 171 * ix Mod mod1
iy = 172 * iy Mod mod2
iz = 170 * iz Mod mod3
r = ix / mod1 + iy / mod2 + iz / mod3
arr_rnd(p) = r - Int(r)
Next p
End Sub
==================================================
RAND()函数的源代码
An Efficient and Portable Pseudo-Random Number Generator

   real function random()
c
c    Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2
c
c    Returns a pseudo-random number rectangularly distributed
c    between 0 and 1. The cycle length is 6.95E+12 (See page 123
c    of Applied Statistics (1984) vol.33), not as claimed in the
c    original article.
c
c    IX, IY and IZ should be set to integer values between 1 and
c    30000 before the first entry.
c
c    Integer arithmetic up to 30323 is required.
c
   integer ix, iy, iz
   common /randc/ ix, iy, iz
c
   ix = 171 * mod(ix, 177) - 2 * (ix / 177)
   iy = 172 * mod(iy, 176) - 35 * (iy / 176)
   iz = 170 * mod(iz, 178) - 63 * (iz / 178)
c
   if (ix .lt. 0) ix = ix + 30269
   if (iy .lt. 0) iy = iy + 30307
   if (iz .lt. 0) iz = iz + 30323
c
c    If integer arithmetic up to 5212632 is available, the preceding
c    6 statements may be replaced by:
c
c    ix = mod(171 * ix, 30269)
c    iy = mod(172 * iy, 30307)
c    iz = mod(170 * iz, 30323)
c
   random = mod(float(ix) / 30269. + float(iy) / 30307. +
   +                      float(iz) / 30323., 1.0)
   return
   end

[ 本帖最后由灰袍法师于 2011-6-15 01:58 编辑 ]

sandraliuyi · 发表于 2011-4-26 05:00

xiexiexie xie

jianbing · 发表于 2011-5-31 07:06

原帖由 chenjun 于 2004-10-26 00:55 发表
做得好，一点改进建议，A1=RANK(B1,$B$1:$B$50)，B列的RAND函数公式单元格的小数位没有关系的。

学习

lfeng89 · 发表于 2011-6-15 01:36

能否产生这样的一个函数：例如随机产生一列1-10 的随机数，列中任意一个数与当前表格前五位数不重复（例如2,3,6,8,5，第六位数不能就不能是23658中的任意一位）

jianbing · 发表于 2011-6-15 04:27

假设A1:A3有3个一位数不重复。
A4=INDEX(SMALL(IF(FREQUENCY($A$1:$A3,ROW($1:$10)-1)=0,ROW($1:$11)-1,99),ROW(INDIRECT("1:"&10-COUNT($A$1:A3)))),1+RAND()*(10-COUNT($A$1:A3)))

下拉公式到A10
得到其它7个不重复随便数。

wendellzone · 发表于 2011-7-20 09:59

支持楼主奉献精神

喜欢没刺的鱼 · 发表于 2012-1-9 13:46

恩，很好用，还要研究一下原理

可宜 · 发表于 2012-11-16 14:41

楼主辛苦啦，如果不加辅助列是不是更好些

		自动登录	找回密码
密码			免费注册

[原创]不重复随机数

回复 1楼 gvntw 的帖子

这个问题可以定论了

求解随机数

回复 66楼 lfeng89 的帖子

[原创]不重复随机数

回复 1楼 gvntw 的帖子

这个问题可以定论了

求解 随机数

回复 66楼 lfeng89 的帖子

求解随机数