使用没有VBA的公式在Excel中生成唯一的随机数 [英] Generating unique random numbers in Excel using formula without VBA
问题描述
假设A1是空白或文本标题,你可以使用这个数组公式在A2
= SMALL(IF(COUNTIF(A $ 1:A1,ROW(INDIRECT(1:51)) - 1)= 0,ROW (INDIRECT(1:51)) - 1),INT(RAND()*(51-ROWS(A $ 2:A2)+1)+1))
使用CTRL + SHIFT + ENTER确认,以便在公式栏中的公式周围出现像{和}的花括号,现在复制到A6
将从0到50生成5个整数,而不重复
I have to generate five distinct random numbers from 0 to 50 using only formulas and not VBA. How should I do it?
Assuming A1 is blank or a text header you can use this "array formula" in A2
=SMALL(IF(COUNTIF(A$1:A1,ROW(INDIRECT("1:51"))-1)=0,ROW(INDIRECT("1:51"))-1),INT(RAND()*(51-ROWS(A$2:A2)+1)+1))
confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar, now copy to A6
That will generate 5 integers from 0 to 50 without repeats
这篇关于使用没有VBA的公式在Excel中生成唯一的随机数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!