excel - 计算日期范围的总和

Question

我想计算一系列重叠日期的多个值的总和。它应该返回特定年份的总和，并按比例拆分超过一年的值。

例子:

╔══════════╦════════════╦════════════╗
║  Value   ║    From    ║     To     ║
╠══════════╬════════════╬════════════╣
║ 100,00 € ║ 01.01.2015 ║ 31.12.2015 ║
║ 150,00 € ║ 01.07.2015 ║ 31.06.2016 ║
║ 300,00 € ║ 01.01.2016 ║ 31.12.2016 ║
╚══════════╩════════════╩════════════╝

2015 年的预期结果:175,00 €
2016 年的预期结果:375,00 €

我没有找到使用 SUMIF() 或 SUMIFS() 的有效方法。特别是对于重叠日期按比例拆分结果。

Answer 1

我建议使用辅助列，列标题是计算总数的年份，即 2015 年和 2016 年。

我在数据中添加了一个额外的行，以确保它在时间段不是整年的情况下也能正常工作。

重叠区域是两个日期范围较高端的较小值减去两个日期范围较低端的较大值。如果结果为负，则没有重叠，结果应设置为零。对于从 E2 开始的每一行，这产生了以下公式:-

=MAX((MIN(DATE(E$2,12,31),$C3)-MAX(DATE(E$2,1,1),$B3))+1,0)

第一个列标题在 E2 中。

然而，根据问题中给出的数据，这会产生一个不准确的答案，因为一年中第二个 6 个月的天数比第一个多6 个月。

如果您使用以下公式计算整月，您可以获得准确的答案:-

=MAX((MIN(E$2*12+12,YEAR($C3)*12+MONTH($C3))-MAX(E$2*12+1,YEAR($B3)*12+MONTH($B3)))+1,0)

然后计算出 G3 中每个时间段的总重叠:-

=E3+F3

并计算支出的比例为年度 X 重叠量/H3 中的总重叠量:-

 =$A3*E3/$G3    

该方法可以很容易地扩展到任意日期范围，而不仅仅是整年。

工作天数:-

几个月的工作:-

这是一个数组公式(必须用Ctrl-Shift-Enter 输入)，一步计算，但看起来有点长。在生产版本中，$C3:$C6 将被替换为 $C:$C 等。:-

=SUM(IF(IF(E$2*12+12<YEAR($C3:$C6)*12+MONTH($C3:$C6),E$2*12+12,YEAR($C3:$C6)*12+MONTH($C3:$C6))-IF(E$2*12+1>YEAR($B3:$B6)*12+MONTH($B3:$B6),E$2*12+1,YEAR($B3:$B6)*12+MONTH($B3:$B6))+1>0,IF(E$2*12+12<YEAR($C3:$C6)*12+MONTH($C3:$C6),E$2*12+12,YEAR($C3:$C6)*12+MONTH($C3:$C6))-IF(E$2*12+1>YEAR($B3:$B6)*12+MONTH($B3:$B6),E$2*12+1,YEAR($B3:$B6)*12+MONTH($B3:$B6))+1,0)*$A3:$A6/(YEAR($C3:$C6)*12+MONTH($C3:$C6)-YEAR($B3:$B6)*12-MONTH($B3:$B6)+1))

对于部分在 2015 年和 2016 年之外的日期范围，这两种方法(辅助列和数组公式)会给出不同的结果，例如1/7/14-30/6/15 将以第一种方式将全部金额投入 2015 年，而以第二种方式仅投入一半。