VBA rand如何使用上限和下限生成随机数? [英] How does VBA rand generate random numbers using the upperbound and lowerbound?

问题描述

所以这也许是多余的，也许就像问为什么大多数人天生用五根手指，最后的简短答案总是:因为那样，它才行得通，但我讨厌这个答案，该死的我想要了解VBA中的Rnd()函数如何工作.

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.

用于Office Excel女士的MSDN表示RND定义为:

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

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

接着说

"number的值确定Rnd如何生成随机数:对于 任何给定的初始种子，都会生成相同的数字序列，因为 Rnd函数的每个后续调用均使用前一个数字作为 序列中下一个数字的种子."

"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."

其次是:

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

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

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

例如:
Dim MyValue MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

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

但是那是怎么工作的呢?这些数字从哪里来?为什么6 * Rnd + 1会为您提供1到6之间的随机数，而6 * Rnd + 5会为您提供5到10之间的数字?

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?

此外，如果对于VBA的创建者来说显而易见的是，使用什么公式可以成功地将其缩小到特定范围，为什么RND函数不仅仅带有可选的Ubound和Lbound参数?我不是唯一一个看那个公式的人，那到底是什么?

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?

最终，对于我的任何伪随机数需求来说，它当然都可以正常工作，也许我在嘴里看着一匹礼品马，但仍然如此！

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!

编辑

在我看来，这个问题可能基于数学本身.如果您选择一个小整数，您将应用什么函数使该整数适合指定范围..有人可以解释此公式的工作原理吗?

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?

推荐答案

以下是逐步指南:

1. Rnd给出一个介于0和<之间的随机小数. 1
2. 6 * Rnd给出0到<之间的随机小数. 6
3. Int(6 * Rnd)将其四舍五入到 ，这样您将获得0到5之间的随机值

1. Rnd gives a random decimal between 0 and < 1
2. 6 * Rnd gives a random decimal between 0 and < 6
3. Int(6 * Rnd) round it down so you get a random value between 0 and 5

在下限和上限之间生成一个随机数是很常见的. Excel确实具有RANDBETWEEN函数来做到这一点:

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

Value = WorksheetFunction.RandBetween(1, 6)

---

现在，让我们将其放入Lbound和Ubound(假设它们都是整数和Lbound < Ubound)

---

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

首先，定义:

n = ubound - lbound

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

Next, we will rewrite the MSDN formula slightly:

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

从#3，我们知道Int(((n + 1) * Rnd)给出一个介于0和n之间的随机整数.因此，当您将该随机数添加到下限时，您会在下限和上限之间获得一个数字；

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;

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

这篇关于VBA rand如何使用上限和下限生成随机数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！