excel - 找出较长期间内较短的天数进行计算

Question

我有一组日期，总共 7 个周期，每个周期跨越 6 个月，每个周期都有相应的计算因子。

我将让用户输入他们希望完成计算的期间，该期间可以属于 6 个月期间之一，也可以全部或部分包含在两个或多个此类期间之间。

插图:

1. 预设时间段:

用户输入:

我已经获得了用户输入的用于计算的周期的十进制值(以月为基础)。首先(请参阅用户输入)，2015 年 1 月 1 日至 2015 年 4 月 29 日期间的十进制值为每月 3.97。该时期的计算如下:

n*3.97*113%

对于第二个例子，2015年4月30日至2015年6月30日期间的十进制值为2.03，将用于按113%进行计算，然后将结果添加到已完成的计算中2015 年 7 月 1 日至 2015 年 12 月 20 日期间为 119%，使用十进制值 5.65:

(n*2.03*113%)+(n*5.65*119)

我认为我可以处理周期的中断，因为修订事件在特定日期每两年举行一次，但欢迎就此提出建议。更重要的是，我需要帮助跟踪与用户输入的周期相对应的预设计算因子(例如 132%)，如上所示。可行吗？

Answer 1

我将使用标准方法来查找 overlap between two dates并将任务分为三个部分，如我对 this recent question 的回答所示。

(1) 第一部分是查找用户的日期范围与整月中的一个或多个修订期之间的重叠，并且需要一个数组公式。我选择使用Datedif function ¹ 获取重叠开始和结束之间的月份差异。如果没有重叠，则输入到 Datedif 的开始日期将晚于结束日期，并且它将返回一个可由 Iferror 捕获的错误。如果用户的日期从 A2 和 B2 开始，则在 C2 中给出:

=SUM(IFERROR((DATEDIF(IF(K$2:K$8>A2,K$2:K$8,A2),IF(L$2:L$8<B2,L$2:L$8,B2),"m")+1)*M$2:M$8,0))

必须使用CtrlShiftEnter将其输入为数组公式

上述结果包括用户输入的第一个月和最后一个月，即使它们是不完整的月份。然后需要减去第一个月和最后一个月中缺失的天数。

(2) 第一个月缺失的天数占 D2 中该月天数的比例:

=SUMIFS($M$2:$M$8,$K$2:$K$8,"<="&A2,$L$2:$L$8,">="&A2)*(A2-EOMONTH(A2,-1)-1)/(EOMONTH(A2,0)-EOMONTH(A2,-1))

OP 指出，这也可以使用 sumproduct、vlookup 或 index/match 来完成。

(3) 上个月缺失的天数占 E2 中该月天数的比例:

=SUMIFS($M$2:$M$8,$K$2:$K$8,"<="&B2,$L$2:$L$8,">="&B2)*(EOMONTH(B2,0)-B2)/(EOMONTH(B2,0)-EOMONTH(B2,-1))

总数仅为 (1)-(2)-(3) 或

=C2-D2-E2

我把OP的两个例子的结果放在H2和H3中进行比较:我的结果在前3位有效数字上与它们一致。

n*3.97*113%

(n*2.03*113%)+(n*5.65*119)  

在所有情况下，我都设置 n=1 并忽略了比率是百分比的事实。

<小时/>

这显示了如何手动计算结果:

<小时/>

¹ 使用 Datedif 的优点:

^{(1) 与仅使用月份函数不同，可以跨年份边界工作。(2) 与 Iferror 配合使用，可以方便地识别不匹配的日期范围。}

使用 Datedif 的缺点:

^{(1) 这是一个未记录的函数，将来可能会被撤销。(2) 在这种特殊情况下，所有日期计算都在同一年之内，因此月份是可用的。}