엑셀(Excel) PERCENTILE 와 QUARTILE 이용 백분위수와 사분위수 구하기

엑셀을 이용한 데이터 분석에서 평균만 가지고는 부족한 경우가 많습니다. 데이터가 어느 위치에 집중되어 있는지, 상위 몇 퍼센트에 해당하는 값이 무엇인지 파악하려면 백분위수와 사분위수 분석이 필요합니다. 이번 컨텐츠에서는 PERCENTILE 함수와 QUARTILE 함수를 사용하여 백분위수와 사분위수를 구하는 방법을 소개합니다. 

업무에서 데이터를 분석할 일이 많거나, 통계 보고서 작성이 필요한 분들이라면 매우 유용하게 활용하실 수 있습니다.
실제 샘플 데이터와 함께 따라할 수 있도록 구성하였으니, 엑셀을 처음 접하는 분들도 쉽게 활용해 보세요.

 

1. 백분위수 구하기 – PERCENTILE 함수 사용

데이터에서 상위 몇 퍼센트에 해당하는 기준값을 구하고 싶을 때 PERCENTILE 함수를 사용합니다. 예를 들어 상위 10% 기준값을 구하고 싶다면, 다음과 같이 입력합니다.

=PERCENTILE(A2:A21, 0.9)

 

이 수식은 A2부터 A21 범위의 데이터 중 상위 10%에 해당하는 값을 반환합니다. 여기서 0.9는 90%를 의미하고, 0.5는 중앙값(50%)을 의미합니다.

 

2. 사분위수 구하기 – QUARTILE 함수 사용

사분위수는 데이터를 4등분하여 나눈 기준점입니다. 엑셀에서는 QUARTILE 함수를 사용하여 1사분위(Q1), 2사분위(Q2), 3사분위(Q3)를 구할 수 있습니다.

• 1사분위수(Q1): =QUARTILE(A2:A21, 1)
• 2사분위수(Q2, 즉 중앙값): =QUARTILE(A2:A21, 2)
• 3사분위수(Q3): =QUARTILE(A2:A21, 3)

각 사분위수는 데이터의 하위 25%, 50%, 75% 지점을 의미하며, 데이터의 분포를 시각적으로 이해하는 데 큰 도움이 됩니다.

 

3. PERCENTILE.INC 와 PERCENTILE.EXC의 차이 이해하기

엑셀 2010 이후 버전에서는 PERCENTILE 함수가 두 가지로 나뉘었습니다.

• PERCENTILE.INC: 백분위수 계산 시 0과 1을 포함
• PERCENTILE.EXC: 백분위수 계산 시 0과 1은 제외하고 계산

일반적인 상황에서는 PERCENTILE.INC를 사용하면 충분합니다.

=PERCENTILE.INC(A2:A21, 0.75)

 

이 수식은 75% 지점에 해당하는 값을 반환하며, 이는 Q3와 동일한 결과입니다.

 

4. 사분위수를 활용한 이상값 탐지 예시

사분위수를 사용하면 데이터에서 이상값(outlier)을 찾는 데도 활용할 수 있습니다. 이상값 기준은 다음과 같이 계산할 수 있습니다.

• IQR (사분위 범위) = Q3 - Q1
• 상한값 = Q3 + 1.5 * IQR
• 하한값 = Q1 - 1.5 * IQR

이를 통해 극단적으로 높은 점수나 낮은 점수를 식별할 수 있습니다. 수식으로는 다음과 같이 사용합니다:

=QUARTILE(A2:A21, 3) + 1.5 * (QUARTILE(A2:A21, 3) - QUARTILE(A2:A21, 1))

 

요약 정리

이번 컨텐츠에서는 PERCENTILE과 QUARTILE 함수를 이용해 백분위수와 사분위수를 구하는 방법을 소개했습니다.
실제 데이터를 기반으로 분석하는 과정에서 이 함수들은 데이터의 분포를 파악하고, 이상값을 탐지하며, 기준 점수를 설정하는 데 매우 유용합니다. 업무에 필요한 통계 자료를 정리할 때 꼭 한 번 활용해보시기 바랍니다.