基于每月日期计划在Google Sheets生成日期列表的实现方法

前提假设 #

• 月度计划表格位于A2:C7（A列=每月日期，B列=金额，C列=备注）
• 起始日期StartDate存于单元格D1，结束日期EndDate存于单元格E1

解决方案：数组公式实现 #

直接在空白单元格输入以下数组公式（新版Google Sheets可直接回车，旧版需按Ctrl+Shift+Enter）：

=LET(
  所有月份, SEQUENCE(DATEDIF(D1,E1,"M")+1,1,EOMONTH(D1,-1)+1),
  扩展计划, FLATTEN(ARRAYFORMULA(所有月份&"|"&A2:C7)),
  拆分数据, SPLIT(扩展计划,"|"),
  计划日期, INDEX(拆分数据,,1),
  每月计划日, INDEX(拆分数据,,2),
  金额, INDEX(拆分数据,,3),
  备注, INDEX(拆分数据,,4),
  真实日期, ARRAYFORMULA(
    IF(
      DAY(EOMONTH(计划日期,0)) < 每月计划日,
      EOMONTH(计划日期,0),
      DATE(YEAR(计划日期),MONTH(计划日期),每月计划日)
    )
  ),
  过滤结果, FILTER({真实日期,金额,备注},真实日期>=D1,真实日期<=E1),
  SORT(过滤结果,1,TRUE)
)

公式逻辑说明 #

1. 生成覆盖的所有月份：通过SEQUENCE和DATEDIF计算起止日期包含的所有月份的第一天，确保每个月都被处理
2. 关联计划与月份：用FLATTEN和ARRAYFORMULA把每个月份和所有计划条目组合，形成可拆分的字符串数组
3. 拆分并提取数据：将组合后的字符串拆分为月份、计划日、金额、备注四个字段
4. 计算真实日期：判断计划日是否超过当月天数，若超过则自动替换为当月最后一天，否则生成对应日期
5. 过滤与排序：只保留在起止日期范围内的记录，并按日期升序排列

示例效果 #

当StartDate=11/01/2024、EndDate=12/31/2024时，输出结果会自动处理11月无31号的情况，将水费条目映射为11/30/2024，同时包含12月所有计划的对应日期。

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