엑셀(EXCEL) 데이터의 백분위수를 구하는 PERCENTILE 함수(.EXC, .INC의 차이) PERCENTRANK 집단에서 값의 위치를 구해보자

백분위 : 특정 집단의 점수 분포에서 한 개인의 상대적 위치를 나타내는 유도 점수
백분위수 : 주어진 자료를 크기순으로 나열하여 백등분하였을 때 해당되는 관찰값.

(다음 국어사전 참고)

 

데이터는 상대적인 위치가 중요한 경우가 많습니다.

100점 만점인 시험에서 내가 70점 정도일때 평균이 40점이라면 잘 한 것이지만, 평균이 80점이라면 노력해야 합니다.

성적이나 연봉을 상위 10%, 하위 20% 이렇게 나누곤 하는데 이것이 백분위수입니다.

 

320x100

 

 

1. PERCENTILE = PERCENTILE.inc 함수(두 함수는 같습니다.)

 

PERCENTILE와 PERCENTILE.inc 용법이나 의미가 완전히 같은 함수로 PERCENTILE는 구버전에 해당합니다.

앞으로도 구버전 함수를 지울 일은 없어보이지만 가능하면 신버전 함수로 문서를 만드는 습관을 키웁시다.

PERCENTILE.INC(array,k) : 데이터 집단(array) 안에서 백분위수 k에 해당하는 수를 계산합니다.

 

• Array : 데이터 배열 또는 범위로 이 집단의 상대 순위를 기준으로 합니다.
• k : 0에서 1 사이 범위의 백분위수 값입니다.
• 데이터와 데이터 사이의 값을 구하는 함수임으로 데이터의 숫자가 n-1개라고 봐야 합니다.
• k값이 1/(n-1)의 배수라면 구성 데이터 중의 하나와 값이 같습니다. 그 외의 숫자에서는 순위를 기준으로 가까운 두 숫자 사이의 수가 계산 됩니다.

일반적으로 사용하는 백분위 수입니다.

값들의 집단(array)가 있으면 k 값에 해당하는 숫자를 계산하는데, 1에 가까울수록 상위, 0에 가까울 수록 하위입니다.

즉 0.9는 상위 10%, 0.1은 하위 10%로 계산합니다.

그리고 어디까지나 집단의 등수를 기준으로 계산하는데 예를 보겠습니다.

 

• k = 0.95 인 경우 : 1등이 100으로 갑자기 커서 1등과 2등의 사이 숫자인 59.5로 계산합니다.
• k = 0.85 인 경우 : 2등 19와 3등 17의 사이인 18로 계산합니다.

 

 

 

 

2. PERCENTILE.exc 함수

 

PERCENTILE.INC(array,k) : 데이터 집단(array) 안에서 백분위수 k-th에 해당하는 수를 계산합니다. 데이터 중 가장 큰 수는 (k = 1-1/(n+1))가 작은수 (k = 1/(n+1))가 됩니다.

 

• Array : 데이터 배열 또는 범위로 이 집단의 상대 순위를 기준으로 합니다.
• k : 0에서 1 사이 범위의 백분위수 값입니다.
• k값이 1/(n+1)의 배수라면 구성 데이터 중의 하나와 값이 같습니다. 그 외의 숫자에서는 순위를 기준으로 가까운 두 숫자 사이의 수가 계산 됩니다.

 

.exc는 자체로 이해하는 것보다 .inc와 비교하는 것이 빠릅니다.

.inc는 데이터 집단(arry)에서 가장 큰 수 k = 1, 가장 작은 수는 k = 0이라고 두고 백분위수를 구합니다.

하지만 .exc는 가장 큰수 위에 숫자가 하나 더 있고, 가장 작은 수 아래 숫자가 하나 더 있는 상황이 됩니다.

이건 1등에서 n등까지 있을 때 n등을 0으로 하지 않고 등수 하나를 배정합니다.

따라서 k값이 1- 1/(n+1)보다 크면 에러를 칩니다.

 

 

3. PERCENTRANK = PERCENTRANK.inc 함수(두 함수는 같습니다.)

 

PERCENTRANK와 PERCENTRANK.inc 용법이나 의미가 완전히 같은 함수로 PERCENTRANK는 구버전에 해당합니다. 

 

PERCENTRANK.INC(array,x,[significance]) : 데이터 집단에 특정 수가 있다고 치고, 백분위수(k값)을 구해줍니다.

 

• Array : 상대 순위를 정의하는 숫자 값의 배열 또는 범위입니다.
• x : 순위를 확인할 값입니다.
• [Significance] : 백분율 값의 유효 자릿수를 나타내는 값입니다. 생략할 경우 세 자릿수(0.xxx)로 사용됩니다.
• x의 값이 데이터에 사용된 값의 최대값보다 크거나 최소값보다 작으면 오류가 납니다.

 

이 함수는 PERCENTILE.inc의 역이 됩니다.

백분위수 k를 입력하면 거기에 해당하는 숫자를 뽑아주는데, 이경우는 숫자 x를 입력하면 백분위수 k를 구해줍니다.

데이터가 있을때 내 성적이 어디쯤에 해당하는지, 연봉이 어디쯤 되는지 알 수 있습니다.

 

 

 

4. PERCENTRANK.exc 함수

 

PERCENTRANK.exc(array,x,[significance]) : 데이터 집단에 특정 수가 있다고 치고, 백분위수(k값)을 구해줍니다. 0과 1을 제외하고 0~1 사이의 숫자를 반환합니다.

 

• Array : 상대 순위를 정의하는 숫자 값의 배열 또는 범위입니다.
• x : 순위를 확인할 값입니다.
• [Significance] : 백분율 값의 유효 자릿수를 나타내는 값입니다. 생략할 경우 세 자릿수(0.xxx)로 사용됩니다.
• x의 값이 데이터에 사용된 값의 최대값보다 크거나 최소값보다 작으면 오류가 납니다.

 

여전히 좀 개념이 어렵기는 하지만, 이 경우는 이해가 좀 나아집니다.

.inc가 0~1(0%~100%)까지 등수를 반영한다면, .exc는 1/(n+1) ~ 1 - 1/(n+1)의 등수를 반영합니다.

즉 0과 1을 제외하는 것입니다.

 

---

 

결과값이 이해 되시나요? 값들의 중심이 아닌, 등수를 기준으로 숫자를 파악한 후에 그 중간에서 값을 계산합니다.

뉴스에서도 연봉이나 세금의 백분위수는 대부분의 사람들이 체감하는 평균과 오차가 많다고 합니다.

바로 이같은 계산 방법 때문에 평균에 비해 백분위수 50%가 멀리 분포하는 일이 생깁니다.

하지만 백분위수는 인구를 기준으로 분석하는 데이터라서 중요한 통계기법중에 하나입니다.

애시당초 한개의 데이터로 모든걸 설명할 수는 없으니 많은 데이터중 하나라고 생각하고 접근해 주세요.