엑셀 배우기, COUNTIF/ SUMIF/ COUNTIFS/ AVERAGE 함수로 전체 평균 이상인 학생들의 평균 구하기!

지난 엑셀강좌 엑셀 배우기, SUMIFS 함수로 월별/품목별 판매수량/매출액 구하기! 에서 SUMIFS 함수가 다중 조건을 만족하는 셀값의 합을 구하는 함수였다면, 오늘의 엑셀 함수인, COUNTIFS는 다중 조건의 만족하는 셀의 개수를 구하는 함수입니다. SUM은 여러 셀의 합계를 구하는 함수고, COUNT는 여러 셀의 개수를 구하는 함수이니, 두 함수의 차이를 이해하기는 어렵지 않을 것이라 생각됩니다.

 기본적으로 두 함수의 메커니즘 자체가 동일합니다. SUMIFS는 'SUM(합) + IFS(다중 조건)' 구조로 다중 조건을 만족하는 셀값의 합이고, COUNTIFS는 'COUNT(개수) + IFS(다중 조건)' 구조로 다중 조건을 만족하는 셀의 개수라고 생각하시면 되겠습니다. 같은 맥락에서 COUTIF는 'COUNT(개수) + IF(단일 조건)' 구조로, 단일 조건을 만족하는 셀의 개수가 되는 것입니다. 두 함수의 관계는 SUMIF 함수와 SUMIFS 함수와 완전히 동일합니다. 조건의 개수와 함수의 구성 인수에서만 차이가 있을 뿐입니다.  

일단 아래의 표는 학생 7명과 각각에 대응하는 3과목 성적의 데이터입니다. 그리고 11번 행에 AVERAGE 함수로 각 과목별 평균 값을 계산했습니다. SUM 함수와 같은 기초중의 기초 함수로, 함수 구성은 '=AVERAGE(평균할 범위 또는 평균할 값들)'로 평균할 범위 전체를 지정해도 되고, 평균할 값들을 하나씩 입력해주어도 됩니다.

이제 본격적으로 COUTIF 함수와 COUTIFS 함수를 활용해 보도록 하겠습니다. 앞서 설명한대로 COUNTIF 함수는 '단일' 조건을 만족하는 셀의 개수, COUNTIFS 함수는 '다중' 조건을 만족하는 셀의 개수를 세는 함수들입니다. 먼저 COUNTIF 함수 구성은 '=COUNTIF(조건의 범위, 조건)'인데, 이 조건을 텍스트로 처리해줘야 한다는 것이 특이한 점입니다. 즉, 조건를 쌍따옴표(" ")로 묶어 주는 것입니다. 

아래의 C12셀의 COUNTIF 함수는 학생들의 과목 1의 점수 범위인 C3:C9의 범위에서, 해당 과목의 평균 값인 58점 이상인 셀을 개수를 셉니다. 앞서 AVERAGE 함수로 계산한 C11셀의 점수, 58을 조건 인수에 바로 입력하면 ">=58"와 같이 조건 전체를 쌍따옴표로 처리해주면 되는데, 해당 조건 안에 숫자가 아닌 특정 셀값을 지정하고 싶다면, & 연산자를 사용해서, ">="&C11 형태로 처리해 주어야 합니다. 

COUNTIF 함수를 활용하면서 거의 유일하게 실수하는 부분이 이것이니 잘 기억해 두시기 바립니다. 물론 지정 범위 내에서 조건과 '동일한' 숫자 또는 특정 셀과 '동일한' 셀을 찾는 다면, 조건에 쌍따옴표를 쓸 필요 없이 그냥 그 숫자나 특정 셀을 조건 인수에 바로 입력해 해주면 됩니다.

