如何在Google Sheets中按月份周数重复数据行？

需求说明 #

现有如下格式的原始数据：

metric         time        Forecast
new student    01/31/2023  2000
new student    02/28/2023  1000
new student    03/31/2023  0
new student    04/30/2023  -1000

需要将每行数据按照对应月份的实际周数重复，例如2023年1月有5周则对应行重复5次，2月有4周则对应行重复4次，最终生成包含week列的展开数据：

metric         time        week Forecast
new student    01/31/2023  1    2000
new student    01/31/2023  2    2000
new student    01/31/2023  3    2000
new student    01/31/2023  4    2000
new student    01/31/2023  5    2000
new student    02/28/2023  1    1000
new student    02/28/2023  2    1000
new student    02/28/2023  3    1000
new student    02/28/2023  4    1000
...

原公式问题 #

之前尝试的公式无法正确计算月份周数，所有月份均返回5：

=IF(ISBLANK(B2), "", IFERROR(IF(MONTH(B2+1)<>MONTH(B2), CEILING(DAY(EOMONTH(B2, 0))/7), CEILING(DAY(EOMONTH(B2, 0)-1)/7)), 1))

正确解决方案 #

1. 准确计算月份周数的公式 #

以单元格B2为日期单元格，使用以下公式可计算对应月份的实际周数：

=WEEKNUM(EOMONTH(B2,0),2)-WEEKNUM(EOMONTH(B2,0)-DAY(EOMONTH(B2,0))+1,2)+1

公式逻辑：

• EOMONTH(B2,0)：获取当前月份的最后一天
• EOMONTH(B2,0)-DAY(EOMONTH(B2,0))+1：获取当前月份的第一天
• WEEKNUM(...,2)：以周一作为一周的起始日计算周数（若需以周日为起始，将参数改为1即可）
• 用月末周数减去月初周数再加1，得到该月份实际包含的周数

2. 自动生成重复行的数组公式 #

在Google Sheets中，可直接用数组公式生成完整的展开数据。假设原始数据在A2:C5区域，在空白单元格输入以下公式：

=ARRAYFORMULA(
  SPLIT(
    FLATTEN(
      MAP(A2:A5, B2:B5, C2:C5, 
        LAMBDA(metric, date, forecast, 
          IF(ISBLANK(date),, 
            REPT(metric&"|"&date&"|", 
              WEEKNUM(EOMONTH(date,0),2)-WEEKNUM(EOMONTH(date,0)-DAY(EOMONTH(date,0))+1,2)+1
            )
            &SEQUENCE(1, WEEKNUM(EOMONTH(date,0),2)-WEEKNUM(EOMONTH(date,0)-DAY(EOMONTH(date,0))+1,2)+1,1)&"|"&forecast&"|"
          )
        )
      )
    ), "|"
  )
)

方案逻辑：

• MAP遍历每一行原始数据
• 计算当前月份周数，用REPT重复拼接metric和date内容
• 用SEQUENCE生成对应周数的序号，拼接后和forecast组合
• FLATTEN将所有重复内容展开为单行列表
• SPLIT按分隔符拆分，直接生成包含metric、time、week、Forecast的完整表格

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