问题描述
我想在 Excel 中使用 RAND() 函数生成一个介于 0 和 1 之间的随机数.
但是，我希望 80% 的值介于 0 和 0.2 之间，90% 的值介于 0 和 0.3 之间，95% 的值介于 0 和 0.5 之间，等等.
这让我想起我曾经上过一门应用统计学课程，但不是课程中的实际内容......
如何使用 Excel 公式实现此结果的最佳方法.或者，这种统计计算是什么/我可以谷歌搜索的任何其他指针.
==================
用例:
我有一列仪表读数，我想复制 7 次(每一列代表一个新的月份).每列有 55 000 行.虽然每个月的仪表读数都需要变化，但作为时间序列时，每个仪表编号应该有 7 个实际读数.
目的是生成真实的数据以转化为热图(即标记外围仪表读数)
我认为没有完全符合您要求的公式.我会使用一个非常简单的解决方案:
- 使用
=RANDBETWEEN(0,20)/100 生成 80% 的数据 - 使用
=RANDBETWEEN(20,30)/100 生成 10% 的数据 - 使用
=RANDBETWEEN(30,50)/100 生成 5% 的数据 - 等等
您可以通过修改参数轻松更改生成数据的精度，例如:=RANDBETWEEN(0,2000)/10000 将生成小数点后最多 4 位的数据.>
更新
对用例使用正态分布，例如:
=NORMINV(RAND(), 20, 5)其中 20 是平均值，5 是标准偏差.
I want to use the RAND() function in Excel to generate a random number between 0 and 1.
However, I would like 80% of the values to fall between 0 and 0.2, 90% of the values to fall between 0 and 0.3, 95% of the values to fall between 0 and 0.5, etc.
This reminds me that I took an applied statistics course once upon a time, but not of what was actually in the course...
How is the best way to go about achieving this result using an Excel formula. Alternatively, what is this kind of statistical calculation called / any other pointers that I can Google around for.
=================
Use case:
I have a single column of meter readings, which I would like to duplicate 7 times (each column for a new month). each column has 55 000 rows. While the meter readings need to vary for each month, when taken as a time series, each meter number should have 7 realistic readings.
The aim is to produce realistic data to turn into heat maps (i.e. flag outlying meter readings)
I don't think that there is a formula which would fit exactly to your requirements. I would use a very straightforward solution:
- Generate 80% of data using
=RANDBETWEEN(0,20)/100 - Generate 10% of data using
=RANDBETWEEN(20,30)/100 - Generate 5% of data using
=RANDBETWEEN(30,50)/100 - and so on
You can easily change the precision of generated data by modifying the parameters, for example: =RANDBETWEEN(0,2000)/10000 will generate data with up to 4 digits after decimal point.
UPDATE
Use a normal distribution for the use case, for example:
=NORMINV(RAND(), 20, 5)
where 20 is a mean value and 5 is a standard deviation.
这篇关于如何使用 Excel 在正态分布中生成随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！