使用公式生成不重复的随机数 [英] Generate random numbers which do not repeat by using formulas

问题描述

我想生成 0 到 20 之间的 4 个随机数，它们之间不重复.即对于每个后续生成的随机数，它必须排除任何先前生成的随机数的值.

I want to generate 4 random numbers between 0 and 20 which do not repeat among themselves. i.e. For each subsequent generated random number, it must exclude the value of any previously generated random numbers.

例如

生成的第2个随机数不能等于第1个，第3个不能等于第1个和第2个，最后第4个不能等于第1个，第2个，第3个.

The 2nd generated random number must not be equal to the 1st one, the 3rd one must not be equal to the 1st and 2nd ones, and finally the 4th one must not be equal to the 1st, 2nd, and 3rd ones.

如何使用 Excel 公式实现这一目标?

How to achieve that using Excel formulas?

推荐答案

把这个放在 A2 中:

Put this in A2:

=AGGREGATE(15,6,(ROW($1:$21)-1)/(COUNTIF($A$1:A1,(ROW($1:$21)-1))=0),RANDBETWEEN(1,22-ROW(1:1)))

然后复制四个单元格.

这篇关于使用公式生成不重复的随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！