티스토리 뷰

카테고리 없음

엑셀 PERCENTILE과 QUARTILE 함수 활용법 총정리

TV드라마재방송다시보기 2025. 4. 25. 12:09

엑셀에서 PERCENTILE과 QUARTILE 함수를 활용하여 데이터의 분포를 정확히 파악하는 방법을 알아보세요. 백분위수와 사분위수 계산을 통해 데이터 분석 능력을 향상시키고, 통계적 인사이트를 얻는 방법을 상세히 설명합니다. 실무에서 바로 활용할 수 있는 예제와 함께 엑셀 데이터 분석 기술을 마스터하세요.

백분위수와 사분위수의 개념 이해하기

데이터 분석에서 평균이나 중앙값만으로는 데이터의 전체적인 분포를 파악하기 어렵습니다. 이때 백분위수와 사분위수가 중요한 역할을 합니다. 이 두 개념은 데이터가 어떻게 분포되어 있는지, 특정 값이 전체 데이터에서 어떤 위치를 차지하는지 파악하는 데 도움을 줍니다.

백분위수(Percentile)는 데이터를 100등분했을 때 각 구간의 경계값을 의미합니다. 예를 들어, 90번째 백분위수는 데이터의 90%가 이 값보다 작거나 같은 값을 의미합니다. 시험 성적에서 상위 10%에 들기 위한 최소 점수를 알고 싶다면 90번째 백분위수를 구하면 됩니다.

사분위수(Quartile)는 데이터를 4등분한 지점의 값으로, 데이터의 분포를 빠르게 파악할 수 있게 해줍니다.

  • 1사분위수(Q1): 데이터의 25% 지점 (하위 25%)
  • 2사분위수(Q2): 데이터의 50% 지점 (중앙값)
  • 3사분위수(Q3): 데이터의 75% 지점 (상위 25%)

이러한 개념을 엑셀에서는 PERCENTILE 함수와 QUARTILE 함수를 통해 쉽게 계산할 수 있습니다.

PERCENTILE 함수 사용법 및 예제

PERCENTILE 함수는 데이터 집합에서 특정 백분위에 해당하는 값을 찾아주는 함수입니다. 엑셀 2010 이후 버전에서는 PERCENTILE.INC와 PERCENTILE.EXC로 나뉘었습니다.

PERCENTILE 함수의 기본 구문

=PERCENTILE(array, k)
=PERCENTILE.INC(array, k)
=PERCENTILE.EXC(array, k)
  • array: 백분위수를 계산할 데이터 범위
  • k: 구하고자 하는 백분위 (0~1 사이의 값)

PERCENTILE.INC와 PERCENTILE.EXC의 차이점

  • PERCENTILE.INC: 기존 PERCENTILE 함수와 동일하며, k값에 0과 1을 포함할 수 있습니다.
  • PERCENTILE.EXC: k값에 0과 1을 포함할 수 없습니다.

PERCENTILE 함수 활용 예제

예를 들어, A1:A20 범위에 학생들의 시험 점수가 있다고 가정해 보겠습니다.

  1. 상위 10%에 해당하는 점수 구하기:또는
  2. =PERCENTILE.INC(A1:A20, 0.9)
  3. =PERCENTILE(A1:A20, 0.9)
  4. 중앙값(50% 지점) 구하기:이는 =MEDIAN(A1:A20)과 동일한 결과를 제공합니다.
  5. =PERCENTILE(A1:A20, 0.5)
  6. 하위 25%에 해당하는 점수 구하기:
  7. =PERCENTILE(A1:A20, 0.25)

백분율 표기법 사용하기:
k값은 소수점 형태(0.9) 또는 백분율 형태(90%)로 입력할 수 있으며, 결과는 동일합니다.

=PERCENTILE(A1:A20, 90%)

QUARTILE 함수 사용법 및 예제

QUARTILE 함수는 데이터 집합의 사분위수를 계산하는 데 특화된 함수입니다. 엑셀 2010 이후 버전에서는 QUARTILE.INC와 QUARTILE.EXC로 나뉘었습니다.

QUARTILE 함수의 기본 구문

