동점자 석차 구하기 수식 설명

이전의 글에서, 우선 순위를 부여하여 동점자의 석차를 구하는 수식을 알아보았습니다.

위 그림에서, 학생4의 순위를 구하는 식은 아래와 같습니다.

=IF(COUNTIF($F$2:$F$16,F5)>1,RANK(F5,$F$2:$F$16)+SUMPRODUCT(($F$2:$F$16=F5)*($H$2:$H$16>H5)),RANK(F5,$F$2:$F$16))

IF(COUNTIF($F$2:$F$16,F5)>1,		총점이 자신과 같은 사람의 수가 1보다 크면 (두명 이상이면)	총점이 384점인 사람은 총 4명이므로, 참이다.
	,RANK(F5,$F$2:$F$16)	총점을 기준으로 한 자신의 순위에다가	총점 기준 순위는 1위 (동점자는 4명)
	+SUMPRODUCT(($F$2:$F$16=F5)*($H$2:$H$16>H5))	거기에 총점이 자신의 총점과 같고, 가중값이 자신보다 큰 사람의 수를 더해준다	총점이 같고 가중값이 자신보다 큰 사람은 총 3명 있으므로, 3
RANK(F5,$F$2:$F$16))		총점이 자신과 같은 사람의 수가 1보다 크지 않으면(자신뿐이면) 총점을 기준으로 한 자신의 순위를 구한다.

 

그래서 최종순위는 3+1=4 가 됩니다.

 

즉, 기존의 RANK함수로만 순위를 구하면 동점자는 무조건 같은 순위로 산출됩니다. 여기서, 과목별 가중치를 부여한 가중값을 구한 후, 이 가중값이 자신보다 높은 사람은 자신보다 순위가 위로 가게 되므로, 최종 순위를 구하는 식은 다음과 같습니다.

동점자를 같은 순위로 처리한 순위 + 가중값이 자신보다 높은 사람 수 = 가중치 반영한 최종 순위

 

SUMPRODUCT함수를 사용한 수식은, 이전에 쓴 글에도 있지만,

($F$2:$F$16=F5) 이나 ($H$2:$H$16>H5)

이런 식으로 배열과 비교연산자(<,>,=)가 함께 있으면, 그 배열에서 해당 조건을 만족하는 셀의 개수를 구하고,

($H$2:$H$16)

이렇게 배열만 있으면 해당 배열에 있는 모든 셀을 더한다는 의미입니다.

따라서

SUMPRODUCT(($F$2:$F$16=F5)*($H$2:$H$16>H5)

이렇게 두 개의 수식을 연결하면, 두 개의 조건을 모두 만족하는 셀의 개수를 구하라는 의미이며,

SUMPRODUCT(($F$2:$F$16=F5)*($H$2:$H$16)

이렇게 두 개의 수식을 연결하면, ($F$2:$F$16=F5)의 조건을 만족하는 ($H$2:$H$16)배열의 셀을 더한다는 의미입니다.

 

SUMPRODUCT함수는 처음에 이해하기는 조금 어렵지만, 자유롭게 쓸 수 있으면 활용도가 매우 높으므로 연구해 두시면 좋을겁니다.

http://flogsta.tistory.com/search/sumproduct