RANK와 PERCENTILE로 순위 매기기

값 자체뿐 아니라 그룹 안에서의 위치를 알고 싶은 경우가 많습니다. 이 영업 사원은 1위일까요, 12위일까요? 이 점수는 상위 10%에 속할까요?

두 가지 함수군이 이에 답해 줍니다. RANK는 순서상의 위치를 알려 주고, PERCENTILE은 지정한 기준점에 해당하는 값을 알려 줍니다. 두 함수를 함께 사용하면 원시 숫자를 순위와 기준값으로 바꿀 수 있습니다.

RANK.EQ는 범위 안에서 값의 위치를 알려 줍니다. 값, 범위, 정렬 순서 지정값을 전달합니다.

• 순서 0(또는 생략)은 큰 값부터 순위를 매깁니다(내림차순).
• 순서 1은 작은 값부터 순위를 매깁니다(오름차순).

여기서는 가장 높은 매출 수치가 1위가 됩니다.

달러 기호에 주목하십시오. $B$2:$B$20입니다. 수식을 아래로 복사할 때 값 참조 B2는 B3, B4 등으로 바뀌지만 범위는 고정된 상태로 유지되어야 합니다.

달러 기호를 잊으면 행마다 범위가 함께 이동하여 엉뚱한 순위가 만들어집니다. 여기서는 범위에 절대 참조를 사용하는 것이 필수입니다.

RANK.EQ는 동점인 값에 같은 순위를 부여한 다음 순위를 건너뜁니다. 두 값이 공동 2위이면 둘 다 2위가 되고 다음 값은 4위가 됩니다(3위 없음).

동점인 값에 평균 순위를 부여하려면 RANK.AVG를 사용하십시오. 2위와 3위에 해당하는 두 값이 동점이면 각각 2.5위가 됩니다.

동점이 있어도 순위를 모두 다르게 만들려면 COUNTIF를 사용한 동점 해결식을 추가하십시오. 이 식은 현재 행보다 위에 같은 값이 몇 개 있는지 세고 그만큼을 추가합니다.

이렇게 하면 중복 없이 1, 2, 3, 4로 이어지는 깔끔한 순서가 보장되므로 순위표에 유용합니다.

PERCENTILE.INC는 특정 백분율 지점에 어떤 값이 있는지 알려 줍니다. 범위와 0에서 1 사이의 분수를 전달합니다.

아래 수식은 90번째 백분위수, 즉 데이터의 90%가 그 값보다 작거나 같은 지점의 값을 반환합니다. "상위 10% 성과자"와 같은 기준을 설정할 때 매우 유용합니다.

사분위수는 데이터를 네 부분으로 나눕니다. QUARTILE.INC 함수는 이를 간단히 해 줍니다. 사분위수 1은 25번째 백분위수, 사분위수 2는 50번째 백분위수(중앙값), 사분위수 3은 75번째 백분위수와 같습니다.

아래 수식은 PERCENTILE.INC(A2:A101,0.75)와 동일한 세 번째 사분위수를 반환합니다.

두 가지 방식이 있습니다. PERCENTILE.INC(포함 방식)는 0과 1을 허용하고, PERCENTILE.EXC(제외 방식)는 0과 1 사이의 값만 허용하며 양 끝값은 사용하지 않습니다.

일상적인 보고서에서는 대부분 .INC가 표준적인 선택입니다. 경계점을 제외하는 통계 관례를 따를 때는 .EXC를 사용하십시오.

RANK는 위치를 알려 주고 PERCENTILE은 기준점의 값을 알려 줍니다. PERCENTRANK.INC는 백분위수와 반대 방향으로 작동합니다. 값을 주면 그 값이 몇 번째 백분위수인지 알려 줍니다.

어떤 학생의 점수가 88점이고 PERCENTRANK가 0.92를 반환했다면, 그 학생은 그룹의 92%보다 높은 점수를 받은 것입니다. 백분율로 표시하려면 100을 곱하십시오.

RANK와 PERCENTILE은 숫자에만 사용할 수 있습니다. 범위 안의 텍스트 레이블과 빈 셀은 무시되므로, 실수로 헤더가 포함되어도 순위 계산이 중단되지 않습니다.

다만 텍스트로 저장된 숫자는 주의해야 합니다. 이러한 값은 완전히 건너뛰므로 모든 순위와 백분위수가 달라집니다. 순위가 이상해 보인다면 모든 값이 왼쪽 정렬된 텍스트가 아니라 실제 숫자인지 확인하십시오.

영업 담당자 20명의 매출이 B열에 있습니다. 각 담당자의 순위와 상위 사분위에 해당하는지 여부를 확인하려고 합니다.

• 순위: =RANK.EQ(B2,$B$2:$B$21,0)
• 상위 사분위 플래그는 매출을 75번째 백분위수와 비교합니다.

아래 수식은 기준값에 따라 각 담당자에게 상위 또는 일반을 반환합니다.

순위 함수를 얼마나 이해했는지 확인해 보십시오.

이제 그룹 안에서 값의 위치를 정하는 방법을 배웠습니다.

• RANK.EQ는 순서상 위치를 반환합니다. 순서 0은 큰 값부터, 1은 작은 값부터 정렬합니다. $를 사용해 범위를 고정하십시오.
• RANK.AVG는 동순위의 평균을 계산합니다. 고유한 순위를 만들려면 COUNTIF를 추가하십시오.
• PERCENTILE.INC는 기준점에 해당하는 값을 반환하며, QUARTILE.INC는 25/50/75번째 백분위수를 간단히 구하는 방법입니다.
• PERCENTRANK.INC는 그 반대입니다. 주어진 값의 백분위수를 반환합니다.