Please Enable JavaScript!
Gon[ Enable JavaScript ]

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

엑셀(Excel)/Excel
반응형

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

 

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

 

 

 

 

이제 상대 평가로 인원을 뽑을 수 있는 데이터가 만들어 졌습니다. 전체 인원에서 절반만 합격시키고 싶다면 어떻게 해야 할까요? 구한 값에서 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 함수 사용

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

댓글을 달아 주세요