excel - Excel：动态总和偏移量不为零但需要停止到今天

Question

当达到像今天这样的某个月份时，我正在努力如何限制我的公式的范围。在我的公式中，它在今天几个月之后继续总结接下来的四个月。在我的示例中，我想总结零后的前 4 个月，但直到今天的月份。如果不到 4 个月，假设到今天的月份还剩 3 个月，那么它应该只添加剩余的三个月。我需要限制宽度，但它应该总是四个，只有当它小于当前月份时它才应该小于四个月。

简	二月	三月	四月	君	七月	八月	十月	十一月	十二月
0	0	2	3	4	5	6	7	2	3	---> 第一期= 14 第二期= 13
0	0	0	0	0	0	2	1	4	2	---> 第一个周期= 3 第二个周期= 0

下图更好看

例如

前四个月：

=IFERROR(SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.4))," ")

后四个月：

=IFERROR(SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.8))
-SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.4))," ")

这两个公式都超出了当前月份并捕获了所有超出的日期，如 11 月、12 月等，我不知道如何将其保持在 4 个月，仅在剩余月份少于 4 个月时进行调整

非常感谢你的帮助！

标签： excelexcel-formulasummatchoffset

Answer 1

对于第一行的第一个时期，请使用：

=SUMPRODUCT($A2:$L2*(COLUMN($A2:$L2)>=AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1))*(COLUMN($A2:$L2)<AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)))*($A$1:$L$1<TODAY()))

并向右和向下复制

如果第一行包含实际格式化的日期而不是文本，则该公式将起作用

---

第一行第一个期间的解释：

1. $A2:$L2返回给定范围内的值数组：

   {0,0,1,3,4,6,5,5,5,10,4,3}

2. AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)返回值大于 0 的最小列号：

   3

3. 4*(COLUMN(A2)-1)变为4*(1-1)并返回：

   0

4. 所以表达式AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1)变为3+0并返回：

   3

5. 比较COLUMN($A2:$L2)>=AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1)变成{1,2,3,4,5,6,7,8,9,10,11,12}>=3并返回布尔数组：

   {F,F,T,T,T,T,T,T,T,T,T,T,T}

6. 类似地，第二个表达式COLUMN($A2:$L2)<AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2))变为{1,2,3,4,5,6,7,8,9,10,11,12}<7并返回：

   {T,T,T,T,T,T,F,F,F,F,F,F}

7. 第三个表达式$A$1:$L$1<TODAY()将第一行中的日期与今天的日期进行比较，并在 10 月 27 日返回：

   {T,T,T,T,T,T,T,T,T,T,F,F}

8. 所以我们得到的最终表达式{0,0,1,3,4,6,5,5,5,10,4,3}*{F,F,T,T,T,T,T,T,T,T,T,T,T}*{T,T,T,T,T,T,F,F,F,F,F,F}*{T,T,T,T,T,T,T,T,T,T,F,F}是：

   {0,0,1,3,4,6,0,0,0,0,0,0}

9. SUMPRODUCT总结如下：

   14