Excel随机数产生函数Rand与RandBetween的用法,含生成固定的、不重复的或指定范围的随机数

在 Excel 中,生成随机数可以用Rand函数或RandBetween函数,其中前者用于生成 0 到 1 的小数随机数,后者用于生成指定范围的整数随机数。Rand函数也可以生成指定范围的随机数,但要用公式 =RAND()*(b-a)+a,且生成的随机数仍然为小数。

直接用Rand函数或RandBetween函数生成随机数,都可能产生重复的随机数,如果要求生成不重复的随机数,需先产生种子再用种子生成随机数,或用 Small + If + CountIf + Row + Int + Rand 函数生成。在默认情况,用Rand函数或RandBetween函数生成的随机数容易发生变化,如果要求生成不变的随机后,需要把它们转为数值。

一、Excel随机数产生函数的语法

(一)Rand函数

1、表达式:RAND()

2、说明:

A、Rand函数用于生成 0 到 1 之间的随机数;如果要生成 a 与 b 之间的随机数,需要使用 RAND()*(b-a)+a;例如生成 1 到 100 的随机数,可以用公式 =RAND()*(100-1)+1。

B、用Rand函数生成的随机数,每次计算工作表时会变为另一个随机数,如果要求不变,输入公式 =RAND() 后,保持编辑状态,按 F9 把公式转为数值,具体见下文的实例。

(二)RandBetween函数

1、表达式:RANDBETWEEN(Bottom, Top)

2、说明:

A、RandBetween函数用于生成任意两个指定数之间的随机数,参数 Bottom 为指定数值范围的最小值、Top 为指定数值范围的最大值。

B、与Rand函数一样,RandBetween函数生成的随机数,每次计算工作表时也会变为另一个随机数,如果要求已生成的随机数不变,输入完公式后,按 F9 把公式转为数值即可。

二、Excel随机数产生函数 Rand 的使用实例

(一)生成 0 到 1 之间的固定不变的小数随机数

选中 A1 单元格,输入公式 =rand(),按回车,返回随机数 0.500716885;双击 A1,按回车,随机数变为 0.140809335,每执行一次公式,随机数会变化;再次双击 A1,按 F9,则公式转为随机数 0.859460188902102,再怎么双击 A1,也不会出现公式,随机数也不会再变化;操作过程步骤,如图1所示:

图1

(二)生成指定范围的随机数

1、假如要生成 100 到 1000 之间的随机数。双击 A1 单击格,把公式 =INT(RAND()*(1000-100)+100) 复制到 A1,按回车,返回随机数816;操作过程步骤,如图2所示:

图2

2、公式 =INT(RAND()*(1000-100)+100) 说明:

用 Rand 函数生成 100 到 1000 之间的随机数可用公式 =RAND()*(b-a)+a,a 为 100,b 为 1000,但用此公式生成的随机数是小数,如果要生成整数的随机数, 要用 Int 函数取整。

三、Excel随机数产生函数 RandBetween 的使用实例

(一)生成指定范围的固定不变的整数随机数

假如要生成 0 到 100 之间的随机数。双击 A1 单元格,把公式 =RANDBETWEEN(0,100) 复制到 A1,按回车,返回随机数 57;双击 A1,按回车,随机数变为 15,每执行一次公式,随机数也会变化;再次双击 A1,按 F9,把公式转为数值 10,再双击 A1,不会再出现公式;操作过程步骤,如图3所示:

图3

(二)生成正负数之间的随机数

假如要生成 -100 到 100 之间的随机数。双击 A1 单元格,把公式 =RANDBETWEEN(-100,100) 复制到 A1,按回车,返回随机数 9;再次双击 A1,按回车,返回随机数 -99;操作过程步骤,如图4所示:

图4

四、Excel随机数产生函数的扩展应用

(一)生成固定不变的随机数

Excel生成固定不变的随机数有两种方法,一种为输入公式后按 F9,把公式转为数值,在上面已经介绍过;另一种方法为把生成的随机数复制为数值,此方法可以批量把生成的随机数转为固定不变的数值,具体方法如下:

