如何在Excel中生成含起止日期区间月份列的动态透视表

原始数据 #

预期效果 #

方案1：用Excel Power Query实现动态生成与计算 #

这个方法能自动识别数据中的最小/最大日期，动态生成月份列，且数据更新后可一键刷新。

1. 加载数据到Power Query
   选中原始数据区域，点击「数据」选项卡→「从表格/范围」，确认表头存在后进入Power Query编辑器。

2. 转换日期格式为标准日期

   • 添加自定义列，命名为StartDate，公式：

     Date.FromText("01/" & [start date], [Format="dd/MM/yy"])
     

   • 同理添加EndDate列，公式：

     Date.FromText("01/" & [end date], [Format="dd/MM/yy"])
     

   • 删除原有的start date和end date列。

3. 生成完整月份序列

   • 新建空白查询，命名为MonthSequence，打开高级编辑器，替换代码（把OriginalData换成你的原始数据查询名称）：

     let
         Source = OriginalData,
         MinDate = List.Min(Source[StartDate]),
         MaxDate = List.Max(Source[EndDate]),
         MonthCount = Duration.TotalMonths(MaxDate - MinDate) + 1,
         MonthList = List.Dates(MinDate, MonthCount, #duration(31,0,0,0)),
         MMYYFormat = List.Transform(MonthList, each Date.ToText(_, "MM/yy"))
     in
         MMYYFormat
     

   • 运行后得到从最小到最大日期的所有月份列表。

4. 拆分数据到对应月份
   回到原始数据查询（OriginalData），添加自定义列CoveredMonths，公式：

   List.Transform(
       List.Dates([StartDate], Duration.TotalMonths([EndDate] - [StartDate]) + 1, #duration(31,0,0,0)),
       each Date.ToText(_, "MM/yy")
   )
   

   点击该列右侧的扩展按钮，选择「扩展到新行」，此时每条记录会拆分成其覆盖的每个月份一行，Qty值保持不变。

5. 透视生成最终表格

   • 将MonthSequence转成表格并添加列名Month，回到原始拆分后的数据查询，点击「合并查询」，按Name和CoveredMonths与MonthSequence匹配。
   • 点击「转换」选项卡→「透视列」，设置：
     • 值列：Qty
     • 列值：Month
     • 聚合函数：求和
     • 替换空值：0
   • 点击「关闭并上载」，得到动态结果表格，后续更新原始数据后只需刷新即可。

方案2：用Excel动态数组公式实现 #

适合熟悉Excel公式的用户，无需Power Query：

1. 定义最小/最大日期
   在空白单元格输入（假设start date在C列，end date在D列）：

   =DATE(RIGHT(MIN(C2:C4),2),LEFT(MIN(C2:C4),2),1) // 最小日期，命名为MinDate
   =DATE(RIGHT(MAX(D2:D4),2),LEFT(MAX(D2:D4),2),1) // 最大日期，命名为MaxDate
   

   可通过「公式→定义名称」为这两个单元格命名，方便后续调用。

2. 生成动态月份列
   在表头行（比如G1单元格）输入：

   =TEXT(SEQUENCE(DATEDIF(MinDate,MaxDate,"m")+1,1,MinDate,"M"),"MM/yy")
   

   自动生成从最小到最大日期的所有月份列。

3. 计算每个月份的物品可用量
   在物品名称列（比如F2输入chair，F3输入table），G2单元格输入：

   =SUMIFS($B$2:$B$4,$A$2:$A$4,$F2,$C$2:$C$4,"<="&G$1,$D$2:$D$4,">="&G$1)
   

   向右向下填充公式，空值可通过IFERROR(公式,0)强制显示为0。

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