vba 如何在excel中按照对数正态分布创建随机数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23699738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a random number following a lognormal distribution in excel?
提问by ciso
If I want a set of data with a mean of 10 and a standard deviation of 5, as an example, it can be calculated with a normal distribution using this formula:
如果我想要一组平均值为 10 且标准差为 5 的数据，例如，可以使用以下公式通过正态分布计算：
NORMINV(RAND(), Mean, Stdev)
NORMINV(RAND(), 10, 5)
This results in numbers ranging from roughly -5 to to 25 with a frequency distribution that looks normal with most values centerred around the mean.
这导致数字范围从大约 -5 到 25，频率分布看起来正常，大多数值都集中在平均值附近。
How to get a similar set of numbers but have them follow a lognormal distribution, one where there is a higher probability of larger numbers and no probability of going below zero?
如何获得一组相似的数字，但让它们遵循对数正态分布，其中较大数字的概率较高且不可能低于零？
I would like to do this using Excel without addons.
我想在没有插件的情况下使用 Excel 来做到这一点。
I tried:
我试过：
LOGINV(RAND(), Mean, Stdev)
LOGINV(RAND(), 10, 5)
.. but this creates very large numbers (to the power of 20, etc) that look meaningless.
.. 但这会产生非常大的数字（以 20 的幂等），看起来毫无意义。
回答by duncan
Lognormal is e^N(m,s). So the answer, using your construct for normal, would be
对数正态是 e^N(m,s)。所以答案，使用你的构造正常，将是
=EXP( NORMINV(RAND(),Mean,Stdev) )
However that will give you very large values. Next step is to scale the mean and standard deviation. In pseudocode,
但是，这将为您提供非常大的值。下一步是缩放均值和标准差。在伪代码中，
scaled mean = ln( m^2 / sqrt( m^2 + s^2 ))
scaled sd = sqrt( ln(( m^2 + s^2 ) / m^2 ))
