들어가기 전에
엑셀에서 자주 쓰는 함수(예: SUM, AVERAGE, MIN, MAX 등) 수식 내에 단 하나의 오류 값이라도 있으면 제대로 작동하지 않을 수 있습니다. 범위에 #DIV/0!이나 #VALUE! 같은 오류 문자 하나만 있어도 전체 수식이 오류를 발생시키죠. AGGREGATE 함수는 오류를 무시한 채 계산을 수행해 IFERROR보다 간결하고 안정적인 수식을 작성할 수 있습니다. 특히 합계·평균·최대값 등 다양한 계산을 오류 영향 없이 처리해 데이터 분석 효율을 높여주는 함수입니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: My Excel formulas used to break all the time until I found this function
- URL: https://www.makeuseof.com/excel-formulas-used-to-break-until-i-found-this-function/
다른 함수들이 할 수 없는 일을 하는 AGGREGATE 함수
수식에 오류가 발생하면 IFERROR를 사용하는 경우가 많습니다. 이 방법도 효과는 있지만, 잘못된 데이터를 처리하기 위해 모든 수식을 추가적인 논리로 감싸 야 하므로 임시방편일 뿐입니다. AGGREGATE는 함수 자체에 오류 처리 기능이 내장되어 있어 이러한 문제를 해결해 줍니다.
다음 구문을 사용합니다.
=AGGREGATE(function_num, options, ref1, [ref2], …)
각 인수의 역할은 다음과 같습니다.
- function_num: 1부터 19까지의 숫자로, AGGREGATE 함수가 수행할 계산 유형 지정
- 예를 들어, 1은 AVERAGE, 4는 MAX, 5는 MIN, 9는 SUM을 의미
- options: 0부터 7까지의 숫자로, AGGREGATE 함수가 무시할 항목을 정의
- 가장 유용한 옵션은 5(숨김 행 무시), 6(오류 값 무시), 7(둘 다 무시)
- ref1: 계산할 셀 범위
=IFERROR(SUM(F1:F10),“”) 대신 =AGGREGATE(9, 6, F1:F10)을 작성합니다. 결과는 동일하지만, AGGREGATE 함수가 오류 값을 자동으로 건너뛰므로 중첩이나 추가 수식이 필요하지 않습니다. 더 깔끔하고 유지 관리가 훨씬 쉽습니다.
숨겨진 행과 필터링된 행 올바르게 처리
엑셀에서 SUBTOTAL 함수보다 AGGREGATE 함수를 선호합니다. 예시 판매 스프레드시트에서 데이터 세트의 ‘판매 금액’ 열을 필터링할 때(일부 행만 표시하도록 설정) SUM 함수는 숨겨진 행을 포함해 모든 행을 합산합니다. SUBTOTAL 함수는 필터링된 행을 잘 처리하지만 오류를 동시에 무시할 수는 없습니다. AGGREGATE 함수는 두 가지 모두 가능합니다.
판매 데이터를 예로 들면, 행을 필터링한 후 =AGGREGATE(9, 5, F2:F10) 공식을 사용하면 표시된 판매 금액 값만 합산됩니다. 옵션 5는 AGGREGATE가 숨겨진 행을 건너뛰도록 지시합니다. 데이터 범위 내에 오류 값이 흩어져 있는 경우 옵션 7로 전환하면 숨겨진 행과 오류를 한 번에 무시할 수 있습니다.
이는 여러 사람이 서로 다른 필터를 적용하는 공유 워크북에서 특히 중요합니다. 일반 SUM 함수는 필터가 활성화되는 순간 잘못된 합계를 제공하며, 사용자는 이를 인지하지 못할 수도 있습니다. 반면 AGGREGATE는 자동으로 조정되어 결과가 항상 화면에 표시된 내용을 반영합니다.
AGGREGATE는 수직 범위에서만 작동합니다. 수평 범위나 여러 시트에 걸친 3차원 참조는 지원하지 않으므로 데이터 구조를 설계할 때 이 점을 유념하세요.
19가지 다른 함수를 대체할 수 있는 AGGREGATE 함수
AGGREGATE는 단순히 SUM보다 나은 함수가 아닙니다. 19개의 내장 함수를 지원하며, 각 함수에는 번호가 부여됩니다. 1은 AVERAGE, 2는 COUNT, 4는 MAX, 5는 MIN, 9는 SUM 등입니다. 하지만 기본 함수 외에 제가 가장 실용적이라고 생각하는 함수는 LARGE(14), SMALL(15), MEDIAN(12)입니다. 이 함수들은 데이터에 누락이나 오류가 있을 때 특히 오류가 발생하기 쉽습니다.
일부 사람들은 AGGREGATE 함수가 순위 함수를 처리할 수 있다는 사실을 모릅니다. 이것이 바로 11개의 함수만 지원하고 LARGE, SMALL, PERCENTILE을 포함하지 않는 SUBTOTAL 함수와 AGGREGATE 함수의 차이점입니다.
정리되지 않은 데이터에서 최고값과 최저값 찾기
스프레드시트에서 가장 높은 매출액을 찾고 싶다고 가정해 보세요. 일반적으로 =LARGE(F1:F10, 1)을 사용합니다. 하지만 해당 범위 내 단 한 개의 셀이라도 오류가 포함되어 있으면 수식이 실패합니다.
AGGREGATE 함수를 사용하면 수식이 =AGGREGATE(14, 6, F1:F10, 1)로 바뀝니다. 함수 번호 14는 LARGE 함수를 사용하라는 의미이며, 옵션 6은 오류 값을 건너뛰고, 마지막 인수는 순위를 지정합니다. 이 데이터셋에서 수식은 북부 지역의 John Smith's Electronics 항목에서 가장 높은 판매액인 3,750을 반환합니다.
동일한 논리가 SMALL 함수에도 적용됩니다. 최저 판매 금액을 추출하려면 =AGGREGATE(15, 6, F1:F10, 1)을 사용하면 1,800(동부 지역의 마이크 윌슨의 가정용품 판매액)이 반환됩니다. 두 공식 모두 데이터를 미리 정리할 필요가 없습니다.
AGGREGATE 함수가 빛을 발하는 일상적인 상황들
AGGREGATE 함수는 특히 외부에서 가져온 데이터 작업을 할 때 가장 많이 활용합니다. 외부 소스나 CSV 파일에서 가져온 데이터에는 거의 항상 오류가 섞여 있습니다. 따라서 여기저기서 데이터 유형 불일치나 깨진 참조가 발생하죠. 계산 전마다 범위를 정리하는 대신 AGGREGATE 함수를 사용하면 정확한 합계, 평균, 순위를 얻을 수 있습니다.
필터링된 요약 행에서도 유용합니다. 다른 사용자가 자체 필터를 적용할 보고서를 작성할 때, 요약 행에 AGGREGATE 함수를 배치하면 숫자가 항상 표시된 내용과 일치하도록 합니다. 일반 AVERAGE 함수는 그렇게 하지 않습니다. 숨겨진 행까지 포함하여 오해의 소지가 있는 결과를 제공합니다.
19개 함수 번호를 모두 외울 필요는 없습니다. 셀에 =AGGREGATE(를 입력하면 Excel이 지원되는 모든 함수와 해당 번호를 드롭다운 목록으로 표시합니다.
마치며
AGGREGATE 함수 자체만으로도 다양한 기능을 수행하지만, IF, CHOOSE, INDEX 같은 함수와 함께 사용하면 더욱 유용해집니다. 예를 들어, 특정 조건을 충족하는 표시된 행만 합산하려면 다단계 배열 수식이 필요하지만, 조건부 수식 안에 AGGREGATE 함수를 중첩하면 동일한 작업을 수행할 수 있습니다.
또한 AGGREGATE의 배열 형식을 살펴볼 가치가 있습니다. 이 형식은 셀 참조 대신 배열 표현식을 받아들이며 더 고급 필터링을 가능하게 합니다. 데이터 분석에 엑셀을 활용한다면, 이 함수는 많은 복잡한 작업을 단순화해 줍니다.
'Excel' 카테고리의 다른 글
| [파워툴스 업데이트] 현재 파일 삭제 기능 추가 (버전 4.6.0) (0) | 2026.06.30 |
|---|---|
| 엑셀 코파일럿으로 스프레드시트 작업을 더 쉽게 만드는 6가지 방법 (0) | 2026.06.26 |
| [엑셀 바이브 코딩 #3] "바이브 코딩으로 만드는 나만의 엑셀 자동화 도구 — 파워툴스 마스터클래스" 정식 오픈 (0) | 2026.06.22 |
| IF 대신 사용하면 편리한 이 엑셀 함수 (0) | 2026.06.19 |
| 엑셀 체크박스 수동 클릭 대신, 수식을 이용해 자동화하기 (0) | 2026.06.18 |
