G2=IFERROR(OFFSET(D$1,MATCH(ROW()-2,MMULT(N(ROW($1:$98)>COLUMN(A:CT)),COUNTIF(B:B,D$2:D$99)),),),"")
另解{=IF(COUNTIF(G$1:G1,INDEX(D:D,SMALL(MATCH(B$2:B$16,D:D,0),ROW()-1))),"",INDEX(D:D,SMALL(MATCH(B$2:B$16,D:D,0),ROW()-1)))
另解{=SUBSTITUTE(INDEX(B:B,RIGHT(SMALL(MATCH(B$2:B$16,D:D,)/1%+ROW($2:$16),ROW(A1)),2)),LOOKUP(1,0/(G$1:G1<>""),G$1:G2),)
另解{=IFERROR(INDEX($D$2:$D$6,MATCH(ROW(A1),MMULT(COUNTIF(OFFSET($D$1,,,ROW($D$1:$D$6)),TRANSPOSE($B$2:$B$16)),ROW($B$2:$B$16)^0)+1,0)),"")
E2(輔助)=SUMPRODUCT(COUNTIF(B:B,D$1:D1))
G2(E欄輔助)=IFERROR(OFFSET(D$1,MATCH(ROW()-2,E$2:E$6,),),"")
留言
張貼留言