엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

환경: Microsoft Excel 2013

 

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

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

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

 

PERCENTRANK.EXC(Array, X, Significance)

 

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

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

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 


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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

PERCENTILE 상대 순위 구하기

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용

 

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

엑셀 Excel 성적 상대 평가로 합격자 구할 때 PERCENTRANK, PERCENTILE 함수 사용


Posted by 녹두장군

댓글을 달아 주세요