여러 조건으로 순위 구하기

일반적으로 인사 평가 시 평가 점수를 기준으로 순위를 구할 때 RANK 함수를 이용하게 되는데 다음 그림과 같이 평가 점수가 같으면 동 순위로 계산되므로, 동료평가 점수, 어학 점수 등 여러 조건을 추가하여 순위를 계산해야 하는 경우가 있습니다.

이번 글에서는 같은 순위가 있을 때 조건을 추가하여 순위를 다시 계산하는 방법을 알아보겠습니다.

실습용 엑셀파일 다운로드 : 엑셀에서-여러-조건으로-순위구하기

순위가 같을 경우 조건을 추가하여 순위 구하기

아래 인사 평가표는

'평가 총점'을 기준으로 '총점 순위'를 구하고
순위가 같을 경우 '동료평가 점수'를 반영하여 순위를 다시 구하고
그래도 같은 순위가 있으면 어학 점수를 반영하여 순위를 다시 구합니다.

먼저 RANK 함수로 순위를 구해보겠습니다.

다음 수식을 [F5] 셀에 입력하고, 수식을 복사하여 나머지 [F6:F14] 범위에 붙여 넣으면 전체 총점 순위가 계산됩니다.

=RANK(C5,$C$5:$C$14)

'평가 총점' 기준으로 동점자가 있으므로 다음과 같이 일부 동일 순위가 나옵니다.

총점 기준으로 동 순위일 때 동료평가 점수가 높으면 상위 순위가 되도록 다시 계산해보겠습니다.

[G5] 셀에 다음 수식을 입력하고, 수식을 복사하여 나머지 [G6:G14] 범위에 붙여 넣으면 동료평가를 반영한 순위가 계산됩니다.

=F5+COUNTIFS($C$5:$C$14,C5,$D$5:$D$14,">"&D5)

총점 순위(F5)에 COUNTIFS 함수로 '나와 평가 총점이 같고 나보다 동료평가 점수가 높은 사람의 수'를 구해서 더하면 동료평가를 반영한 순위가 됩니다.

이 수식에서 COUNTIFS 가 핵심입니다.

COUNTIFS($C$5:$C$14,C5,$D$5:$D$14,">"&D5)

평가 총점 범위 [$C$5:$C$14] 에 있는 값이 C5와 같고(나와 평가 총점이 같으면서)
동료평가 점수 범위 [$D$5:$D$14]에 있는 값이 [D5]셀의 값보다 크면(동료평가 점수가 나의 점수보다 높다면)

아래와 같이 양진우를 나라고 가정하고 설명하면 나와 '평가 총점'이 같은 사람 중에 나보다 '동료평가 점수'가 높은 사람의 수(결국 나의 순위가 됨)를 구하는 문제입니다.

이렇게 구한 값(1)에 양진우의 총점 순위(7)를 더하면 동료평가를 반영한 양진우의 순위는 8이 됩니다.

선우재덕 기준으로 계산하면 평가 총점이 82점인 사람 중에서 동료평가 점수가 4점보다 높은 사람은 없으므로 0이 반환되고 선우재덕의 총점순위(7)을 더하면 순위는 7위를 그대로 유지합니다.

그런데 문제는 이누리, 신지수는 동료평가 점수도 같으므로 동료평가 점수를 반영해서 순위를 계산해도 여전히 2위로 동일합니다.

동료평가 점수를 반영해도 동 순위가 나올 때 처리

동료평가 점수를 반영해도 동일 순위 문제가 해결이 안될 경우에는 추가 조건(어학점수 등)을 반영해서 순위를 다시 계산하면 됩니다.

앞에서 살펴본 방식과 기본 원리는 동일하고 조건만 추가되는 방식입니다.

[H5] 셀에 다음 수식을 입력하고 수식을 복사하여 나머지 [H6:H14] 범위에 붙여 넣으면 어학점수 반영한 순위가 계산됩니다.

=G5+COUNTIFS($C$5:$C$14,C5,$D$5:$D$14,D5,$E$5:$E$14,">"&E5)

둉료평가를 반영한 순위(G5)에 COUNTIFS 함수로 '나와 평가 총점이 같고 나와 동료평가 점수도 같으면서 어학점수가 높은 사람의 수'를 구해서 더하면 어학점수를 반영한 순위가 됩니다.

어학점수까지 반영하니 위와 같이 동 순위 문제가 해결되었습니다. 실무에서 이보다 많은 조건을 반영해야 한다면 위에서 설명한 방식으로 조건을 추가하면 됩니다.

참고) 동 순위 문제를 해결하기 위해 COUNTIFS 함수가 아니라 다음과 같이 배열수식을 사용하는 경우도 있습니다. 기본 원리는 동일합니다. COUNTIFS 함수를 쓸 수 없는 환경이 아니라면 배열수식보다는 COUNTIFS 함수를 쓰는 것이 이해하기도 쉽고 배열수식을 잘못 입력해서 생기는 오류의 가능성도 적습니다.

=RANK(C5,$C$5:$C$14)+SUM(($C$5:$C$14=C5)*($D$5:$D$14>D5))