Percentile(백분위수), Quartile(사분위수) 함수

1. 의미

과거 버전에서는 Percentile과 Quartile함수를 사용했는데, Percentile.inc, Percentile.exc 등으로 inc, exc가 붙은 함수로 대체되었으므로 Percentile과 Quartile 함수는 사용하지 않는 것이 바람직합니다.

- PERCENTILE.INC : 범위에서 k번째 백분위수 값을 반환하는데, k는 0에서 1 사이인데 0과 1을 포함
- PERCENTILE.EXC : 범위에서 k번째 백분위수 값을 반환하는데, k는 0에서 1 사이인데 0과 1은 제외합니다.

INC는 inclusive, EXC는 exclusive입니다.

- QUARTILE.INC : 0에서 1 사이(0과 1 포함)의 백분위수 값을 기준으로 데이터 집합의 사분위수를 반환

- QUARTILE.EXC : 0에서 1 사이(0과 1 제외)의 백분위수 값을 기준으로 데이터 집합의 사분위수를 반환

2. 구문

PERCENTILE.INC(array,k)
PERCENTILE.EXC(array,k)
QUARTILE.INC(array, quart)
QUARTILE.EXC(array,quart)

- array : 숫자 값의 배열 또는 셀 범위
- k : 구하려고 하는 0과 1 사이의 값으로 위에서 본 바와 같이 , inc는 0과 1을 포함하고, exc는 0과 1은 제외합니다.
- quart : inc의 경우는 0, 1, 2, 3, 4이 모두 가능한데, exc의 경우는 1,2,3만 가능

QUARTILE.INC의 경우 quart가 0, 2, 4일 때 MIN(최솟값), MEDIAN(중앙값, 중위수), MAX(최댓값) 함수로 구한 값과 동일.
QUARTILE.EXC의 경우 quart가 2일 때 MEDIAN함수로 구한 값과 동일.

3. 사용 예

percentile,quartile함수.xlsx

0.01MB

A열의 판매금액을 기준으로 백분위수를 구해보겠습니다. 정렬을 안 해도 값을 구해주지만 이해를 돕기 위해 오름차순으로 정렬했습니다.

가. Percentile.Inc와 Percentile.Exc

(1) Percentile.Inc

이해하기 쉽도록 0부터 10까지, 다시 말해 A2셀부터 A12셀까지의 범위를 대상으로 해보겠습니다.
k값을 먼저 구해야 하는데, Percentile.Inc의 경우는 1/(n-1)의 배수이고, Percentile.Exc의 경우는 1/(n+1)의 배수입니다. 만약 배수가 아니라면 보간법을 적용하여 값을 구합니다.

따라서, B2셀의 수식은 첫 번째이므로 0/(11-1)이 되는데, 앞에 있는 0을 row() 함수를 이용해 row()-2로 구할 수 있고, 11은 count(a2:a12)로 구할 수 있는데, a2에서 a12는 고정이므로 절대 참조형식으로 입력하기 위해 F4키를 눌러야 합니다.

따라서, B2셀의 수식은 =(row()-2)/(count($a$2:$a$12)-1)이 됩니다.

B2셀의 채우기 핸들을 B12셀까지 끕니다. 그러면 0부터 1까지 0.1 단위로 채워집니다.

이제 C2셀에 =percentile.inc($a$2:$a$12,b2)라고 입력해야 하는데,
=per까지 입력하면 per이 포함된 함수가 나열되므로 화살표키를 이용하거나, 마우스로 더블 클릭하여 =percentile.inc(를 입력합니다.

그러면 인수로 array와 k를 입력하라고 합니다.

A2셀을 마우스로 클릭한 다음 A12셀까지 끌고, F4키를 누릅니다.

이제 k를 입력해야 하는데 k가 b2셀에 있으므로 ,(쉼표)를 입력한 다음 b2셀을 마우스로 클릭하고 )(괄호)를 닫습니다.

엔터 키를 누르면 첫 번째 값 193,000이 구해지고 아래 셀로 이동합니다.

B2셀로 이동한 다음 C2셀의 채우기 핸들을 더블 클릭합니다. 왼쪽 열을 쫓아가는 줄 알았더니 A열을 쫓아가서 C16셀까지 채워집니다.

마우스로 C13셀부터 C16셀까지 선택한 후 Delete키를 눌러 지웁니다.
C열의 값이 A열의 값과 같습니다. 10개 구간으로 나눠지므로 똑같은 것입니다.

