VBA rand如何使用上限和下限生成随机数？

so maybe this is redundant, maybe it's like asking why most humans are born with 5 fingers, the short answer in the end is always: because that's how it is and it just works, but I hate that answer and dammit I want to know how Rnd() function in VBA works.

所以也许这是多余的,也许就像问为什么大多数人天生就有5个手指,最后简短的回答总是:因为它就是这样,它只是起作用,但我讨厌那个答案并且该死我想知道如何VBA中的Rnd()函数有效。

The MSDN for Ms Office Excel says that RND is defined as:

Office Office Excel的MSDN表示RND定义为:

Rnd[(number)] 'The optional number argument is a Single or any valid numeric expression.

Rnd [(number)]'可选的数字参数是单个或任何有效的数字表达式。

It goes on to say

它继续说

"The value of number determines how Rnd generates a random number: For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence."

“number的值确定Rnd如何生成随机数:对于任何给定的初始种子,生成相同的数字序列,因为对Rnd函数的每次连续调用都使用前一个数字作为序列中下一个数字的种子。”

followed by this:

接下来是这样的:

To produce random integers in a given range, use this formula:

要在给定范围内生成随机整数,请使用以下公式:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Int((上限 - 下限+ 1)* Rnd +下限)

so for example:
Dim MyValue MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

例如:Dim MyValue MyValue = Int((6 * Rnd)+ 1)'生成1到6之间的随机值。

But how does that work? where are these numbers coming from? why does 6 * Rnd + 1 get you random number between 1 and 6, but 6 * Rnd + 5 gets you a number between 5 and 10?

但是这有什么作用呢?这些数字来自哪里?为什么6 * Rnd + 1可以获得1到6之间的随机数,但6 * Rnd + 5可以获得5到10之间的数字?

furthermore, if it was so apparent to the creators of VBA what formula to use to successfully narrow this down to a specific range, why not just have the RND function come with optional Ubound and Lbound arguments? I can't be the only one looking at that formula going what in the world is that?

此外,如果VBA的创建者使用什么公式来成功地将其缩小到特定范围,那么为什么不让RND函数带有可选的Ubound和Lbound参数呢?我不可能是唯一一个看着这个公式走向世界的人吗?

At the end of the day it works of course fine for any of my pseudo random number needs and maybe I'm looking a gift horse in the mouth but still!

在一天结束时,它对我的任何伪随机数的需求当然都很好,也许我正在寻找一个礼物马在嘴里,但仍然!

EDIT

It occurs to me that this question might be based in Math itself. if you take a small integer what functions do you apply to make that integer fit in a specified range.. so can anyone explain how this formula works?

在我看来,这个问题可能基于数学本身。如果你取一个小整数,你应用什么函数来使整数适合指定的范围..所以任何人都可以解释这个公式是如何工作的?

4 个解决方案

#1

Here's a step by step guide:

这是一步一步的指南:

Rnd gives a random decimal between 0 and < 1

Rnd给出0到<1之间的随机小数

6 * Rnd gives a random decimal between 0 and < 6

6 * Rnd给出0到<6之间的随机小数

Int(6 * Rnd) round it down so you get a random value between 0 and 5

Int(6 * Rnd)将其向下舍入,以便获得0到5之间的随机值

It's very common to generate a random number between a lower and upper bound. Excel does have a RANDBETWEEN function to do this:

在下限和上限之间生成随机数是很常见的。 Excel确实有一个RANDBETWEEN函数来执行此操作:

Value = WorksheetFunction.RandBetween(1, 6)

Edit: now let's fit that into Lbound and Ubound (assuming both are integers and Lbound < Ubound)

编辑:现在让它适合Lbound和Ubound(假设两者都是整数,Lbound )

First, define:

n = ubound - lbound

Next, we will rewrite the MSDN formula slightly:

接下来,我们将略微重写MSDN公式:

   Int((ubound - lbound + 1) * Rnd + lbound)
== Int((ubound - lbound + 1) * Rnd) + lbound
== Int(((n + 1) * Rnd)              + lbound

From #3, we know that Int(((n + 1) * Rnd) gives a random integer between 0 and n. So when you add that random number to the lowerbound, you get a number between the lowerbound and the upperbound;

从#3开始,我们知道Int(((n + 1)* Rnd)给出一个介于0和n之间的随机整数。因此,当你将该随机数添加到下限时,你会得到一个介于下限和上限之间的数字;

   Int(((n + 1) * Rnd) + lbound
== 0...n               + lbound
== lbound...ubound

#2

using the formula like RndBetween like make_random_old = CLng((up - down + 1) * Rnd + down)

使用像RndBetween这样的公式,如make_random_old = CLng((上 - 下+ 1)* Rnd +下)

It is extremely wrong (30 minutes debugging of my life was the price to understand it!) :)

这是非常错误的(30分钟调试我的生活是理解它的价格!):)

