D2{=RIGHT(MAX(IF(A$2:A$21=A2,ROUND($B$2:$B$21*10^6,)/1%+ROW($2:$21))),2)*1
F2:G2 下拉{=IFERROR(OFFSET($A$1,RIGHT(LARGE(IF(MATCH($A$2:$A$21,$A:$A,)=ROW($2:$21),ROUND($B$2:$B$21*10^6,)/1%+ROW($1:$20)),ROW(A1)),2),(A$1=$B$1)*2),"")
2:J2 下拉{=IFERROR(OFFSET($A$1,RIGHT(LARGE(IF(MATCH($A$2:$A$21,$A:$A,)=ROW($2:$21),RIGHT(SMALL(ROUND(($A$2:$A$21=TRANSPOSE($A$2:$A$21))*TRANSPOSE($B$2:$B$21*10^6),)+ROW($1:$20)*10^12,ROW($1:$20)*20),11)/1%+ROW($1:$20)),ROW(A1)),2),(A$1=$B$1)*2),"")
K2=IF(I2="","",INDEX(B:B,VLOOKUP(I2,A:D,4,)))
留言
張貼留言