[정렬 안 해도 백분위수 잘 구해줌]
범위를 A2에서 A12셀까지로 하고 백분위수를 구하면 순서대로 값이 표시됩니다. 위와 값이 다른 것은 범위 내 숫자가 다르기 때문입니다.

[보간법 적용]
위에서 k값이 1/(n-1)의 배율에 해당하지 않는다면 보간법을 적용한다고 했는데,
C12셀의 수식에서 B12를 0.95로 수정하면
0.95는 0.9에 해당하는 값 690,000에 1에 해당하는 값 751,000에서 0.9에 해당하는 값 690,000을 뺀 값의 50%를 더한 값이므로
690,000 + (751,000 - 690,000) * 50% = 720,500이 됩니다.

(2) Percentile.Exc

(가) Percentile.Inc함수의 0.1에서 0.9로 범위를 정한 경우

0과 1을 제외해야 하므로 범위를 A3셀에서 A11셀까지로 변경합니다. k는 B열의 값을 사용하겠습니다.
수식은
=PERCENTILE.EXC($A$3:$A$11,B3)
이며, 값이 신기하게 Percentile.Inc함수를 사용한 것과 동일합니다.

이것은 Percentile.Exc함수의 경우 k는 1/(n+1)의 배수인데,
위 경우 n이 9이므로 1(9+1)이 되고, 이것이 Percentile.Inc의 k값과 같기 때문입니다.

(나) Percentile.Inc함수의 범위를 사용한 경우
A2셀에서 A12셀까지 범위를 지정해서 Percentile.Exc함수의 값을 구하면
k값은 0과 1을 제외하기 때문에 D2셀과 D12셀은 #NUM! 에러가 발생합니다.

그런데 D3셀의 값이 C3셀의 값과 다릅니다.

이것은 k값이 1/(11+1) = 0.08이기 때문입니다.
E2셀에서 k값을 구하는데 0과 1에 해당하는 값은 제외하기 때문에 193,000위에 숫자가 하나 있고, 751,000 아래에 숫자 하나가 있다고 봐야 합니다.

따라서, E2셀의 k값은 (row()-1)/12가 됩니다. E2셀의 채우기 핸들을 더블 클릭하면 아래와 같이 k값이 1까지 구해집니다.
0은 1행에 있다고 봐야 합니다.

(다) (가)에서 D3셀의 값이 212,000이 나온 이유
① Percentile.Exc
D2셀의 수식에서 B2를 E2로 바꾸고, 채우기 핸들을 D13셀까지 끕니다. 그러면 1에 해당하는 값이 #NUM! 에러가 발생합니다. 그리고, k값이 0.17일 경우 Percentile.Exc의 값이 288,000인 것을 알 수 있습니다.

따라서, 212,000은 D2셀 193,000과 D2셀 값 288,000 사이에 있고, k값으로 보면 0.08과 0.17 사이에 있는 것입니다.

212,000 - 193,000 = 19,000이고,
19,000 / (288,000-193,000) = 0.2이므로,
E2 + (E3 - E2) * 0.2 = 0.1이 되고,
※ 보이는 숫자로만 하면 0.08 + (0.17 - 0.18) * 0.2 = 0.098이 나오는데 이것은 소수점이하에 숫자가 더 있기 때문입니다.

D3셀의 수식을 =PERCENTILE.EXC($A$2:$A$12,E2+(E3-E2)*0.2)로 바꾸면 212,000이 구해집니다.

다시 말해 Percentile.Exc인 경우는 범위 내 첫 번째 값의 k값이 1/(n+1)이고, 마지막 값 아래에 값이 하나 더 있고 이것의 k값이 1이라는 것을 명심하고 있어야 합니다.

다시 말해 위 경우 0.08보다 작은 값 또는 0.92보다 큰 값을 입력하게 되면 0 또는 1이 아니라도 #NUM! 에러가 발생합니다. 왜냐하면 k값이 가장 작은 값 0.08과 가장 큰 값 0.92 사이여야 하기 때문입니다.

D2셀에 k값으로 0.05를 넣으면 0이 아니라도 #NUM! 에러가 발생하고,

0.95를 넣어도 1이 아니지만 마찬가지로 에러가 발생합니다.

(라) 구간이 10개가 넘는 경우
① Percentile.Exc
범위를 A2셀에서 A16셀까지로 하고, 참조셀을 E2셀로 한 다음

E2셀의 수식은 count함수를 이용해 =(ROW()-1)/(COUNT($A$2:$A$16)+1)로 입력합니다.

