회사에서 인사평가를 할 때 전체 순위를 구하고 전체 조직 하위의 본부나 팀 단위의 그룹별 순위를 구해야 할 때가 있습니다. 이번 글에서 전체 순위를 구한 후 본부, 팀 등 그룹별로 순위를 구하는 방법을 알아보겠습니다. 이 방식은 회사뿐 아니라 학교에서 학년 전체 석차를 구한 후 반별 석차를 구할 때도 사용할 수 있습니다.
부서내 순위 구하기
아래의 인사 평가표에서 '평가 점수'를 기준으로 '전체 순위'를 구하고 같은 부서 내의 순위를 구해 보겠습니다.
먼저 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
관련 글
이번 글에서는 countifs 함수를 사용하여 그룹별로 데이터 순위를 구하는 방법에 대하여 말씀드리겠습니다.
1.COUNTIFS 함수
- COUNTIFS 함수는 데이터 목록에서 입력된 데이터 전체 순위가 아닌 특정 항목의 그룹별 순위를 구하고자 할 때 사용합니다.
- 즉, COUNTIFS 함수를 이용하여 그룹별 순위를 구하고자 하는 그룹 항목과 순위를 구하려고 하는 항목을 조건으로 지정하여 해당 조건을 만족하는 데이터의 개수를 구하면 그룹별 순위에 해당합니다,
2.COUNTIFS 함수 사용방법
- 아래 사진을 보시면 지점별 거래 현황을 정리한 엑셀 사진을 보실 수 있습니다.
- 여기서 1번에는 판매금액이 들어가 있습니다.
- 2번 박스에는 지점별 판매순위가 있습니다. 지점은 A,B,C,D,E 총 5개의 지점이 있으니 단순한 RANK 함수로는 출력 값 반환이 불가능합니다.
- 이럴때에 사용하는 함수가 바로 COUNTIFS 함수의 활용입니다,
- 3번 박스로 표시되어있는 셀에 셀 포인터를 올립니다,
- 이후 =COUNTIFS($B$4:$B$17,B4,$F$4:$F$17,">="&F4) 를 입력한 뒤에 ENTER를 눌러줍니다.
- 이 식은 지점이 입력되어 있는 B 열 영역에서 B4 셀에 입력된 지점과 동일하고, 판매금액이 입력된 F열에서 F4 셀에 입력된 값보다 크거나 같은 데이터의 개수를 반환하는 수식입니다.
- 식을 입력한 뒤에 채우기 핸들을 내려줍니다.
- 여기까지 완료하셨다면 위의 사진에 보이는 2번 박스처럼 지점별 판매 순위가 입력되어 있을 것입니다.
이상 COUNTIFS 함수를 사용하여 그룹별 데이터 순위구하는 방법이었습니다.