求Excel公式：跨月采样周期排放量按月比例分配求和

适用场景 #

针对记录24小时至1周的采样周期排放量，按日历月统计总量，跨月采样周期需按时间占比分摊至对应月份。

F3单元格公式 #

假设：

• A列：采样开始时间（格式为dd/mm/yy hh:mm）
• B列：采样结束时间
• C列：该采样周期的总排放量
• E3单元格为目标统计月份（如Feb-24，需设置为日期格式，实际存储当月任意一天即可）

公式：

=SUMPRODUCT(C:C*MAX(0,MIN(B:B,EOMONTH(E3,0)+1)-MAX(A:A,EOMONTH(E3,-1)+1))/(B:B-A:A))

公式拆解说明 #

1. EOMONTH(E3, -1)+1：计算目标月份的第一天（如Feb-24对应2024/2/1 00:00）
2. EOMONTH(E3, 0)+1：计算目标月份的下一个月第一天（如Feb-24对应2024/3/1 00:00，作为当月结束边界）
3. MAX(A:A, EOMONTH(E3, -1)+1)：取采样开始时间与当月第一天的较晚值，确定该采样在当月的实际起始时间
4. MIN(B:B, EOMONTH(E3, 0)+1)：取采样结束时间与下月第一天的较早值，确定该采样在当月的实际结束时间
5. MIN(...) - MAX(...)：得到采样周期落在当月的时长，用MAX(0,...)排除完全不在当月的采样（避免出现负数）
6. /(B:B-A:A)：计算当月时长占整个采样周期的比例
7. *C:C：得到该采样周期分摊至当月的排放量
8. SUMPRODUCT：对所有采样周期的分摊排放量求和，得到当月总排放

注意事项 #

• 务必确保A、B列为标准日期时间格式（不是文本），否则公式会计算错误
• 若数据量较大，建议用具体行范围替代整列（如A2:A1000代替A:A），提升计算效率
• 若存在采样开始/结束时间相同的无效数据，需提前过滤，避免出现除以0的错误
• 月末/月初的边界采样（如2月最后一天到3月第一天），公式会自动按时间占比拆分计算

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