在Excel中，如何在日期范围内随机选择一个值？

Question

我已经按照下面的图片设置了我的示例：

我正在尝试在单元格F3中制作一个公式，该公式将：

"在A2:A11之间选择一个随机事件，其中用户输入日期（E3）介于开始日期（B2:B11）和结束日期（C2:C11）之间"

我能想到的唯一方法是：

=COUNTIFS(C2:C11,">="&E3,B2:B11,"<="&E3)

但这只返回可以选择的EVENTS数量。

非常感谢您提供任何意见。

这个的最终值的例子可能是：

事件3或事件4，因为2012年7月2日发生在两个事件的开始和结束日期之间。

- bagofmilk

Answer 1

我想出了这个公式：

=INDEX(A:A,SMALL(IF((E3>=B2:B11)*(E3<=C2:C11),ROW(A2:A11)),
                  RANDBETWEEN(1,SUMPRODUCT((E3>=B2:B11)*(E3<=C2:C11)))
                )
      )

使用数组项 (CTRL+SHIFT+ENTER)。

1. IF((E3>=B2:B11)*(E3<=C2:C11),ROW(A2:A11)) 返回行号的数组，其中 E3 在 START 和 FINISH 日期之间
2. RANDBETWEEN(1,SUMPRODUCT((E3>=B2:B11)*(E3<=C2:C11))) 生成介于 1 和相应事件数量之间的随机数
3. SMALL(IF(..),RANDBETWEEN(..)) 从步骤 1 中的数组中获取随机行号。
4. INDEX(A:A,SMALL(..)) 获取相应的事件名称。

- Dmitry Pavliv

---

首先...这太棒了。问题是它要么提取了不正确的“事件2”，要么返回了“#NUM!”的值。但这似乎非常接近。 - bagofmilk

你是按下了CTRL+SHIFT+ENTER来计算公式还是只按了ENTER键？ - Dmitry Pavliv

啊...那就是问题所在了...你是正确的，谢谢！ - bagofmilk

@bagofmilk，顺便说一下，如果没有适当的事件，公式会返回“#NUM!”，但是您可以像这样处理它：=IFERROR(INDEX(...),"没有这样的事件") - Dmitry Pavliv

Answer 2

我知道这个问题已经有答案了，但是我想提供一个略微不同的选项，它不需要数组输入（Ctrl+Shift+Enter）：

=IFERROR(INDEX(A:A,LARGE(INDEX((B2:B11<=E3)*(C2:C11>=E3)*ROW(A2:A11),),RANDBETWEEN(1,COUNTIFS(B2:B11,"<="&E3,C2:C11,">="&E3)))),"No events found on that date")

这里提供一种向后兼容的公式：

=IF(SUMPRODUCT(--(B2:B11<=E3),--(C2:C11>=E3))=0,"No events found on that date",INDEX(A:A,LARGE(INDEX((B2:B11<=E3)*(C2:C11>=E3)*ROW(A2:A11),),INT(RAND()*SUMPRODUCT(--(B2:B11<=E3),--(C2:C11>=E3)))+1)))

- tigeravatar

---

谢谢！这也非常有帮助！ - bagofmilk