엑셀 Excel 성적 상대 평가 PERCENTRANK, PERCENTILE 함수 사용

학생들의 성적에서 절대 평가로 합격자를 걸러 내고자 할 때 평균을 이용하게 됩니다. 그럼 절대 평가가 아닌 상대 평가로 전체 인원에서 특정 % 만큼 합격을 처리하고 싶다면 어떻게 해야 할까요? 이 때는 엑셀에서 제공하는 백분위수 함수를 이용해서 구할 수 있습니다. 상대 순위를 구할 수 있는 백분위수 함수는 2가지가 있습니다. 그 차이점과 어떻게 구현하는지 알아 보겠습니다.

 

 

 

▼ 그림처럼 평균을 구해서 80점 이상인 사람들에게 합격을 부여하는 방식은 절대 평가입니다. AVERAGE 함수를 이용해서 평균을 구한 후 IF 함수로 합격 여부를 판단해야겠죠.

 

▼ 그럼 상대 평가로 전체 인원에서 절반만 합격을 시키고 싶다면 어떻게 해야 할까요? 엑셀에서 상대 평가 함수는 PERCENTRANK 입니다. 이 함수는 엑셀 2013 버전으로 넘어오면서 두 가지로 나눠집니다. EXC 와 INC 가 그것인데 차이는 경계값의 제외 여부입니다. 먼저 경계값을 제외하는 PERCENTRANK.EXC 함수를 선택합니다. 

 

▼ 함수의 원형은 다음과 같습니다. Array 에 평균값 영역을 넣고 X 에 백분위수를 구할 값을 넣습니다. Significance 는 소수점의 개수를 나타냅니다. 0 이면 기본 3자리를 표시합니다.

 

 

PERCENTRANK.EXC(Array, X, Significance)

 

Array : 평균값이 있는 영역을 지정합니다.

X : 백분위수를 구할 값입니다. 해당 값이 평균값들 중에서 어느 위치에 해당하는지 % 로 알려 주게 됩니다.

Significance : 백분율에서 유효 자릿수의 개수입니다. 소수점의 개수가 됩니다. 

 

▼ 백분위수로 구한 값은 0 과 1 사이의 소수점입니다. 이것을 다시 백분율로 표시하려면 100 을 곱해야겠죠. 수식에서 결과값에 100 을 곱했습니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 데이터 퍼센트로 변경하는 간단한 방법 ▶ 엑셀 PERCENTRANK 이용해 전체 점수에서 순위 백분율로 표시 하기 ▶ 엑셀 Excel 숫자데이터 소수점 자리수와 백분율 조절하기 ▶ 엑셀 백분위수에 해당하는 값 구하기 ▶ 엑셀 절대값, 수식 고정 하는 방법

 

 

 

 

▼ 이제 상대 평가로 인원을 뽑을 수 있는 데이터가 만들어 졌습니다. 전체 인원에서 절반만 합격시키고 싶다면 어떻게 해야 할까요? 구한 값에서 50% 이상을 모두 합격 처리해 주시면 되겠죠. 

 

 

◎ PERCENTILE 상대 순위 구하기

 

 

▼ PERCENTRANK 함수는 각 평균이 전체에서 차지하는 위치를 백분율로 알려 주었습니다. 하자만 상대 평가 함수인 PERCENTILE 는 내가 구하고자 하는 백분위수를 지정해 주면 해당 값을 반환해 줍니다. 그러니까 50% 를 지정하면 전체 평균값에서 50% 에 해당하는 평균 값을 넘겨 주겠죠. 그 값은 합격 여부를 판단하는 기준이 됩니다. 함수 사용법은 간단합니다. Array 에 구할 평균값들을 넣고 K 에 백분위수 값을 지정합니다. 그림처럼 0.5 면 50% 에 해당하는 값을 반환하겠죠. 

 

▼ PERCENTILE.EXC 로 구한 값은 78 입니다. 전체 인원에서 절반 이상을 합격 처리하기 위한 값으로 78 이 나왔습니다. 

 

▼ IF 함수를 이용해서 78 와 각 평균값을 비교합니다. 78 이상인 평균을 합격 처리 하면 5명이 나옵니다. 첫 번째 소개한 함수인 PERCENTRANK 를 이용한 것과 같은 결과가 나왔습니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 데이터 퍼센트로 변경하는 간단한 방법 ▶ 엑셀 PERCENTRANK 이용해 전체 점수에서 순위 백분율로 표시 하기 ▶ 엑셀 Excel 숫자데이터 소수점 자리수와 백분율 조절하기 ▶ 엑셀 백분위수에 해당하는 값 구하기 ▶ 엑셀 절대값, 수식 고정 하는 방법