我正在尝试从数组中选择一个随机单元格然后向下填充，固定列但让行保持相对。例如，我正在尝试从 A1:E500 中选择一个随机单元格的内容以用于 F500 中的公式（我可以这样做）。然后我希望 F501、F502、F503 等从与随机选择的单元格相同的后续行（但相同的列）中的单元格中检索值。有没有办法在单个公式中执行此操作，而不必创建额外的工作表列？我问这个问题是因为我将在许多数组中重复此操作。我想我问的是是否有一种方法可以从数组中选择一个随机单元格，但以这样的方式在公式中对其进行编码，即当我向下填充时，第一种情况下随机选择的单元格将作为我执行填充操作时的锚点。在此先感谢您的帮助或指导！
答案1
假设您有 Excel 365 / 2021，您可以使用带有溢出的 LET 函数从数组中返回所需长度的数据，比如说 3 行，一次返回：
=LET(R, RANDBETWEEN(1,ROWS(data)-2), C, RANDBETWEEN(1,COLUMNS(data)),INDEX(data,R,C):INDEX(data,R+2,C))
其中数据是定义的命名范围，例如 A1:E500。+-2 偏移量是为了确保您不会在最后一行中选择一行，然后在其下方没有数据可返回 - INDEX 会因尝试索引超出范围而返回错误。
如果您没有 LET，或者需要能够复制和粘贴带有相对引用的公式，您可以专门使用两个单独的单元格来选择随机索引 R 和 C，例如在 F500 和 F501 中，然后在 G500 中输入您的第一个公式：
=INDEX(data,$F$500-ROW($G$500)+ROW(),$F$501-COLUMN($G$500)+COLUMN())
您也可以使用 OFFSET 或 INDIRECT，但这两个公式都是易失性函数，会一直重新计算，从而减慢您的工作簿速度。
美丽的事物
话虽如此，如果您不介意使用 OFFSET 的 CPU 开销，您可以组合相对命名范围和 OFFSET 来创建一个真正的随机单元格，其作用就像相对引用一样。
- 单击“公式”功能区 > 定义名称
- 将数据定义为 =$A$1:$E$500
- 将单元格 F1 设置为 =RANDBETWEEN(1,ROWS(data))-1，并将单元格命名为 RR
- 将单元格 G1 设置为 =RANDBETWEEN(1,COLUMNS(data))-1，并将单元格命名为 CC
- 将光标放在 F500 上 （第 6 步非常重要）
- 将 randomcell 定义为 =OFFSET(A1,RR,CC) ...不是 1 澳元
- 现在在 F500 中输入 =randomcell
- 向下或向右填充，它将像相对引用 A1 一样更新，即使在公式中使用
- 如果您想要锁定随机引用以表示列（例如 $A1 样式），请将 randomcellC 定义为 =OFFSET($A1,RR,CC)，并将光标放在 F500 中