excel中如何实现随机比例分配
acerverbo 的回答不错;方法也比较巧妙。但是忽略一点,就是楼主对分配后的数列的方差(即整齐程度,如果楼主想避免太多的较小值低于10,较大值高于1000等极端情况的话)没有做考虑。
我的建议是在acerverbo 的回答的基础上做一定的修改。
A1
输入
=ROUND(2*RAND()*5000/31,0)
A2输入
=ROUND(2*RAND()*(5000-SUM($A$1:$A1))/(31-ROW(1:1)),0)
A3~A30
从A2拖拉下来拖至A30
A31输入
=5000-(SUM(A1:A30))
然后在A32处用=SUM(A1:A31)恒等于5000做一个校验,...全部
acerverbo 的回答不错;方法也比较巧妙。但是忽略一点,就是楼主对分配后的数列的方差(即整齐程度,如果楼主想避免太多的较小值低于10,较大值高于1000等极端情况的话)没有做考虑。
我的建议是在acerverbo 的回答的基础上做一定的修改。
A1
输入
=ROUND(2*RAND()*5000/31,0)
A2输入
=ROUND(2*RAND()*(5000-SUM($A$1:$A1))/(31-ROW(1:1)),0)
A3~A30
从A2拖拉下来拖至A30
A31输入
=5000-(SUM(A1:A30))
然后在A32处用=SUM(A1:A31)恒等于5000做一个校验,以防操作失误;
通过在随意一个空白处 =stdev(A1:A31) 计算分配后数据的标准差,可以发现修改后的算法能稳定的获得一个较小的标准差,不停的刷新的计算,验证很多次结论是其标准差值在60-130之间波动,而原算法的标准差一般主要在170-250间波动,而且第31项的值经常出现过于偏大的情况;
原理在于rand()()生成[0,1]区间的均匀分布值,5000/31=169(通过round(169。
29,0)函数四舍五入取整)的旨意在于获得一个参考平均值,然后以169。29乘以rand()()使之波动,波动范围在0至169,均值大概在75; 2*rand()()的妙处在于随机生成[0,2]区间的均匀分布值,乘以169之后的值围绕均值169左右波动,避免了过小的极端情况,另外也可以避免最后一项过大的极端情形。
over。收起