- 최초 작성일: 2002-07-23
- 최종 수정일: 2026-09-29
- 조회수: 25 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 오류값이 포함된 평균구하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
"A beard well lathered, is half shaved. Well begun is half done."(비누 거품칠을 잘 한 수염은 반은 면도를 한 것이나 다름없다) — 우리말의 '시작이 반이다'에 해당하는 표현입니다. 무슨 일이든 처음 시작하기가 제일 어려운 법이지요. 오늘은 재고 관리 업무를 하시는 독자분의 질문에서 출발해, 오류값이 섞인 범위의 평균을 구하는 방법을 살펴보겠습니다.
- 최신 글을 불러오는 중...
오류값이 포함된 평균구하기
핵심 요약: ISERROR와 COUNT로 오류값을 제외한 평균 구하기
AVERAGE 함수는 범위 안에 #N/A 같은 오류값이 하나라도 있으면 결과 역시 오류로 나옵니다. ISERROR 함수로 오류값을 걸러내고 COUNT 함수로 유효 개수만 세는 배열 수식을 사용하면 이 문제를 해결할 수 있습니다.
- 문제 상황: 범위에 #N/A가 하나라도 있으면 AVERAGE 결과가 오류로 나옵니다.
- 해결 수식: =SUM(IF(ISERROR(범위),0,범위))/COUNT(범위)
- 입력 방법: Ctrl+Shift+Enter로 배열 수식으로 입력해야 합니다.
질문 하나
전 회사에서 재고 관리와 발주 업무를 하고 있는 사원입니다. 최근 3개월 소요량의 평균을 내려고 하니 #N/A가 있는 줄이면 평균에도 #N/A가 뜨더라구요. 어떻게 하는지 알려주시면 감사하겠습니다.
이 질문에 대해 이철현님이 아래와 같은 답변을 주셨습니다.
AVERAGE 함수의 인수에 오류값이 포함되어 있으면 AVERAGE 결과값도 오류가 나기 때문인 것 같습니다. 저는 아래처럼 하고 있습니다.
1. 범위(range라고 미리 이름 붙임) 내에서 오류 아닌 셀들의 합계 구함: {=SUM(IF(ISERROR(range),0,range))}
2. COUNT(range): COUNT 함수는 텍스트, 논리값, 오류값, 빈 셀은 제외하고 셀 수를 세기 때문에 자동적으로 오류 셀은 제외됩니다.
3. 오류 아닌 셀들의 평균 = 1 ÷ 2
아래와 같은 데이터가 있다고 가정합니다. 여기서 Data1 부분의 평균을 구하는 것은 손바닥 뒤집기보다 쉽습니다.
=TEXT("Data1 영역의 평균은 "&AVERAGE(B47:B56),"##.0")&"입니다"그러나 동일한 수식을 Data2에도 그대로 적용하면 #N/A, 즉 Not Available 오류가 발생합니다.
=AVERAGE(C47:C56)| Data1 | Data2 |
|---|---|
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =NA() |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
=INT(RAND()*100) | =INT(RAND()*100) |
이처럼 수식이나 결과값의 일부에 오류 표시가 있을 경우, 평균이나 합계를 구해야 할 경우가 있을 것입니다. 이럴 때에는 위에서 이철현님이 알려드린 것처럼 배열 수식에 정보 함수의 하나인 ISERROR 함수를 사용하시면 됩니다.
=SUM(IF(ISERROR(C47:C56),0,C47:C56))/COUNT(C47:C56)먼저 ISERROR 함수를 사용하여 범위 내에 오류값이 있나 없나를 검사해서 유효한 값만 summing 합니다. 그런 다음 COUNT 함수를 사용하여 숫자가 포함된 셀의 개수를 카운팅합니다. COUNT 함수는 숫자로 바꿀 수 없는 텍스트가 들어있거나 오류값이 들어있는 셀은 카운팅하지 않습니다. 그런 다음 Ctrl + Shift + Enter 키를 함께 눌러 수식을 마무리 합니다.
Q&A 덕에 오늘 강의는 완전히 거저 먹고 가는군요.
정리 — 오류값을 제외한 평균 구하기
| 함수 | 역할 |
|---|---|
| ISERROR(범위) | 오류값 여부를 TRUE/FALSE로 판별 |
| SUM(IF(ISERROR(범위),0,범위)) | 오류값은 0으로 치환해 합계 계산 (배열 수식) |
| COUNT(범위) | 오류값·텍스트·빈 셀을 제외한 숫자 개수 계산 |
| 합계 / COUNT | 오류값을 제외한 평균값 |
자주 묻는 질문 (FAQ)
Q1. AVERAGE 함수의 대상 범위에 오류값이 있으면 왜 결과도 오류가 되나요?
AVERAGE 함수는 인수 중 하나라도 #N/A, #DIV/0! 같은 오류값이면 계산 자체가 불가능해 결과값도 오류로 반환됩니다. 오류값을 무시하고 나머지 값만으로 평균을 구하려면 별도의 수식이 필요합니다.
Q2. 오류값을 제외하고 평균을 구하는 배열 수식은 어떻게 만드나요?
=SUM(IF(ISERROR(범위),0,범위))/COUNT(범위) 를 Ctrl+Shift+Enter로 입력합니다. ISERROR로 오류값인 셀은 0으로 치환해 합계에서 제외하고, COUNT 함수는 오류값·텍스트·빈 셀을 세지 않으므로 유효한 숫자 개수만 분모로 사용됩니다.
Q3. COUNT 함수가 오류값을 세지 않는다는 것은 무슨 의미인가요?
COUNT 함수는 숫자가 들어있는 셀만 개수로 세고, 텍스트나 논리값, 오류값, 빈 셀은 세지 않습니다. 따라서 오류값이 포함된 범위에서 COUNT를 사용하면 자동으로 오류값이 제외된 유효 데이터 개수를 얻을 수 있습니다.
마치며
ISERROR와 COUNT를 조합한 배열 수식은 실무 데이터에서 불가피하게 발생하는 오류값을 다룰 때 매우 유용합니다. 재고 관리, 매출 집계 등 실제 업무 데이터에는 오류값이 섞이기 쉬우니 오늘 배운 패턴을 꼭 익혀 두시기 바랍니다.