=QUARTILE(array, quart)
=QUARTILE.INC(array, quart)
=QUARTILE.EXC(array, quart)
  • array: 사분위수를 계산할 데이터 범위
  • quart: 구하고자 하는 사분위수 (0~4 사이의 정수)

quart 인수의 의미

quart 값 의미
0 최소값
1 1사분위수 (25번째 백분위수)
2 2사분위수 (50번째 백분위수, 중앙값)
3 3사분위수 (75번째 백분위수)
4 최대값

QUARTILE 함수 활용 예제

A1:A20 범위에 학생들의 시험 점수가 있다고 가정해 보겠습니다.

  1. 1사분위수(Q1) 구하기:이는 =PERCENTILE(A1:A20, 0.25)와 동일한 결과를 제공합니다.
  2. =QUARTILE(A1:A20, 1)
  3. 중앙값(Q2) 구하기:이는 =MEDIAN(A1:A20)과 동일한 결과를 제공합니다.
  4. =QUARTILE(A1:A20, 2)
  5. 3사분위수(Q3) 구하기:이는 =PERCENTILE(A1:A20, 0.75)와 동일한 결과를 제공합니다.
  6. =QUARTILE(A1:A20, 3)

백분위수와 사분위수를 활용한 데이터 분석

백분위수와 사분위수는 데이터 분석에서 다양하게 활용될 수 있습니다. 몇 가지 실용적인 활용 방법을 살펴보겠습니다.

이상치(Outlier) 탐지하기

사분위수를 이용하면 데이터에서 이상치를 쉽게 탐지할 수 있습니다. 일반적으로 다음 공식을 사용합니다:

  • 하한 경계: Q1 - 1.5 × IQR
  • 상한 경계: Q3 + 1.5 × IQR
  • IQR(사분위 범위): Q3 - Q1

예를 들어, 데이터가 A1:A100 범위에 있다면:

Q1 = QUARTILE(A1:A100, 1)
Q3 = QUARTILE(A1:A100, 3)
IQR = Q3 - Q1
하한 경계 = Q1 - 1.5*IQR
상한 경계 = Q3 + 1.5*IQR

이 경계를 벗어나는 값들은 이상치로 간주할 수 있습니다.

성적 평가 및 등급 부여하기

백분위수를 활용하여 성적 분포에 따른 등급을 부여할 수 있습니다:

  • A 등급: 상위 10% (90번째 백분위수 이상)
  • B 등급: 상위 1030% (7090번째 백분위수)
  • C 등급: 상위 3070% (3070번째 백분위수)
  • D 등급: 상위 7090% (1030번째 백분위수)
  • F 등급: 하위 10% (10번째 백분위수 미만)

데이터 시각화 향상시키기

사분위수를 활용하여 박스 플롯(Box Plot)을 그리면 데이터의 분포를 시각적으로 쉽게 파악할 수 있습니다. 엑셀에서는 다음 값들을 계산하여 박스 플롯을 그릴 수 있습니다:

  • 최소값: =MIN(데이터 범위) 또는 =QUARTILE(데이터 범위, 0)
  • Q1: =QUARTILE(데이터 범위, 1)
  • 중앙값: =QUARTILE(데이터 범위, 2)
  • Q3: =QUARTILE(데이터 범위, 3)
  • 최대값: =MAX(데이터 범위) 또는 =QUARTILE(데이터 범위, 4)

실무에서 PERCENTILE과 QUARTILE 함수 활용하기

영업 성과 분석

영업 팀의 실적 데이터가 있을 때, 백분위수와 사분위수를 활용하여 다음과 같은 분석을 할 수 있습니다:

  1. 상위 20% 영업사원의 최소 실적 파악:
  2. =PERCENTILE(영업실적_범위, 0.8)
  3. 영업사원들의 실적 분포 파악:
  4. 하위 25%: =QUARTILE(영업실적_범위, 1) 중앙값: =QUARTILE(영업실적_범위, 2) 상위 25%: =QUARTILE(영업실적_범위, 3)

고객 데이터 분석

고객의 구매 금액 데이터를 분석할 때:

  1. VIP 고객 기준 설정 (상위 10%):
  2. =PERCENTILE(구매금액_범위, 0.9)
  3. 고객 세그먼트 나누기:
  4. 프리미엄 고객: Q3 이상 일반 고객: Q1~Q3 저구매 고객: Q1 미만

