生成 N 个随机数，其和为常数 K - Excel [英] Generate N random numbers whose sum is a constant K - Excel

问题描述

如何在 Excel 中生成这些数字.

How can I generate those numbers in Excel.

我必须生成 8 个随机数，总和总是 320.我需要大约 100 组左右.http://en.wikipedia.org/wiki/User:Skinnerd/Simplex_Point_Picking.这里解释了两种方法.

I have to generate 8 random numbers whose sum is always 320. I need around 100 sets or so. http://en.wikipedia.org/wiki/User:Skinnerd/Simplex_Point_Picking. Two methods are explained here.

或任何其他方式，以便我可以在 Excel 中完成.

Or any other way so I can do it in Excel.

推荐答案

您可以使用 RAND() 函数在A列.

You could use the RAND() function to generate N numbers (8 in your case) in column A.

然后，在 B 列中，您可以使用以下公式 B1=A1/SUM(A:A)*320, B2=A2/SUM(A:A)*320 等等(其中 320 是您感兴趣的总和).

Then, in column B you could use the following formula B1=A1/SUM(A:A)*320, B2=A2/SUM(A:A)*320 and so on (where 320 is the sum that you are interested into).

所以你可以在 A1 中输入 =RAND()，然后将它向下拖到 A8.然后在B1输入=A1/SUM(A:A)*320，拖到B8.B1:B8 现在包含 8 个随机数，总和为 320.

So you can just enter =RAND() in A1, then drag it down to A8. Then enter =A1/SUM(A:A)*320 in B1 and drag it to B8. B1:B8 now contains 8 random numbers that sum up to 320.

样本输出:

这篇关于生成 N 个随机数，其和为常数 K - Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！