엑셀에서 데이터의 평균을 산출해야 할 때 우리는 보통 가장 친숙한 AVERAGE 함수를 선택하곤 합니다. 하지만 시험을 치르지 않은 학생의 0점이나 아직 판매가 이루어지지 않은 항목의 0원이 섞여 있으면 분석 결과가 심각하게 왜곡됩니다. 이러한 0 값들을 그대로 포함하여 평균을 계산하면 전체 평균치가 하락하여 장표의 신뢰도가 떨어지게 마련입니다. 그렇다고 수백 행이 넘는 데이터 중에서 0이 적힌 칸만 마우스로 하나씩 찾아내어 수동으로 삭제할 수는 없는 노릇입니다. 오늘은 기존 표의 데이터 배열을 전혀 건드리지 않고 조건에 맞는 값만 골라내어 0을 제외한 진짜 평균을 구하는 방법을 상세히 알아보겠습니다.
1. 일반 AVERAGE 함수의 한계와 실무 데이터가 왜곡되는 이유
학교나 학원의 코딩 성적표를 정리하거나 회사의 분기별 영업 실적을 집계할 때 평균값은 통계의 기준이 됩니다.
기본적인 AVERAGE 함수는 지정한 범위 내에 있는 모든 숫자를 더한 뒤 전체 셀의 개수로 나누어 결론을 도출합니다.
여기서 문제는 결석이나 데이터 미입력으로 인해 채워진 숫자 0까지 정식 데이터로 인식하여 분모에 포함한다는 점입니다.
실제 시험에 응시한 학생들의 평균 실력을 보고해야 하는 상황인데 시험을 안 본 학생의 0점 때문에 평균이 대폭 깎이는 현상이 생깁니다.
결국 장표를 검토하는 상사나 제삼자에게 잘못된 통계 지표를 전달하게 되므로 실무에서는 매우 주의해야 하는 부분입니다.
따라서 무조건 전체를 계산하는 방식 대신 특정 숫자를 걸러내고 연산하는 영리한 수식의 활용이 필요합니다.
2. AVERAGEIF 함수와 연산자를 조합한 조건부 평균 산출법
이처럼 숫자 0을 계산에서 완전히 배제하고 싶을 때는 평균 함수에 조건 기능을 결합한 AVERAGEIF 함수를 사용하면 간단하게 해결됩니다.
이 함수는 내가 지정한 범위 안에서 특정 조건에 부합하는 셀들만 자석처럼 골라내어 평균을 내주는 고마운 도구입니다.
수식을 작성하기 위해 결과 값이 출력될 빈 셀을 선택하고 등호와 함께 =AVERAGEIF( 구문을 명확하게 입력해 줍니다.
함수의 첫 번째 인수 자리에는 평균을 구하고자 하는 성적이나 실적 데이터 범위 전체를 마우스로 길게 드래그하여 지정합니다.
이어서 쉼표를 찍은 뒤 두 번째 인수 자리에 0을 제외하라는 조건을 큰따옴표 내부에 문장 형태로 기입해 주어야 합니다.
서로 일치하지 않음을 뜻하는 크거나 작다 부호와 숫자 0을 결합하여 "<>0" 서식을 두 번째 칸에 넣어주고 가로를 닫습니다.
최종 구문은 =AVERAGEIF(범위, "<>0") 형태가 되며 이는 0과 같지 않은 값들만 모아서 평균을 내라는 명확한 명령이 됩니다.
엔터를 누르면 0점을 얻은 칸은 분모와 분자 계산에서 모두 제외된 채 실제 점수가 있는 데이터로만 채워진 정확한 평균값이 화면에 나타납니다.
3. 특정 점수대 영역으로 수식 조건을 다양하게 응용하는 테크닉
이 조건부 평균 함수는 숫자 0을 제외하는 상황 외에도 실무에서 다양한 통계 기준에 맞춰 응용할 수 있어 확장성이 뛰어납니다.
두 번째 인수 자리에 들어가는 비교 연산자의 방향과 수치를 조율하면 내가 원하는 특정 점수대만 격리하여 추출하는 것이 가능해집니다.
예를 들어 우리 반 학생들 중에서 코딩 성적이 90점 이상인 우수자들의 평균 점수만 따로 요약하여 상사에게 보고해야 하는 상황이 있습니다.
이럴 때는 수식의 조건 칸을 크거나 같다 부호로 변경하여 =AVERAGEIF(범위, ">=90") 형태로 수식을 입력하시면 됩니다.
반대로 특정 기준치 미만인 낙제점수대 영역의 평균을 구하고 싶다면 작다 부호를 활용하여 조건을 바꿔주면 오차 없이 결과가 나옵니다.
마우스로 데이터를 정렬하거나 필터를 걸어서 확인하는 번거로운 공정 없이 수식 한 줄로 모든 보고서 양식을 제어할 수 있는 유용한 기술입니다.
[전문가의 실무 한끗 팁]
만약 내가 지정한 범위 내의 모든 데이터가 전부 0점이거나 아예 비어있는 상태라면 AVERAGEIF 함수는 계산할 분모가 없어 화면에 #DIV/0! 에러를 출력합니다.
보고서에 이런 수식 에러 기호가 노출되면 장표의 가시성이 크게 떨어지므로 에러를 깨끗하게 처리해 주는 사후 보정 장치가 필요합니다.
이럴 때는 수식 외곽을 에러 방지 함수로 가볍게 감싸서 =IFERROR(AVERAGEIF(범위, "<>0"), 0) 형태로 공식을 구성해 보세요.
조건에 맞는 값이 단 하나도 없는 예외 상황이 발생하더라도 지저분한 기호 대신 깔끔한 숫자 0이나 공백으로 화면을 정돈해 주어 문서의 완성도가 높아집니다.
4. 장표의 신뢰도를 높이기 위한 통계 데이터 최종 마감 수칙
조건부 함수를 활용해 원하는 평균값을 정확하게 도출했다면 최종 저장 전 데이터 무결성 관점에서의 사후 검증을 진행해야 합니다.
간혹 원본 데이터 영역에 숫자가 아닌 텍스트 글자나 공백이 섞여 있으면 함수가 해당 칸을 인식하지 못해 엉뚱한 결론을 낼 리스크가 있습니다.
자동 채우기 핸들을 사용해 다른 열로 공식을 복사했을 때는 참조하는 데이터 범위 주소가 내 의도대로 똑바르게 움직이고 있는지 수식 입력줄을 눈으로 확인해야 합니다.
사소해 보이는 함수 옵션 제어의 차이가 불필요하게 수작업으로 계산기를 두드리며 허비하던 반복 노동 시간을 획기적으로 축소시켜 줍니다.
오늘 함께 완전히 손에 익힌 AVERAGEIF 조건 수식을 실무 파일에 바로 적용해 보시고 신속하게 담당 업무를 마감하여 당당하게 퇴근하시기 바랍니다.