품질 관리

제품 생산 공정에서 품질 관리를 위해:

  1. 허용 오차 범위 설정:
  2. 하한: =PERCENTILE(측정값_범위, 0.025) 상한: =PERCENTILE(측정값_범위, 0.975)
  3. 공정 능력 분석:
  4. 중앙값: =QUARTILE(측정값_범위, 2) 분산 정도: Q3 - Q1

고급 기법: PERCENTILE과 QUARTILE 함수의 조합 활용

조건부 백분위수 계산하기

특정 조건을 만족하는 데이터에 대해서만 백분위수를 계산하고 싶을 때는 배열 수식을 활용할 수 있습니다:

=PERCENTILE(IF(조건_범위=조건, 데이터_범위), k)

이 수식은 Ctrl+Shift+Enter로 입력해야 합니다(Excel 365에서는 자동으로 처리됨).

동적 백분위수 계산하기

셀 참조를 활용하여 사용자가 원하는 백분위수를 동적으로 계산할 수 있습니다:

=PERCENTILE(데이터_범위, 백분위수_셀/100)

여기서 '백분위수_셀'은 사용자가 입력한 백분위수 값(1~100)이 있는 셀입니다.

결론

엑셀의 PERCENTILE과 QUARTILE 함수는 데이터 분석에서 매우 유용한 도구입니다. 이 함수들을 활용하면 데이터의 분포를 더 정확하게 이해하고, 의미 있는 통계적 인사이트를 얻을 수 있습니다. 특히 평균이나 중앙값만으로는 파악하기 어려운 데이터의 특성을 분석할 때 큰 도움이 됩니다.

백분위수와 사분위수는 데이터 분석의 기본이면서도 강력한 도구입니다. 이 개념을 제대로 이해하고 엑셀 함수를 활용할 수 있다면, 더 정확하고 의미 있는 데이터 분석이 가능해집니다. 실무에서 다양한 데이터 세트에 이 함수들을 적용해보며 데이터 분석 능력을 향상시켜 보세요.

자주 묻는 질문

Q: PERCENTILE.INC와 PERCENTILE.EXC의 주요 차이점은 무엇인가요?

A: PERCENTILE.INC는 기존 PERCENTILE 함수와 동일하며 k값에 0과 1을 포함할 수 있습니다. 반면 PERCENTILE.EXC는 k값에 0과 1을 포함할 수 없습니다. 일반적인 상황에서는 PERCENTILE.INC를 사용하는 것이 좋습니다.

Q: 사분위수와 백분위수 중 어떤 것을 사용해야 할까요?

A: 데이터를 4등분하여 대략적인 분포를 파악하려면 사분위수(QUARTILE)를 사용하고, 더 세밀한 분석이나 특정 백분율 지점의 값을 알고 싶다면 백분위수(PERCENTILE)를 사용하는 것이 좋습니다.

Q: PERCENTILE 함수에서 k값을 소수점(0.9)과 백분율(90%)로 입력할 때 결과가 동일한가요?

A: 네, 동일합니다. 엑셀에서는 두 형식 모두 같은 결과를 제공합니다. 예를 들어 =PERCENTILE(데이터_범위, 0.9)와 =PERCENTILE(데이터_범위, 90%)는 같은 결과를 반환합니다.

Q: 이상치(Outlier)를 탐지할 때 사분위수를 어떻게 활용하나요?

A: 일반적으로 Q1(1사분위수)에서 IQR(Q3-Q1)의 1.5배를 뺀 값보다 작거나, Q3(3사분위수)에서 IQR의 1.5배를 더한 값보다 큰 데이터를 이상치로 간주합니다. 이를 엑셀 수식으로 표현하면 Q1-1.5*IQR과 Q3+1.5*IQR입니다.

Q: QUARTILE 함수에서 quart 인수에 소수점 값을 입력하면 어떻게 되나요?

A: QUARTILE 함수에서 quart 인수에 소수점 값을 입력하면 소수점 이하는 버려집니다. 예를 들어, quart 값으로 1.7을 입력하면 1로 처리됩니다.