arrays - 一致随机数的命名范围

Question

背景

继question I asked a while ago之后关于获取一组不同(但不一定是唯一的)随机数，其答案是:

=RANDBETWEEN(ROW(A1:A10)^0,10)

获取 10 个 1 到 10 之间的随机数的数组

问题

如果我使用上面的公式创建一个命名范围(称为“randArray”)，我希望能够多次引用 randArray 并获得相同一组随机数。当然，每次我按 F9 或更新工作表时，它们都会更改 - 但会一起更改。

这就是我得到的，两组完全不同的随机数

我对此行为并不感到惊讶，但是如何在不使用 VBA 且不将随机数放入工作表中的情况下实现此目的？

<小时/> 如果您有兴趣

此示例旨在实现 MCVE。在我的实际情况中，我使用随机数来估计 Pi。用户规定要应用多少个随机点并获得相应的准确估计。出现问题的原因是我还对点进行了绘制，当点数量较少时，可以很清楚地看到估计和图表并不代表相同的数据集

<小时/>

更新

我已将初始赏金授予@Michael，以表彰其提供有趣且不同的解决方案。我仍在寻找一个完整的解决方案，允许用户规定要使用多少个随机点，尽管可能没有完美的答案，但我仍然对任何其他可能的解决方案感兴趣，并且非常乐意提供更多的赏金。

感谢迄今为止做出贡献的所有人。

Answer 1

该解决方案生成 10 个看似随机的数字，范围在 1 到 10 之间，每次持续近 9 秒。这允许重复调用同一公式以在单次刷新中返回同一组值。

如果需要，您可以修改时间范围。较短的时间段允许更频繁的更新，但也会稍微增加在切换点之后发生对公式的某些调用的极不可能的机会，从而导致后续调用的第二组 10 个随机数。

首先，定义一个包含 10 个不同素数的数组“Primes”:

={157;163;167;173;179;181;191;193;197;199}

然后，定义将返回 10 个随机数的数组的公式:

=MOD(ROUND(MOD(ROUND(NOW(),4)*70000,Primes),0),10)+1

说明:

我们需要构建自己的随机数生成器，我们可以在一段时间内使用相同的值作为种子；足够长的时间，以便被调用的公式能够继续返回相同的值。

首先，我们创建一个种子:ROUND(NOW(),4) 每 0.0001 天 = 8.64 秒创建一个新的种子编号。

我们可以使用以下公式生成粗略的随机数:

随机 = 种子 * 7 mod Prime

https://cdsmith.wordpress.com/2011/10/10/build-your-own-simple-random-numbers/

理想情况下，通过从先前的输出中获取输入来生成随机数序列，但我们无法在单个函数中做到这一点。因此，它使用 10 个不同的素数，本质上是启动 10 个不同的随机数生成器。现在，它在生成随机数方面的可靠性较低，但下面的测试结果表明它实际上似乎做得相当不错。

ROUND(NOW(),4)*70000 使我们的种子达到一个整数，同时乘以 7

MOD(ROUND(NOW(),4)*70000,Prime) 生成从 0 到相应质数的 10 个随机数的序列

ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0) 需要让我们返回整数，因为 Excel 似乎很难将 Mod 应用于 float 字。

=MOD(ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0),10)+1 仅取个位中的值(来自0 到 9) 并将其转换为从 1 到 10 的随机数

测试结果:

我为种子值生成了 500 批 10 个随机数(按列而不是按行)，种子值递增 0.0001，并计算了每个素数中每个数字出现的次数。您可以看到每个数字总共出现了近 500 次，并且每个数字在每个素数之间的分布几乎相等。因此，这可能足以满足您的目的。

查看立即连续生成的数字，您可以看到相邻素数之间的相似之处，它们并不完全相同，但它们在某些地方非常接近，即使它们偏移了几行。但是，如果刷新以随机间隔发生，您仍然会得到看似随机的数字，这应该足以满足您的目的。否则，您仍然可以将此方法应用于更复杂的随机数生成器，或者尝试相距更远的素数的不同组合。

更新 1:尝试找到一种方法，能够在不存储素数列表的情况下指定生成的随机数的数量。

尝试 1:使用单个素数和一组种子:

=MOD(ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))/10000,4)*70000,1013),0),10)+1

这确实给了你一个均匀的分布，但它实际上只是一遍又一遍地重复完全相同的 10 个数字序列。对样本的任何分析都与分析 =MOD(ROW(1:SampleSize),10)+1 相同。我认为您想要更多的变化!

尝试 2:处理仍然使用 10 个素数的二维数组......

更新 2:不起作用。它的表现很糟糕。已提交采用类似但不同方法的新答案。