Excel: 이상값을 제외하고 평균을 계산하는 방법

Excel에서 이상값을 제외하면서 평균을 계산하는 방법에는 두 가지가 있습니다.

1. 평균을 계산하고 TRIMMEAN을 사용하여 이상값을 제외합니다.

2. 평균을 계산하고 사분위수 범위를 사용하여 이상값을 제외합니다.

두 가지 방법을 모두 사용하는 방법을 설명하기 위해 Excel에서 다음 데이터 세트를 사용합니다.

방법 1: 평균을 계산하고 TRIMMEAN을 사용하여 이상값 제외

Excel의 TRIMMEAN 함수를 사용하면 데이터 세트의 상단과 하단에서 특정 비율의 관측치를 제외하면서 값 범위의 평균을 계산할 수 있습니다.

예를 들어 다음 수식을 사용하여 총 관측치 20%(상위 10% 및 하위 10%)를 제외하고 A열의 평균 값을 계산할 수 있습니다.

 =TRIMMEAN( A2:A16 , 20%)

데이터 세트에 15개의 값이 있으므로 10%는 1.5와 같으며 이는 1로 반올림됩니다. 따라서 이 공식은 가장 작은 값과 가장 큰 값을 제외하고 범위에 있는 값의 평균을 계산합니다.

특이치를 제외한 평균은 58.30769 입니다.

방법 2: 평균을 계산하고 사분위간 범위를 사용하여 이상값 제외

IQR(사 분위수 범위 )은 데이터 세트에서 75번째 백분위수(Q3)와 25번째 백분위수(Q1) 간의 차이입니다. 평균 50% 값의 분포를 측정합니다.

관측치가 세 번째 사분위수(Q3) 위 사분위수 범위의 1.5배이거나 첫 번째 사분위수(Q1) 아래 사분위수 범위의 1.5배인 경우 관측치를 이상값으로 정의할 수 있습니다.

다음 공식을 사용하여 Excel에서 데이터 세트의 사분위수 범위를 계산할 수 있습니다.

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

다음 스크린샷은 이 수식을 사용하는 방법을 보여줍니다.

그런 다음 다음 공식을 사용하여 IQR을 사용하여 이상값을 식별하고 데이터세트에서 이상값인 모든 값에 “1”을 할당할 수 있습니다.

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

다음 스크린샷은 이 수식을 사용하는 방법을 보여줍니다.

이 데이터세트에서는 단 하나의 값인 164 만이 이상값으로 판명되었습니다.

마지막으로 다음 공식을 사용하여 데이터 세트에서 이상값이 아닌 모든 값의 평균을 계산할 수 있습니다.

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

다음 스크린샷은 이 수식을 사용하는 방법을 보여줍니다.

특이치를 제외한 평균은 55.42857 입니다.

추가 리소스

다음 자습서에서는 Excel에서 다른 일반적인 작업을 수행하는 방법을 설명합니다.

Excel에서 사분위수 범위(IQR)를 계산하는 방법
Excel에서 그룹별 평균을 계산하는 방법
Excel에서 가중 이동 평균을 계산하는 방법