基于起止日期按月份拆分数量列值至多行并计算剩余数量

一、Power Query批量处理方案 #

适合处理大量数据，步骤如下：

• 选中原始数据区域，点击「数据」选项卡 → 「从表格/区域」，将数据导入Power Query编辑器
• 添加自定义列生成区间内所有月份的起始日序列：
  点击「添加列」→「自定义列」，输入公式：

  {Number.From(Date.StartOfMonth([起始日期]))..Number.From(Date.StartOfMonth([结束日期]))}
  

• 展开自定义列：点击列右侧的展开按钮，选择「展开到新行」
• 计算当月实际起止日期：
  • 添加「当月起始日」列：

    Date.From([自定义])
    

  • 添加「当月结束日」列：

    if Date.EndOfMonth(Date.From([自定义])) > [结束日期] then [结束日期] else Date.EndOfMonth(Date.From([自定义]))
    

• 计算当月分配数量：
  先添加「总月份数」列：

  Date.Month([结束日期]) - Date.Month([起始日期]) + 1 + (Date.Year([结束日期]) - Date.Year([起始日期]))*12
  

  再添加「当月分配数量」列（按平均分配，需按天数调整可替换公式）：

  Number.Round([数量]/[总月份数], 0)
  

  按天数精准分配的公式：

  Number.Round([数量] * (Duration.Days([当月结束日]-[当月起始日])+1)/Duration.Days([结束日期]-[起始日期]), 0)
  

• 计算剩余数量：
  先给原始数据添加「原始行索引」列（避免分组混乱），再按「原始行索引」分组，对组内的「当月分配数量」添加累计求和列，最后用总数量减去累计和得到剩余数量：

  [数量] - List.Sum(List.Range([当月分配数量], 0, [组内索引]))
  

• 清理多余列后，点击「关闭并上载」即可得到拆分结果

二、Excel公式法（适合少量数据） #

假设原始数据在A列（起始日期）、B列（结束日期）、C列（数量），从第2行开始：

• 生成月份起始日（E列）：E2输入公式，下拉至出现错误值：

  =IFERROR(EDATE(MAX(E$1:E1),1),A2)
  

• 计算当月结束日（F列）：F2输入：

  =MIN(EOMONTH(E2,0),B2)
  

• 计算总月份数（G列）：G2输入：

  =DATEDIF(A2,B2,"m")+1
  

• 当月分配数量（H列，平均分配）：H2输入：

  =ROUND(C2/G2,0)
  

  按天数分配的公式：

  =ROUND(C2*(F2-E2+1)/(B2-A2+1),0)
  

• 剩余数量（I列）：I2输入：

  =C2-SUMIF(E$2:E2,"<="&E2,H$2:H2)+H2
  

  下拉公式即可得到每行的剩余数量

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