기본 콘텐츠로 건너뛰기

[엑셀] AVERAGEIF 계열 — 평균이라는 연산이 조건과 만날 때 평균의 정의(합/개수)와 조건부 집계의 결합

부서별 평균 급여, 학년별 평균 점수처럼 "조건에 맞는 것들만 골라서 평균을 내야" 하는 상황을 만나면 많은 사람이 AVERAGEIF를 검색해서 그대로 수식만 베껴 쓴다. 그런데 정작 "평균이라는 연산 자체가 무엇인지" 다시 떠올려보면, AVERAGEIF가 왜 그런 결과를 내는지 훨씬 명확하게 이해할 수 있다. 질문을 하나 던져보자. 조건에 맞는 항목이 하나도 없을 때 평균은 어떻게 될까?

평균이란 무엇인가 — 합을 개수로 나눈 값

평균은 수학적으로 아주 단순하다. "값들을 모두 더한 합"을 "값의 개수"로 나눈 것이다. 이 정의를 기억해두면 AVERAGEIF의 동작 방식이 훨씬 쉽게 이해된다. AVERAGEIF는 새로운 개념을 도입한 함수가 아니라, 앞서 다룬 SUMIF(조건에 맞는 값의 합)와 COUNTIF(조건에 맞는 값의 개수)를 내부적으로 결합해 그 결과를 나눈 것과 같은 일을 한다고 보면 된다.

AVERAGEIF의 동작 원리

=AVERAGEIF(B2:B50, "영업팀", C2:C50)

이 수식은 B2:B50에서 "영업팀"인 위치를 찾아 그 위치에 해당하는 C2:C50의 값들만 골라낸 뒤, 그 값들의 합을 다시 그 개수로 나눈다. 즉 개념적으로는 다음 계산과 동일한 결과를 낸다.

  1. SUMIF(B2:B50, "영업팀", C2:C50) — 영업팀 급여의 합
  2. COUNTIF(B2:B50, "영업팀") — 영업팀 인원 수
  3. 1번 결과를 2번 결과로 나눈 값 — 이것이 곧 AVERAGEIF의 결과

AVERAGEIF가 SUMIF, COUNTIF와 인수 구조(조건 범위, 조건, 대상 범위)를 거의 그대로 물려받은 이유도 여기에 있다. 셋 모두 '조건 범위를 참/거짓으로 나눠 대상 범위와 짝짓는다'는 동일한 뼈대 위에서, 마지막에 더하기·세기·나누기 중 무엇을 하느냐만 다를 뿐이다.

AVERAGEIFS — 여러 조건의 평균

=AVERAGEIFS(C2:C50, B2:B50, "영업팀", D2:D50, ">=2024-01-01")

SUMIFS·COUNTIFS와 마찬가지로, AVERAGEIFS도 조건이 여러 개일 때 각 조건의 참/거짓 배열을 AND로 겹친 뒤 그 위치의 값만으로 합과 개수를 계산해 나눈다. 인수 순서 역시 SUMIFS처럼 '평균 낼 범위'가 맨 앞에 온다는 점을 기억해두면 헷갈리지 않는다.

가장 많이 걸려 넘어지는 두 가지 함정

첫 번째는 조건에 맞는 항목이 하나도 없을 때다. 앞서 던진 질문의 답인데, 이 경우 분모(개수)가 0이 되므로 AVERAGEIF는 #DIV/0! 오류를 반환한다. 조건이 잘못 입력되었거나 데이터에 해당 값이 아직 없을 때 자주 마주치는 오류이므로, IFERROR로 감싸서 "데이터 없음" 같은 안내 문구로 바꿔주는 것이 실무에서는 일반적이다.

두 번째는 빈 셀과 0의 차이다. AVERAGEIF는 대상 범위에서 빈 셀은 계산에서 아예 제외하지만, 셀에 숫자 0이 입력되어 있으면 그 0은 평균 계산에 그대로 포함된다. 예를 들어 미입력 상태와 실제 매출 0원은 시트에서는 비슷해 보여도 평균 결과에는 전혀 다른 영향을 준다. 데이터를 입력할 때 이 둘을 구분해서 관리해야 나중에 평균값이 왜곡되는 것을 막을 수 있다.

Q. 조건에 맞는 항목이 1개뿐이면 평균은 어떻게 되나요?

