Excel中的随机数数组 [英] Array of random numbers in Excel

问题描述

如何使用 RAND 或 RANDBETWEEN 函数创建一个随机数组，以便您可以模拟在一个公式中，平均10个六面体模具（例如）的结果？

How can you create an array of random numbers using the RAND or RANDBETWEEN functions such that you could simulate the average outcome of 10 rolls of a six sided die (for example) in one formula?

我以前尝试过以下内容：

I have tried the following in the past:

=SUMPRODUCT((ROW(A1:A10)^0)*(INT(RAND()*6)+1))/10

但它只生成一次随机数，并重复10次，所以平均值等于单个随机输出。我需要的是一个公式中的10个不同的随机数。

But it only generates the random number once and repeats it 10 times, so the average is just equal to the single random output. What I need is 10 different random numbers in one formula.

更新：我知道这是可能的帮助列，但我具体有兴趣只使用一个单元格 - 也专门寻找一种仅使用工作表函数的解决方案，而不是VBA

Update: I know this is possible with a helper column but I'm specifically interested in using just one cell - also specifically looking for a solution which only uses worksheet functions/ not VBA

推荐答案

RANDBETWEEN 函数可以处理数组输入。所以

The RANDBETWEEN function can deal with array inputs. So

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

有效地创建：

=RANDBETWEEN({1;1;1;1;1;1;1;1;1;1},{6;6;6;6;6;6;6;6;6;6})

它返回1到6之间的10个不同随机数的数组。这可以很容易地被更改为100（或多个）随机数，将 A1：A10 更改为 A1：A100 。

which returns an array of 10 different random numbers between 1 and 6. This could easily be changed to 100 (or however many) random numbers by changing A1:A10 to A1:A100.

因此，一个单独的公式为10个单独的骰子卷可以是：

Therefore, a single formula for the average of 10 separate dice rolls could be:

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

或等效的CSE公式

{=AVERAGE(RANDBETWEEN(ROW(A1:A10)^0,6))}

这篇关于Excel中的随机数数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！