엑셀 순위구하기(RANK함수), 중복값 순위매기기대해 살펴보자
순위구하기(Rank함수)는 학교 같은반 학생들의 성적순위나 보험사 판매실적에 순위에 따른 상여금 지급대상자 등 다양하게 활용될 수 있다. 단지 결과값(합계, 최종값)으로 동일 순위가 표기되도 괜찮다면 Rank함수만으로도 고민해결
하지만, 예를 들어 같은반 학생들 중 동일한 성적이 존재할 경우 과목별 가중치에 따른 순위구하기(?) 다시 말해 Rank함수로 구해진 순위에서 중복값 표기가 없는 결과를 얻고자 할때 해결방법을 알아보자
RANK(순위를 구하려는 수, 숫자 목록의 배열 또는 참조, [순위 결정 방법])
RANK함수는 수 목록에서 특정 수의 순위 반환으로 목록에 있는 다른 수와의 상대 크기를 말하며 수의 위치와 순위가 같아질 수 있다. 엑셀 상위버전은 RANK함수에 더하여 RANK.AVG 함수, RANK.EQ 함수 제공된다.
(중복순위)주의점은 중복 숫자에는 같은 순위가 매겨지고 다음 숫자의 순위에 영향을 준다.
예를 들어 오름차순으로 정렬된 정수 목록에서 숫자 10이 두 번 나타나고 모두 순위가 5이면 11의 순위는 7이 되어 순위 6에 해당하는 숫자는 없게된다.
RANK함수와 SUM함수 조합으로 중복값(중복순위) 없애기 예제를 통해 살펴보자.
엑셀예제는 "ㅇㅇ초등학교 ㅇ반 성적현황"으로 9명의 성적결과 ⓐ총점이 같은 경우 ⓑ국어,수학,영어, 사회 과목 순으로 높은 점수에 대한 순위구하기다.
엑셀예제 중복순위(중복값) 없애기 결론은 아래 구문과 같다.
○ 구문 : {=RANK(총점,총점범위)+SUM((총점범위=총점)*(과목1범위>과목1))+SUM((총점범위=총점)*(과목1범위=과목1)*(과목2범위>과목2))+SUM((총점범위=총점)*(과목1범위=과목1)*(과목2범위=과목2)*(과목3범위>과목3))}
엑셀예제 순위구하기로 도출된 결과항목(ⓐ ~ ⓓ )을 하나씩 살펴보자.
ⓐ [총점] 순위구하기
- 구문 : =RANK($F4,$F$4:$F$12)
- RANK함수 결과 대상학생 중 총점이 동일한 1등 4명, 5등 4명, 9등 1명
- RANK함수는 동일점수에 대한 동일순위가 반환되고 다음 순위에 영향을 미치기 때문
ⓑ [총점],[국어] 순위구하기
- 구문 : {=RANK($F4,$F$4:$F$12)+SUM(($F$4:$F$12=$F4)*($B$4:$B$12>$B4))}
- ⓐ 동일순위 중 [국어]과목 높은 점수 순으로 1등 4명 => 3명으로 반환되며 다음 순위에도 영향 미침
ⓒ [총점],[국어],[수학] 순위구하기
- 구문 : {=RANK($F4,$F$4:$F$12)+SUM(($F$4:$F$12=$F4)*($B$4:$B$12>$B4))+SUM(($F$4:$F$12=$F4)*($B$4:$B$12=$B4)*($C$4:$C{$12>$C4))}
- ⓐ,ⓑ 동일순위 중 [국어]과목 높은 점수 순으로 1등 3명 => 2명으로 반환되며 다음 순위에도 영향 미침
ⓓ [총점],[국어],[수학] 순위구하기
- 구문 : {=RANK($F4,$F$4:$F$12)+SUM(($F$4:$F$12=$F4)*($B$4:$B$12>$B4))+SUM(($F$4:$F$12=$F4)*($B$4:$B$12=$B4)*($C$4:$C$12>$C4))+SUM(($F$4:$F$12=$F4)*($B$4:$B$12=$B4)*($C$4:$C$12=$C4)*($D$4:$D$12>$D4))}
- ⓐ,ⓑ,ⓒ 동일순위 중 [국어]과목 높은 점수 순으로 1등 2명 => 1명으로 반환되며 다음 순위에도 영향 미침
끝으로 엑셀예제를 통해 살펴본 결과 중복값 없는 순위구하기는
○ [총점] => [과목1] 점수 => [과목2] 점수 => [과목3] 점수...순차적으로 항목별 높은 점수을 비교하며 순위구하기
① [총점]이 같은 경우 [과목1] 점수가 높은 순
② [총점],[과목1]이 같은 경우 [과목2] 점수가 높은 순
③ [총점],[과목1],[과목2]이 같은 경우 [과목3] 점수가 높은 순
④ 순차적으로 진행하며 추가되는 과목이 있는 경우 반복유형으로 적용
○ 수식 구문 부가설명
- SUM(($F$4:$F$12=$F4)*($B$4:$B$12>$B4))
- [총점]이 같을 경우 반환값 [1]=[TRUE], [과목1]점수 높은지 반환값 [1]=[TRUE] 또는 [0] = [FALSE] 곱
- 반환값 1*1(TRUE*TRUE)=1, 1*0(TRUE*FALSE)=0 의 값 반환
- Rank함수 [총점] 우선순위로 [과목]별 순위 반환값을 합하여 순위가 정해짐
○ 수식구문 중괄호 {} 는 배열수식으로 수식 입력 후 CSE(ctrl + shift + enter)키 동시 누름 표시
엑셀예제파일 -
엑셀 순위구하기(rank함수), 중복값 순위매기기.xlsx