그 항목 하나의 값이 그대로 평균값이 된다. 합을 개수 1로 나누는 것이므로 자기 자신과 같아진다.

Q. AVERAGEIF와 AVERAGE를 같이 쓸 수 있나요?

가능하다. 조건 없이 전체 평균이 필요하면 AVERAGE, 조건이 필요하면 AVERAGEIF·AVERAGEIFS를 쓰면 된다. 두 함수는 상황에 따라 같은 시트 안에서 얼마든지 함께 사용된다.

마무리

AVERAGEIF를 외울 때는 새로운 함수를 배운다고 생각하기보다, "합을 개수로 나눈다"는 평균의 정의 위에 SUMIF와 COUNTIF의 조건부 집계 원리를 그대로 얹은 것이라고 이해하는 편이 오래 기억에 남는다. 다음 글에서는 이런 조건부 집계와는 조금 다른 접근으로, 배열끼리의 곱셈만으로 조건을 표현하는 SUMPRODUCT의 원리를 살펴본다. 

이 블로그의 인기 게시물

[엑셀]엑셀 날짜는 사실 숫자다 — 1900년부터 세어온 일련번호의 정체

 날짜가 적힌 셀에 그냥 1을 더하면 무슨 일이 벌어질까? 신기하게도 결과는 정확히 '다음 날 날짜'가 된다. 문자처럼 보이는 날짜에 어떻게 숫자를 더했는데 날짜가 하루 늘어날 수 있을까. 답은 간단하다. 엑셀에서 날짜는 애초에 문자가 아니라 숫자이기 때문이다. 날짜가 숫자로 저장된다는 사실 확인하기 날짜가 입력된 셀의 서식을 '일반'으로 바꿔보면 그 정체가 바로 드러난다. 2026-01-15라고 표시되던 셀이 갑자기 46037 같은 숫자로 바뀐다. 이 숫자가 바로 엑셀이 내부적으로 이 날짜를 저장하고 있는 실제 값, 즉 시리얼 넘버(일련번호)다. 화면에 "2026-01-15"라고 보이는 것은 앞서 TEXT 함수 편에서 다룬 것처럼, 이 숫자 위에 날짜 서식이라는 표시 규칙이 덧씌워진 결과일 뿐이다. 1900년을 기준으로 세는 시리얼 넘버 시스템 엑셀은(윈도우 버전 기준) 1900년 1월 1일을 시리얼 넘버 1로 정하고, 그날부터 하루가 지날 때마다 숫자를 1씩 늘려가는 방식으로 날짜를 표현한다. 46037이라는 숫자는 곧 "1900년 1월 1일로부터 46,037번째 되는 날"이라는 뜻이다. 날짜에 숫자를 더하거나 빼는 계산이 자연스럽게 되는 이유가 바로 여기에 있다. 날짜끼리의 덧셈·뺄셈은 사실 이 일련번호끼리의 평범한 산술 연산일 뿐이다. =DATE(2026,1,15) + 30 이 수식은 2026년 1월 15일의 시리얼 넘버에 30을 더한 것과 같으므로, 결과는 정확히 30일 뒤의 날짜인 2026년 2월 14일이 된다. 시간은 어떻게 표현될까 — 소수점 이하가 하루의 몇 분의 몇인지 날짜가 정수로 표현된다면, 시간은 그 정수의 소수점 이하 부분으로 표현된다. 하루 24시간을 1로 놓았을 때, 낮 12시는 하루의 정확히 절반이므로 0.5가 된다. 예를 들어 46037.5라는 값은 "2026년 1월 15일 낮 12시"를 의미한다. 날짜와 시간이 함께 입력된 셀을 다룰 때 정수 부...

