- =REDUCE(K3:O3,A4:A15,LAMBDA(x,y,VSTACK(x,LET(_c,TOCOL(IF((F$4:F$11&$G$4:$G$11=OFFSET(y,,1)&OFFSET(y,,2))*(I$4:I$11>=COLUMN(A:ZZ)),H$4:H$11,\),3),_b,TAKE(DROP(_c,SUMIF(B$3:OFFSET(y,-1,1),OFFSET(y,,1),D$3)),OFFSET(y,,3)),_a,UNIQUE(_b),IFNA(HSTACK(OFFSET(y,,,,3),_a,MAP(_a,LAMBDA(x,SUM(N(_b=x))))),OFFSET(y,,,,3))))))
复制代码 形式1- =IFNA(REDUCE(A3:J3,A4:A16,LAMBDA(x,y,VSTACK(x,LET(_a1,VSTACK(IFNA(HSTACK(L4:M4,TRANSPOSE(VSTACK(N3:Q3,N4:Q4))),L4:M4),IFNA(HSTACK(L5:M5,TRANSPOSE(VSTACK(N3:Q3,N5:Q5))),L5:M5)),_a,TOCOL(IF((CHOOSECOLS(_a1,1)&CHOOSECOLS(_a1,2)=OFFSET(y,,1)&OFFSET(y,,2))*(CHOOSECOLS(_a1,4)>=COLUMN(A:ZZ)),CHOOSECOLS(_a1,3),\),3),_b1,SUMIF(B$3:OFFSET(y,-1,1),OFFSET(y,,1),D$3),_b2,SUMIF(B$3:OFFSET(y,,1),OFFSET(y,,1),D$3),_b,TAKE(DROP(_a,_b1),OFFSET(y,,3)),_c,UNIQUE(_b),_d,TOROW(MAP(_c,LAMBDA(x,SUM(N(_b=x))))),_e,HSTACK(OFFSET(y,,,,4),_b2),_f,IFS(@_c=F3,HSTACK(_e,_d),@_c=G3,HSTACK(_e,"",_d),@_c=H3,HSTACK(_e,"","",_d),@_c=I3,HSTACK(_e,"","","",_d)),_f)))),"")
复制代码 形式2
|