硬凑
=DROP(LET(q,LOOKUP(ROW($2:$22),ROW($2:$22)/($B$2:$B$22<>""),$B$2:$B$22),w,LOOKUP(ROW($2:$22),ROW($2:$22)/($C$2:$C$22<>""),$C$2:$C$22),REDUCE("",UNIQUE(TOCOL(B2:B22,3)),LAMBDA(x,y,VSTACK(x,HSTACK(UNIQUE(FILTER(CHOOSE({1,2,3},q,w,D2:D22),q=y)),TRANSPOSE(BYCOL((q=y)*(D2:D22=TRANSPOSE(TAKE(UNIQUE(FILTER(CHOOSE({1,2,3},q,w,D2:D22),q=y)),,-1))),LAMBDA(a,SUM(a)))),TRANSPOSE(BYCOL((q=y)*(D2:D22=TRANSPOSE(TAKE(UNIQUE(FILTER(CHOOSE({1,2,3},q,w,D2:D22),q=y)),,-1)))*(E2:E22<>""),LAMBDA(a,SUM(a)))),TRANSPOSE(BYCOL((q=y)*(D2:D22=TRANSPOSE(TAKE(UNIQUE(FILTER(CHOOSE({1,2,3},q,w,D2:D22),q=y)),,-1)))*(E2:E22=""),LAMBDA(a,SUM(a)))) ))))),1)
|