순위를 나타낼 때 동일값의 공동순위 상관없이 다음 값은 순차적인 순위로 나타내기

엑셀에서 순위를 구하는 함수인 RANK, RANK.EQ, RANK.AVG는 처음엔 모두 1등으로 설정하고, 자신의 점수를 다른 점수와 비교해서 자신보다 큰 값이 있으면 1씩 증가하는 방식으로 구현되어 있습니다.

그래서 1등이 둘인 경우 다음 등수는 3등이 됩니다.

 

유튜브 댓글에 M*******님이 질문을 하셨습니다.

 

강사님! 강의 잘 듣고 있습니다.
그런데 RANK함수로 순위 결정에 있었어 똑 같은 수치가 나올 때는 어떤 방법으로 하나요?
예) 1,2,3,4,5,6,7 순위로 나오게 끔 하고 싶은데 5위가 2개입니다. 그러면 6위가 없고 바로 7위가 표기 됩니다.
RANK.AVG함수로 했을 때 소숫점 까지 똑같이 나올 경우도 있네요..
강사님께서 해결해 주시면 고맙겠습니다.

 

 

찾아보니 SQL에는 DENSE_RANK 함수가 있어 원하는 대로 구할 수 있지만 엑셀에는 이 함수가 없습니다.

https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver16

 

 

엑셀에서 순위를 나타낼 때 동일값의 공동순위 상관없이 다음 값은 순차적인 순위로 나타내는 방법을 소개하겠습니다.

 

실습 파일 다운로드

중복등수 다음을 순차적으로 나타내기_실습.xlsx

0.01MB

 

출석번호 5, 7번이 공동 2등이어서 다음 순위인 출석번호 1번은 4등입니다.

 

새롭게 순위를 구하기 위해 I열 순위를 지웁니다.

 

H열 점수 기준으로 내림차순 정렬합니다.

 

[I5] 셀에 수식을 작성합니다.

=SUMPRODUCT(1/COUNTIF($H$5:H5,$H$5:H5))

 

나머지 셀에 채우기 합니다.

 

[I5:I14] 셀 범위를 복사한 뒤 [선택하여 붙여넣기-값] 합니다.

 

B열 출석번호 기준으로 오름차순 정렬합니다.

 

 

SUMPRODUCT 함수와 COUNTIF 함수를 이용한 수식은 점수를 인식해서 등수를 매긴 것이 아니라 순서대로 번호를 매기는 역할을 합니다.

이때 중복 값이 있으면 같은 번호를 매기고 다음 번호는 순차적인 번호를 부여합니다.

 

그래서 점수를 기준으로 정렬한 뒤 함수로 등수를 구하고 값으로 변환해서 원래 순서대로 되돌린 것입니다.

만약 등수를 구하려는 데이터에 출석번호와 같은 원래 순서를 나타내는 필드가 없다면 점수로 정렬하기 전에 임의로 필드를 하나 추가해서 순번을 매기고 등수를 구한 뒤 다시 되돌린 다음 해당 필드는 없앱니다.

 

이 강좌에서 소개한 방법 말고 좀 더 좋은 방법이 있을 수 있습니다. ^^

 

필요하신 분께 도움이 되길 바랍니다.