이상치란 데이터 집합에서 대부분의 값들과 비교했을 때 현저히 높거나 낮은 값을 의미합니다. 엑셀에서 데이터를 분석할 때 이상치는 분석 결과를 왜곡시킬 수 있습니다. 예를 들어, 데이터 세트의 평균값은 실제 데이터를 제대로 반영하지 못할 수 있습니다. 엑셀은 이러한 이상치를 관리하는 데 유용한 여러 기능을 제공하며, 이 기능들을 자세히 알아보겠습니다.
간단한 예시
아래 그림을 보면, 이상치(Eric의 값 2와 Ryan의 값 173)를 비교적 쉽게 알아볼 수 있습니다. 이처럼 작은 데이터 세트에서는 수동으로 이상치를 찾아 처리하는 것이 어렵지 않습니다.
하지만 데이터 세트가 커지면 상황이 달라집니다. 이상치를 효율적으로 식별하고 통계 분석에서 제외하는 것이 중요합니다. 지금부터 이 작업을 수행하는 방법에 대해 알아보겠습니다.
데이터에서 이상치를 찾는 방법
데이터 세트에서 이상치를 찾기 위해 다음 단계를 따릅니다:
1. 1사분위수와 3사분위수를 계산합니다 (이 개념은 잠시 후 설명합니다).
2. 사분위수 범위(IQR)를 계산합니다 (이 역시 곧 설명합니다).
3. 데이터 범위의 상한과 하한을 결정합니다.
4. 이 경계를 기준으로 이상치를 식별합니다.
아래 그림에서 데이터 세트 오른쪽에 있는 셀들은 이러한 계산 결과를 저장하는 데 사용됩니다.
이제 시작해 보겠습니다.
1단계: 사분위수 계산
데이터를 네 개의 동일한 부분으로 나누었을 때, 각 부분을 사분위수라고 부릅니다. 가장 낮은 25%의 데이터는 1사분위수를, 다음 25%는 2사분위수를 구성하는 식입니다. 이상치에 대한 가장 일반적인 정의는 1사분위수 아래 1.5 IQR보다 작거나 3사분위수 위 1.5 IQR보다 큰 데이터 포인트입니다. 이 때문에 사분위수 계산을 먼저 수행해야 합니다.
엑셀은 사분위수를 계산하는 QUARTILE 함수를 제공합니다. 이 함수는 두 가지 정보, 즉 배열(array)과 사분위수(quart)를 필요로 합니다.
=QUARTILE(array, quart)
배열은 분석하려는 값들의 범위이며, 사분위수는 반환하려는 사분위수를 나타내는 숫자입니다 (예: 1사분위수는 1, 2사분위수는 2 등).
참고: 엑셀 2010부터는 QUARTILE 함수의 개선 버전인 QUARTILE.INC 및 QUARTILE.EXC 함수가 추가되었습니다. 하지만 QUARTILE 함수는 여러 버전의 엑셀에서 호환성이 더 높습니다.
예시 표로 돌아가겠습니다.
1사분위수를 계산하기 위해 F2 셀에 다음 수식을 입력합니다:
=QUARTILE(B2:B14,1)
수식을 입력하면 엑셀이 쿼트 인수에 대한 옵션 목록을 표시합니다.
3사분위수를 계산하기 위해 F3 셀에 이전과 같은 수식을 입력하지만, 1 대신 3을 사용합니다.
=QUARTILE(B2:B14,3)
이제 셀에 사분위수 값이 표시됩니다.
2단계: 사분위수 범위(IQR) 계산
사분위수 범위(IQR)는 데이터 값의 중간 50%를 의미합니다. 이는 3사분위수 값에서 1사분위수 값을 뺀 값입니다.
F4 셀에 다음과 같은 간단한 수식을 입력하여 IQR을 계산합니다.
=F3-F2
이제 사분위수 범위가 표시됩니다.
3단계: 하한 및 상한 결정
하한 및 상한은 유효한 데이터 범위에서 가장 작거나 큰 값을 의미합니다. 이 경계 값보다 작거나 큰 값은 이상치입니다.
IQR 값에 1.5를 곱한 후 1사분위수에서 빼서 F5 셀에 하한을 계산합니다.
=F2-(1.5*F4)
참고: 이 수식에서 괄호는 곱셈 연산이 뺄셈보다 먼저 계산되도록 하는 역할을 하지만, 수식의 가독성을 높이는 데에도 도움이 됩니다.
F6 셀에서 상한을 계산하기 위해 IQR에 1.5를 다시 곱하지만, 이번에는 3사분위수 값에 더합니다.
=F3+(1.5*F4)
4단계: 이상치 식별
이제 모든 기본 데이터가 준비되었으므로, 하한 값보다 작거나 상한 값보다 큰 외부 데이터 요소를 식별해야 합니다.
이 논리적 테스트를 수행하기 위해 OR 함수를 사용하고, C2 셀에 다음 수식을 입력하여 이 조건을 만족하는 값을 표시합니다.
=OR(B2$F$6)
그런 다음 C3부터 C14까지의 셀에 수식을 복사합니다. TRUE 값은 이상치를 나타내며, 보시다시피 데이터에 두 개의 이상치가 존재합니다.
평균 계산 시 이상치 제외
QUARTILE 함수를 사용하여 IQR을 계산하고, 일반적인 이상치 정의를 따르는 방법을 살펴봤습니다. 그러나 값 범위의 평균을 계산하고 이상치를 제외할 때는 보다 쉽고 빠르게 사용할 수 있는 함수가 있습니다. 이 방법은 이전처럼 이상치를 명확하게 식별하지는 않지만, 이상치로 간주될 수 있는 데이터에 유연하게 대처할 수 있습니다.
우리가 사용할 함수는 TRIMMEAN이며, 아래에서 구문을 확인할 수 있습니다.
=TRIMMEAN(array, percent)
배열은 평균을 계산하려는 값의 범위이며, 백분율은 데이터 세트의 상단과 하단에서 제외할 데이터 요소의 백분율입니다 (백분율 또는 소수 값으로 입력할 수 있습니다).
평균을 계산하고 이상치의 20%를 제외하기 위해 이 예시에서 셀 D3에 다음 수식을 입력했습니다.
=TRIMMEAN(B2:B14, 20%)
여기까지 이상치를 처리하는 두 가지 다른 방법을 살펴봤습니다. 보고서 요구 사항에 따라 이상치를 식별하거나, 평균과 같은 계산에서 제외하고 싶을 때, 엑셀은 필요한 기능을 모두 갖추고 있습니다.