星期六, 2月 24, 2024

6 个方法帮你搞定EXCEL随机数生成

来源:数据化管理

原标题:Excel 模拟数据来分析,6 个方法帮你搞定!

 

在日常工作中,并不是每个人都需要随机数字,但在一些场景中你又必须要有随机数字 😂,例如你想生成一些临时练手的随机数据,或者想用 Excel 做一个随机抽奖功能,或者你想做个练习算 24 点的游戏……

本文给大家介绍几种生成随机数字的方法,可满足你大多数场景的需要。

RAND 函数

这是 Excel 中生成随机数字最简单的方法:使用RAND函数,因为它不需要输入任何参数!

生成一个 0 到 1 之间的随机数字

=RAND( )
6 个方法帮你搞定EXCEL随机数生成
RAND函数

生成两个数字之间的任意随机数

如果你希望是包括小数在内的任意随机数,使用RAND函数也是可以搞定的,具体的公式模板是= RAND() * ( 上限 - 下限 ) + 下限,例如你要生成[1, 10)之间的任意随机数,使用以下公式即可

= RAND() * (10 - 1) + 1

当然你可以简洁地写成=RAND() * 9 + 1,但在初次书写时,你按公式模板去写会更清楚。

生成两个数字之间的随机整数

如果你希望是包括小数在内的任意整数,使用RAND函数还是可以搞定的,具体的公式模板是= ROUND( RAND() * (上限 - 下限) + 下限, 0),例如你要生成[1, 10)之间的任意整数,使用以下公式即可

= ROUND( RAND() * (10 - 1) + 1, 0)

其实就是对随机数的结果做一个四舍五入,保留 0 位小数,结果就是整数了。但由于生成的随机数是离散的,四舍五入后的结果有可能出现重复值,这具体要看范围的上下限间距大小。

RANDBETWEEN 函数

对于只是生成区间的随机整数,那么RANDBETWEEN就是最佳选择了,因为使用起来非常简单,例如生成[1, 10]之间的随机整数,只需要这样写:

= RANDBETWEEN(1, 10)

这就比上面介绍的 RAND 函数要简单多了,如果需要包括小数,不妨可以这样 = RANDBETWEEN(1, 10) + RAND(),这也是超级方便了!

RANDARRAY 函数

这是在 Excel 2021 中新推出的函数,先看下它的语法:

= RANDARRAY([rows], [columns], [min], [max], [whole_number])
  • rows是要返回的行数

  • columns 是要返回的列数

  • min 是随机数的最小值

  • max是随机数的最大值

  • whole_number为 TRUE 可返回整数,FALSE 可返回小数

然后看几个示范你肯定明白了:

= RANDARRAY(4, 3)
6 个方法帮你搞定EXCEL随机数生成
生成 4 行 3 列的 1 以内的随机小数
= RANDARRAY(4, 3, 1, 10)
6 个方法帮你搞定EXCEL随机数生成
生成 4 行 3 列的 1 至 10 区间的任意随机数
= RANDARRAY(4, 3, 1, 10, TRUE)
6 个方法帮你搞定EXCEL随机数生成
生成 4 行 3 列的 1 至 10 区间的任意随机整数

很多朋友可能觉得奇怪,为什么上面三张图的随机数区域外层都有一层框线?因为它们都是属于一个公式生成出来的,属于一个整体,当你按 F9 重算进,所有的单元格也都会一起变,这是 Excel 2021 的新特性。当你需要生成区域的随机数,RANDARRAY函数就超级方便了,升级新版本的体验还是很不错的!

使用分析工具生成随机数

什么是分析工具?这是 Excel 里的一个加载项,使用它你不写公式也能生成随机数。

我们要先在 Excel 中调出分析工具加载项,方法是:开发工具 → Excel 加载项 → 勾选分析工具库,具体见下图:

6 个方法帮你搞定EXCEL随机数生成
加载分析工具,不知道开发工具的朋友可以百度

然后我们就可以运行分析工具库,使用里面的随机数发生器功能了:

6 个方法帮你搞定EXCEL随机数生成
分析工具-随机数发生器

