[ 응용 ] 최댓값과 최솟값을 제외한 값들의 평균을 구하는 방법

아래 그림과 같은 자료가 있다고 할 때

최댓값과 최솟값을 제외한 값들의 평균을 구하는 방법을 알아보겠습니다.

 

728x90

 SUM(), MAX(), MIN(), COUNTA() 함수 사용

 

1. 최대값 찾기 : MAX(B2:F2)

2. 최소값 찾기 : MIN ( B2:F2 )

3. 최대값과 최솟값을 제외한 합계 구하기

• 모든 값의 합계 구하기 : SUM ( B2:F2 )
• 합계에서 최대값과 최솟값 제외하기 : SUM ( B2:F2 ) - MAX( B2:F2 ) - MIN ( B2:F2 )

4. 데이터의 개수 세기(최대값과 최솟값 제외)

• =COUNTA( B2:F2 ) - 2
• 전체 데이터의 개수 COUNTA( B2:F2 )에서 최댓값과 최솟값의 개수 2개 제외

5. 평균 계산하기

=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNTA(B2:F2)-2)

 

개인적으로 이 방법이 가장 간단하고 직관적인 방법이라고 생각합니다.

여기에 조금 더해 소수점 둘째자리에서 반올림하여

소수점 첫째자리까지만 표시하려면 아래와 같이 사용하면 됩니다.

 

=ROUND((SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNTA(B2:F2)-2),1)

 

 AVERAGEIFS() 함수 또는 AVERAGE(), IF() 함수 사용

 

AVERAGEIF() 함수를 사용할 경우 배열을 사용해야 하며

엑셀365 이전 버전의 엑셀에서는 배열을 사용 시

CTRL+SHIFT+ENTER를 사용해 수식을 입력해야 합니다.

 

처음 소개했던 방식을 거의 유사하게 사용합니다.

=AVERAGE(IF((B2:F2<>MAX(B2:F2))*(B2:F2<>MIN(B2:F2)), B2:F2))

1. B2:F2 범위에서 최댓값과 그렇지 않은 값을 비교합니다. 다른 값들은 TRUE로 변환됩니다.

2. B2:F2 범위에서 최솟값과 그렇지 않은 값을 비교합니다. 다른 값들은 TRUE로 변환됩니다.

3. 두 조건이 모두 참일 때만 TRUE를 반환합니다. 여기서 최댓값과 최솟값을 제외한 셀 값만 TRUE가 됩니다.

4. 이렇게 되면 보이지는 않지만 수식이 아래와 비슷한 구조가 됩니다.

• 위 자료에서 최댓값은 B2셀인 66 / 최솟값은 E2셀인 26
• 그러면 IF 함수의 첫 번째 인자에 있는 수식이 반환하는 값은 {0,1,1,0,1}
• 두 번째 인자는 {66, 43, 27, 26, 34}
• 두 개의 인자를 비교하여 남는 값은 {43, 27, 34}
• 즉, 첫 번째 인자 범위 중 값이 1인 부분만 두 번째 인자 범위에서 남게 됨.

5. AVERAGE 함수를 통해 평균값을 구함

 

이 수식은 아래와 같이 바꿀 수 있습니다.

=AVERAGEIFS(B2:F2, B2:F2, "<>"&MAX(B2:F2), B2:F2, "<>"&MIN(B2:F2))

 

이 수식은 엑셀의 배열에 관한 이해가 필요합니다.

 

반응형

 AVERAGE(), FILTER() 함수 사용

 

이 방법은 엑셀365에서만 가능합니다.

FILTER() 함수는 엑셀365에서 지원합니다.

 

이 수식은 AVERAGE(), IF() 함수를 사용 논리와 같습니다.

=AVERAGE(FILTER(B2:F2, (B2:F2<>MAX(B2:F2))*(B2:F2<>MIN(B2:F2))))

 

 

 마 치 며

 

개인적으로 첫 번째 방식이 가장 직관적이고 보기 편합니다.

문제를 풀어야 하는 등의 시험 상황이거나 지식 테스트를 받아야 하는 상황이 아니라면

첫번째 방법을 사용하겠습니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기