그러면 k값이 0.06부터 0.94까지 구해지고, Percentile.Exc의 값은 A열과 동일하게 193,000부터 975,000까지 구해집니다.

② Percentile.Inc
B열과 C열의 수식도 바꿔보겠습니다.
C2셀의 수식은 =PERCENTILE.INC($A$2:$A$16,B2)이고,
B2셀의 수식은 =(ROW()-2)/(COUNT($A$2:$A$16)-1)입니다.

두 개 모두 값을 같은데 K값만 다르다는 것을 알 수 있습니다.
Percentile.Inc의 경우는 k값이 0부터 1까지 있고,
Percentile.Exc의 경우는 0보다 큰 값부터 1보다 작은 값까지 있습니다.

나. Quartile.Inc와 Quartile.Exc

(1) Quartile.Inc

두 개의 차이도 0과 1이 되느냐 아니냐입니다.

G2셀에 =Quattile.Inc(라고 입력하고, A2셀부터 A16셀까지 선택한 후 F4 키를 누르고 ,를 누르면 0부터 4에 해당하는 설명이 나오는데, 큰 글씨는 최댓값이라고 잘못 표시되고, 오른쪽의 작은 글자는 최솟값이라고 정확하게 표시됩니다.

0을 선택하는데, G2셀에 있으므로 G2셀을 클릭하고, 옆으로 복사할 때 G열이 변하지 않도록 G왼쪽에 $표시를 넣습니다. F4 키를 이용하면 3번 눌러야 하고, 직접 키보드에서 Shift + 4 키를 눌러도 됩니다. 그리고, 괄호를 닫고 엔터키를 누르면 최솟값 193,000이 구해집니다.

중앙값은 개수가 15개이므로 (15+1)/2는 8번째 수인 9행 607,000이고,
최댓값은 16행 975,000,
1분위수는 8개의 중앙값이므로 (8+1)/2는 4.5이므로 4와 5의 평균, (309,000 + 412,000) / 2 = 360,500,
3분위수는 8+4.5=12.5이므로 12행과 13행의 평균, (751,000 + 796,000) / 2 = 773,500입니다.

만약 0, 1, 2, 3, 4가 아닌 숫자 2.5, 3.6, 4.5 등을 입력하면 반올림하지 않고 정수 부분 기준으로 분위수를 구해주며, 5.5를 입력하면 4를 초과하기 때문에 #NUM! 에러가 발생합니다.

(2) Quartile.Exc

H2셀부터 H6셀까지 범위를 선택한 후 맨 아래 오른쪽이 채우기 핸들을 I열로 끕니다.

그러면 값이 같은데, INC를 EXC로 바꿔줘야 합니다.

i2셀부터 i6셀까지 범위를 선택한 후 홈 탭 > 편집 그룹 > 찾기 및 선택을 누른 다음 바꾸기를 누릅니다. 단축키는 Ctrl + H입니다.

찾을 내용에는 inc, 바꿀 내용에는 exc라고 입력하고 왼쪽 아래 모두 바꾸기 버튼을 누릅니다.

그러면 선택한 범위 내 INC가 EXC로 5개가 바뀌는데, 확인 > 닫기 버튼을 누릅니다.

① 1, 2, 3분위수 구하기 방법 1
0과 4에 해당하는 값은 #NUM! 에러가 발생하고, 중앙값은 같은데
1,3분위수는 다릅니다.

EXC이므로 위, 아래에 1행씩 더 있다고 가정하는 것이므로
(17+1)/2 = 9행인 607,000이 중앙값이 되며,
1분위수는 (9+1)/2 = 5행인 309,000이고,
4분위수는 9 + 4 = 13행인 796,000입니다.

② 1, 2, 3분위수 구하기 방법 2
Percentile.Exc를 이용해서 Quatile.Exc의 4분위수를 구할 수 있습니다.
아래를 보면 0.25, 0.50, 0.75가 1, 2, 3분위수가 됩니다.

범위를 A2에서 A11로 하면 0.5도 없고, 0.25, 0.75가 없기 때문에 보간법으로 구해야 합니다.

중앙값은 0.45와 0.55의 가운데이므로 (412,000 + 516,000) / 2 = 464,000이고,

1분위수는 0.25이므로 288,000과 292,000 사이에 있습니다.
보간법을 이용해 구하면
288,000 + (292,000 - 288,000) * (0.25-0.18) / (0.27-0.18) 이 됩니다.

percentile,quartile함수(완성).xlsx

0.01MB