이제, 다중 조건을 처리할 수 있는 COUNTIFS로 세과목 모두 평균 이상을 기록한 학생이 몇명이나 되는지 확인해 보도록 하겠습니다. COUNTIFS 함수의 구성은 '=COUNTIFS(조건 1의 범위, 조건 1, 조건 2의 범위, 조건 2, 조건 3의 범위, 조건 3,...)입니다. 함구 구성 내에서 조건을 처리하는 방식은 COUNTIF와 마찬가지로 쌍따옴표("" "")로 조건을 묶어 줍니다. 아래 C14 셀 값을 풀이해 보도록 하겠습니다. 

학생들의 과목 1 점수 범위인 C3:C9에서 해당 과목의 평균 값인 C11보다 크거나 같은 셀과, 마찬가지로 과목2 점수 범위인  D3:D9에서 해당 과목의 평균 값인 D11보다 크거나 같은 셀,  마지막으로 과목 3의 범위 E3:E9에서 해당 과목의 평균 값인 E11보다 크거나 같은 셀, 이 세가지 조건을 모두 만족시키는 셀의 개수를 세는 것입니다. 그래서 아래에서는 학생 7, 딱 한명만 세 과목 모두 평균 이상을 넘긴 학생으로 카운트되었습니다.

마지막으로, 각 과목별로 전체 학생들의 평균 점수 이상인 학생들의 해당 과목 평균 점수는 어떻게 구해야 할까요? 여기에서 다시 SUMIF 함수를 활용하면 됩니다. 즉, 과목 1에서 전체 학생들의 평균 점수 이상인 학생들의 해당 과목 점수 합계를 구하고, 이 점수를 COUNTIF 함수로 구한 C11 값으로 나눠주면 간단히 해결되겠지요? 그래서 엑셀과 함수는 창의성이라고 말씀드리는 부분입니다. 

아주 간단한 더하기 나누기인데, 그럴듯한 '정보'를 얻었으니 말입니다. 물론 SUMIF 함수 복습을 위해서 만든 문제이긴 합니다만, 이런식의 정보 처리가 필요한 경우는 단순히 컴퓨터활용능력 시험 말고도 실무에서도 아주 많습니다. 일단 아래의 C16셀을 풀어보겠습니다. 

SUMIF 함수의 구성은 '=SUMIF(조건의 범위, 조건, [합계할 범위])'입니다. 참고로 SUMIF 함수에서 [합계할 범위] 인수를 따로 구성하지 않으면,'조건의 범위'에서 해당 조건에 맞는 셀들의 합을 구하게 됩니다. 

그리고 지정한 범위 내에서 조건과 동일한 숫자 값을 가진 셀들 찾는 것이 아니라면, 조건 인수 처리는 COUNTIF 함수로 마찬가지로, 쌍따옴표와 & 연산자를 추가해 주어야 합니다. 또한 지정 범위 내에서 조건과 '동일한' 숫자 또는 특정 셀과 '동일한' 셀을 찾는 다면, 조건에 쌍따옴표를 쓸 필요 없이 그냥 조건 인수에 바로 그 숫자 또는 특정 셀을 입력하면 되겠습니다.

학생들의 과목 1 점수 범위인 C3:C9에서 해당 과목의 전체 학생들의 평균 점수인 C11보다 크거나 값은 셀 값들의 총합을 모두 구하고, 과목 1의 전체 학생들의 평균 점수 이상인 학생들의 숫자인 C13 값으로 SUMIF 함수 값을 나눠주고 있습니다. 

사실, 이와 같이 조건을 만족하는 셀의 평균을 구하는 함수가 별도로 있습니다. 바로 AVERAGEIF 함수와 AVERAGEIFS 함수입니다. 당연히, SUMIF/S 함수, COUNTIF/S 함수와 동일한 맥락에서 사용되는 함수입니다. 

이 AVERAGEIF/S 함수에 대한 내용은 다음 포스팅은 포스팅으로 미루고, 오늘의 엑셀 강좌는 여기서 마무리 하도록 하겠습니다. 혹시 설명을 생략한 부분이 있거나 이해가 되지 않는 부분은 아래에 댓글 남겨주시기 바랍니다.