双击 A1 单元格,里面是一个生成随机数的公式,按回车,生成新的随机数;框选 A1:B9,按 Ctrl + C 复制;当前选项卡为“开始”,单击窗口左上角的“粘贴”,在弹出的选项中选择“粘贴数值”下的“值”图标,则选中的所有单元格中的生成随机数公式转为数值,再次双击 A1,已经没有公式;操作过程步骤,如图5所示:

图5

(二)生成不重复的随机数

(1)先生成种子,再用种子生成不重复的随机数

1、生成不重复的小数随机数

A、生成种子。双击 A1 单元格,把公式 =RAND() 复制到 A1,按回车,生成一个小数随机数;选中 A1,把鼠标移到 A1 右下角的单元格填充柄上,按住左键,往下拖一直拖到 A10,则所经过单元格都变为同一随机数,按 Ctlr + S 保存,则 A1:A10 更新为不同的随机数;

B、生成不重复随机数。双击 B1,把公式 =RAND() 复制到 B1,输入 *A1,按回车,返回一个新的随机数;选中 B1,把鼠标移到 B1 右下角的单元格填充柄上,鼠标变为黑色加号后,双击左键,则 B2:B10 变为与 B1 一样的随机数,按 Ctrl + S 保存,B2:B10 变为不同的随机数;操作过程步骤,如图6所示:

图6

2、生成不重复的指定范围的整数随机数

A、假如要生成 50 到 100 之间的随机数。双击 A2 单元格,把公式 =RANDBETWEEN(50,100) 复制到 A2,按回车,生成一个种子随机数,用往下拖的办法生成其它种子随机数;

B、双击 B2,把公式 =INT(RANDBETWEEN(50,100)*A2/100) 复制到 B2,按回车,生成一个随机数,用双击 B2 单元格填充柄的方法生成其它随机数;操作过程步骤,如图7所示:

图7

C、公式 =INT(RANDBETWEEN(50,100)*A2/100) 说明:

先用 RANDBETWEEN(50,100) 产生 50 到 100 的随机数,再用该随机数与 A2 中的种子随机数相乘,然后再除 100(由于两个 50 到 100 的数值相乘扩大了 100 倍,因此要缩小 100 倍),最后用 Int 函数取整。

提示:先生成种子再用种子生成随机数不能保证生成的随机数一定不重复,如果要求生成唯一的随机数,需要下文的方法。

(2)用 Small + If + CountIf + Row + Int + Rand 函数生成不会重复的随机数

A、假如要生成 1 到 12 之间的不重复随机数。双击 A2 单元格,把公式 =SMALL(IF(COUNTIF(A$1:A1,ROW($1:$12))=0,ROW($1:$12)),INT(RAND()*(12-ROW(1:1))+1)) 复制到 A2,按 Ctrl + Shift + 回车,生成随机数 6;把鼠标移到 A2 右下角的单元格填充柄上,鼠标变为黑色加号后,往下拖一直拖到 A13,则所经过单元格全变为 6,按 Ctrl + S 保存,则 A2:A13 变为 1 到 12 的随机数;操作过程步骤,如图8所示:

图8

B、公式 =SMALL(IF(COUNTIF(A$1:A1,ROW($1:$12))=0,ROW($1:$12)),INT(RAND()*(12-ROW(1:1))+1)) 说明:

[1]、A$1 表示对列相对引用、对行绝对引用($ 表示绝对引用),当往下拖时,A1 不会变为 A2、A3 等;当往右拖时,A1 会变为 B1、C1 等。A1 表示对列和行都是相对引用,当往下拖时,A1 会变为 A2、A3 等;当往右拖时,A1 会变为 B1、C1 等。

[2]、A$1:A1 用于返回当前单元格到拖到单元格的所有数值;当公式在 A2 时,A$1:A1 返回 A1,A1 为空,因此返回 0;当公式在 A3 时,A$1:A1 变为 A$1:A2,它以数组形式返回 A1、A2 中的数值,即返回 {0;5};其它的以此类推。

