엑셀 할머니 2화 - 평균내기

며칠 후...

좋았어. 정기공연도 잘 끝났으니 결산보고서를 작성해 볼까?

이번 정기공연은 관객과 동아리 선배들이 점수를 매겼다고 하던데. 평균을 내자.

메뉴에서 평균을 골라서 평균을 내 보면...

응? 평균이 생각보다 낮네.

...가 아니라! 누가 0점을 준 거야! 이럴 거면 점수를 제출하지 말라고. 0점은 제외하고 평균을 내야겠어. 그런데 어떻게 제외하지? 또 일일이 0점을 제외하고 더해야 하는 건가...

이잉. 민호가 또 고생이구나.

어, 할머니!

미안하다 얘야. 음악을 듣느라 늦었다.

할머니는 무슨 음악을 좋아하세요?

뭐 들리는 대로 듣지.

오늘은 모차르트와 레논인지 레몬인지 하는 가수가 같이 공연을 했단다.

그, 그래요...

아무튼 평균을 구하려는데 빵점을 제외한 평균을 구하고 싶은 것 아니냐?

네 맞아요.

평균은 홈 메뉴에 있는 '자동합계' 부분에서 구하거나

AVERAGE 함수를 이용하면 되는데, 이런 식이면

하나씩 더해서 나누는 수밖에 없겠어요.

이잉. 엑셀은 그리 단순한 프로그램이 아니야.

아무리 윈도우 만든 회사에서 만들었기쏘니

사람들이 왜 엑셀만 찾겠니.

그럼 이 경우에도 함수가 있나요?

그렇고말고. AVERAGEIF 함수가 있으면

지금 네 고민을 해결해줄 거다.

AVERAGEIF라, 평균(AVERAGE)과 만약(IF)이라니. 설마...

그래, 역시 내 증손주라 눈치가 빠르구나.

AVERAGEIF 함수는 범위 내에서 조건에 맞는 숫자만 평균을 내주는 함수란다.

그런 함수가 있었군요.

그럼 0을 제외하려면 어떤 조건을 입력해야 하죠?

AVERAGEIF 함수 뒤에는 평균을 구할 범위와 조건이 각각 들어간단다.

앞에는 구하려는 범위를 넣고, 뒤에는 "<>0"

을 한번 넣어보려무나.

어! 평균값이 올랐어요. 0이 제외된 모양인데요!

"<>0"을 넣었으니 0보다 작거나 0보다 큰 수만 평균에 들어갈 게다.

그러니 0은 제외되는 거지.

그런데 여기 AVERAGEIF 함수에 입력하는 [average_range]는 뭐죠?

괄호가 있으니 필수입력은 아닌 것 같은데...

나 닮아서 호기심도 좋아요.

[average_range]는 평균을 구할 실제 범위란다.

생략 시 range에 쓴 범위와 동일하게 계산되지.

예를 들어 이 표에서 3학년 이상 학생의 점수만 평균을 내고 싶다면...

기준의 대상이 되는 범위, 기준, 평균을 구할 범위 순대로 입력하면 된단다.

오늘도 또 배워가네요.

고맙습니다 할... 어? 이 시간에 웬 전화.

여보세요? 어, 하~ 알았어.

표정이 안 좋아 보이는구나.

동아리에서 온 전화예요.

이번 경연 평균점수를 낼 때 최댓값과 최솟값을 제외하라네요.

참. 무슨 올림픽 체조도 아니고...

MAX 함수와 MIN 함수로 가장 큰 수와 작은 수를 전체합에서 빼고,

인원수에서 2씩 빼서 나누면 되겠죠.

굳이 그렇게 할 필요가 없다.

엑셀 함수가 있는데 손으로 할 필요는 없잖니?

여기에도 함수가 있나요?

사실 '최댓값과 최솟값만 제외하고 평균을 내는 함수'는 없단다

하지만 비슷한 함수로 AVERAGEIFS와 TRIMMEAN 함수가 있지.

뭔가 어려워지는걸요.

시작은 늘 어려워.

그걸 버텨내고 달성해야 달콤한 거란다.

AVERAGEIFS는 AVERAGEIF에 S가 붙었네요.

영어에서 복수에 S 붙이는 것도 아니고...

놀랍게도 그게 맞단다.

AVERAGEIFS 함수는 여러 조건들을 만족하는 셀의 평균을 구하는 함수란다.

AVERAGEIF 함수를 여러 개 합쳐놓은 것과 같지.

그럼 조건이 둘 필요하겠네요.

하나는 최댓값 제외, 다른 하나는 최솟값 제외.

AVERAGEIFS 함수 구성은 위 그림과 같단다.

평균을 구할 범위, 첫 조건의 대상범위, 첫 조건...

지금은 모든 범위를 평균을 구할 점수대로 하고, 조건에는 

"<"&MAX(범위) ">"&MIN(범위)를 넣어보려무나.

어디 보자, 범위는 점수대로 하고

할머니가 말씀하신 조건을 넣으면...

와! 신기하네요.

그런데 AVERAGEIFS 함수를 쓰려니 조금 피곤하네요.

매 조건마다 조건범위를 입력하니 함수가 길어지기도 하고...

그럼 TRIMMEAN 함수를 써 보는 건 어떠니?

TRIMMEAN 함수는 상, 하위 비율을 제외하고 평균을 구하는 함수란다.

비율이요?

개수가 아니라요?

지금 민호가 쓰는 결산내역은 사람 수가 적지만,

나중에 몇 백, 몇 천 명의 숫자를 다룰 때는

갯수보다는 비율이 훨씬 쓸만할 거란다.

1명이 차지하는 비율 정도는 구할 수 있지 않겠니?

알았어요.

사람이 총 10명이니까. 한 명이 차지하는 비율은 10%.

위아래로 10%만 제외하면 되겠네요.

조심해야 한단다.

TRIMMEAN 함수에 입력할 제외 비율은

위아래를 합친 비율이란다.

그러니 0.1이 아니라 0.2를 입력해야 한다. 

주의! TRIMMEAN 함수가 제외하는 셀 개수가 홀수로 계산된다면, 함수는 하나를 포기하고 짝수개를 반으로 나누어 위아래에서 제외합니다 (예 : 50개 데이터에서 0.3 비율로 제외한다면 15개가 아니라 14개(상위 7 + 하위 7)가 제외됩니다.)

좋았어. 할머니 말씀대로 위아래를 합쳐서 0.2만큼...

어라?

AVERAGEIFS로 구한 평균과 TRIMMEAN으로 구한 평균이

다른데요?

그건 두 함수가 조금 다르게 처리해서 그렇단다.

AVERAGEIFS 함수에는 부등호를 썼지?

그러니 최댓값과 최솟값을 가진 점수는 전부 제외되었지.

그런데 TRIMMEAN 함수는

상, 하위 비율만큼만 잘라내는 방식이기 때문에

최댓값, 최솟값 중 정해진 개수만 제외된단다.

그러니까 AVERAGEIFS에서는 공동 1등과 공동 꼴등이 모두 제외된 반면,

TRIMMEAN 함수에서는 1등과 꼴등 중 하나씩만 제외된 거군요!

잘 하는구나!

평균의 세계는 생각보다 넓단다.

아직 남은 이야기가 무궁무진하지!

다음 시간을 기대하려무나.