vba Excel 中的 RAND() 函数用于蒙特卡罗模拟有多好？

How good is the RAND() function in Excel for Monte Carlo simulation?

提问by Aaron Lockey

I'm implementing a Monte Carlo simulation in 3 variables in Excel. I've used the RAND() function to sample from Weibull distributions (with long tails). The functions applied to the samples are non-linear but smooth (exp, ln, cos, etc). The result for each sample is a pass/fail, and the overall result is a probability of failure.

我正在 Excel 中的 3 个变量中实现蒙特卡罗模拟。我已经使用 RAND() 函数从 Weibull 分布（长尾）中采样。应用于样本的函数是非线性但平滑的（exp、ln、cos 等）。每个样本的结果是通过/失败，总体结果是失败的概率。

I have also implemented this by both numerical integration and Monte Carlo in MathCad, getting the same result both times. MathCad uses (I think) a Mersenne Twister random number generator.

我还在 MathCad 中通过数值积分和蒙特卡罗实现了这一点，两次都得到了相同的结果。MathCad 使用（我认为）Mersenne Twister 随机数生成器。

My excel spreadsheet is getting consistently different results (ie always larger). I have checked the equations are the same.

我的 excel 电子表格总是得到不同的结果（即总是更大）。我已经检查过方程式是否相同。

What random number generator does Excel use, and how good is it? Is it possible that this is the source of my problem? I have assumed the Excel implementations of exp, cos etc are ok.

Excel 使用什么随机数生成器，它有多好？这可能是我问题的根源吗？我假设 exp、cos 等的 Excel 实现没问题。

Finally, is there a way to implement Monte Carlo to mitigate against the (known) poor properties of a particular random number generator? (I've heard of Markov chains, random walks etc, but don't really know much about them)

最后，有没有办法实现蒙特卡罗来减轻特定随机数生成器的（已知）不良属性？（我听说过马尔可夫链、随机游走等，但对它们不太了解）

Many thanks.

非常感谢。

采纳答案by rcs

There is a journal paper on this topic by McCullough (2008): On the accuracy of statistical procedures in Microsoft Excel 2007 (Computational Statistics and Data Analysis)

McCullough (2008) 发表了一篇关于此主题的期刊论文： 关于 Microsoft Excel 2007 中统计程序的准确性（计算统计和数据分析）

Quoting the original article:

引用原文：

The random number generator has always been inadequate. With Excel 2003, Microsoft attempted to implement the Wichmann–Hill generator and failed to implement it correctly. The fixedversion appears in Excel 2007 but this fixwas done incorrectly. Microsoft has twice failed to implement correctly the dozen lines of code that constitute the Wichmann–Hill generator; this is something that any undergraduate computer science major should be able to do. The Excel random number generator does not fulfill the basic requirements for a random number generator to be used for scientific purposes:

