Google Sheets中使用Arrayformula按条件自动填充列如何避免行偏移

问题根源 #

你当前使用的公式属于纯动态数组输出，只会覆盖公式所在列的对应单元格值，输出的行顺序、行数完全随源表Research的条件变化，但目标表中其余列的手动输入内容是和单元格行号绑定的，不会随提取的条目同步移动，因此会出现数据错配。

补充优化点：你原公式中的REGEXEXTRACT(Research!$A$1:$A$100,".*")属于冗余代码，直接写Research!$A$1:$A$100即可得到完全相同的结果，可直接简化公式。

可选解决方案 #

方案1：全列关联查询（无代码，新手友好） #

如果你的目标表所有字段都可从源表关联提取，不需要手动填写自定义内容，可以直接把所有需要的字段整合到QUERY中，确保整行数据同步更新，参考公式：
=IFERROR(QUERY(Research!A:Z,"SELECT A, C, D WHERE B='Yes'",1))
说明：公式中C、D为你需要从源表同步的其他列号，可根据实际需求调整。该方案仅适用于目标表无手动录入内容的场景。

方案2：Apps Script自动同步整行（匹配整行增删需求） #

该方案可实现源表条件变化时，自动在目标表插入/删除整行，不会导致手动录入的内容偏移，操作步骤如下：

1. 打开表格，点击顶部菜单栏「扩展程序」-「Apps Script」
2. 删除编辑器内默认代码，粘贴以下代码：

function syncResearchToTarget() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 替换为实际的源表、目标表名称
  const sourceSheet = ss.getSheetByName("Research");
  const targetSheet = ss.getSheetByName("目标表名");
  // 获取源表符合条件的行数据
  const sourceData = sourceSheet.getDataRange().getValues();
  const matchedRows = sourceData.filter(row => row[1] === "Yes");
  // 获取目标表现有数据，以第一列Agency名称为唯一标识
  const targetData = targetSheet.getDataRange().getValues();
  const existingAgencies = targetData.map(row => row[0]);
  // 新增源表中符合条件但目标表不存在的条目
  matchedRows.forEach(row => {
    const agencyName = row[0];
    if (!existingAgencies.includes(agencyName)) {
      // 可根据需要在数组中补充其他列的默认值
      targetSheet.appendRow([agencyName]);
    }
  });
  // 删除目标表中不再符合条件的整行
  for (let i = targetData.length - 1; i >= 0; i--) {
    const agencyName = targetData[i][0];
    const isStillMatched = matchedRows.some(row => row[0] === agencyName);
    if (!isStillMatched) {
      targetSheet.deleteRow(i + 1);
    }
  }
}

3. 保存项目，点击运行完成一次授权，再设置自动触发：点击左侧闹钟图标「触发器」-「添加触发器」，选择syncResearchToTarget函数，触发来源选「电子表格」，事件类型选「更改」，保存即可。
   设置完成后，只要源表内容有修改，就会自动同步整行数据到目标表，不会出现偏移错配问题。

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