指定一个平均值，生成一组随机数

祝洪忠-

今天和大家分享一个比较冷门的技巧。

如下图所示，在D1单元格输入指定的平均值，需要在A4:F11单元格区域内生成一组随机数。要求这些随机数的平均值要等于D1指定的值。

在A4单元格输入以下公式：

=IF(AVERAGE($A$4:$F$11)=$D$1,A4,RANDBETWEEN(30,80)/10)

简单说下公式的意思：

RANDBETWEEN函数用于生成指定区间的随机数。

先使用RANDBETWEEN(30,80)生成30至80之间的随机数，再除以10，就得到3至8之间的小数。

AVERAGE($A$4:$F$11)用于计算A4:F11单元格的平均值。

在A4单元格内，用IF函数判断，如果A4:F11单元格的平均值等于D1单元格指定的值，就返回A4的本身的值，否则就生成3至8之间的小数。

由于公式引用了本身的值，所以输入公式后，会弹出警告对话框：

不要着急，还有一个开关需要我们打开。

依次单击【文件】【选项】【公式】，勾选【启用迭代计算】

最多迭代次数可以设置为1000，这里设置的次数越多，得到符合条件随机数的可能性就越大，但是次数过多，会增加计算量。

OK，只要我们修改D1单元格中指定的平均值，就会生成一组随机数，这些随机数的平均值就等于D1单元格的值。

需要注意，生成随机数的区间要根据指定平均值的大小适当调整。

假如指定平均值为10，而将随机数的区间设置为11至20，这个是永远不会得到预期结果的。

你也试试？

作者：祝洪忠

jsahchn

学习了。谢谢楼主分享！

antiash

这个思路第一次看到，我还以为是frequency函数呢，谢谢分享

little-key

这个方法在统计上有用，生成随机种子

willylucy

祝老师，我最近在微信推送上也看见这个教程了。
在这里有个问题想请教一下，如果数据特别多的话，即使迭代3万次也达不到需要的平均值
请问如何解决啊。

祝洪忠-

willylucy 发表于 2018-10-10 17:25
祝老师，我最近在微信推送上也看见这个教程了。
在这里有个问题想请教一下，如果数据特别多的话，即使迭代 ...

1. =IF(AVERAGE($A$4:$F$33)=$C$2,A4,RANDBETWEEN(3500,4500))

复制代码

注意公式中的AVERAGE($A$4:$F$33) 应该是实际的随机数产生范围

willylucy

祝洪忠- 发表于 2018-10-10 20:24
注意公式中的AVERAGE($A$4:$F$33) 应该是实际的随机数产生范围

谢谢祝老师！
根据您指出的错误，我调好后，反复试验了很多次。
然后又发现一个问题：
设定的平均值约接近随机值范围中点的时候，产生的随机数据集合的结果就约容易符合条件。

例如：
我想要平均值是50，
随机范围最好设置成1-100，或者40-60，或者30-70。
这样设置，产生的所有随机数就更容易达到50。
再例如：
我想要平均值是50，
随机范围最好设置成40-100，或者10-60，或者10-70.
这样设置，即使迭代3万次也很难达到平均值是50的结果。

我感觉这个问题可能和以下几点有关系：
1、average这个函数求平均值的方式有关，
2、randberween产生随机数的规律有关（可能并非是真正的随机）
反过来我们可能有办法验证一组数据是不是“造假”得到的...
3、随机范围和需要的数据个数，两者之间必须能成立。
也就是说，不论想得到平均值还是求和值，随机范围和数据个数之间必须
很方便的成立。不然得到的一组随机数就不达标。
比如，我设定的随机个数，需要有小数点后的数字才能成立，
但我设定的随机范围里面不含小数，只有整数，随意试验多次也不能得到结果。要想成功可能需要Randbetween和Rand函数结合使用。
RANDBETWEEN(3500,4500))+rand()或者+(Randbetween（1,100）/100)
但是试验结果还是不行。

最后，请问老师一个问题
如何能一次性真正得到想要的平均值和求和值。

arvinzwgdb

谢谢祝老师！

jd688

我也遇到同样的问题，生成的数据的平均值与固定平均值相差比较大。查阅多方还是找不到解决的办法

fsw0812

我做过一个六位随机数获得指定和的表格，用的是穷举法，不断按F9获得结果