如何用Excel公式将捐赠记录按周期逐月展开

需求说明 #

现有活动捐赠成员清单，每位成员按周期按月捐赠，需将包含ID、姓名、捐赠金额、捐赠起止日期、状态的原始表格，展开为按捐赠周期逐月生成单条记录的格式，全程仅用Excel公式实现。

原始表格示例

目标展开格式

优化后的Excel公式 #

针对原公式需逐个处理列的问题，以下公式可批量处理重复列，更简洁易维护：

=LET(
    原始数据, A2:F6,
    起止日期, INDEX(原始数据,,4):INDEX(原始数据,,5),
    月度序列, MAP(INDEX(起止日期,,1), INDEX(起止日期,,2), LAMBDA(开始,结束, EDATE(开始, SEQUENCE(DATEDIF(开始,结束,"m")+1,,0)))),
    重复次数, BYROW(月度序列, LAMBDA(x, ROWS(x))),
    重复基础列, TOCOL(INDEX(原始数据,,{1,2,3,6}),,1),
    展开日期, TOCOL(月度序列,,1),
    HSTACK(
        INDEX(重复基础列, TOCOL(SEQUENCE(ROWS(原始数据))/1^0, REPT(1,重复次数),,1)),
        TEXT(展开日期, "dd-mmm-yyyy")
    )
)

公式说明 #

1. 原始数据：定义待处理的原始数据范围（示例为A2:F6，可根据实际表格调整）
2. 起止日期：提取表格中的捐赠开始、结束日期列
3. 月度序列：对每一行的起止日期，生成从开始月到结束月的每月1日日期序列
4. 重复次数：计算每一行需要重复的记录数（即捐赠周期的月数）
5. 重复基础列：提取需要重复的固定列（ID、姓名、捐赠金额、状态）并转为单列格式
6. 展开日期：将所有月度日期序列转为单列格式
7. HSTACK：将重复后的固定列与展开的日期列合并，生成最终的展开表格

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