D2 下拉{=IFERROR(INDIRECT(TEXT(SMALL(IF(REPT(A$2:B$10,COUNTIF(OFFSET(A$2,,{0,1},ROW($1:$9)),A$2:B$10)>COUNTIF(OFFSET(A$2:A$10,,{1,0}),A$2:B$10))<>"",ROW($2:$10)/1%+{1,2}),ROW(A1)),"!R0C00"),),"")
E2 下拉{=IFERROR(INDIRECT(TEXT(SMALL(IF({1,0},IF(ISNA(MATCH(A$2:A$10&COUNTIF(OFFSET(A$2,,,ROW($1:$9)),A$2:A$10),B$2:B$10&COUNTIF(OFFSET(B$2,,,ROW($1:$9)),B$2:B$10),)),ROW($2:$10)/1%+1),IF(ISNA(MATCH(B$2:B$10&COUNTIF(OFFSET(B$2,,,ROW($1:$9)),B$2:B$10),A$2:A$10&COUNTIF(OFFSET(A$2,,,ROW($1:$9)),A$2:A$10),)),ROW($2:$10)/1%+2)),ROW(A1)),"!R0C00"),),"")
另解{=IFERROR(RIGHT(SMALL(IFERROR(REPT(A$2:B$10,COUNTIF(OFFSET(A$2,,{0,1},ROW($1:$9)),A$2:B$10)>COUNTIF(OFFSET(A$2:A$10,,{1,0}),A$2:B$10))+ROW($2:$10)/1%%+{1,2}/1%,"1E9"),ROW(A1)))*1,"")
留言
張貼留言