急，只有起止日期，有重叠，怎么计算出各月的租金啊

1111Zoey

hugeinfo 发表于 2024-12-13 17:41
=LET(d,DROP(REDUCE("",A2:A962,LAMBDA(x,y,VSTACK(x,LET(a,SEQUENCE(OFFSET(y,,1)-y+1,,y,1),IFNA(HSTACK( ...

你好可以了 是计算延后了 再次感谢

zwzc

=LET(aa,DROP(REDUCE("",A2:A962,LAMBDA(x,y,VSTACK(x,DROP(REDUCE("",SEQUENCE(DATEDIF(TEXT(y,"e/mm"),OFFSET(y,,1),"M")+1),LAMBDA(s,t,LET(a,OFFSET(y,,2)/(OFFSET(y,,1)-y+1),b,TEXT(OFFSET(y,,1),"e/mm"),c,TEXT(EDATE(y,t-1),"e/mm"),IFNA(VSTACK(s,IF((b=c),IF(DATEDIF(TEXT(y,"e/mm"),OFFSET(y,,1),"M")=0,HSTACK(c,OFFSET(y,,2)),HSTACK(c,(OFFSET(y,,1)-c+1)*a)),HSTACK(c,ABS(TEXT(EDATE(y,t),"e/mm")-MAX(c,y))*a))),"")))),1)))),1),bb,TAKE(aa,,1),cc,TAKE(aa,,-1),dd,GROUPBY(bb,cc,SUM,,0),dd)

写的长了点

hugeinfo

1111Zoey 发表于 2024-12-13 17:54
你好 怎么我复制出来就变成了1900/1了

=LET(d,DROP(REDUCE("",A2:A962,LAMBDA(x,y,VSTACK(x,LET(a,SEQUENCE(OFFSET(y,,1)-y+1,,y,1),b,IFNA(HSTACK(a,OFFSET(y,,2)/ROWS(a)),OFFSET(y,,2)/ROWS(a)),GROUPBY(TEXT(TAKE(b,,1),"yyyy/mm"),TAKE(b,,-1),SUM,,0))))),1),GROUPBY(TAKE(d,,1),TAKE(d,,-1),SUM,,0))

这样运算快点   你试试

mykitami

=LET(t,DROP(REDUCE("",C2:C962,LAMBDA(x,y,LET(s,OFFSET(y,,-2),a,TEXT(SEQUENCE(OFFSET(y,,-1)-s+1,,s),"e-mm"),b,y/ROWS(a),VSTACK(x,IF({1,0},a,b))))),1),VSTACK({"年月","月租金"},GROUPBY(TAKE(t,,1),TAKE(t,,-1),SUM,0,0,)))

有些卡

1111Zoey

hugeinfo 发表于 2024-12-14 07:37
=LET(d,DROP(REDUCE("",A2:A962,LAMBDA(x,y,VSTACK(x,LET(a,SEQUENCE(OFFSET(y,,1)-y+1,,y,1),b,IFNA(HST ...

谢谢你 果然快了很多