엑셀 PERCENTILE.EXC 함수 사용법 마스터하고 특정 백분위수 제외 값 정확히 찾기

엑셀로 데이터 분석 작업을 하다 보면 특정 백분위수를 기준으로 값을 찾아야 할 때가 있죠? 😊 예를 들어, 상위 10%의 성적을 제외한 값을 구하거나, 하위 5%를 제외한 데이터 범위를 알고 싶을 때 말이에요. 이럴 때 아주 유용하게 사용할 수 있는 함수가 바로 PERCENTILE.EXC 함수랍니다! 오늘은 이 함수에 대해 쉽고 자세하게 알아보도록 할게요.

 

📌 핵심만 콕! PERCENTILE.EXC 함수는 지정한 배열에서 k번째 백분위수를 반환하며, 0%와 100%는 제외해요!

 

📊 엑셀 PERCENTILE.EXC 함수, 너는 누구냐?

PERCENTILE.EXC 함수는 데이터 집합에서 특정 백분위수(k)에 해당하는 값을 찾아주는 통계 함수예요. 여기서 중요한 점은 이름 뒤에 붙은 EXC인데요, 이는 Exclusive(제외하는)의 약자로, 0번째 백분위수(최솟값)와 100번째 백분위수(최댓값)를 계산 범위에서 제외한다는 의미를 가져요.

 

만약 0%와 100%를 포함해서 계산하고 싶다면 PERCENTILE.INC 함수를 사용해야 한답니다. 이 둘의 차이점을 아는 것이 정말 중요해요! 😉

 

✍️ PERCENTILE.EXC 함수 기본 구문 및 인수 살펴보기

함수를 사용하려면 먼저 그 생김새와 필요한 재료들을 알아야겠죠?

함수 구문:

=PERCENTILE.EXC(array, k)

인수 설명:

• array: 필수 요소예요. 백분위수를 구하려는 숫자 데이터 배열 또는 범위랍니다. 쉽게 말해, 우리가 분석하고 싶은 데이터들이 모여있는 곳이죠!
• k: 필수 요소예요. 0과 1 사이의 백분위수 값을 지정해요. 예를 들어, 25번째 백분위수를 찾고 싶다면 0.25를 입력하면 된답니다. 단, PERCENTILE.EXC 함수는 0과 1을 포함하지 않는 값만 사용 가능해요!

⚠️ 여기서 잠깐! 노트(NOTE):

• k 값은 반드시 0보다 크고 1보다 작아야 해요. 즉, 0 < k < 1 조건을 만족해야 합니다.
• 만약 array가 비어 있거나, k가 숫자 형식이 아니거나, k 값이 1/(COUNT(array)+1)보다 작거나 COUNT(array)/(COUNT(array)+1)보다 크면 #NUM! 오류가 발생해요. 이 범위는 데이터 개수(n)에 따라 달라지는데, 쉽게 말해 데이터 양 끝단의 특정 백분위수는 계산할 수 없다는 의미예요. 예를 들어 데이터가 3개일 때, k는 1/(3+1) = 0.25 보다 크고 3/(3+1) = 0.75 보다 작아야 해요.
• array에 숫자가 아닌 텍스트 값이 포함되어 있다면 계산에서 제외돼요.

 

💼 실제 업무 활용 예제 (feat. CSV 데이터)

백문이 불여일견! 실제 업무에서 PERCENTILE.EXC 함수를 어떻게 활용할 수 있는지 예제를 통해 알아볼까요? 아래 예제 데이터는 실제 업무에서 충분히 만날 수 있는 상황들이에요!

예제 1: 영업팀 실적 분석 - 상위 10% 제외한 기준점 찾기 (90번째 백분위수)

우리 회사 영업팀의 월별 판매량 데이터를 보고, 너무 특출난 상위 10%를 제외한 실적 기준점을 알고 싶다고 가정해 볼게요. 즉, 90번째 백분위수 값을 구하는 것이죠. (0.9는 1보다 작으니 PERCENTILE.EXC 사용 가능!)

데이터 (csv):

사원명,월별 판매량(건)
김민준,150
이서연,220
박도윤,180
최아라,250
정지후,130
윤채원,280
강하준,190
배서아,210
신은우,160
조유나,300
한로아,170
송이안,230

함수 사용:

셀 B14에 90번째 백분위수(상위 10% 바로 아래 값)를 구하려면 아래와 같이 입력해요.

=PERCENTILE.EXC(B2:B13, 0.9)

 

예제1: 엑셀 PERCENTILE.EXC 함수 90%값 기준

결과:

엑셀은 B2부터 B13까지의 판매량 데이터 중 90번째 백분위수에 해당하는 값을 계산해서 보여줄 거예요. 이 값은 상위 10%를 제외한 직원들의 실적을 가늠하는 기준이 될 수 있겠죠? (실제 값은 데이터 분포에 따라 계산됩니다. 이 경우 약 294가 나올 수 있습니다.)

 

예제 2: 제품 품질 관리 - 하위 5% 결함률 제외 기준 찾기 (5번째 백분위수)

생산된 제품의 결함률 데이터를 가지고, 매우 낮은 하위 5%의 결함률을 보이는 특이 케이스를 제외하고 일반적인 결함률 범위를 파악하고 싶어요. 즉, 5번째 백분위수 값을 구하는 거죠. (k=0.05)

데이터 (csv):

제품코드,결함률(%)
P001,0.5
P002,0.2
P003,0.8
P004,0.15
P005,0.3
P006,0.6
P007,0.25
P008,0.4
P009,0.1
P010,0.7
P011,0.35
P012,0.45

함수 사용:

셀 B14에 5번째 백분위수(하위 5% 바로 위 값)를 구하려면 아래와 같이 입력해요.

