Excel:将随机数放入数组的公式

Question

这个让我很困惑，所以我想我会和你们分享，看看我得到了什么:)

一般问题

我在电子表格中有以下数据

如您所见，我有两组相同的标题“猫、狗、人……”(确切的名称无关紧要)

这两组被归类在“从”列或“到”行下。
表格的大部分是 0 到 1 之间的数字数组或空单元格。

基本上我想做的是找到给定的Rand()编号沿每个 排列行 .然后，我从与放置随机数的位置相对应的“到”行返回项目。

即沿第 1 条阅读 猫 行，随机数(也介于 1 和 0 之间)被排名。如果它在 0 到 0.3658 之间……我返回 Cat，0.7193……而 1 给了我 Van

所以理想情况下，我从 返回值至垂直于Rand() 的上限的一组标题。数字谎言。

尝试的解决方案

为了达到这个排名，我一直在使用 Match(value,array,0)函数(0，因为我的数字按升序排列)。

因此，我的公式的简化版本是:
=Index(To_Headings,MATCH(RAND(),From_Row,0))
在哪里 To_Headings是数组E3:I3和 From_Row是我使用另一个公式生成的数组，结果为 D#:I# (# 是与 来自 标题相关的行号，因此必须是 4 到 8 之间的整数)

但是，如果您特别细心，您会发现这是我的解决方案不足的地方

正如我所说，理想情况下，我想找到 RAND() 所在位置的上限，因为它始终与我想要的输出 在同一列中至标题。 MATCH()参数为 0 时返回 RAND() 的下限谎言。通常这是所需列左侧的第 1 列。

例如阅读 猫 再次行 - 对于 0.5 的随机数，它所在的范围是 0.3658... 和 0.7193... 。上限直接低于 狗 ，我想要的输出。较低的是所需 1 左侧的 1 列，因此在我的公式中，我在使用 Index 读取时简单地移回右侧



然而，空白单元格使这毫无用处。对于 0.719... 和 1 之间的随机数，下限现在是 两个 上限左侧的列。在其他情况下，它可以是 3 或 4，实际上是任何数字。这是因为空白单元格将上限推到了更右侧。

是的，记住所有这些，谁能告诉我如何对 RAND() 数进行排名，以便它给我上限？当然，我已经尝试使用 -1 作为参数进行匹配，但是因为这需要降序，所以问题也会翻转!

我想我可以尝试计算下限右侧是否有任何空白，并抵消我的 INDEX这么多而不是 1，但我可以看到这会在我的代码中添加很多行，我真的需要保持它的精简，因为它将在大约 10000 个单元格中运行!

Answer 1

您可以使用此数组公式。

由于您的数据是按升序排列的，因此这将找到随机数小于或等于数据集中数字的第一个单元格:

=INDEX($E$3:$I$3,MATCH(TRUE,$J$2<=E4:I4,0))

作为一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。如果正确完成，Excel 将输入 {}围绕公式。