J3=SUMPRODUCT((A3:A41=I2)*(RANK(C3:C41,C:C)<=10))
K3=SUMPRODUCT((E3:E41=I2)*(RANK(G3:G41,G:G)<=10))
M3{=MIN(J3:K3-L3)
J4=COUNTIF(M12:M18,"><")
K4=COUNTIF(M21:M26,"><")
L4=SUMPRODUCT(COUNTIFS(M12:M18,M21:M26,M12:M18,"><"))
M12 下拉{=INDEX(B:B,SMALL(IF((A$3:A$41=I$2)*(RANK(C$3:C$41,C:C)<=10),ROW($3:$41),99),ROW(A1)))&""
M21 下拉{=INDEX(F:F,SMALL(IF((E$3:E$41=I$2)*(RANK(G$3:G$41,G:G)<=10),ROW($3:$41),99),ROW(A1)))&""
N12=IF(M12="","",VLOOKUP(M12,B:C,2,))
N21=IF(M21="","",VLOOKUP(M21,F:G,2,))
留言
張貼留言