Google Sheets批量重复跨表数据引用公式优化需求

基础方法：动态行号公式+填充 #

针对INDEX填充行号不更新的问题，核心是让公式根据Sheet2的当前行号，自动计算对应Master_Sheet的目标行：

1. ID行公式（Sheet2的A1单元格）：
   =IF(MOD(ROW()-1,5)=0, INDEX(Master_Sheet!A:A, INT((ROW()-1)/5)+1), "")

• 逻辑：判断当前行是否为每组的第1行（行号1、6、11...），是则引用Master_Sheet对应行的ID，否则留空。

2. 性别行公式（Sheet2的A2单元格）：
   =IF(MOD(ROW()-2,5)=0, INDEX(Master_Sheet!C:C, INT((ROW()-2)/5)+1), "")

• 逻辑：判断当前行是否为每组的第2行（行号2、7、12...），是则引用Master_Sheet对应行的性别，否则留空。

3. 特征行公式（Sheet2的A3单元格）：
   =IF(MOD(ROW()-3,5)=0, INDEX(Master_Sheet!B:B, INT((ROW()-3)/5)+1), "")

• 逻辑：判断当前行是否为每组的第3行（行号3、8、13...），是则引用Master_Sheet对应行的特征，否则留空。

4. 批量填充：选中A1:A3，按住单元格右下角的填充柄向下拖动到目标行数即可，公式会自动适配每行的引用逻辑，空行自动留空。

进阶方法：ARRAYFORMULA一键生成（无需下拉） #

如果需要一次性生成数千行数据，推荐用ARRAYFORMULA批量生成，无需手动下拉：

无表头场景（Master_Sheet的A列从第1行开始是数据） #

在Sheet2的A1单元格输入：

=ARRAYFORMULA(
  FLATTEN(
    LAMBDA(master_data,
      MAP(SEQUENCE(COUNTA(Master_Sheet!A:A)), LAMBDA(row_num,
        {
          master_data[row_num,1],  // 引用ID列
          master_data[row_num,3],  // 引用性别列
          master_data[row_num,2],  // 引用特征列
          "",
          ""
        }
      ))
    )(Master_Sheet!A:C)
  )
)

有表头场景（Master_Sheet的第1行是表头，数据从第2行开始） #

修改公式跳过表头：

=ARRAYFORMULA(
  FLATTEN(
    LAMBDA(master_data,
      MAP(SEQUENCE(COUNTA(Master_Sheet!A:A)-1,1,2), LAMBDA(row_num,
        {
          master_data[row_num,1],
          master_data[row_num,3],
          master_data[row_num,2],
          "",
          ""
        }
      ))
    )(Master_Sheet!A:C)
  )
)

• 逻辑：先获取Master_Sheet的A-C列数据，遍历每一行生成「ID、性别、特征、空、空」的数组，再通过FLATTEN将二维数组展开为一维，自动填充所有行。

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