0을 제외한 평균값 구하는 함수는 조건부 평균값을 구할 수 있는 AVERAGEIF 함수를 사용합니다. AVERAGEIF 함수는 ‘일 평균 판매량’, ‘특정 제품의 판매량’과 같이 조건에 따른 평균값을 구하기 위해 많이 사용합니다.
이번 포스팅에서는 0을 제외한 평균값과 같은 조건부 평균값을 구하는 함수에 대해 알아보겠습니다. AVERAGEIF 함수 적용 방법을 알아보고 이를 실제 실무에서 사용할 때 주의 사항도 함께 알아보겠습니다.
0을 제외한 평균값 구하기
가장 먼저 0을 제외한 평균값을 AVERAGEIF 함수를 통해 구해보겠습니다.
0을 제외한 평균값 구하는 함수 작성법
- 값을 입력할 셀을 선택해주세요
- =AVERAGEIF(범위선택,”>0″) 함수를 적용해주세요.
함수를 해석해보면 해당 범위 안에 0보다 큰 숫자들의 평균값을 구해달라는 내용입니다. 범위를 선택할 때 괄호를 입력한 후 평균값을 구하고자 하는 범위를 드래그하거나 셀 값(예시 A1:A30)을 직접 입력하면 됩니다.
적용된 함수와 같이 ‘AVERAGEIF(범위선택,”>0″)’로 함수를 입력해주면 됩니다. 위 함수를 보면 조건값에 “>0″을 입력했습니다.
이는 0을 제외하는 조건을 입력하기 위해 0보다 크다는 수식을 입력한 것입니다. 이 때 주의사항은 조건 앞, 뒤에 따옴표(“)가 반드시 입력되어야 합니다.
따옴표를 입력하지 않은 경우 함수가 적용되지 않습니다. 만약 함수를 직접 작성하기 어렵다면 아래 방법을 통해 0을 제외한 평균값을 구하면 됩니다.
수식입력을 통해 AVERAGEIF 함수 사용하기
수식입력을 통해 구할 경우에도 똑같은 AVERAGEIF 함수입니다.
아래 방법을 통해 함수를 적용할 수 있습니다.
- 메뉴바에서 수식을 클릭합니다.
- 함수삽입을 클릭합니다.
- AVERAGEIF 함수를 검색합니다.
- Range 항목을 클릭 후 평균값을 구할 범위를 드래그해줍니다.
- Criteria 항목에 >0을 입력해줍니다.
- 확인을 클릭합니다.
AVERAGEIF 함수에 대한 자세한 설명은 Microsofr 지원을 참고바랍니다. 조건부 평균값을 구하는 AVERAGEIF 함수를 사용하면 0을 제외한 평균값 외에도 특정 수 이상의 평균값 등 다양한 조건의 평균값을 구할 수 있습니다.
0을 제외한 평균값 구할 경우는 보통 일 평균 판매량과 같이 기간에 따른 평균값이 필요할 때입니다. 이와 같이 실무에 AVERAGEIF 함수를 적용할 때 주의사항을 정리해보겠습니다.
AVERAGEIF 함수 주의사항
AVERAGEIF 함수의 주의사항은 다음과 같습니다.
- 참조 범위(average_range) 안에 빈 셀이 있는 경우는 해당 셀은 무시됩니다.
- 범위가 빈 값이거나 텍스트 값인 경우 오류 값(#DIV/0!)으로 처리됩니다.
- criteria 값에 숫자가 아닌 셀값을 입력할 수도 있습니다. 다만, 선택된 셀이 비어 있는 경우 0으로 처리됩니다.
- 조건부 평균값을 구하는 범위 안에 조건을 만족하는 셀이 없다면 오류 값(#DIV/0!)으로 처리됩니다.
실무 적용 시 주의사항
<일 평균값 구할 때>
만약 판매 일자별 누적 데이트를 바탕으로 일 평균 판매량(평균값)을 구할 때 발생하는 오류에 대해 설명드리겠습니다.
일자 별 판매량을 작성하는 가운데 아직 지나지 않은 영업일의 경우 빈 셀이거나 0으로 입력되어 있을 수 있습니다. 많은 판매처에서 동일한 제품을 취급하고 있을 경우 전체 판매량을 확인하기 위해 동일 제품의 출고량을 합산하게 됩니다.
이 때 특정일에 한 제품의 판매량이 전 판매처에서 없을 경우 합산된 값은 “0”으로 표시 됩니다. 문제는 아직 도래하지 않은 판매일의 전체 판매처의 값을 합산한 값도 “0”으로 표시 되는 것입니다.
해당 셀에 “0”이라도 값이 존재하기 때문에 AVERAGEIF 함수로 0을 제외한 평균값을 구할 경우, 아직 도래하지 않은 판매일의 값까지 계산되어 현재까지의 평균값이 아닌 한 달 전체의 평균값이 적용되는 오류가 발생합니다. 이를 방지하기 위해서 일 평균값을 구할 경우 AVERAGEIF 함수가 아닌 아래 수식을 적용해주시는 것이 좋습니다.
=SUM(범위선택)/(TODAY()-DATE(0000,0,0))
DATE 함수 괄호 안의 값은 아래와 같습니다.
- 0000 : 년도
- 0 : 월
- 0 : 일
위 수식은 전체 판매량을 더한 값을 특정일부터 오늘까지를 일자수를 계산하여 나눈 것입니다. 보통 전날 판매량까지 데이터를 정리하기 때문에 해당 값으로 정리해서 사용하면 보다 정확한 값을 입력할 수 있습니다.
영업일 기준으로 보다 정확한 평균값을 얻기 위해서는 현재까지의 영업일을 계산한 셀을 만들고 현재까지 판매량을 더한 값을 해당 셀로 나눠주는 것이 가장 정확합니다.