如何在Google表格中按单元格值重复行并使日期间隔7天？

我有一个包含「Input Sheet」和「Output Sheet」两张工作表的Google表格，需求是根据「Input Sheet」中H列的单元格值，将对应行重复指定次数到「Output Sheet」。目前已经用以下公式实现了行重复，但F列（周起始日期）和G列（周结束日期）只会原样重复原数据，无法实现重复行的日期依次间隔7天的效果。

原实现公式 #

=ARRAYFORMULA(
     VLOOKUP(
        TRANSPOSE(SPLIT(QUERY(REPT(ROW('Input Sheet'!A2:A)&" ",'Input Sheet'!H2:H),,9^9)," ")),
        {ROW('Input Sheet'!A2:A),'Input Sheet'!A2:I},
        {1,2,3,4,5,6,7,8,9}+1,0
     )
)

当前效果与期望效果对比 #

当前效果：

Week (Start) |  Week (End)  | Weeks
Apr 7, 2024  | Apr 27, 2024 | 3 (row 1)
Apr 7, 2024  | Apr 27, 2024 | 3 (row 2)
Apr 7, 2024  | Apr 27, 2024 | 3 (row 3)

期望效果：

Week (Start)  |  Week (End)  | Weeks
Apr 7, 2024   | Apr 13, 2024 | 3 (row 1)
Apr 14, 2024  | Apr 20, 2024 | 3 (row 2)
Apr 21, 2024  | Apr 27, 2024 | 3 (row 3)

修改后的公式 #

=ARRAYFORMULA(
  LET(
    repeated_rows, TRANSPOSE(SPLIT(QUERY(REPT(ROW('Input Sheet'!A2:A)&" ",'Input Sheet'!H2:H),,9^9)," ")),
    row_numbers, XMATCH(repeated_rows, ROW('Input Sheet'!A2:A)),
    original_data, 'Input Sheet'!A2:I,
    counts, 'Input Sheet'!H2:H[row_numbers],
    seq, BYROW(repeated_rows, LAMBDA(r, COUNTIF(repeated_rows, "<="&r) - SUM(INDEX(counts, 1):INDEX(counts, XMATCH(r, repeated_rows)-1)))),
    start_dates, INDEX(original_data, row_numbers, 6) + (seq-1)*7,
    end_dates, start_dates + 6,
    final_data, HSTACK(INDEX(original_data, row_numbers, {1,2,3,4,5}), start_dates, end_dates, INDEX(original_data, row_numbers, {8,9})),
    final_data
  )
)

公式说明 #

1. 用LET函数封装变量，简化逻辑并提升性能：
   • repeated_rows：生成重复的原数据行号序列，保留原公式核心逻辑
   • row_numbers：匹配每个重复行对应的原数据行号
   • seq：计算每个重复行在当前组内的序号（比如重复3次的组，序号依次为1、2、3）
   • start_dates：基于原起始日期，加上(seq-1)*7天生成递增的周起始日期
   • end_dates：通过起始日期加6天，自动生成当周结束日期
   • final_data：将调整后的日期列与其他原数据列拼接，输出最终结果

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