성과급 등수 매기다가 동점자 처리로 골머리 앓았던 이야기

분기 성과 평가 결과를 가지고 순위를 매겨야 했는데, 정렬로 순서만 바꿔서는 "이 사람이 몇 등"이라는 표시가 자동으로 안 남는다는 게 문제였습니다. 순위를 매번 손으로 세어 적어 넣다가, RANK 함수를 알게 된 뒤로는 점수만 있으면 등수가 자동으로 계산되게 만들 수 있었습니다. 오늘은 순위를 매길 때 쓰는 RANK 계열 함수와, 상위/하위 값을 뽑아낼 때 쓰는 LARGE, SMALL을 정리해보겠습니다.

RANK.EQ — 값이 전체에서 몇 등인지 계산하기

 

=RANK.EQ(순위를 구할 값, 전체 범위, 정렬 방식)

• 순위를 구할 값: 지금 몇 등인지 알고 싶은 셀
• 전체 범위: 비교 대상이 되는 전체 범위
• 정렬 방식: 0 또는 생략하면 내림차순(값이 클수록 1등), 0이 아닌 값을 넣으면 오름차순(값이 작을수록 1등)

실무 예시: 매출 높은 순으로 등수 매기기

 

=RANK.EQ(B2, $B$2:$B$20, 0)

B2의 매출액이 B2부터 B20 전체 범위 안에서 몇 번째로 높은지 계산합니다. 매출이 가장 높은 사람이 1등이 되도록 내림차순(0)으로 지정했습니다. 여기서 전체 범위는 절대참조($)로 고정해야 한다는 걸 잊으면 안 됩니다. 이전 글에서 다룬 참조 방식 원리가 여기서도 그대로 적용됩니다. 절대참조 없이 이 수식을 아래로 복사하면 범위가 계속 밀려서 엉뚱한 순위가 나옵니다.

오름차순으로 순위를 매겨야 하는 경우

값이 작을수록 좋은 순위(예: 처리 시간이 짧을수록 1등)라면 정렬 방식에 0이 아닌 값을 넣습니다.

 

=RANK.EQ(B2, $B$2:$B$20, 1)

동점자는 어떻게 처리되는가

RANK.EQ는 같은 값이 여러 개 있으면 모두 같은 순위를 부여하고, 그 다음 순위는 건너뜁니다. 예를 들어 90점이 두 명 있어서 공동 1등이라면, 두 사람 모두 1등으로 표시되고 그 다음 사람은 2등이 아니라 3등으로 넘어갑니다. 실무에서 성과급이나 시상 순위를 매길 때 이 방식이 표준으로 쓰이는 경우가 많습니다.

RANK.AVG — 동점자에게 평균 순위 부여하기

동점자 처리 방식이 다른 함수도 있습니다.

 

=RANK.AVG(B2, $B$2:$B$20, 0)

RANK.EQ와 문법은 동일하지만, 동점자가 있을 때 순위를 건너뛰는 대신 평균을 내서 부여합니다. 앞서 예로 든 공동 1등 두 명이 있다면, RANK.EQ는 둘 다 1등으로 표시하지만 RANK.AVG는 1등과 2등의 평균인 1.5등으로 표시합니다. 학술 통계나 정밀한 순위 비교가 필요한 상황에서 가끔 쓰이지만, 일반적인 업무 보고서에서는 RANK.EQ를 훨씬 더 많이 씁니다.

조건에 맞는 그룹 안에서만 순위 매기기

전체가 아니라, 같은 부서 안에서만 순위를 매기고 싶은 경우가 실무에서는 더 흔합니다. RANK 함수 자체에는 조건을 거는 기능이 없어서, 이전 글에서 다룬 배열 수식을 응용합니다.

 

=SUMPRODUCT(($B$2:$B$20=C2)*($D$2:$D$20>D2))+1

C열에 부서, D열에 점수가 있다고 가정했을 때, "같은 부서(B열)이면서 나보다 점수(D열)가 높은 사람이 몇 명인지" 세고 거기에 1을 더하는 방식입니다. 나보다 높은 사람이 0명이면 1등, 2명이면 3등이 되는 원리입니다. 배열 수식 글에서 다룬 조건 곱셈(AND) 원리가 여기에 그대로 쓰입니다.

LARGE, SMALL — 몇 번째로 큰 값, 작은 값 直접 가져오기

RANK가 "이 값이 몇 등인지"를 구하는 함수라면, LARGE와 SMALL은 반대로 "몇 등에 해당하는 값이 얼마인지"를 구하는 함수입니다.

 

=LARGE(범위, 몇 번째로 큰 값인지)
=SMALL(범위, 몇 번째로 작은 값인지)

실무 예시: 매출 상위 3위 금액만 뽑아내기

 

=LARGE(B2:B20, 1)  → 1등 매출액
=LARGE(B2:B20, 2)  → 2등 매출액
=LARGE(B2:B20, 3)  → 3등 매출액

누가 몇 등인지는 관심 없고, 상위 몇 개의 금액 자체만 알고 싶을 때 씁니다. 이전 글에서 다룬 상위/하위 조건부 서식과 함께 쓰면, LARGE로 구한 3위 금액을 조건부 서식의 기준값으로 넣어서 "상위 3위 안에 드는 셀 자동 강조" 같은 걸 만들 수도 있습니다.

순위에 해당하는 이름까지 같이 찾고 싶다면

LARGE로 구한 값과, 이전 글에서 다룬 INDEX/MATCH를 조합하면 "1등의 이름"까지 구할 수 있습니다.

 

=INDEX(A2:A20, MATCH(LARGE(B2:B20, 1), B2:B20, 0))

B열에서 가장 큰 값(1등 매출)의 위치를 MATCH로 찾고, 그 위치에 해당하는 이름을 A열에서 INDEX로 가져오는 방식입니다. VLOOKUP과 INDEX/MATCH를 비교했던 글에서 다룬 원리가 여기서 실제로 활용되는 예시입니다.

자주 하는 실수 정리

• 전체 범위를 절대참조로 고정하지 않아서 수식을 복사하면 순위가 다 틀어지는 경우: RANK 계열 함수에서 가장 흔한 실수입니다. 범위는 항상 절대참조로 고정해야 합니다.
• 오름차순/내림차순 인자를 빼먹거나 반대로 넣어서 순위가 거꾸로 나오는 경우: 값이 클수록 좋은 지표인지, 작을수록 좋은 지표인지 먼저 확인하고 인자를 정해야 합니다.
• 동점자 처리 방식(RANK.EQ vs RANK.AVG)을 상황에 안 맞게 골라서 결과가 어색해지는 경우: 일반적인 보고서라면 RANK.EQ가 무난합니다.
• LARGE, SMALL에 넣는 순번이 범위 안의 데이터 개수를 넘어가서 오류가 나는 경우: 예를 들어 데이터가 10개뿐인데 LARGE(범위, 15)처럼 15등을 요청하면 오류가 뜹니다.

마치며

순위는 단순히 정렬해서 눈으로 보는 것과, 각 값에 몇 등인지 숫자로 표시해두는 것이 실무에서는 꽤 다르게 쓰입니다. 정렬만으로는 원본 순서가 바뀌어버리지만, RANK 함수를 쓰면 원본 순서는 그대로 둔 채 옆 칸에 순위만 계산할 수 있어서 보고서 형태를 유지하면서도 순위 정보를 같이 보여줄 수 있습니다. 성과 평가나 매출 순위처럼 등수가 자주 필요한 업무를 하신다면 꼭 활용해보시길 추천드립니다.