动态数组公式求助

ljt1015

求助

求助内容如图，感觉是要用个动态求和的数组公式，但实在想不出来该怎么写，只好求助论坛的大神了。

solextrade

=IF(B3<>B2,LOOKUP(,0/FREQUENCY(SUMIF(B:B,B3,O:O),SUBTOTAL(9,OFFSET(AE3,,,,ROW($1:$99)))),AE$2:AF$2),"")

咔咔乱坠

365可用，请参考

1. =IF(A3,@FILTER(R$2:DE$2,REDUCE(,IF(R$2:DE$2>=TODAY(),R3:DE3,),LAMBDA(x,y,HSTACK(x,SUM(TAKE(x,,-1),y))))>SUM(O3:O5)),"")

复制代码

砍一点，sum多余了

1. =IF(A3,@FILTER(R$2:DE$2,REDUCE(,IF(R$2:DE$2>=TODAY(),R3:DE3,),LAMBDA(x,y,HSTACK(x,TAKE(x,,-1)+y)))>SUM(O3:O5)),"")

复制代码

mykitami

=LET(tod,INDEX($R$2:$DE$2,MATCH(TODAY(),$R$2:$DE$2,)),lj,MAP(SEQUENCE(,500),LAMBDA(x,SUM(TAKE(OFFSET(tod,1,0,1,500),,x)))),c,SUM(O3:O5)-lj,INDEX(OFFSET(tod,0,0,1,500),MATCH(1=1,c<0,)))