[엑셀]LEFT·RIGHT·MID의 공통점 — 텍스트는 결국 문자들의 배열이다

 "010-1234-5678"이라는 전화번호에서 가운데 네 자리만 뽑아내고 싶을 때, 많은 사람이 LEFT나 RIGHT는 익숙하게 쓰면서도 MID는 어렵게 느낀다. 그런데 이 세 함수는 사실 하나의 동일한 원리 위에서 동작한다. 텍스트를 하나의 덩어리가 아니라, 문자 하나하나가 순서대로 나열된 '배열'로 바라보는 관점이다. 텍스트를 문자 단위 배열로 보는 관점 "엑셀"이라는 두 글자 텍스트는, 배열의 눈으로 보면 1번째 자리에 "엑", 2번째 자리에 "셀"이 들어 있는 구조와 다르지 않다. 앞서 여러 글에서 다룬 배열이 셀들의 모음이었다면, 문자열 함수에서의 배열은 '문자들의 모음'이라는 점만 다를 뿐 "위치를 기준으로 원하는 부분만 골라낸다"는 발상 자체는 동일하다. LEFT·RIGHT·MID가 공유하는 하나의 원리 =LEFT("010-1234-5678", 3) → "010" =RIGHT("010-1234-5678", 4) → "5678" =MID("010-1234-5678", 5, 4) → "1234" 세 함수 모두 "어디서부터, 몇 글자를" 가져올지를 지정한다는 점에서 본질적으로 같은 함수다. LEFT는 항상 1번째 자리부터 시작한다고 고정해둔 것이고, RIGHT는 항상 끝에서부터 거꾸로 센 것이며, MID는 시작 위치를 자유롭게 지정할 수 있도록 일반화한 버전이다. 즉 LEFT와 RIGHT는 MID의 특수한 경우라고 볼 수 있다. MID로 문자 단위 추출을 직접 확인해보기 이 원리를 직접 확인해보는 가장 좋은 방법은 MID로 한 글자씩 뽑아보는 것이다. =MID("엑셀",1,1) → "엑" (1번째 자리 문자 1개) =MID("엑셀",2,1) → ...

[엑셀]배열수식이란 무엇인가 — 하나의 셀에 여러 계산이 담기는 원리 스칼라 연산과 배열 연산의 차이

 셀 하나를 클릭했는데 그 안에서 수백 번의 계산이 동시에 일어나고 있다면 믿어지는가. 앞선 글에서 다룬 SUMPRODUCT나 SUMIF의 조건 판정이 바로 그런 경우였다. 겉으로는 셀 하나에 결과값 하나만 보이지만, 그 이면에서는 범위 전체를 대상으로 한 계산이 통째로 진행되고 있었다. 이 글에서는 이 현상의 정체, 즉 배열수식이라는 개념을 원리 차원에서 짚어본다. 스칼라 연산과 배열 연산의 차이 평소 우리가 쓰는 대부분의 수식은 '스칼라 연산'이다. =A2+B2 처럼 셀 하나와 셀 하나를 계산해서 값 하나를 내놓는 방식이다. 입력도 하나, 출력도 하나다. 반면 '배열 연산'은 여러 개의 값을 하나의 묶음(배열)으로 취급해서 한꺼번에 계산한다. =A2:A10+B2:B10 이라고 쓰면, 이는 A2+B2, A3+B3, ... A10+B10이라는 아홉 번의 계산을 동시에 수행하라는 뜻이 된다. 입력은 여러 개(범위 대 범위)지만, 이 계산 결과를 최종적으로 어떻게 압축해서 보여줄지는 그 다음 문제다. 결과가 여러 개인데 셀은 하나뿐이라면? 여기서 배열수식의 진짜 개념이 드러난다. 아홉 번의 계산 결과 아홉 개의 값이 나왔는데, 이를 담을 셀이 하나뿐이라면 엑셀은 어떻게 해야 할까. 과거 방식에서는 이 아홉 개의 값을 SUM이나 COUNT 같은 함수로 다시 하나로 압축해서 보여주는 방식을 썼다. 예를 들어 {=SUM(A2:A10*B2:B10)} 이라는 수식은 A와 B 각 행을 곱한 아홉 개의 결과를 배열로 만든 뒤, 그 아홉 개를 SUM으로 다시 하나로 합쳐 셀 하나에 담아내는 구조다. 여기서 중괄호 {} 는 사용자가 직접 입력하는 기호가 아니라, 예전 엑셀에서 Ctrl+Shift+Enter를 눌러 "이건 배열수식입니다"라고 선언했을 때 엑셀이 자동으로 붙여주던 표시였다. 왜 예전에는 이런 특별한 입력이 필요했나 일반 함수는 셀 하나 대 셀 하나로 계산하도록 설계되어 있었기 때문에, 여러 값을 배열째로 넘기려면 엑셀에게...