엑셀 순위 추출 - egsel sun-wi chuchul

회사에서 인사평가를 할 때 전체 순위를 구하고 전체 조직 하위의 본부나 팀 단위의 그룹별 순위를 구해야 할 때가 있습니다.

이번 글에서 전체 순위를 구한 후 본부, 팀 등 그룹별로 순위를 구하는 방법을 알아보겠습니다. 이 방식은 회사뿐 아니라 학교에서 학년 전체 석차를 구한 후 반별 석차를 구할 때도 사용할 수 있습니다.

부서내 순위 구하기

아래의 인사 평가표에서 '평가 점수'를 기준으로 '전체 순위'를 구하고 같은 부서 내의 순위를 구해 보겠습니다.

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

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

=RANK(D5,$D$5:$D$14)

다음과 같이 전체 순위가 구해졌습니다.

이제 부서별로 평가 점수 기준으로 순위를 계산해보겠습니다.

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

=COUNTIFS($B$5:$B$14,$B5,$D$5:$D$14,">"&$D5)+1

COUNTIFS 함수로 나와 부서가 같고($B$5:$B$14,$B5) 나보다 평가 점수가 높은 사람($D$5:$D$14,">"&$D5)의 수를 구해서 1을 더하면 부서 내 순위가 됩니다.

아래와 같이 양진우를 나라고 가정하고 설명하면 나와 같은 부서(기획팀)인 사람 중에 나보다 '평가 점수'가 높은 사람은 2명입니다. 결국 2에 1을 더하면 부서내에서 나의 순위(3)가 됩니다.

윤갑수 기준으로 계산하면 같은 부서(기획팀) 사람 중에서 평가 점수가 88점보다 높은 사람은 없으므로 COUNTIFS 함수는 0을 반환하고 0에 1을 더하면 윤갑수의 순위(1)가 됩니다.

다음과 같이 부서 내 순위가 구해졌습니다.

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

=SUMPRODUCT(($B$5:$B$14=B5)*($D$5:$D$14>D5))+1

관련 글