직장에서 보고서를 취합하다 보면 가장 골치 아픈 순간이 언제인가요?
저는 열심히 수식을 걸어놨는데, 데이터 중간에 #N/A, #VALUE!, #DIV/0! 같은 오류 메시지가 발생했을 때입니다.
더 큰 문제는 이 오류 하나 때문에 전체 합계(SUM)나 평균(AVERAGE)까지 덩달아 오류가 난다는 점입니다. 엑셀의 SUM 함수는 단 하나의 오류만 있어도 "나 계산 못 해!" 하고 포기를 선언해 버린답니다
이럴 때 대부분의 사용자는 IFERROR 함수를 써서 원본 데이터를 하나하나 수정하느라 시간을 낭비해야 합니다. 하지만 AGGREGATE 함수 하나만 알면, 오류가 있든 말든 신경 쓰지 않고 "정상적인 숫자만 골라서" 완벽하게 계산해 낼 수 있습니다.
오늘은 SUBTOTAL 함수의 형님 격이자, 엑셀 고수들의 비밀 무기인 AGGREGATE 함수의 사용법을 완벽하게 정리해 보도록 하겠습니다.
1. AGGREGATE 함수란 무엇인가요?
이름이 조금 어렵나요? 'Aggregate'는 '집합하다', '모으다'라는 뜻을 나타내고 있습니다. 쉽게 말해 "종합 선물 세트" 같은 함수입니다. 지난 시간에 말씀드린 SUBTOTAL 함수가 '숨겨진 행'을 무시하는 기능을 가졌다면, AGGREGATE 함수는 한술 더 떠서 '숨겨진 행'은 물론이고 '각종 오류 값'까지 무시할 수 있는 강력한 기능을 가지고 있습니다.
한 줄 요약:
"중간에 에러(#N/A)가 있어? 괜찮아, 그거 빼고 나머지끼리만 더해줄게!"
2. AGGREGATE 함수 공식 (인수 3가지)
이 함수는 기능이 많은 만큼 입력해야 할 재료(인수)가 3가지나 됩니다. 하지만 겁먹지 마세요. 실무에서 쓰는 조합은 정해져 있습니다.
=AGGREGATE(함수 번호, 옵션 번호, 계산 범위)
① 첫 번째 인수: 함수 번호 (무엇을 계산할까?)
SUBTOTAL 함수와 번호가 똑같습니다. 합계를 구할지, 평균을 구할지 정하는 번호를 말합니다.
- 1: AVERAGE (평균)
- 2: COUNT (숫자 개수)
- 3: COUNTA (비어있지 않은 셀 개수)
- 9: SUM (합계) ★ 가장 중요
② 두 번째 인수: 옵션 번호 (무엇을 무시할까?) ★핵심★
이 부분이 AGGREGATE 함수의 존재하는 가장 중요한 이유입니다. 어떤 방해물을 무시할지 결정합니다.
| 번호 | 기능 (무시하는 대상) |
|---|---|
| 4 | 아무것도 무시 안 함 |
| 5 | 숨겨진 행 무시 |
| 6 | 오류 값만 무시 (#N/A 등) |
| 7 | 숨겨진 행 + 오류 값 모두 무시 |
실무에서는 '6번(오류 무시)'을 가장 많이 씁니다.
③ 세 번째 인수: 범위
계산할 데이터 영역을 드래그하면 됩니다.
3. 실전 예제: VLOOKUP 오류가 섞인 매출표 합계 구하기
상황을 가정해 봅시다. 품목별 단가를 VLOOKUP 함수로 불러왔는데, 신제품이라 아직 단가표에 없는 품목이 있어서 #N/A 오류가 떴습니다.
- 사과: 1,000원
- 배: 2,000원
- 망고: #N/A (단가표에 없음)
- 포도: 3,000원
문제 상황 (SUM 함수의 한계)
이 상태에서 =SUM(범위)를 입력하면 결과는 무엇일까요?
1,000 + 2,000 + #N/A + 3,000 = #N/A
결국 전체 합계도 오류가 되어 보고서를 망치게 됩니다.
해결책 (AGGREGATE 적용)
이때 AGGREGATE 함수를 사용해서 "오류는 투명 인간 취급하고 합계를 구해라"라고 명령해 봅시다.
- 9: 합계(SUM)를 구할 거야.
- 6: 오류 값(#N/A)은 무시할 거야.
- C2:C5: 이 범위 안에서 계산해 줘.
이렇게 입력하고 엔터를 치면, 엑셀은 망고의 오류(#N/A)를 0으로 취급하거나 건너뛰고, 1,000 + 2,000 + 3,000 = 6,000원이라는 정확한 정답을 내놓습니다.
4. 심화 비교: IFERROR vs SUBTOTAL vs AGGREGATE
비슷해 보이는 이 세 가지 방법, 도대체 언제 무엇을 써야 할까요? 엑셀 고수로 가는 명쾌한 비교를 해드리겠습니다.
| 함수 | 오류 처리 방식 | 추천 상황 |
|---|---|---|
| IFERROR | 오류 자체를 0이나 빈칸("")으로 변경 | 데이터 원본을 깔끔하게 보여줘야 할 때 |
| SUBTOTAL | 숨긴 행은 무시하지만, 오류가 있으면 계산 불가 |
데이터에 오류는 없고 필터링만 할 때 |
| AGGREGATE | 오류가 있든 숨겨졌든 다 무시하고 결과 도출 |
오류가 섞인 데이터의 통계를 낼 때 |
즉, "원본 데이터의 #N/A 표시는 그대로 두고(누락된 것을 알아야 하니까), 합계만 제대로 구하고 싶다"면 AGGREGATE가 유일한 정답입니다.
5. 보너스 꿀팁: 오류 무시하고 'N번째로 작은 값' 찾기
AGGREGATE 함수는 SUM이나 AVERAGE 같은 기본 연산 외에도 SMALL(작은 값 찾기)이나 LARGE(큰 값 찾기) 기능도 지원합니다. 함수 번호 15번(SMALL)을 쓰면 됩니다.
예를 들어 "오류를 제외하고 데이터 중에서 2번째로 작은 값을 찾아라" 같은 복잡한 미션도 해결 가능합니다.
(15번은 SMALL 함수, 6번은 오류 무시, 마지막 2는 2번째를 의미합니다.)
일반 SMALL 함수는 범위 내에 오류가 하나라도 있으면 작동하지 않지만, AGGREGATE는 오류를 피해 가며 정확히 값을 찾아냅니다.
6. 마무리하며
지금은 이름은 낯설지만 기능은 그 어떤 함수보다 강력한 AGGREGATE 함수에 대해 알아보았습니다.
핵심 요약:
- 데이터에 #N/A 같은 오류가 섞여 있을 때 사용한다.
- 공식은
=AGGREGATE(9, 6, 범위)이것만 기억하면 된다. (9=합계, 6=오류 무시) - 원본 데이터를 건드리지 않고 결과만 깔끔하게 뽑아낸다.
이제 오류가 떴다고 해서 당황하거나, 수백 개의 VLOOKUP 수식을 고치느라 야근하지 마세요. AGGREGATE 함수 하나면 퇴근 시간 1시간이 빨라집니다. 동료들이 "오류 났는데 합계가 어떻게 나왔어?"라고 물어본다면 멋지게 이 함수를 알려주세요.
다음 포스팅에서는 날짜 데이터에서 연/월/일을 자유자재로 추출하는 YEAR, MONTH, DAY 함수와 텍스트 날짜를 진짜 날짜로 바꾸는 DATE 함수에 대해 다뤄보겠습니다. 엑셀 고수를 위해 함께 하나씩 해봐요!.