엑셀 배우기, AVERAGEIF/ AVERAGEIFS 함수 활용, 단일/다중 조건의 평균 구하기!

지난 포스팅 엑셀배우기, COUNTIF/SUMIF/COUNTIFS/AVERAGE 함수로 전체 평균 이상인 학생들의 평균 구하기! 말미에, 단일 조건을 만족하는 셀 값의 합을 구하는 SUMIF 함수와 단일 조건을 만족하는 셀의 개수를 구하는 COUNTIF 함수를 함께 활용해서 단일 조건을 만족하는 셀들의 평균값을 구해봤습니다. 오늘은 이 두 함수로 계산한 내용을 AVERAGEIF 함수를 활용해서 한번에 계산해 보도록 하겠습니다.

물론 이제는, AVERAGEIF 및 AVERAGEIFS 함수의 기능도 쉽게 예상가능하실 겁니다. COUNTIF와 COUNTIFS 함수, SUMIF와 SUMIFS 함수의 관계와 마찬가지로 AVERAGEIF/S 함수는 단일 또는 다중 조건을 만족하는 셀 값의 평균을 구하는 함수입니다. 

모든 함수들 중에서도 COUNT 함수 및 SUM 함수 종류, AVERAGE 함수 종류는 컴퓨터활용능력 시험은 물론 엑셀 실무에서도 아주 유용하게 활용되는 함수들이니, 함수의 구조와 기능에 대한 완벽한 이해가 필요한 부분입니다!

먼저 위에서는 AVERAGE 함수로 각 과목별 점수의 평균과 학생별 모든 과목의 평균을 구해봤습니다. 함수 구성은 '=AVERAGE(평균할 범위 또는 평균할 셀들)' 입니다. 그리고 아래에서는 AVERAGEIF 함수를 이용해서 각 과목별 평균 점수 이상인 학생들의 해당 과목의 평균 점수를 다시 구해보았습니다.

일단, AVERAGEIF 함수 구성은, '=AVERAGEIF(조건의 범위, 조건, [평균할 범위])'입니다. COUNTIF/S 함수, SUMIF/S 함수와 마찬가지로 '조건'이 특정 숫자 또는 특정 셀과 '동일한' 값이 아니라면, 쌍따옴표(" ")로 묶어서 문자열로로 만들어 주어야 합니다. 또한 옵션 인수인 [평균할 범위]를 생략하면, 조건의 범위에서 해당 조건에 해당하는 값들을 대상으로 평균 값을 산출합니다.

아래의, C16 셀의 수식을 살펴보면, 조건의 범위와 평균할 범위가 값기 때문에, 맨 마지막 인수인 C4:C13은 생략 가능합니다.  조건 인수 부분은 이미 앞서 각 과목별 평균 점수를 AVERAGE 함수로 구했으니, 수식을  '=AVERAGEIF(C4:C13,">="&C15,C4:C13)'로 구성해도 되는데, 아래는 C15 셀의  함수 수식인 'AVERAGE(C4:C13)' 다시 풀어서 쓴 것 뿐입니다.

그리고 이제 AVERAGEIFS 함수를 알아보도록하겠습니다. 먼저 AVERAGEIFS 함수 구성은 '=AVERAGEIFS(평균할 범위, 조건1의 범위, 조건1, 조건 2의 범위, 조건 2, ...)입니다. COUTIFS 및 SUMIFS 함수와 함수의 기본 구조는 완전히 동일합니다. 

다만 셀의 개수를 세느냐, 셀 값의 합계를 구하느냐, 평균을 구하느냐의 차이일 뿐입니다. 아래의 I15셀의 함수 수식이 꽤 길지요? 아래의 수식은 각 과목별로 평균 이상을 기록한 한생들의 전체 과목 평균 점수를 다시 평균한 점수를 구하는 것에 대한 답입니다. 

즉, 학생들의 5개과목 평균 값들인 H4:H13 범위에서, 과목 1에서 평균 점수 65점 이상을 기록하고, 과목 2에서 평균 점수 57점을 이상을 기록하고, 과목 3에서 평균 점수 60점을 기록하고, 과목 4에서 평균 점수 61점을 기록하고,  과목 5에서 평균 점수 63점을 기록한 학생들만을 대상으로 그들의 전과목 평균 점수를 다시 평균한 값이란 의미입니다.

아래에서는 노란색으로 표시한 학생6, 7, 8이 다섯 과목에서 모두 평균 이상을 기록한 학생이고, 그들의 총점 평균, 80. 65, 90을 각각 평균하니, 85점이 되는 것입니다. 그리고 3등을 기록한 학생 9는 과목 1에서 평균 점수 이하를 기록해, 위의 조건을 만족하지 못한 것입니다. 

이렇게 해서 성적 좋은 학생들 뽑아서 서울대 진학반 만들고 그렇게 하면 되겠죠? 그리고 학생 9는 따로 상담을 해야겠네요. '너는 다 좋은데 과목 1을 중점적으로 더 하면 전체 석차에서 확 치고 나갈 수 있을 거다' 이런 식으로 말입니다. 

마지막으로 등수 이야기를 했으니, RANK 함수까지 언급하고, 이번 포스팅을 마무리하도록 하겠습니다. 아래에서는 RANK.EQ 함수를 사용했는데 구성은 RANK 함수와 동일합니다. '=RANK.EQ(순위를 구할 셀, 순위를 구할 범위)'입니다. RANK.EQ는 비교하는 범위 내에 동일한 값이 있을 경우에, 해당 값에 동일한 순위를 출력합니다.RANK 함수와 RANK.EQ 함수간의 특이할만한 차이는 없답니다. 그냥 둘 중에 아무 함수나 사용하셔도 무방합니다.  

단, 이 '순위를 구할 범위'는 반드시 절대 주소로 지정해야합니다. 그렇게 하지 않으면, I14셀을 채우기 핸들로 아래로 복사했을 때, 각 셀마다 참조하는 범위가 바뀌게 됩니다. 각 셀마다 참조하는 범위가 다른데, 올바른 순위 값을 출력할 수가 없습니다. 

이전부터 절대 주소 이야기를 계속 강조하겠지만, 채우기 핸들 사용했을 때, 본인이 예상한 결과와 다른 함수 값이 나오는 경우라면 거의 대부분이 함수가 참조하는 범위의 주소를 절대 주소로 지정하지 않았기 대문에 발생하는 경우가 대부분입니다. 그러니 항상 절대 주소로 범위 지정하는 습관을 들이도록 하세요!