在Google Sheets中不使用AGGREGATE函数生成排除指定范围的1-6随机数的方法

我懂你在Excel里用AGGREGATE能轻松搞定带排除条件的随机数生成，但Google Sheets确实不支持这个函数的部分功能。下面给你两个实用的替代方案，都能在Q7:Q12区域生成1-6之间的随机数，同时避开P7:P12里的数值：

方案一：单个单元格公式（适合手动下拉填充） #

在Q7单元格输入以下公式，然后下拉到Q12即可：

=RANDBETWEEN(1,6 - COUNTIF($P$7:$P$12, "<="&6)) + COUNTIF($P$7:$P$12, "<="&RANDBETWEEN(1,6 - COUNTIF($P$7:$P$12, "<="&6)))

公式逻辑拆解： #

1. 先计算1-6范围内被排除的数值数量，得到可用数值的总数，生成一个缩小范围的随机数
2. 再加上被排除列表中小于等于这个随机数的数值数量，相当于自动跳过那些被排除的值，最终得到符合要求的随机数

方案二：数组公式（一次性填充整个区域，更高效） #

在Q7单元格输入以下数组公式（直接回车即可，Google Sheets会自动识别并填充到Q7:Q12）：

=ARRAYFORMULA(LET(
  excluded, P7:P12,
  available, FILTER(SEQUENCE(6), ISNA(MATCH(SEQUENCE(6), excluded, 0))),
  MAP(SEQUENCE(ROWS(excluded)), LAMBDA(x, INDEX(available, RANDBETWEEN(1, ROWS(available)))))
))

公式逻辑拆解： #

• LET(excluded, P7:P12, ...)：给排除区域起别名，让公式结构更清晰易读
• available, FILTER(SEQUENCE(6), ISNA(MATCH(SEQUENCE(6), excluded, 0)))：生成1-6中不在排除列表里的可用数值数组
• MAP(SEQUENCE(ROWS(excluded)), LAMBDA(x, ...))：针对Q7:Q12的每个单元格，独立执行随机选值逻辑
• INDEX(available, RANDBETWEEN(1, ROWS(available)))：从可用数值数组里随机挑选一个值

额外说明： #

• 如果P7:P12里存在重复的排除值，公式依然能正常工作，因为MATCH只会识别数值是否存在，不关心重复次数
• 和Excel的随机函数一样，每次工作表刷新（比如输入新内容、按F9键），这些随机数会重新生成

内容的提问来源于stack exchange，提问作者Richard