엑셀 다중조건 순위 - egsel dajungjogeon sun-wi

• 홈

직장생활/엑셀 동영상강의

[ 엑셀 영상강의 ] 순위를 구하는 방법과 중복 순위에 대한 다양한 조건 만드는 방법

공대녀의 엑셀천재 2021. 3. 1. 15:57

엑셀로 순위를 구하는 방법과 중복 순위에 대한 다양한 조건을 만드는 방법

RANK함수를 사용해 순위를 구하는 방법을 알아보고,

중복 순위가 발생했을 경우에 다양한 상황에 맞는 활용법을 소개합니다 :)

[ YOUTUBE ]

[ 예제 파일 다운로드 ]

순위구하기.xlsb

0.01MB

'엑셀, 이렇게 하지 마라!' 책 미리보기

▶ 교보문고 :  bit.ly/39Mg0Rt

▶ 예스 24 : bit.ly/3oNx6lW

▶ 인터파크 : bit.ly/3q09c8B

Tag

관련글

• [ 엑셀 영상강의 ] 시작일과 마지막일을 설정하는 선택목록 만들기 2021.03.22

• [ 엑셀 영상강의 ] 목록에 체크박스 만들어 점수 계산하는 방법 2021.02.24

• [ 엑셀 영상강의] 워드보다 쉽고 빠르게 엑셀로 목차만드는 방법 2021.02.16

• [ 엑셀 영상강의 ] 원하는 위치로 빠르게 이동하거나, 필요한 셀만을 선택해주는 이동옵션 2021.01.10

안녕하세요?

RANK 함수를 이용한 동일순위의 순위를 재결정하는 방법을 알아 보도록 하겠습니다.

아래 그림과 같이 전체 총점기준으로 순위를 결정했을때 3등이 3명이 나오는데...

출석점수가 높은 사람이 높은순위를 주고 싶다고 설정합니다.

먼저 여기서 RANK 함수의 원리에 대해서 일단 생각을 하고 넘어가야 합니다.

RANK 함수는 나보다 점수가 높은 사람수 + 1 이라고 생각하면 됩니다.

그래서 위 그림에서 [K4]셀에...

=COUNTIF($J$4:$J$20,">"&J4)+1

로 입력후 아래로 드래그 하면...

=RANK.EQ(J4,$J$4:$J$20)

로 입력후 아래로 드래그 한것과 같은 결과를 나타냅니다.

여기까지는 모두 이해되셨죠?

그럼 특정조건(출석점수가 높은)에 맞는 데이터 숫자를 현재순위에 더해주면 원하는 결과를 얻을 수 있겠죠?

그 특정조건(출석점수가 높은)은 나와 총점이 같고, 그리고 출석점수가 높은 사람의 수가 될겁니다.

그러므로 이러한 경우 다중조건에 대한 갯수를 구할 수 있는 COUNTIFS함수를 이용하면 문제는 간단히 해결됩니다.

[M4]셀에...

=RANK.EQ(J4,$J$4:$J$20)+COUNTIFS($J$4:$J$20,J4,$C$4:$C$20,">"&C4)

로 입력후 아래로 드래그 하면 동일점수에 대해 출석점수가 높은 사람의 등수가 높게 나타나겠죠.

COUNTIFS함수의 첫번째 조건은 [J4:J20]셀에서 [J4]셀과 같고, [C4:C20]셀에서 [C4]셀값보다 큰 값의 갯수를 카운트 하라는 뜻이므로 카운트값에 RANK함수 결과를 더해주면 아래와 같이 원하는 결과를 얻으실 수 있습니다.

위 그림에서 [B6]의 진형식은 초기 순위는 3등이었지만 같은 3등이었던 박정훈, 양진식중 박정훈이 본인보다 출석점수가 높아 4등으로 순위가 결정되었고 마찬가지로 양진식은 진형식과 박정훈이 모두 본인보다 출석점수가 높아 5등이 된것을 확인할 수 있습니다.

그럼 또...!

RANK함수는 엑셀2007부터 RANK.EQ함수로 바뀌었다. 

(엑셀2007 이하 버전에서 정상적으로 작동하기 위해서는 RANK함수를 사용해야 한다는 뜻)

RANK.EQ함수를 이용해서 등수를 구해보자. 

RANK.EQ함수의 사용법은 아래처럼 간단하다.

=RANK.EQ(순위를 구하려는 값, 범위, [정렬방법])

첫번째 인수로 순위를 구하려는 값

두번째 인수로 순위를 구하려는 범위를 지정하면 된다. 

(세번째 인수를 사용하지 않거나 0을 사용하면 기본값인 내림차순으로, 1을 사용하면 오름차순으로 순위를 계산한다. 즉. 세번째 인수로 1을 사용하면 성적이 제일 낮은 사람이 1로 계산된다)

아래 그림에서 다음 수식을 입력하였다. 

=RANK.EQ(D2,$D$2:$D$14)

위 그림에서 볼 수 있듯, 동점자가 있을 때 모두 같은 등수로 처리하고 동점자수만큼 건너뛰고 다음 순위를 처리한다. 

RANK함수 중에서 RANK.AVG함수는 동점자가 있을 때 동점자 순위의 구간 평균값을 나타낸다.  

(2등이 2명이라면 2등과 3등의 평균인 2.5로 표시되고

4등이 3명이라면 4,5,6 등의 평균인 5로 표시된다)

이제 각 부서별로 몇 등을 했는지 확인해보자. 

전체 등수와 함께 계열별, 팀별(반별) 등수(순위)를 구하기 위해서는 배열수식을 이용하거나 Sumproduct함수를 이용한다. 

=SUMPRODUCT(($A$2:$A$14=A2)*($D$2:$D$14>D2))+1

이 수식은 부서명이 같은 경우와 성적이 자신보다 큰 경우를 AND조건으로 구하고 거기에 1을 더하는 수식이다. 

(1등인 경우 자신보다 성적이 큰 숫자가 없기 때문에 0이 나오기 때문이다)

배열수식으로 구하는 방법은 sum함수와 sumproduct함수가 비슷하기 때문에 수식도 비슷하다. 

=SUM(($A$2:$A$14=A2)*($D$2:$D$14>D2))+1