确定后,会弹出一个窗口,你要在里面设置随机数的一些参数:

  • 变量个数,指你要生成几列随机数?如果留空,输出区域的所有列都会生成随机数

  • 随机数个数,指你要生成几行随机数?如果留空,输出区域的所有行都会生成随机数

  • 分布,指生成随机数的分布规律,从下拉列表中去选择即可,常用的如均匀分布、正态分布、离散分布等都可以选。

  • 参数,指按你选的分布规律需要设置相应的参数,例如下图中选择的是均匀分布,就应输入随机数区间的上下限

  • 随机数基数,这个基数将会是随机数算法的起点,如果你是使用相同的种子,那么程序将产生相同的随机数。如果留空,它将从计时器事件中获取种子值。

  • 输出区域,就是指你要把随机数输出到的单元格区域范围,如果你在上面指定了变量个数随机数个数,则在这里只需要选定结果输出区域的左上角单元格即可;反之你就需要指定整个输出的单元格范围,例如我下图的示范。请注意,单元格中的原有数据将被覆盖!

  • 新工作表组,选中后将会自动生成一个新工作表,并将结果输出到单元格 A1 中,旁边的框中可以指定新工作表名称,留空则用默认名称

  • 新工作簿,就是把随机数结果输出至一个新工作簿中,将会输出到第一张工作表的单元格 A1 中

6 个方法帮你搞定EXCEL随机数生成
在A12:D19范围中生成均匀分布的1至100的任意随机数

生成唯一的随机数

例如你可能希望生成 1 至 10 范围内的 5 个随机整数,但是必须不能有重复值出现。这应该如何解决?可以分两步解决,第一步是正常地使用 RANDBETWEEN 生成随机整数,第二步是对第一步的随机数进行排名。

6 个方法帮你搞定EXCEL随机数生成
排名法去重取随机值

第二步排名时用的公式是:

= RANK.EQ(B3, $B$3:$B$7) + COUNTIF($B$3:B3, B3) - 1

RANK.EQ函数是对数值进行排名,遇到相同数字时会出现并列排名,所以再加上滚动的COUNTIF函数对并列排名进行补偿即可实现不重复排名了。因为 B 列是随机变化的,然后我再根据 B 列进行不重复排名也是随机变化的,那么最终你要的 5 个随机整数就是上图中 2,1,5,3,4 这个序列了。

SEQUENCE + SORTBY + RANDARRAY + INDEX

如果你觉得上面的解决方法不优雅,那来看看 Excel 2021 版本的解决方法,使用体验会更好!

=INDEX(
    SORTBY(
        SEQUENCE(10),
        RANDARRAY(10)
    ),
    SEQUENCE(5)
)
6 个方法帮你搞定EXCEL随机数生成
Excel 2021版本用函数生成唯一随机数

整体的思路是先生成一个 1 至 10 的序列,然后进行随机排序,再取随机排序后的前 5 个数字出来即可。

用 VBA 生成随机数

有些朋友是在使用 VBA 设计数据产品的,以下就补充一些 VBA 生成随机数的办法,供大家参考使用。我们可以调用上面介绍的函数:RAND、RANDBETWEEN、RANDARRAY,例如你要在A1:A5中生成随机整数:

Sub CreateRandomNumbers()
    Dim rng As Range
    For Each rng In ActiveSheet.Range("A1:A5")
        rng.Value = Application.WorksheetFunction.RandBetween(1, 10)
    Next rng
End Sub

VBA 怎么生成唯一值的随机数?可以用字符串先连接起已生成的数字,再判断新数字是否已存在于旧字符串中,如果存在的话就重复再跑一次即可:

Sub CreateRandomUniqueNumbers()
    Dim rng As Range, randNumber As Integer, randNumberString As String
    For Each rng In ActiveSheet.Range("A1:A5")
Repeat:
        randNumber = Application.WorksheetFunction.RandBetween(1, 5)
        If VBA.InStr(randNumberString, randNumber) Then GoTo Repeat
        rng.Value = randNumber
        randNumberString = randNumberString & randNumber & "|"
    Next rng
End Sub

当然也可以使用字典或集合去存储已生成的随机数。

最后

本文给大家介绍了我知道的在 Excel 中生成随机数的所有方法了,有整数、小数,有分布规律的随机数,唯一随机数,相信应该可以满足大多数的使用场景了。

但是,你要知道以上这些数字其实都是由算法生成的伪随机数,它们并不算真正意义的随机数。如果你要探寻真正的随机数,就必须借助电脑环境以外的随机事件来驱动了!

例如掷硬币、骰子?电子元件的噪音?核裂变?

当然这就扯远了,对于构建一般模拟和统计分析的需求来说,Excel 的随机数就能满足使用了!

好吧,文章结束了,希望对大家有帮助!

相关文章

财保研习社高端交流群

spot_img

热 门 文 章