엑셀 0(Zero)을 제외한 평균 계산하기

1. 0을 아예 없애는 방법 — 찾기/바꾸기

그래서 알려드린 방법은 "찾기/바꾸기" 기능을 사용하는 것이었습니다.

Ctrl + H 키를 누른 다음,

"찾을 내용" 부분에는 0을,
"바꿀 내용" 부분에는 아무 것도 입력하지 않고
"모두 바꾸기" 버튼을 누르는 것이지요.

2. 원본은 그대로 두고 0을 제외한 평균 구하기

그런데 원본 데이터는 그대로 둔 채로 0을 제외한 평균을 구해야 하는 경우가 있습니다.

번호	값(B26:B45)
1	7
2	7
3	5
4	7
5	0
6	2
7	5
8	9
9	9
10	8
11	2
12	8
13	1
14	0
15	1
16	6
17	9
18	1
19	1
20	1

위와 같은 표에서 0을 제외한 나머지 값들의 평균을 구해야 한다면 어떻게 해야 할까요?

이럴 때에는 아래와 같이 하면 됩니다.

=AVERAGEIF(B26:B45,"<>0")

결과: 4.944444…(0이 아닌 값 18개의 합 89 ÷ 18)

Averageif 함수는 Excel 2007 이후 버전부터 제공되는 함수로
그 이전 버전을 사용하신다면 아래와 같이 할 수도 있습니다.

=SUMIF(B26:B45,"<>0")/COUNTIF(B26:B45,"<>0")

결과: 4.944444…

우리가 아주 잘 알고 있는 Sumif와 Countif 함수를 이용하였군요.

그런가 하면 이렇게 할 수도 있겠습니다.

{=AVERAGE(IF(B26:B45<>0,B26:B45))}

결과: 4.944444…

수식 앞과 뒤에 { }가 붙은 것을 보니 뭐 생각나는 것 없습니까?

그렇지요. 바로 "배열 수식(Array Formula)"입니다.

{ }은 손으로 직접 입력하는 것이 아니라 =AVERAGE(IF(B26:B45<>0,B26:B45))까지 입력하고
Ctrl + Shift + Enter 키를 함께 치면 자동으로 생깁니다.

B26:B45<>0

이 부분이 의미하는 것은, B26:B45 셀의 값이 0인지를 하나씩 확인하는 것입니다.
그래서 0이 아닌 경우에는 Average를 계산할 때 반영하라는 의미입니다.

3. 예제 표에 들어 있는 수식 — RANDBETWEEN

참고로 위의 표에는 이런 수식이 들어 있습니다.

=RANDBETWEEN(0,10)

Randbetween은 사용자가 지정한 범위 내에 있는 임의의 숫자를 만들어주는 함수입니다.
위와 같이 Randbetween(0, 10)이라고 하면, 0 ~ 10 사이에 있는 임의의 숫자를 만들어줍니다.

정리 — 0을 제외한 평균 구하는 방법 비교

방법	수식	특징
AVERAGEIF	=AVERAGEIF(B26:B45,"<>0")	가장 간단함. Excel 2007 이후 버전
SUMIF ÷ COUNTIF	=SUMIF(B26:B45,"<>0")/COUNTIF(B26:B45,"<>0")	이전 버전에서도 사용 가능
배열 수식	{=AVERAGE(IF(B26:B45<>0,B26:B45))}	Ctrl+Shift+Enter로 입력(최신 Excel은 Enter만으로 가능)

자주 묻는 질문 (FAQ)

마치며

오늘은 여기까지…