Excel에서 이상 값 함수를 사용하는 방법 (및 이유)

이상 값은 데이터에있는 대부분의 값보다 훨씬 높거나 낮은 값입니다. Excel을 사용하여 데이터를 분석 할 때 특이 치로 인해 결과가 왜곡 될 수 있습니다. 예를 들어 데이터 세트의 평균 평균은 실제로 값을 반영 할 수 있습니다. Excel은 이상 값을 관리하는 데 도움이되는 몇 가지 유용한 기능을 제공하므로 살펴 보겠습니다.

빠른 예

아래 이미지에서 이상 값은 Eric에게 할당 된 값 2와 Ryan에게 할당 된 173의 값으로 합리적으로 쉽게 식별 할 수 있습니다. 이와 같은 데이터 세트에서는 이러한 이상 치를 수동으로 파악하고 처리하기가 쉽습니다.

더 큰 데이터 세트에서는 그렇지 않습니다. 이상 값을 식별하고 통계 계산에서 제거 할 수있는 것은 중요합니다. 이것이이 기사에서 수행하는 방법에 대해 살펴볼 것입니다.

데이터에서 특이 치를 찾는 방법

데이터 세트에서 특이 치를 찾기 위해 다음 단계를 사용합니다.

1 사 분위수와 3 사 분위수를 계산합니다 (이것들이 무엇인지 잠시 이야기하겠습니다).
사 분위수 범위를 평가합니다 (이에 대해 좀 더 자세히 설명하겠습니다).
데이터 범위의 상한과 하한을 반환합니다.
이러한 경계를 사용하여 외부 데이터 포인트를 식별합니다.

아래 이미지에 표시된 데이터 세트의 오른쪽에있는 셀 범위는 이러한 값을 저장하는 데 사용됩니다.

시작하자.

1 단계 : 사 분위수 계산

데이터를 분기로 나누면 각 세트를 사 분위수라고합니다. 범위에서 가장 낮은 25 %의 숫자가 1 사 분위를 구성하고 다음 25 %가 2 사 분위를 구성하는 식입니다. 특이 치의 가장 널리 사용되는 정의는 1 사 분위보다 1.5 사 분위 범위 (IQR) 이상이고 3 사 분위보다 1.5 사 분위 범위보다 높은 데이터 포인트이기 때문에이 단계를 먼저 수행합니다. 이러한 값을 결정하려면 먼저 사 분위수가 무엇인지 파악해야합니다.

Excel은 사 분위수를 계산하는 QUARTILE 함수를 제공합니다. 여기에는 배열과 쿼트의 두 가지 정보가 필요합니다.

= QUARTILE (배열, 쿼트)

배열은 당신이 평가되는 값의 범위입니다. 그리고 quart 는 반환하려는 사 분위수를 나타내는 숫자입니다 (예 : 1 사 분위는 1, 2 사 분위는 2 등).

참고 : Excel 2010에서 Microsoft는 QUARTILE 함수의 개선 사항으로 QUARTILE.INC 및 QUARTILE.EXC 함수를 출시했습니다. QUARTILE은 여러 버전의 Excel에서 작업 할 때 이전 버전과 더 호환됩니다.

예제 테이블로 돌아 갑시다.

1 사 분위수를 계산하기 위해 셀 F2에서 다음 공식을 사용할 수 있습니다.

= QUARTILE (B2 : B14,1)

수식을 입력하면 Excel에서 quart 인수에 대한 옵션 목록을 제공합니다.

3 사 분위수를 계산하기 위해 F3 셀에 이전과 같은 공식을 입력 할 수 있지만 1 대신 3을 사용합니다.

= QUARTILE (B2 : B14,3)

이제 셀에 표시된 사 분위수 데이터 포인트가 있습니다.

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에 다음 수식을 입력하여 이러한 기준을 충족하는 값을 표시합니다.

= 또는 (B2 $ F $ 6)

그런 다음 해당 값을 C3-C14 셀에 복사합니다. TRUE 값은 이상 값을 나타내며 보시다시피 데이터에 두 개가 있습니다.

평균 평균을 계산할 때 이상 값 무시

QUARTILE 함수를 사용하여 IQR을 계산하고 가장 널리 사용되는 이상 값 정의로 작업 할 수 있습니다. 그러나 값 범위에 대한 평균 평균을 계산하고 이상 값을 무시하면 더 빠르고 쉽게 사용할 수있는 기능이 있습니다. 이 기술은 이전과 같이 이상 치를 식별하지 못하지만 이상치 부분을 고려할 수있는 것에 유연하게 대처할 수 있습니다.

필요한 함수는 TRIMMEAN이며 아래에서 해당 구문을 볼 수 있습니다.

= TRIMMEAN (배열, 백분율)

배열은 당신이 평균 원하는 값의 범위입니다. %는 (당신이 비율이나 소수 값으로 입력 할 수 있습니다) 데이터 세트의 상단과 하단에서 제외 할 데이터 포인트의 비율입니다.

평균을 계산하고 이상 값의 20 %를 제외하기 위해 아래 수식을 셀 D3에 입력했습니다.

= TRIMMEAN (B2 : B14, 20 %)

특이 치를 처리하기위한 두 가지 다른 함수가 있습니다. 일부보고 요구 사항을 식별하거나 평균과 같은 계산에서 제외시키려는 경우 Excel에는 요구 사항에 맞는 기능이 있습니다.