[엑셀 문답] 조건부 평균 함수식이 오답인 이유?

간단한 엑셀 문제이지만, 실무에서 너무도 자주 접하는 함수식 문제 형태이다. 해당 조건을 만족하는 레코드( 행 )의 요구 필드( 열 )를 계산하는 것이다. 단순한 문제이지만 많은 분들이 어려워 하는 것 같다. 문제의 핵심은 참조이다. 이를 해결해 보자.

**님의 질문

.....
엑셀 과제인데 아무리 수식 입력해도 답이 안나온다. 합격인의 평균을 구하는 수식을 작성하고 싶다.

[그림1] 합격인의 평균 계산 엑셀 함수식

어떻게 함수식을 입력을 해야 할까??
보다 자세한 질문은 '합격여부' 필드가 '합격' 인 사람의 '필기시험' 과 '실기시험' 의 평균을 구하는 수식은 ... ?
.....

마늘빵의 답변

.....
질문의 문제를 해결할 수 있는 엑셀 함수식은 너무도 많다. 답변으로는 대표적인 조건부 평균 계산 함수, 2가지를 사용한다.

1. AVERAGEIF 함수를 사용하는 방법
2. DAVERAGE 함수를 사용하는 방법

먼저, 가장 단순하면서 이 문제에 가장 적합한 AVERAGEIF 함수로 풀어 보자.

[컴활 2급] 컴퓨터활용능력 실기 엑셀 AVERAGEIF 함수 - 통계 :
http://secstart.tistory.com/555

1. AVERAGEIF 함수식 사용

[그림2] 엑셀 AVERAGEIF 함수로 조건부 평균 계산

D12 셀에 포인터를 놓고 다음의 수식을 입력한다.

=AVERAGEIF( $F$3:$F$11, "합격", D3:D11 )

위의 함수식은 F3:F11 의 조건비교 범위의 각 셀의 값이 "합격" 인지를 비교하여 만족한다면 D3:D11 범위의 '필기시험' 필드( 열 )의 평균을 구하는 것이다.

F3:F11 의 range 인수를 절대참조한 이유는, 채우기핸들로 D12 셀의 수식을 E12 셀로 복사할 경우, 합격여부의 범위를 고정하기 위해서이다. 이 절대 참조가 없다면 수식 복사시 잘못된 결과가 반환될 것이다.

[엑셀 문답] 엑셀의 채우기 핸들을 바로 알고 싶어요 :
http://secstart.tistory.com/615

[그림3] 엑셀 상대참조 수식복사시 오답 원인

AVERAGEIF 함수는 COUNTIF, SUMIF 함수와 같은 부류의 함수이며 인수 사용법 또한 동일하다. 함께 배워두면 쉽게 여러 함수를 내것으로 만들 수 있다는 것도 알아두자.

두번째로, 조건부 계산에 최적화된 데이터베이스 함수를 사용하여 평균을 구해보자

[엑셀 모바일] DAVERAGE 함수로 조건부 필드 평균 구하기 :
http://secstart.tistory.com/744

2. DAVERAGE 함수식 사용

[그림4] 엑셀 DAVERAGE 함수로 조건부 평균 계산

D12 셀에 포인터를 놓고 다음의 DAVERAGE 함수식을 작성한다.

=DAVERAGE( $B$2:$F$11, D2, $H$2:$H$3 )

엑셀 데이터베이스 함수 중 하나인 DAVERAGE 함수를 이용한 답변이다. 위 수식은 B2:F11 의 테이블 영역을 데이터베이스화하여 D2 필드( 열 )의 평균을 계산한다. 단, H2:H3 의 조건을 만족하는 행( 레코드 )에 한해서 계산은 이루어진다.

'합격여부' 필드가 '합격' 인 레코드들의 '필기시험' 의 평균을 구하는 것이 위의 수식인 것이다.

위의 수식에서도 핵심은 데이터베이스 범위와 조건범위는 절대참조 되어 있고 '필드' 인수인 D2 는 상대참조 되어 있다. D12 에서 E12 로 오른쪽으로 수식복사 시 변해서는 안 되는 인수와 변해야만 하는 인수를 정확하게 짚어주는 것이다.

질문자는 이때, 상대 참조와 절대 참조의 구분을 확인하지 못한듯하다.
.....

이렇게 2가지 방법으로 조건을 만족하는 값들의 평균을 구할 수 있다. 물론 다른 방법도 얼마든지 있다.

이 질문과 답변을 보여 준 이유는 요구에 맞는 수식을 완성하였더라도 참조의 방법이 잘못되었다면 단일 셀 밖에 정답을 얻을 수 없다는 것이다. 이것은 엑셀 최대의 이점인 수식 복사를 활용하지 못하는 것이다. 참조 시 절대 참조와 상대 참조, 혹은 혼합 참조를 제대로 활용하여 엑셀의 수식 복사 기능을 100% 사용할 수 있기 바란다.

[엑셀] 상대 참조, 절대 참조, 혼합 참조의 차이 배우기
http://secstart.tistory.com/154

마늘빵.