여러 조건에 맞는 셀의 평균 구하기 (AVERAGEIFS 함수)

고생끝에 SUMPRODUCT함수를 완전히 이해했다. 그래서 다중 조건에 맞는 셀의 합이나 평균을 구할 수 있게 되었다. 예를 들면, 8반학생 중에 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 평균을 구하고 싶다면?


그럼 수식은 다음과 같이 된다.

=sumproduct(n(학생의반=8)*n(영어>90)*n(수학>80)*(국어))/sumproduct(n(학생의반=8)*n(영어>90)*n(수학>80))

8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 합계/(나누기) 8반 학생중 영어90점 이상, 수학 80점 이상인 학생들의 숫자 = 8반 학생중 영어90점 이상, 수학 80점 이상인 학생들의 국어점수 평균


SUMPRODUCT함수가 각 조건들의 논리값중 TRUE만을 1로 반환한다는 점을 이용, 분모에는 주어진 조건을 만족하는 셀의 개수를, 분자에는 주어진 조건을 만족하는 셀안의 값의 합을 점수합계/학생수=평균 이라는 공식에 넣어서 만든 수식이다. 나름대로 애써서 만들었고, 그 결과에 대해 만족했다.


그런데.......


오늘 AVERAGEIFS라는 함수를 알게 되었다. 2007에서 새로 만들어진 함수인데, 위의 수식을 간단하게 만들 수 있다.

=AVERAGEIFS(국어,반,"=8",영어,">90",수학,">80")

너무나 간단해서 허탈하다.

마찬가지로, 다중조건에 맞는 셀의 값의 합을 구하는 함수는 SUMIFS 이다.
8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 국어점수의 합계는 다음과 같다.

=SUMIFS(국어,반,"=8",영어,">90",수학,">80")

다중조건에 맞는 셀의 개수를 구하는 함수는 COUNTIFS이다.
8반이며 영어90점 이상이고 수학이 80점 이상인 학생들의 수는 다음과 같다.

=COUNTIFS(반,"=8",영어,">90",수학,">80")

*참고1* 수식안에 들어있는 한글 (반, 영어, 수학, 국어)들은 셀의 범위가 이름지정으로 되어있다고 본 것이다. (예, $B$1:$B$400=반)
*참고2* SUMPRODUCT함수 안에 있는 n() 함수는 논리값 TRUE, FALSE 의 숫자값으로의 반환(1과 0)을 명확히 하기 위해 붙인것이다. 보통은 붙이지 않아도 상관없다.