本帖最后由 mykitami 于 2024-10-23 19:55 编辑
需要先在单元格中设置两个日期,用于统计起始日期与结束日期之间的数据
=LET(s,DROP(REDUCE("",SEQUENCE(G1-E1+1,,E1),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(INDIRECT("'"&y&"'!B2"),INDIRECT("'"&y&"'!A3:B999")),y)))),1),b,FILTER(s,INDEX(s,,2)<>0),data,DROP(PIVOTBY(INDEX(b,,2),INDEX(b,,1),TAKE(b,,-1),SINGLE,0,0,,0,),1),lz,DROP(REDUCE("",SEQUENCE(ROWS(data)),LAMBDA(m,n,LET(a,TOCOL(SCAN(0,SEQUENCE(COLUMNS(data)-1),LAMBDA(x,y,IF(INDEX(INDEX(DROP(data,,1),n,),,y+1)>INDEX(INDEX(DROP(data,,1),n,),,y),x,x+1))),2),VSTACK(m,HSTACK(INDEX(data,n,1),ROWS(FILTER(a,a=MAX(a)))-1))))),1),lj,DROP(REDUCE("",SEQUENCE(ROWS(data)),LAMBDA(m,n,LET(a,TOCOL(SCAN(0,SEQUENCE(COLUMNS(data)-1),LAMBDA(x,y,IF(INDEX(INDEX(DROP(data,,1),n,),,y+1)<INDEX(INDEX(DROP(data,,1),n,),,y),x,x+1))),2),VSTACK(m,ROWS(FILTER(a,a=MAX(a)))-1)))),1),SORT(HSTACK(TAKE(lz,,1),TAKE(lz,,-1)-lj),2,-1))
|