엑셀 평균 구하기, 빈칸과 0 때문에 값이 다를 때

엑셀 평균은 =AVERAGE(B2:B6)처럼 숫자가 있는 범위를 지정해 구한다. 다만 셀 범위 안의 빈칸과 텍스트는 빠지고, 숫자 0은 계산에 포함된다. 겉으로 같은 표처럼 보여도 나누는 개수가 달라지면 평균도 달라진다.

결과가 예상과 다르면 평균 수식을 바꾸기 전에 어느 셀이 숫자로 계산됐는지 확인한다. 아래는 가상 검사 기록이다. 예상값은 별도 산술로 대조했으며, 실제 Excel 앱 실행이나 인쇄 시험 결과는 아니다. 함수 설명은 Microsoft 공식 도움말의 Microsoft 365·Excel 2024·2021·2019·2016 적용 범위를 기준으로 한다.

엑셀 평균, 같은 합계인데 왜 결과가 다를까?

빈칸은 기록이 없다는 뜻이고, 0은 기록된 숫자이다. 이 차이를 없애려고 원본을 일괄 바꾸면 오히려 평균의 의미가 달라질 수 있다.

예를 들어 검사 건수 두 번이 12건과 18건이고 세 번째 기록이 아직 없다면, 입력된 두 숫자의 평균은 (12+18)/2=15이다. 세 번째 검사도 끝났으며 처리 건수가 실제 0건이었다면 (12+18+0)/3=10이다.

이 표에서 15가 항상 맞거나 10이 항상 틀린 것은 아니다. 완료된 검사만 평균 낼지, 예정된 모든 검사까지 포함할지를 먼저 정해야 한다. 미집계 기록을 임의로 0으로 바꾸지 않는다. 평균 숫자 옆에 집계 대상과 미집계 건수를 함께 남기면 오해를 줄일 수 있다.

가상 기록으로 엑셀 평균과 계산 개수 확인하기

AVERAGE 결과 옆에 SUM과 COUNT를 놓으면 분자와 분모를 따로 확인할 수 있다. 다음 표를 빈 시트의 A1:B6에 입력한다. B4에는 아무것도 넣지 않고, B6에는 작은따옴표를 포함해 '24를 입력한다. 작은따옴표는 숫자를 텍스트로 저장하기 위한 입력 표시이다.

1. D2에 =AVERAGE(B2:B6)을 입력한다. 예상값은 10이다.
2. D3에 =SUM(B2:B6)을 입력한다. 예상 합계는 30이다.
3. D4에 =COUNT(B2:B6)을 입력한다. 예상 숫자 개수는 3이다.
4. B6의 24가 실제 검사 건수라면, 원본을 남긴 뒤 B6을 숫자로 고친다. 이 예제에서는 내용을 지우고 숫자 24를 다시 입력할 수 있다.
5. 수정 후 예상값이 합계 54, 숫자 4개, 평균 13.5로 바뀌는지 확인한다.

Microsoft의 AVERAGE 설명은 참조 범위의 텍스트·빈 셀을 제외하고 0은 포함한다고 안내한다. COUNT 도움말은 숫자가 들어 있는 셀의 개수를 세는 용도이다. 따라서 위 표의 최초 결과는 30을 3으로 나눈 10이다.

숫자 개수를 셀 개수 5와 혼동하면 30/5=6이라는 다른 결과가 나온다. 모든 예정 검사를 5회로 보고 미집계까지 0으로 간주한다는 별도 규칙이 없는 한, 이 값을 입력된 숫자의 평균이라고 부르면 안 된다.

엑셀 평균에서 0을 빼도 되는 경우

0을 제외한다는 집계 기준이 있을 때만 AVERAGEIF를 사용한다. 평균을 높게 만들기 위해 0을 빼는 것은 계산 오류를 고치는 일이 아니다.

위 예제에서 B6을 숫자 24로 바꾼 뒤, 별도 셀에 다음 수식을 입력한다.

=AVERAGEIF(B2:B6,"<>0")

"<>0"은 0이 아닌 값이라는 조건이다. 이때 12·18·24의 합 54를 3으로 나누므로 예상 평균은 18이다. 일반 AVERAGE의 13.5와는 집계 대상이 다르다. 두 값은 각각 ‘0 포함 평균’과 ‘0 제외 평균’처럼 구분해서 표시한다.

AVERAGEIF 공식 도움말은 조건을 만족하는 셀이 없으면 #DIV/0!가 반환된다고 설명한다. 값이 전부 0인 표에서 0을 제외하면 평균 낼 숫자가 남지 않는다. 이 경우를 ‘평균 0’으로 바꾸면 계산 대상 없음과 실제 평균 0을 구분할 수 없게 된다.

평균이 이상할 때 원본을 지우기 전에 볼 것

범위 → 숫자 개수 → 0의 의미 → 오류 셀 순서로 확인한다. 데이터가 사라지는 일괄 찾기·바꾸기보다 원인을 좁히는 편이 안전하다.

• 마지막 기록이 빠짐: 수식 입력줄에서 B2:B6이 맞는지 본다. B7에 새 기록을 추가했다면 기존 범위에 자동 포함됐다고 가정하지 않는다.
• 숫자처럼 보이는데 개수가 적음: 텍스트 숫자나 단위가 붙은 24건을 찾는다. 숫자 서식만 바꾸는 것으로 저장된 텍스트까지 변환되는 것은 아니다. 숫자로 변환하는 순서를 확인한다.
• 빈칸처럼 보이는데 평균이 낮음: 0을 숨긴 표시 서식일 수 있다. 해당 셀의 수식 입력줄과 원래 값을 확인한다. 표시를 숨겨도 저장된 0은 평균에 남는다.
• #DIV/0!가 표시됨: 범위에 숫자가 하나도 없는지, 조건에 맞는 숫자가 없는지 확인한다. 빈 셀에 일괄 0을 채우기 전에 기록 누락부터 해결한다.
• #VALUE! 같은 오류가 전파됨: 범위 안의 오류 수식을 먼저 찾는다. 텍스트를 무시하는 동작과 오류값을 무시하는 동작은 다르다. 오류를 숨기는 수식을 먼저 덧씌우지 않는다.

필터로 일부 행을 감췄다고 AVERAGE가 보이는 행만 계산한다고 가정해서도 안 된다. 이 글의 예제는 필터가 없는 전체 범위 기준이다. 화면에서 보이는 자료와 실제 계산 범위가 같은지 필터 범위 점검부터 진행한다.

엑셀 평균을 확인할 때는 결과 하나만 보지 말고 합계 54·숫자 4개·평균 13.5처럼 계산 근거를 함께 본다. 마지막으로 ‘0을 포함했는가’와 ‘미집계를 어떻게 처리했는가’를 적어 두면 다음 사람이 같은 기준으로 기록을 이어갈 수 있다.

출처

1. Microsoft — AVERAGE 함수
2. Microsoft — AVERAGEIF 함수
3. Microsoft — COUNT 함수