동점일 경우 다른 기준으로 순위 매기기

1. 문제

아래와 같이 평가그룹별로 순위를 매기려고 하는데,

동점인 경우 계량점수 반영 우선 순위.xlsx

0.01MB

 

 

동점이 있을 경우 Rank.EQ함수를 사용하면 동일한 순위로 계산돼서 10행과 13행이 모두 3위입니다.

 

그리고, Rank.AVG 함수를 사용하면 순위가 평균돼서 3.5라고 표시됩니다.

 

그렇다고 Rank함수의 경우 2개의 조건을 입력할 수도 없습니다.

이 경우의 해결 방법을 알아보려고 합니다.

 

2. 해법

가. 논리

위의 경우 점수가 높은 것이 순위가 높기 때문에
자신보다 점수가 높은 것의 개수를 세면 
자신이 최고 점수일 경우 0이 나오게 되므로 +1이 순위가 됩니다..

그리고, 동 순위일 경우는
자신의 계량평가 점수보다 높은 것이 있다면 자신이 후순위가 되고,
없다면 1순위가 됩니다.

그리고, 평가그룹별로 구분해서 순위를 매겨야 하므로
평가그룹이 같은 것끼리만 개수를 세야 합니다.

 

나. 수식

(1) 점수가 높은 것의 개수 세기

두 가지 이상 조건을 만족하는 개수를 세야 하므로
CountIf 가 아니라 CountIfs 함수를 사용해야 합니다.

먼저 C열을 기준으로 평가그룹이 같은 것 중 점수가 높은 것의 개수를 세려면

=COUNTIFS($C$3:$C$13,">"&C3,$B$3:$B$13,B3)가 됩니다.

 

이때 점수가 높은 조건을 아래와 같이 ">C3"라고 하면

=COUNTIFS($C$3:$C$13,">C3",$B$3:$B$13,B3)

C3가 문자로 인식되어 계산 결과가 0이 나오므로, 
결합 연산자인 &로 연결해서 ">"&C3라고 해야 하는 점 주의해야 합니다.

 

(2) 계량평가 달성률이 높은 것 세기

그룹이 같아야 한다는 조건은 같고, 숫자의 범위만 달리하면 됩니다.

=COUNTIFS($D$3:$D$13,">"&D3,$B$3:$B$13,B3)

 

달성률이 모두 같아서 높은 것이 없으므로 0.00%가 나왔습니다.
표시형식이 백분율이라 %라고 표시되는 것입니다. 
홈 탭의 표시 형식 그룹에서 쉼표(,)를 눌러 회계형식으로 바꿉니다.

 

(3) 두 개 수식을 결합한 후 1 더하기

이제 (1)과 (2)에서 만든 수식을 +로 연결한 후 0이면 1등이므로 1을 더해야 합니다.
=COUNTIFS($C$3:$C$13,">"&C3,$B$3:$B$13,B3)+COUNTIFS($D$3:$D$13,">"&D3,$B$3:$B$13,B3)+1

3등이 맞습니다.

 

이제 수식을 아래에 채워야 하는데,
채우기 핸들을 끌거나, 복사해서 붙여 넣으면 셀 서식이 없어지므로

 

이럴 때는 복사한 후 수식으로 붙여 넣기를 해야 합니다.

먼저 이전 상태로 돌리기 위해 Ctrl+Z키를 누르고,
복사한 후 fx가 들어가 있는 "수식"으로 붙여 넣기를 합니다.

 

그러면 결과는 아래와 같은데, 10행 이하에 소수점이하 자릿수가 표시되므로

 

Ctrl + 1 키 또는 마우스 오른쪽 버튼을 누른 후 셀 서식 대화 상자를 표시한 후 표시 형식을 살펴보면 회계에 소수 자릿수  0으로 맞게 되어 있지만 확인 버튼을 누르면

 

소수점이하 자릿수가 없어집니다.

 

D그룹의 동점일 경우 순위를 확인해 보니
달성률이 높은 것이 3위, 낮은 것이 4위로 제대로 표시됩니다.

 

달성률이 없는 A그룹부터 C그룹까지도 당초 표시된 평가순위와 일치합니다.

 

일치하는지 여부를 확인할 때는 눈으로 비교하지 않고 빼기를 하면 쉽게 확인할 수 있습니다.

아래 화면은 G3셀에 =E3-F3라고 입력하고, G3의 채우기 핸들을 더블 클릭해서 수식을 아래로 복사한 것입니다.

 

동점인 경우 계량점수 반영 우선 순위(완성).xlsx

0.01MB