목차
이상치는 데이터에 있는 대부분의 값보다 훨씬 높거나 낮은 값입니다. Excel을 사용하여 데이터를 분석할 때 이상치는 결과를 왜곡할 수 있습니다. 예를 들어, 데이터 세트의 평균 평균은 실제로 귀하의 값을 반영할 수 있습니다. Excel은 이상값을 관리하는 데 도움이 되는 몇 가지 유용한 기능을 제공하므로 살펴보겠습니다.
빠른 예
아래 이미지에서 이상치(Eric에 할당된 값 2와 Ryan에게 할당된 값 173)를 합리적으로 쉽게 식별할 수 있습니다. 이와 같은 데이터 세트에서는 이러한 이상치를 수동으로 찾아 처리하는 것이 충분히 쉽습니다.
더 큰 데이터 집합에서는 그렇지 않습니다. 이상치를 식별하고 통계 계산에서 제거할 수 있는 것은 중요합니다. 이것이 이 기사에서 수행하는 방법에 대해 살펴볼 것입니다.
데이터에서 이상치를 찾는 방법
데이터 세트에서 이상값을 찾기 위해 다음 단계를 사용합니다.
1사분위수와 3사분위수를 계산합니다(이들이 무엇인지 잠시 후에 설명하겠습니다).
사분위수 범위를 평가합니다(이에 대해서는 좀 더 아래에서 설명하겠습니다).
데이터 범위의 상한과 하한을 반환합니다.
이러한 경계를 사용하여 외부 데이터 요소를 식별합니다.
아래 이미지에 표시된 데이터 세트의 오른쪽에 있는 셀 범위는 이러한 값을 저장하는 데 사용됩니다.
시작하자.
1단계: 사분위수 계산
데이터를 4분의 1로 나누면 이러한 각 집합을 4분위수라고 합니다. 범위에서 가장 낮은 25%의 숫자가 1사분위수를 구성하고 다음 25%가 2사분위수를 구성하는 식입니다. 이상값에 대한 가장 널리 사용되는 정의는 1사분위수 아래의 1.5사분위수 범위(IQR) 이상이고 3사분위수 위의 1.5사분위수 범위인 데이터 포인트이기 때문에 이 단계를 먼저 수행합니다. 이러한 값을 결정하려면 먼저 사분위수가 무엇인지 파악해야 합니다.
Excel은 사분위수를 계산하는 QUARTILE 함수를 제공합니다. 배열과 쿼트라는 두 가지 정보가 필요합니다.
=QUARTILE(array, quart)
배열은 평가 중인 값의 범위입니다. 그리고 사분위수는 반환하려는 사분위수를 나타내는 숫자입니다(예: 1사분위수는 1, 2사분위수는 2 등).
참고: Excel 2010에서 Microsoft는 QUARTILE 함수의 개선 사항으로 QUARTILE.INC 및 QUARTILE.EXC 함수를 출시했습니다. QUARTILE은 여러 버전의 Excel에서 작업할 때 이전 버전과 더 호환됩니다.
예제 테이블로 돌아가 보겠습니다.
1사분위수를 계산하기 위해 F2 셀에서 다음 공식을 사용할 수 있습니다.
=QUARTILE(B2:B14,1)
수식을 입력하면 Excel에서 quat 인수에 대한 옵션 목록을 제공합니다.
3사분위수를 계산하기 위해 F3 셀에 이전 것과 같은 수식을 입력할 수 있지만 1 대신 3을 사용합니다.
=QUARTILE(B2:B14,3)
이제 셀에 4분위수 데이터 포인트가 표시됩니다.
2단계: 사분위수 범위 평가
사분위수 범위(또는 IQR)는 데이터 값의 중간 50%입니다. 1사분위수 값과 3사분위수 값의 차이로 계산됩니다.
3사분위수에서 1사분위수를 빼는 간단한 공식을 F4 셀에 사용할 것입니다.
=F3-F2
이제 사분위수 범위가 표시되는 것을 볼 수 있습니다.
3단계: 하한 및 상한 반환
하한 및 상한은 사용하려는 데이터 범위의 가장 작은 값과 가장 큰 값입니다. 이러한 경계 값보다 작거나 큰 값은 이상값입니다.
IQR 값에 1.5를 곱한 다음 Q1 데이터 포인트에서 빼서 F5 셀의 하한을 계산합니다.
=F2-(1.5*F4)
참고: 이 수식의 대괄호는 곱하기 부분이 빼기 부분보다 먼저 계산되므로 필요하지 않지만 수식을 더 쉽게 읽을 수 있습니다.
셀 F6의 상한을 계산하기 위해 IQR에 다시 1.5를 곱하지만 이번에는 Q3 데이터 포인트에 추가합니다.
=F3+(1.5*F4)
4단계: 이상값 식별
이제 기본 데이터가 모두 설정되었으므로 외부 데이터 요소(하한 값보다 낮거나 상한 값보다 높은 항목)를 식별해야 합니다.
우리는 사용할 것입니다 OR 함수 이 논리적 테스트를 수행하고 C2 셀에 다음 공식을 입력하여 이러한 기준을 충족하는 값을 표시합니다.
=OR(B2$F$6)
그런 다음 해당 값을 C3-C14 셀에 복사합니다. TRUE 값은 이상치를 나타내며, 보시다시피 데이터에 2개가 있습니다.
평균 평균을 계산할 때 이상값 무시
QUARTILE 함수를 사용하여 IQR을 계산하고 가장 널리 사용되는 이상값 정의로 작업할 수 있습니다. 그러나 값 범위에 대한 평균 평균을 계산하고 이상값을 무시할 때 더 빠르고 쉽게 사용할 수 있는 함수가 있습니다. 이 기술은 이전과 같이 이상값을 식별하지 않지만 이상값 부분으로 간주할 수 있는 항목에 유연하게 대처할 수 있습니다.
우리에게 필요한 함수는 TRIMMEAN이라고 하며 아래에서 해당 구문을 볼 수 있습니다.
=TRIMMEAN(array, percent)
배열은 평균을 구하려는 값의 범위입니다. 백분율은 데이터 세트의 상단 및 하단에서 제외할 데이터 요소의 백분율입니다(백분율 또는 소수 값으로 입력할 수 있음).
우리는 평균을 계산하고 이상치의 20%를 제외하기 위해 아래 공식을 이 예에서 셀 D3에 입력했습니다.
=TRIMMEAN(B2:B14, 20%)
여기에는 이상값을 처리하기 위한 두 가지 다른 기능이 있습니다. 보고 요구 사항에 따라 식별하거나 평균과 같은 계산에서 제외하려는 경우 Excel에는 필요에 맞는 기능이 있습니다.