Google 스프레드시트: 이상값을 제외한 평균 계산

Google 스프레드시트에서 이상치를 제외하면서 평균을 계산하는 방법에는 두 가지가 있습니다.

1. TRIMMEAN을 사용하여 이상값 제외

2. 사분위간 범위를 사용하여 이상값을 제외합니다.

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

방법 1: TRIMMEAN을 사용하여 이상값 제외

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배인 경우 관측치를 이상값으로 정의할 수 있습니다.

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

 =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 입니다.

추가 리소스

다음 튜토리얼에서는 Google Sheets에서 다른 일반적인 작업을 수행하는 방법을 설명합니다.

Google 스프레드시트에서 그룹별 평균을 계산하는 방법
Google 스프레드시트에서 평균 백분율을 계산하는 방법
Google 스프레드시트에서 가중 평균을 계산하는 방법