1. it is not known to pass standard randomness tests, e.g., L'Ecuyer and Simard's (2007) CRUSH tests (these supersede Marsaglia's (1996) DIEHARD tests—see Altman et al. (2004) for a comparison);
2. it is not known to produce numbers that are approximately independent in a moderate number of dimensions;
3. it has an unknown period length; and
4. it is not reproducible.

随机数生成器一直不够用。在 Excel 2003 中，Microsoft 尝试实施 Wichmann–Hill 生成器，但未能正确实施。在固定的版本出现在Excel 2007中，但这个 修复被错误地进行。微软两次未能正确实现构成 Wichmann-Hill 生成器的十几行代码；这是任何本科计算机科学专业都应该能够做到的事情。Excel 随机数生成器不满足用于科学目的的随机数生成器的基本要求：

1. 不知道通过标准随机性测试，例如 L'Ecuyer 和 Simard (2007) 的 CRUSH 测试（这些测试取代了 Marsaglia (1996) 的 DIEHARD 测试——参见 Altman 等人 (2004) 进行比较）；
2. 不知道在中等数量的维度上产生近似独立的数字；
3. 它的周期长度未知；和
4. 它是不可复制的。

For further discussion of these points, see the accompanying article by McCullough (2008); the performance of Excel 2007 in this area is inadequate.

有关这些要点的进一步讨论，请参阅McCullough (2008)随附的文章；Excel 2007 在这方面的表现是不足的。

回答by Fairly Nerdy

Since this is the top result in Google for "how good is Excel's RAND() function" it is worth updating the answers for later versions of Excel

由于这是 Google 中“Excel 的 RAND() 函数有多好”的最高结果，因此值得为更高版本的 Excel 更新答案

This paper by Guy Melard "On the accuracy of statistical procedures in Microsoft Excel 2010" tested the RAND() function in Excel 2010 and found it to be substantially improved over 2007 or 2003. Microsoft switched from an incorrect Wichmann and Hill generator (2007/2003) to the Mersenne Twister algorithm which has a much, much greater cycle length.

盖伊·梅拉德 (Guy Melard) 的这篇论文“关于 Microsoft Excel 2010 中统计程序的准确性”测试了Excel 2010 中的 RAND() 函数，发现它比 2007 年或 2003 年有了实质性的改进。微软从错误的 Wichmann and Hill 生成器 (2007/ 2003) 到 Mersenne Twister 算法，该算法具有更大的循环长度。

The authors of that paper ran it through "Small Crush", "Crush" and "Big Crush" tests for randomness and it passed nearly all of the tests.

该论文的作者对其进行了“Small Crush”、“Crush”和“Big Crush”随机性测试，并且几乎通过了所有测试。

So while it certainly isn't the same as True random numbers, the RAND() function in Excel 2010, and presumably newer versions, can no longer be considered terrible.

因此，虽然它肯定与真正的随机数不同，但 Excel 2010 中的 RAND() 函数以及可能是较新版本的函数不再被认为是可怕的。

It should be noted however, that Excel 2010 still uses two completely different algorithms for the VBA random number generator, and the RNG that is in the data analysis tool-kit. According to Melard, both of those are still terrible, and in fact the VBA uses the same seed number each each time so produces the same numbers.

然而，应该注意的是，Excel 2010 仍然使用两种完全不同的算法用于 VBA 随机数生成器，以及数据分析工具包中的 RNG。根据 Melard 的说法，这两者仍然很糟糕，事实上 VBA 每次都使用相同的种子编号，因此产生相同的编号。

My biggest complaints with the random numbers in Excel are

我对 Excel 中随机数的最大抱怨是

• You can't set the seed, so the numbers are not reproducible
• The random numbers update every time you press enter/delete, and even if you set calculation options to Manual, they still update when you save the Excel file

• 您无法设置种子，因此数字不可重复
• 每次按回车/删除时随机数都会更新，即使您将计算选项设置为手动，它们仍会在您保存 Excel 文件时更新

回答by James

Paul Wilmott, in his Quantitative Finance book, simply adds up the results of 12 calls to RAND() and subtracts 6 for a good approximation to a Normal variable. Quick n Dirty

Paul Wilmott 在他的 Quantitative Finance 一书中，简单地将 12 次调用 RAND() 的结果相加，然后减去 6 以获得对 Normal 变量的良好近似。快速 n 脏

回答by S.Lott

There are commercial products for this. Google turns up two before I got bored of looking

有用于此的商业产品。在我厌倦寻找之前，谷歌出现了两个

http://www.mathwave.com/articles/random-numbers-excel-worksheets.html

http://www.mathwave.com/articles/random-numbers-excel-worksheets.html

http://www.ozgrid.com/Services/excel-random-number-generator.htm

http://www.ozgrid.com/Services/excel-random-number-generator.htm

回答by Todd Main

RAND()is quite random, but for Monte Carlo simulations, may be a little too random (unless your doing primality testing). Most Monte Carlo simulations just require pseudo-random and deterministic sequences. As part of the Excel Analysis ToolPak RANDBETWEEN()may be all you need for pseudo-random sequences.

RAND()非常随机，但对于蒙特卡罗模拟，可能有点太随机了（除非您进行素性测试）。大多数蒙特卡罗模拟只需要伪随机和确定性序列。作为 Excel 分析工具库的一部分，RANDBETWEEN()您可能只需要伪随机序列。