Generate random numbers which do not repeat by usi

问题:

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.

E.g.

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.

How to achieve that using Excel formulas?

回答1:

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)))

And copy down four cells.