Google Sheets：为日期列循环无放回随机选值并支持更新

1. 实现循环无放回随机选取 #

核心公式 #

在C1单元格输入以下公式，它会自动填充整个C列，实现以A列值数量为一轮的无放回随机选取，轮次结束后重置并重新随机排序A列值：

=ARRAYFORMULA(
  LET(
    vals, FILTER(A1:A, A1:A<>""),
    n, COUNTA(vals),
    total_rows, COUNTA(B1:B),
    rounds, CEILING(total_rows/n, 1),
    round_vals, BYROW(SEQUENCE(rounds), LAMBDA(x, SORT(vals, RANDARRAY(n), 1))),
    all_vals, FLATTEN(round_vals),
    cycle_vals, INDEX(all_vals, SEQUENCE(total_rows)),
    IF(B1:B<=NOW(), C1:C, cycle_vals)
  )
)

公式说明 #

• vals：提取A列所有非空可选值
• n：统计可选值数量，作为每轮的取值长度
• rounds：计算覆盖B列所有日期需要的轮次
• round_vals：为每轮生成独立的随机排序数组，保证每轮无放回且顺序随机
• all_vals：拼接所有轮次的随机数组为长列表
• cycle_vals：截取对应B列行数的结果，匹配每个日期

2. 新增A列值时仅更新未来日期结果 #

公式中的IF(B1:B<=NOW(), C1:C, cycle_vals)逻辑自动处理需求：

• 当B列日期小于等于当前系统时间：直接保留C列已生成的旧值，不会随A列新增值改变
• 当B列日期大于当前系统时间：使用更新后的A列可选值重新生成随机结果

注意事项 #

• 首次输入公式时，C列为空，所有日期都会生成随机值；之后系统时间之前的日期值会固定
• 若需手动指定更新分界日期，可将NOW()替换为具体日期单元格（如$D$1）

替代方案（脚本实现更稳定的固定值） #

如果担心公式动态特性可能意外修改旧值，可使用Google Apps Script：

1. 打开Google Sheet，点击扩展程序 > Apps Script
2. 粘贴以下脚本：

function assignRandomValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const vals = sheet.getRange("A1:A").getValues().flat().filter(v => v !== "");
  const n = vals.length;
  const dates = sheet.getRange("B1:B").getValues().flat().filter(v => v instanceof Date);
  const now = new Date();
  
  dates.forEach((date, idx) => {
    const row = idx + 1;
    const cell = sheet.getRange(`C${row}`);
    if (date > now && cell.getValue() === "") {
      const pos = idx % n;
      if (pos === 0) shuffleArray(vals);
      cell.setValue(vals[pos]);
    }
  });
}

function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
}

3. 运行脚本完成授权后，可手动触发assignRandomValues函数，或设置定时触发器自动执行。脚本仅为未来日期填充随机值，旧日期值完全固定。

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