B2{=IF(A2=A$1,"名次",IF(A2,RANK(A2,OFFSET(A2,LOOKUP(1,0/(A$1:A1=A$1),ROW($1:1))-ROW(A1),,MATCH(1=1,A2:A11="",)+ROW(A1)-LOOKUP(1,0/(A$1:A1=A$1),ROW($1:1)))),""))
另解{=IF(A2=A$1,"名次",IF(A2,RANK(A2,INDEX(A:A,MATCH(1,0/(A$1:A2="總分"))+1):INDEX(A:A,MATCH(1=1,A2:A999="",)+ROW()-2)),""))
留言
張貼留言