최초 작성일: 2000-12-17
최종 수정일: 2026-09-29
조회수: 32 회
작성자: 권현욱 (엑셀러)
강의 제목: 음수를 제외한 평균 및 평균근속년수 구하기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

질문 하나

안녕하세요!

중간중간에 마이너스 값이 들어 있는 자료가 있는데 이 자료들 중에서

음수를 제외한 값의 평균을 구하려고 하는데요…

지금은 그냥 컨트롤 키를 눌러 일일이 범위를 지정해서 작업하고 있는데

아무래도 무식한 짓을 하고 있다는 생각이 듭니다.

방법이 있으면 알려주세요. 수고하십시오.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

음수를 제외한 평균 및 평균근속년수 구하기

Q: 음수를 제외한 값의 평균은 어떻게 구하나요?

SUMIF로 0 이상인 값의 합계를 구하고 COUNTIF로 0 이상인 값의 개수를 구해 나누면 됩니다.

Q: 조건이 두 개인 개수나 평균은 어떻게 구하나요?

배열수식을 사용합니다. 곱셈은 And 연산에 해당하므로 두 조건식을 곱하여 연결하면 됩니다. 입력한 뒤 Ctrl+Shift+Enter로 확정합니다.

Q: 특정 근속연수 구간의 평균은 어떻게 구하나요?

해당 구간의 근속연수 합계를 조건에 맞는 인원수로 나눕니다. 합계와 인원수 모두 두 조건식을 곱한 배열수식으로 구할 수 있습니다.

핵심 요약

0 이상인 값만 평균하려면 SUMIF로 조건에 맞는 값을 합산하고 COUNTIF로 개수를 세어 나누면 됩니다.
두 개 이상의 조건이 필요한 경우 배열수식에서 조건식을 곱해 AND 조건으로 결합할 수 있습니다.
근속연수가 1년 초과 3년 이하인 인원수나 평균 근속연수도 같은 배열수식 원리로 계산할 수 있습니다.

에구~ 예제 파일을 함께 보내주셨으면 더 좋았을 것을…

아래와 같은 예제 데이터가 있습니다(질문하실 때에는 예제 데이터를 함께 보내주시기 바랍니다. 꼭!!!)

<Source>

지점명	매출액	환입액	순매출액	평균 순매출액	평균 순매출액2
동서울	20000	-23000	=IF(D23<0,C23+D23,C23-D23)	=AVERAGE(E23:E31)	=SUMIF(E23:E31,">=0",E23:E31)/COUNTIF(E23:E31,">=0")
서서울	12000	1000	=IF(D24<0,C24+D24,C24-D24)
남서울	15000	2340	=IF(D25<0,C25+D25,C25-D25)
북서울	18000	25000	=IF(D26<0,C26+D26,C26-D26)
부산	25000	1790	=IF(D27<0,C27+D27,C27-D27)
대구	13500	500	=IF(D28<0,C28+D28,C28-D28)
대전	30000	-45000	=IF(D29<0,C29+D29,C29-D29)
광주	21400	1400	=IF(D30<0,C30+D30,C30-D30)
제주	10000	890	=IF(D31<0,C31+D31,C31-D31)

조건이 여러 개일 경우에는 배열수식을 사용해도 되겠습니다만, 이 경우에는 그냥 Sumif 함수와 Countif 함수만 조합해도 해결하실 수 있습니다. 위의 계산결과를 보시면, "평균 순매출액"은 말 그대로 순매출액의 단순 평균값을 구한 것이고, "평균 순매출액2"는 순매출액이 0 이상인 값들만 대상으로 평균값을 구한 것입니다.

=SUMIF(E23:E31,">=0",E23:E31)/COUNTIF(E23:E31,">=0")

보시는 것 처럼 방법은 아주 간단합니다. 영역 내에서 0 이상인 값들을 모두 더한 다음 Countif 함수를 사용해서 0 이상인 값들의 수로 나누어주면 그것이 바로 양수값들만의 평균값이겠지요.

기왕 설명을 드린 김에 비슷한 질문 하나 더!(이것은 UNO21님 Site에 올라온 질문에 대해 Exceller가 답변한 것을 옮깁니다).

질문 두울

근속연수에 대해 질문을 드릴께요.

성명 근속연수(년)

XXX 2.1

XXX 0.9

XXX 3.5

XXX 1.2

이런 예제에서 근속연수가 1년에서 3년 미만인 사람의 숫자를 구하려 하는데,

Countif(b1:b5,"<1") 이렇게 하면 1년 이하인 사람의 숫자는 구할 수가 있는데,

"1년에서 3년미만"이라는 부등호는 어떻게 나타내야 하는지 알 수가 없군요.

임시방편으로 =Countif(b1:b5,">1") - Count(b1:b5,">3") 이런 식으로 해결은

했는데 간단하게 나타내는 방법은 없나요?

이 분의 경우에는 조건이 두 가지 이므로… 그렇지요! 배열수식을 사용하시면 될 것 같은 생각이 불현듯 드시지요? ^^

참고: 원문의 예제 표는 수식과 설명 문장이 표 칸 안에 뒤섞여 있어, 예제 데이터와 수식, 설명을 나누어 정리했습니다. 또한 질문은 1년 이상 3년 미만이었지만 아래 수식의 조건은 1년 초과 3년 이하(>1, <=3)입니다. 조건을 바꾸려면 부등호를 조정하시면 됩니다.

성명	근속연수
갑동이	2.3
갑순이	2.6
을동이	3.5
병동이	1.2
을순이	4.8
삼식이	0.6
사식이	1.6
오식이	5.9
오순이	3.5

1년 초과 3년 이하인 사람의 수는 아래 수식으로 구합니다.

{=SUM(IF((C67:C75>1)*(C67:C75<=3),1,0))}

같은 조건에 해당하는 사람들의 평균 근속연수는 아래 수식으로 구합니다.

{=SUM(IF((C67:C75>1)*(C67:C75<=3),(C67:C75),0))/SUM((C67:C75>1)*(C67:C75<=3))}

式에 대해서는 별도의 설명이 필요 없으시겠지요? 배열수식에서 And 연산에 해당하는 *를 사용해서 두 가지 조건식을 연결해 준 것 뿐입니다.

함수든 VBA든 응용이 중요합니다. 아무리 간단한 것이라도 직접 입력해 보고 수정해 보아야 합니다. 단순히 눈으로 쓱 보고서는, '이거 뭐 아무 것도 아니구만!' 하고 있으면 더 이상의 발전은 힘들 것입니다. Easy Come, Easy Go 입니다. 쉽게 들어온 것은 쉽게 나가기 마련이지요.

다음 시간에…

2000-12-17

정리 — 음수를 제외한 평균 및 평균근속년수 구하기

구분	내용
음수 제외 평균	`=SUMIF(E23:E31,">=0",E23:E31)/COUNTIF(E23:E31,">=0")`
원리	0 이상인 값을 모두 더한 뒤 COUNTIF로 센 0 이상인 값의 개수로 나눔
근속연수 인원수	`{=SUM(IF((C67:C75>1)*(C67:C75<=3),1,0))}`
평균 근속연수	`{=SUM(IF((C67:C75>1)(C67:C75<=3),(C67:C75),0))/SUM((C67:C75>1)(C67:C75<=3))}`
조건이 둘일 때	배열수식에서 And 연산에 해당하는 *로 두 조건식을 연결