Question

随机数 和定值 excel公式

excel公式问题，我需要N（可更改）个随机数，要求整数或者指定几位小数（可以更改），然后每个随机出来的数据要在一个指定的范围内（可更改），m<随机数<n （m和n均为正数，但不一定是整数），最后N个随机数的和为M（M指定可更改）
麻烦回答的上传做好的excel表格，谢谢

还是没有成功，付一个失败的，点击后程序就会无响应，但成功的机会也有……





Answer 1

　　1. 先在选项中把启用迭代计算打开，并设置较高的循环计算次数，如1000次以上，再把要求的数据一个个在表格中列出，然后按你的要求在公式中一个个引用它们。下面是一个例表，完全按你的要求做的表格，应该能满足你的要求，虽然要求有些苛刻。如果随机数的和与要求的和有差别，可多按几次F9进行手动计算，直到两者结果相同。

　　2. 如图1，在A列和B列中输入各种可能变化的条件，B6中输入公式：

　　=SUM(E:E)

　　以求出E列中随机数的和

　　3. 选择B5单元格，设置数据有效性，以保证输入要求的和值大于最小随机数与随机数个数的积并小于最大随机数与随机数个数的积，数据有效性设置中，自定义，公式：

　　=AND($B$5>=$B$3*$B$1,$B$5<=$B$4*$B$1)，如图2。

　　4. 在D1中输入公式：

　　=IF(ROW()>$B$1,"",ROW())

　　并设置单元格格式，如图2，以列出随机数的序号

　　5. 在E1中输入公式：

　　=IF(D1="","",IF(OR(E1="",ABS(SUM(E:E)-$B$5)>0.0001),RANDBETWEEN($B$3*POWER(10,$B$2),$B$4*POWER(10,$B$2))/POWER(10,$B$2),E1))

　　以产生满足B列条件的随机数。

　　6. 选择D1：E1，向下拖拉到若干行，多些行没关系，行数大于B1中规定的随机数个数时，会自动填充空值。

　　一般一次能得出结果，如果与要求的和有差别，按F9手动计算，数次后一定会达到一致。

追问

对随机数的个数 以及和的范围没有要求么？ 我输入N=51 和非整数好像不能用

追答

应该是可以的，但N应该是整数。你说的非整数是指哪个数？
还有两点要注意，一是给定的和如果距最大数和最小数的平均值与个数的积较远的话，可能需要手动计算次数相当多才能达到随机数的和与给定的和相一致，特别是随机数个数越多，这种效果越明显。原因是大量的随机数均值总是接近于中值的。这个可以对公式来一个加权计算以加快收敛，但比较复杂，就没做这个步骤了。二个就是数据量越大，越难收敛，特别是如上所说给定和偏离均值与个数的乘积越远，越难收敛。解决的办法只有对偏离值在公式中做一个加权处理。

Answer 2

=ROUND(RAND()*($A$2-$A$3)+$A$3,$A$1)
A1中的数表示保留位数
A2中的数表示n
A3中的数表示m
下拉控制个数

至于你说的和为M，这个有点问题，
数据个数和范围将决定总和，不可能随便制定M

追问

个数和范围是给出的

追答

我明白你的意思，但是个数范围总和
要有一个是确定的，才能编公式
就好像一元二次方程，如果abc都是未知的，那就是一个任意抛物线，连范围都没有
至少有一个值，才能模拟大致位置开口方向什么的

如果你非要一个公式
在D1输入
=IF(AND($E$2>A2,$E$2<A3),IF(MIN($D:$D,1)=D1,ROUND(RAND()*($A$2-$A$3)+$A$3,$A$1)+1,ROUND(RAND()*($A$2-$A$3)+$A$3,$A$1)),ROUND(RAND()*($A$2-$A$3)+$A$3,$A$1))
下拉
在E1输入
=SUM(D:D)
E2输入
=A4-E1
工具-选项-重新计算-迭代计算，勾选迭代计算
A1中的数表示保留位数
A2中的数表示n
A3中的数表示m
A4中的数表示M
下拉控制个数
（注：E2中的数也是这个数组中的一个）

不过迭代计算有时候会很花时间，而且如果你输入的个数、范围、总和是不合理的，是无论如何也算不出结果的

PS：另一个人那个是扯，数字个数多了之后后面的数会趋近于零，rounddown之后就只剩0了

追问

谢谢你，总和M、随机数的个数N、范围上下限m和n都是已知，我只是用字母来代替实际数据，方便理解

追答

我后面说的这个方法你没试么？是可行的啊

虽然偶尔E2的数会超标，找个空单元格按DELETE就行了，一般试两三次就能成功了

追问

和过大不可以么？ 提示出现错误， 48组 最小21.3最大22.9 和1070.4

追答

48×22.9=1099.2>1070.4
也就是说有可能会出错

Answer 3

Excel 中随机数函数是 rand()， 它是产生0～1 之前的随机数
要产生一组数其总和为一个定值，那么这一组数任何一个都不可以大于这个定值
首先产生第一个数（假设那个定值在G1单元格） 如A1单元格 =ROUNDDOWN(RAND()*G1,0)
这样A1产生一个小于那个定值的随机整数。
再产生第二个数，要求这个值需小于 定值减A1单元格的值 如B1单元格 =ROUNDDOWN(RAND()*(G1-A1),0)
再产生第三个数，要求这个值需小于 定值减去前两个数后的值 如C1单元格 =ROUNDDOWN(RAND()*(G1-SUM(A1:B1)),0)
以此类推 最后一个值 是等于定值减去前面的数 如 D1单元格 =G1-SUM(A1:C1)

追问

随机数的范围没有，这种只能保证和的要求，谢谢你