E5{=MAX((B$5:B$14>=B$2)*(C6=C$5:C$14)*B$5:B$14/1%+ROW($5:$14))
G5:I8{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF((MATCH($C$5:$C$14,$C$5:$C$14,)=ROW($1:$10))*($E$5:$E$14>100),$E$5:$E$14),ROW(A1)),2)),"")
K5:M8{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(IFERROR(MATCH(1&$C$5:$C$14,($B$5:$B$14>=$B$2)*1&$C$5:$C$14,),)=ROW($1:$10),TRANSPOSE(RIGHT(SMALL(($C$5:$C$14=TRANSPOSE($C$5:$C$14))*($B$5:$B$14>=$B$2)*$B$5:$B$14/1%+ROW($5:$14)+COLUMN($A:$J)*10^8,COLUMN($A:$J)*10),8)*1)),ROW(A1)),2)),"")
K5:M8另解{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF((COUNTIFS($C:$C,$C$5:$C$14,$B:$B,">"&$B$5:$B$14)=0)*($B$5:$B$14>=$B$2),$B$5:$B$14/1%+ROW($5:$14)),ROW(A1)),2)),"")
留言
張貼留言