You can check why, using the Sub TestMe here:

你可以在这里使用Sub TestMe检查原因:

Public Sub testme()

    Dim l_counter       As Long
    Dim l_random        As Long

    For l_counter = 0 To 10000
        l_random = make_random_old(0, 2)
        If l_random = 3 Then Debug.Print l_random
    Next l_counter

    Debug.Print "END"

End Sub

Public Function make_random(down As Long, up As Long) As Long

    make_random = CLng((up - down + 1) * Rnd + down)

    If make_random > up Then make_random = up
    If make_random < down Then make_random = down

End Function

Public Function make_random_old(down As Long, up As Long) As Long

    make_random_old = CLng((up - down + 1) * Rnd + down)

End Function

As a workaround for some random in RndBetweem, use make_random - the second function in the code. It will save you lots of problems. :)

作为RndBetweem中一些随机的解决方法,请使用make_random - 代码中的第二个函数。它可以为您节省很多问题。 :)

#3

This may make it a bit more clear: Rnd returns a random number in 0 to <1 range (it is a Single -- aka a fraction, so you have all kinds of 0.3948, 0.001, 0.8, etc. -- uniformly distributed pseudo random numbers). The argument in the function is intended for "ease of use" in formulas and algorithms, but arguably not a good fit for a casual user.

这可能会使它更清楚:Rnd返回0到<1范围内的随机数(它是单个 - 也就是一个分数,所以你有各种0.3948,0.001,0.8等 - 均匀分布的伪随机数)。函数中的参数旨在用于公式和算法中的“易用性”,但可以说不适合临时用户。

In general, Rnd actually follows a de-facto standard of how random number generator API is exposed in many, if not all programming languages (i.e. generating a random fraction from 0.0 to <1.0). So if you go from VBA to Python to Java to C -- you'll see a striking resemblance in terms of how it works.

通常,Rnd实际上遵循许多(如果不是全部)编程语言(即,从0.0到<1.0生成随机分数)的随机数生成器API如何暴露的事实标准。因此,如果你从VBA到Python再到Java再到C - 你会看到它在工作方式上有惊人的相似之处。

Having said that, many languages (for example, Python) provide higher level wrappers to the core Rnd function so it will be easier to use in basic scenarios. VBA is a pretty old language -- one that is rarely updated by Microsoft (it is a blessing and a curse), so if you need a helper function you'd have to do it yourself.

话虽如此,许多语言(例如,Python)为核心Rnd函数提供了更高级别的包装器,因此在基本方案中使用它会更容易。 VBA是一种非常古老的语言 - 很少被微软更新(这是一种祝福和诅咒),所以如果你需要辅助功能,你必须自己动手。

Here what you can use instead:

在这里您可以使用的代替:

Function RndBetween(lowerbound As Int, upperbound As Int) As Int
    RndBetween = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Function

And in your code it becomes simple:

在你的代码中它变得简单:

RndBetween(1, 6)

After some time you'll have a module of the helper functions that will make life easier and code much more readable. Until then... * :-)

一段时间后,您将拥有一个辅助函数模块,这将使生活更轻松,代码更易读。在那之前...... * :-)

#4

@Code Different has the correct answer already.This is just for people who don't get it on the first glance

@Code Different已经有了正确答案。这只适用于第一眼看不到它的人

Excel only generates a random number between 0 to 1

Excel仅生成0到1之间的随机数

By multiplying the random number generated in step 1 by a number x we can get a value between 0 and x

通过将步骤1中生成的随机数乘以数字x,我们可以得到0到x之间的值

So how does

那怎么办?

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

Int((上限 - 下限+ 1)* Rnd +下限)

generate a random number between upper and lower bound

在上限和下限之间生成一个随机数

In the last step you provided the number x to which the random number generated in step 1 was multiplied.Here,to generate this number x you do upperbound - lowerbound so that a random_number from 0 to the difference of two numbers i.e (upperbound - lowerbound) is produced.Now if you add the smaller number i.e. lowerbound to random_number it will generate a sum greater than the lowerbound & lesser than upperbound

在最后一步中,您提供了在步骤1中生成的随机数乘以的数字x。在这里,要生成此数字x,您执行上限 - 下限,以便random_number从0到两个数字的差异即(上限 - 下限)生成。现在,如果你将较小的数字,即lowerbound添加到random_number,它将生成一个大于下限和小于上限的总和

#1