=PERCENTILE.EXC(B2:B13, 0.05)

 

예제2-1: 엑셀 PERCENTILE.EXC 함수 가장 잦은 오류 #NUM!

결과:

이 함수는 결함률 데이터에서 5번째 백분위수 값을 찾아줄 거예요. 이 값보다 높은 결함률을 가진 제품들이 일반적인 관리 대상이 될 수 있겠죠. (데이터 개수가 12개이므로 1/(12+1)은 약 0.0769입니다. 따라서 k=0.05는 #NUM! 오류를 반환할 수 있습니다. 이럴 때는 PERCENTILE.INC를 사용하거나, k값을 조정해야 합니다. 여기서는 k를 0.1 (10번째 백분위수)로 수정하여 예시를 보여드리겠습니다.)

 

수정된 함수 사용 (10번째 백분위수):

=PERCENTILE.EXC(B2:B13, 0.1)

 

예제2-2: 엑셀 PERCENTILE.EXC 함수 오류로 K 값 변경

결과는 약 0.115가 나올 수 있습니다. 이는 하위 10%를 제외한 결함률의 시작점을 의미합니다.

⚠️ 중요: 이 예제에서 보듯이, 데이터 개수(n)에 따라 k 값의 유효 범위가 달라져요. k가 1/(n+1)보다 작으면 #NUM! 오류가 발생하니 주의하세요!

 

예제 3: 고객 만족도 조사 - 상위 25% 고객의 만족도 기준점 찾기 (75번째 백분위수)

고객 만족도 조사 점수에서 상위 25%에 해당하는 고객들이 어느 정도의 만족도부터 시작하는지, 즉 75번째 백분위수 값을 알고 싶어요.

데이터 (csv):

고객ID,만족도 점수(100점 만점)
CUST001,85
CUST002,92
CUST003,78
CUST004,60
CUST005,95
CUST006,88
CUST007,70
CUST008,82
CUST009,90
CUST010,75
CUST011,86
CUST012,93

함수 사용:

셀 B14에 75번째 백분위수를 구하려면 아래와 같이 입력해요.

=PERCENTILE.EXC(B2:B13, 0.75)

 

예제3: PERCENTILE.EXC 함수 상위 25% 시작지점

결과:

계산된 값은 만족도 점수 중 상위 25%가 시작되는 바로 그 점수를 알려줄 거예요. (실제 값은 약 91.5가 나올 수 있습니다.) 이 점수 이상인 고객들은 우리 서비스에 매우 만족하고 있다고 볼 수 있겠죠!

 

🚫 PERCENTILE.EXC 함수 사용 시 흔한 오류들!

함수를 사용하다 보면 예상치 못한 오류 메시지를 만날 때가 있어요. 당황하지 마시고, 아래 내용을 참고해서 해결해 보세요!

• #NUM! 오류: 이 오류는 가장 흔하게 발생할 수 있어요.
  • 원인 1: k 인수가 0보다 작거나 같거나, 1보다 크거나 같은 경우. (예: =PERCENTILE.EXC(A1:A10, 1.2) 또는 =PERCENTILE.EXC(A1:A10, 0))
  • 원인 2: array (데이터 범위)가 비어있는 경우.
  • 원인 3: k 값이 데이터 개수(n)에 비해 너무 작거나 큰 경우. 즉, k < 1/(n+1) 이거나 k > n/(n+1) 일 때 발생해요. 데이터가 적을수록 k값의 선택 범위가 좁아지니 주의해야 해요! 예를 들어 데이터가 5개일 때 1/(5+1) = 0.166... 이므로, k=0.1을 사용하면 #NUM! 오류가 떠요.
• #VALUE! 오류:
  • 원인: k 인수가 숫자가 아닌 텍스트 값일 경우 발생해요. (예: =PERCENTILE.EXC(A1:A10, "이십오퍼센트"))
• #N/A 오류:
  • 원인: array 인수에 직접적으로 숫자가 아닌 값을 참조하고, 해당 값이 계산에 필요한 경우 발생할 수 있어요. (일반적으로 범위 내 텍스트는 무시되지만, 직접적인 참조는 문제를 일으킬 수 있습니다.)

오류가 발생하면 가장 먼저 입력한 수식과 데이터 범위를 다시 한번 확인해 보세요! 😊

 

🎓 컴활 자격증과 PERCENTILE.EXC 함수

컴퓨터활용능력(컴활) 시험을 준비하시는 분들이라면 통계 함수 파트에서 PERCENTILE 관련 함수들을 만나게 될 거예요. 특히 PERCENTILE.EXC와 PERCENTILE.INC의 차이점을 명확히 이해하고, 언제 어떤 함수를 사용해야 하는지 구분하는 능력이 중요해요. EXC는 "제외한다" 라는 의미를 기억하시면, 0%와 100%를 포함하지 않는다는 점을 쉽게 떠올릴 수 있을 거예요. 실기 시험에서 데이터 분석 문제로 출제될 수 있으니, 오늘 배운 내용을 잘 기억해두세요! 👍

 

🎉 마무리하며

오늘은 엑셀의 PERCENTILE.EXC 함수에 대해 자세히 알아봤어요. 이 함수를 사용하면 데이터 집합에서 특정 백분위수(0%와 100% 제외)에 해당하는 값을 효과적으로 찾을 수 있답니다. 이제 여러분도 실무 데이터 분석이나 시험 준비에 PERCENTILE.EXC 함수를 자신 있게 활용해 보세요!

 

오늘 내용이 유익했다면 주변 동료들이나 친구들에게도 공유해 주시는 센스! 잊지 마시고요 😉 궁금한 점이 있다면 언제든지 댓글로 남겨주세요!

 

 

작성자: 마늘빵