在Excel中按月份聚合计算日期区间天数总和的方法问询

搞定这个按月统计日期区间天数总和的需求，我有两种实用方案，分别适配不同的数据规模和操作习惯：

方法一：用Excel公式直接计算（适合小规模数据） #

步骤1：准备目标月份列表 #

先在Excel的一列（比如D列）生成Jan-17到Dec-17的月份序列：

• 在D2单元格输入=DATE(2017,ROW(A1),1)，下拉填充到D13
• 选中D2:D13，设置单元格格式为mmm-yy，就能得到你需要的月份格式

步骤2：编写聚合公式 #

在E2单元格（对应D2的月份）输入以下公式，下拉填充到E13：

=SUMPRODUCT(MAX(0,MIN(EOMONTH(D2,0),$C$2:$C$4)-MAX(D2,$B$2:$B$4)+1))

公式拆解：

• MAX(D2,$B$2:$B$4)：取每个日期区间的开始日期和当前月份第一天的较大值（避免区间开始早于当月的情况）
• MIN(EOMONTH(D2,0),$C$2:$C$4)：取每个日期区间的结束日期和当前月份最后一天的较小值（避免区间结束晚于当月的情况）
• MIN(...) - MAX(...) + 1：计算单个区间在当前月的有效天数
• MAX(0, ...)：过滤掉和当前月完全不重叠的区间（防止出现负数天数）
• SUMPRODUCT：把所有区间在当前月的天数累加求和

用你的示例数据测试的话，会得到：

• May-17 → 11
• Jul-17 → 6+6=12
• Aug-17 → 16
  完全符合你的预期结果。

方法二：用Power Query批量处理（适合大规模/需要更新的数据） #

如果你的数据量很大，或者后续需要频繁更新源数据，Power Query会更高效：

步骤1：导入数据到Power Query #

• 选中你的源数据区域（包含ID、Begin Date、End Date、Comment）
• 点击「数据」选项卡 → 「从表格/区域」，在弹出的对话框中勾选「我的表格有标题」，点击确定进入Power Query编辑器

步骤2：拆分日期区间并统计 #

1. 添加自定义列生成日期列表：
   点击「添加列」选项卡 → 「自定义列」，输入以下公式：

   = List.Transform({Number.From([Begin Date])..Number.From([End Date])}, each Date.From(_))
   

   这个公式会把每个日期区间转换成包含所有单日的列表

2. 扩展日期列表：
   点击自定义列右侧的展开按钮 → 「扩展到新行」，把每个日期拆成单独的行

3. 添加月份列：
   点击「添加列」→ 「日期」→ 「月份」→ 「自定义」，设置格式为mmm-yy（比如先生成yyyy-MM格式，再调整为你需要的mmm-yy样式）

4. 按月份分组求和：
   点击「转换」选项卡 → 「分组依据」，设置：

   • 分组依据：选择你刚添加的月份列
   • 新列名：比如「总天数」
   • 操作：选择「计数行」（因为每行对应1天，计数就是天数总和）

步骤3：加载结果回Excel #

点击「主页」选项卡 → 「关闭并上载」，就能得到按月聚合的天数统计表，后续源数据更新时，只需右键表格 → 「刷新」就能自动更新结果。

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