基于Excel中的真实过滤器从列表中随机选择多个值
总结，
我想从Excel中的列表中选择多个随机选择的选项，但被过滤到仅包含另一列中具有真实指示器的选项。需要进行随机选择的数量需要变化。此外，不应拉动重复项。理想情况下，我想将一个公式包含在一个单元格中，如果可以的话，它会溢出到所需的长度。
当前尝试
通过= index，= randbetweeen = filter和helper = rand的合并，我尝试通过列，试图过滤数据，然后从中随机选择。我发现的问题是= index = randbetweeen进行此逻辑工作。 （）= filter不喜欢正确组合（尽管我可能做错了什么）。此外，这涉及将公式复制到我想要一个随机选择的新选项的每个单元格中，如果可能的话，这是不理想的。
所需的输出
| ID | t/f | 数字的示例要随机选择？ | 输出 |
|---|---|---|---|
| 1 | true | 5 | 2 [公式在这里并溢出] |
| 2 | true | 5 | |
| 3 | false | 7 | |
| 4 | false 9 false | 5 | |
| true | 9 | 12 | |
| 6 | false | ||
| 7 | true | ||
| true 8 | 9 true | ||
| 9 | true | ||
| 10 | false 11 true | ||
| 12 | true 12 true | ||
| 12 | true |
这是一个示例，说明了我如何理想地喜欢数据的输出。数据本身在a1：b13中。我将输入在c2中随机选择的选项数，然后将基于d2中包含的解决方案生成输出，该解决方案会相应地溢出。它仅从A列中的ID总体中选择的要求，其中true/false来自该ID的B列的指示器是true。我正在寻找解决这个问题的解决方案，是我需要放入d2的公式。
我一直希望改进的额外细节
，因此总是对某些事情的工作方式进行任何解释，因此我最终不会再问同样的事情了！
我正在使用MSO 365，但由于推出时间表，我仅限于版本2108 。
感谢您的任何帮助！
Summary
I would like to select multiple randomly selected options from a list within Excel, but filtered to only include those with a TRUE indicator in another column. The number of random selections to make will need to be vary. Additionally, duplicates shouldn't be pulled. Ideally, I would like to have the formula contained within one cell, and have it spill to the required length, if such a thing would be possible.
Current attempt
I have tried making this logic work through the combined used of =INDEX, =RANDBETWEEN =FILTER and a helper =RAND() column, in an attempt to filter the data, then randomly select from it. The issue I'm finding is that =FILTER does not like to combine properly (although I'm likely doing something wrong). Additionally, this involves copying the formula down to each cell I'd like a new randomly selected option for, which isn't ideal if possible.
Example of required output
| ID | T/F | Number to randomly select? | Output |
|---|---|---|---|
| 1 | TRUE | 5 | 2 [Formula is here and spill down] |
| 2 | TRUE | 5 | |
| 3 | FALSE | 7 | |
| 4 | FALSE | 9 | |
| 5 | TRUE | 12 | |
| 6 | FALSE | ||
| 7 | TRUE | ||
| 8 | TRUE | ||
| 9 | TRUE | ||
| 10 | FALSE | ||
| 11 | TRUE | ||
| 12 | TRUE |
Here is an example of how I'd ideally like the output of the data. The data itself is in A1:B13. I'd enter the number of options to randomly select in C2, then the output would be generated based on a solution contained in D2, which spills down accordingly. The requirements that it only selects from an ID population in column A where the TRUE/FALSE indicator from column B for that ID is TRUE. I'm looking for the solution to this question to be the formula I'd need to put into D2, please.
Extra detail
I'm always looking to improve, so any explanation on how something works is always appreciated, so I don't end up asking the same things again!
I am using MSO 365, but I am restricted to version 2108 due to rollout schedules.
Thanks for any help with this!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试：
d2中的公式：或者，如果需要这些升级：
最后一个小编辑可以是调整
c2中的值如果需要的话，数量较小（避免泄漏出错误）：Try:
Formula in
D2:Or, if one needs these to be ascending:
A last small edit could be to adjust the value in
C2with a smaller number if need be (avoid errors spilled down):