Power Query/DAX/Excel实现部门支出预测表按频次自动拆分方案

录入样本参考：

预期拆分结果参考：

提前把录入的频次选项映射为间隔月数字（如月付=1、季付=3、年付=12），可以大幅简化后续处理逻辑

方案1：Power Query 实现（优先推荐，适配Power BI模型需求） #

• 第一步：将人工录入的预测表导入Power Query，确认字段完整：支出类别、单次金额、间隔月数（对应频次转换后的数字）、开始日期、结束日期
• 第二步：添加自定义列生成该条记录覆盖的所有日期，M代码为：
  List.Dates([开始日期], Duration.Days([结束日期]-[开始日期])+1, #duration(1,0,0,0))
• 第三步：将生成的日期列表扩展为行，新增筛选判断列，M代码为：
  Date.Diff([开始日期], [扩展后的日期], "Month") mod [间隔月数] =0 and [扩展后的日期] <= [结束日期]
• 第四步：筛选判断列返回TRUE的行，保留支出类别、支付日期、单次金额三个核心字段，加载到Power BI数据模型后即可直接关联实际支出表，支持日期切片器筛选比对。

方案2：Excel公式实现（无需Power Query场景） #

先准备两个基础表：

1. 录入表：将录入区域转为Excel表对象，命名为预测录入表，字段和上述Power Query导入的字段一致
2. 辅助日期表：A列生成你需要覆盖的所有连续日期（比如从2024年1月1日到2027年12月31日，下拉填充即可）

• Excel 365及以上版本（支持动态数组）：在辅助日期表的B列输入公式，直接返回当日所有符合条件的支出记录：
  =FILTER(预测录入表, (预测录入表[开始日期]<=[@日期])*(预测录入表[结束日期]>=[@日期])*(MOD(DATEDIF(预测录入表[开始日期],[@日期],"m"),预测录入表[间隔月数])=0),"无对应支出")
• 低版本Excel（无FILTER函数）：用INDEX+SMALL数组公式，按Ctrl+Shift+Enter回车生效，下拉即可获取当日所有支出类别，再用VLOOKUP匹配对应金额即可：
  =IFERROR(INDEX(预测录入表[支出类别],SMALL(IF((预测录入表[开始日期]<=[@日期])*(预测录入表[结束日期]>=[@日期])*(MOD(DATEDIF(预测录入表[开始日期],[@日期],"m"),预测录入表[间隔月数])=0),ROW(预测录入表)-ROW(预测录入表[#表头])),ROW(A1))),"")

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