[3]、$1 表示对行的绝对引用,当往下拖时,1 不会变为 2、3 等;$12 与 $1 是一个意思;ROW($1:$12) 用于返回 1 到 12 的数组,即返回 {1;2;3;4;5;6;7;8;9;10;11;12}。

[4]、当公式在 A2 时

COUNTIF(A$1:A1,ROW($1:$12)) 变为 COUNTIF(A1,{1;2;3;4;5;6;7;8;9;10;11;12}),A1 为统计个数的范围,数组为条件,执行时,依次取条件数组中的每个元素统计在 A1 中出现的次数,第一次执行取条件数组的第一个元素 1,由于 A1 的数值为 0,因此统计结果为 0;第二次执行取 2,统计结果也为 0;其它的以此类推;最后返回 {0;0;0;0;0;0;0;0;0;0;0;0};

则 COUNTIF(A$1:A1,ROW($1:$12))=0 变为 {0;0;0;0;0;0;0;0;0;0;0;0}=0,接着,取数组中的每个元素与 0 比较,如果相等,返回 True,否则返回 False,最后返回 {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE};

则 IF(COUNTIF(A$1:A1,ROW($1:$12))=0,ROW($1:$12)) 变为 IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},

{1;2;3;4;5;6;7;8;9;10;11;12}),执行时,依次取 IF 条件数组中的每个元素出来,如果为 True,返回第二个数组与之对应的元素,否则返回 False;由于条件数组中全为 True,因此返回 {1;2;3;4;5;6;7;8;9;10;11;12};

1:1 表示对行的相对引用,往下拖时,1:1 会变为 2:2、3:3 等;ROW(1:1) 返回第一行的行号 1;12-ROW(1:1) 返回 11,12 是生成指定范围随机数的上限;

RAND() 用于返回 0 到 1 的小数随机数,假如它返回 0.401319158944677,则 INT(RAND()*(12-ROW(1:1))+1) 变 INT(0.401319158944677*11+1),进一步计算变为 INT(4.414510748391450+1),接着,用 Int 函数取整,结果为 5;

则公式变为 =SMALL({1;2;3;4;5;6;7;8;9;10;11;12},5),最用 Small 函数返回数组中第 5 个最小的数,即返回 5。

[5]、公式在 A3 时

COUNTIF(A$1:A2,ROW($1:$12)) 变为 COUNTIF(A$1:A2,{1;2;3;4;5;6;7;8;9;10;11;12}),执行时,同样依次取条件数组中的每个元素统计在 A1:A2 中的个数,由于 A1 为 0,A2 为 5,因此 A1:A2 返回 {0;5};第一次执行取条件数组中的 1,由于数组 {0;5} 中没有 1,因此返回 0;第二次取 2,同样返回 0;其它的以此类推,最后返回 {0;0;0;0;1;0;0;0;0;0;0;0};

则 COUNTIF(A$1:A2,ROW($1:$12))=0 变为 {0;0;0;0;1;0;0;0;0;0;0;0}=0,计算结果为{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE};

则 IF(COUNTIF(A$1:A2,ROW($1:$12))=0,ROW($1:$12)) 变为 IF({TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12}),进一步计算返回 {1;2;3;4;FALSE;6;7;8;9;10;11;12};剩余步骤与公式在 A2 时一样。

C、如果要生成其它范围的随机数(如 5 到 10 的随机数),只需把上面的公式改一下即可,公式可以这样写:

=SMALL(IF(COUNTIF(B$5:B5,ROW($5:$10))=0,ROW($5:$10)),INT(RAND()*(10-ROW(5:5))+1))

把公式复制到 B6,按回 Ctrl + Shift + 回车,生成一个随机数,然后用往下拖的方法一直拖到 B11,按 Ctrl + S 保存,则生成 5 到 10 的不重复随机数;操作过程步骤,如图9所示:

图9

(0)

相关推荐