Google Sheets多列无放回随机抽取值并保留历史数据需求

一、核心需求回顾 #

• 从A列（值列表）为B列每个非空日期行，生成N列（示例N=4）随机值
• 每行内的N个值无重复，不同行允许重复
• 全局按顺序耗尽A列所有值后，重新生成随机排列继续分配
• 后续向A列添加新值时，仅更新操作日期之后的日期对应的C-F列值，保留历史日期的已生成值

二、Google Apps Script 实现方案（推荐） #

脚本可精准控制随机分配逻辑，同时处理历史值保留的需求。

1. 脚本代码 #

打开Google Sheet，点击「扩展程序」→「Apps Script」，粘贴以下代码：

function generateRandomValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const colA = sheet.getRange("A:A").getValues().filter(val => val[0] !== "");
  const totalVals = colA.length;
  if (totalVals === 0) return;
  
  const N = 4; // 要生成的列数，可根据需求修改
  const colB = sheet.getRange("B:B").getValues();
  const dateRows = colB.filter(row => row[0] !== "").length;
  const today = new Date();
  today.setHours(0, 0, 0, 0); // 重置为当天零点，用于判断日期先后

  // 生成全局随机序列：耗尽A列后重新生成随机排列
  let randomSequence = [];
  let remainingElements = [];
  let currentBlock = [];

  // 遍历每个日期行
  for (let row = 0; row < dateRows; row++) {
    const currentDate = new Date(colB[row][0]);
    currentDate.setHours(0, 0, 0, 0);
    
    // 如果是历史日期（操作当天及之前），跳过不更新
    if (currentDate <= today) {
      // 检查当前行C-F是否已有值，有则直接跳过
      const existingVals = sheet.getRange(row + 1, 3, 1, N).getValues()[0];
      if (existingVals.every(val => val !== "")) continue;
    }

    let needed = N;
    const rowVals = [];

    // 先取剩余的元素
    while (needed > 0 && remainingElements.length > 0) {
      const val = remainingElements.shift();
      rowVals.push(val);
      needed--;
    }

    // 剩余不足时生成新的随机块
    while (needed > 0) {
      // 生成A列的随机排列
      currentBlock = colA.map(val => val[0]).sort(() => Math.random() - 0.5);
      remainingElements = [...currentBlock];
      // 取需要的数量
      while (needed > 0 && remainingElements.length > 0) {
        const val = remainingElements.shift();
        rowVals.push(val);
        needed--;
      }
    }

    // 确保当前行无重复（双重校验）
    const uniqueRowVals = [...new Set(rowVals)];
    while (uniqueRowVals.length < N) {
      const missing = N - uniqueRowVals.length;
      // 补充不重复的随机值
      const available = colA.map(val => val[0]).filter(v => !uniqueRowVals.includes(v));
      const randomSupplements = available.sort(() => Math.random() - 0.5).slice(0, missing);
      uniqueRowVals.push(...randomSupplements);
    }

    // 将值写入C-F列
    sheet.getRange(row + 1, 3, 1, N).setValues([uniqueRowVals]);
  }
}

2. 使用说明 #

• 修改代码中的const N = 4;为你实际需要的列数
• 点击脚本编辑器的「运行」按钮，首次运行需完成授权
• 后续向A列添加新值后，再次运行脚本：脚本会自动跳过操作当天及之前的日期行，仅更新之后的日期对应的C-F列值

3. 关键逻辑说明 #

• 全局耗尽后重排：每次生成A列的随机排列，按顺序取元素，耗尽后自动生成新的随机排列
• 每行去重校验：生成每行值后强制去重，确保满足“每行内无重复”的核心要求
• 历史值保留：通过对比日期与操作当天，跳过已生成值的历史日期行，仅更新未来日期的结果

三、公式方案（快速实现，不严格遵循“耗尽后重排”） #

如果不需要严格的“耗尽A列后重排”逻辑，仅需要每行生成N个无重复随机值，可用数组公式快速实现：

在C1单元格输入以下公式，按回车后向右拖动填充到F列：

=MAKEARRAY(COUNTA(B:B), 4, LAMBDA(r,c, IF(INDEX(B:B, r)="",, INDEX(SORTBY(A:A, RANDARRAY(COUNTA(A:A))), RANDBETWEEN(1, COUNTA(A:A))))))

注意：此公式无法保证全局耗尽后重排，且每次刷新表格会重新生成所有值，无法保留历史值，适合临时使用。

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