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

Question

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

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

插图：

1. 预设时段：

2. 用户输入：

我已经获得了用户输入用于计算的期间的十进制值（每月）。对于第一个实例（请参阅用户输入），从 01-01-2015 到 29-04-2015 期间的十进制值将是每月 3.97。该时期的计算如下：

n*3.97*113%

对于第二种情况，从 30-04-2015 到 30-06-2015 期间的十进制值将是 2.03，将用于在 113% 处进行计算，然后将结果添加到在 119% 处完成的计算中使用 01-07-2015 至 20-12-2015 期间的十进制值 5.65：

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

由于修订活动在特定日期每两年举行一次，因此我认为我可以处理期间的拆分，但欢迎对此提出建议。更重要的是，我需要帮助跟踪用户输入的周期对应的预设计算因子（比如 132%），如上图所示。可行吗？

标签： excelexcel-formula

Answer 1

我将使用标准方法来查找两个日期之间的重叠，并将任务分成三个部分，就像我对这个最近的问题的回答一样。

(1) 第一部分是查找用户的日期范围与整个月份的一个或多个修订期之间的重叠，并且需要一个数组公式。我选择使用Datedif 函数 ¹来获取重叠开始和结束之间的月差。如果没有重叠，则输入到 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 或索引/匹配来完成。

(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 并忽略了比率是百分比的事实。

---

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

---

¹使用 Dateif 的优点：

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

使用 Dateif 的缺点：

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