들어가기 전에
Excel의 AGGREGATE 함수를 사용하면 숨겨진 행, 오류 또는 데이터에 나타나는 다른 함수를 무시하고 계산할 수 있습니다. SUBTOTAL 함수와 유사하지만 더 많은 계산 옵션을 제공하고 계산에서 제외할 항목을 더욱 세부적으로 제어할 수 있습니다. AGGREGATE 함수 기본 구문과 실무 예제, 사용 시 주의할 점 등을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How to Use Excel's AGGREGATE Function to Refine CalculationS
- URL: https://www.howtogeek.com/excel-aggregate-function-refine-calculations/
AGGREGATE 함수 사용 구문
AGGREGATE 함수의 사용 예를 살펴보기 전에 먼저 작동 방식을 살펴보겠습니다. AGGREGATE 함수에는 참조용 구문과 배열용 구문, 두 가지가 있습니다. 하지만 Excel에서 입력하는 인수에 따라 적절한 구문을 자동으로 선택하므로 어떤 구문을 사용할지 고민할 필요는 없습니다. 곧 몇 가지 예를 보여드리면서 두 구문이 어떻게 사용되는지 살펴보겠습니다.
참조 형식 구문
AGGREGATE 함수의 참조 형식 구문은 다음과 같습니다.
=AGGREGATE(a,b,c,d)
- a (필수): 계산에 사용하려는 함수를 나타내는 숫자
- b (필수): 계산에서 무시할 내용을 정의하는 숫자
- c (필수): 함수가 적용될 셀 범위
- d (선택 사항): 추가 범위를 지정하는 최대 252개의 인수 중 첫 번째
배열 형식 구문
반면, 배열을 사용하는 경우 구문은 다음과 같습니다.
=AGGREGATE(a,b,c,d)
- a (필수): 계산에 사용하려는 함수를 나타내는 숫자
- b (필수): 계산에서 무시할 내용을 정의하는 숫자
- c (필수): 함수가 적용될 값의 배열
- d: LARGE, SMALL, PERCENTILE.INC 등의 배열 함수에 필요한 두 번째 인수
함수 및 제외(인수 a 및 b)
위의 구문 형식에 인수 a와 b를 입력할 때 다양한 옵션을 선택할 수 있습니다.
아래 표는 AGGREGATE 계산(인수 a)에서 사용할 수 있는 다양한 함수를 보여줍니다. 함수 이름을 직접 입력하고 싶을 수도 있지만, 이 인수는 사용하려는 함수를 나타내는 숫자여야 한다는 점을 기억하세요. 함수 1~13은 참조 형식 구문과 함께 사용할 수 있으며, 함수 14~19는 배열 형식 구문과 함께 사용할 수 있습니다.
| 숫자 | 함수 | 계산 내용 |
| 1 | AVERAGE | 산술 평균 |
| 2 | COUNT | 숫자 값을 포함하는 셀의 수 |
| 3 | COUNTA | 비어 있지 않은 셀의 개수 |
| 4 | MAX | 가장 큰 값 |
| 5 | MIN | 가장 작은 값 |
| 6 | PRODUCT | 곱셈 |
| 7 | STDEV.S | 단순 표준 편차 |
| 8 | STDEV.P | 인구 기반 표준 편차 |
| 9 | SUM | 추가 |
| 10 | VAR.S | 간단한 변형 |
| 11 | VAR.P | 인구 기반 분산 |
| 12 | MEDIAN | 중간값 |
| 13 | MODE.SNGL | 가장 자주 발생하는 숫자 |
| 14 | LARGE | n 번째로 큰 값 |
| 15 | SMALL | n 번째로 작은 값 |
| 16 | PERCENTILE.INC | 첫 번째 값과 마지막 값이 포함된 n 번째 백분위 수 |
| 17 | QUARTILE.INC | 첫 번째와 마지막 값이 포함된 n 번째 사 분위수 |
| 18 | PERCENTILE.EXC | 첫 번째 값과 마지막 값을 제외한 n 번째 백분위 수 |
| 19 | QUARTILE.EXC | 첫 번째와 마지막 값을 제외한 n 번째 사 분위수 |
다음 표는 AGGREGATE 수식(인수 b) 을 생성할 때 특정 값을 제외하기 위해 입력할 수 있는 숫자를 나열합니다.
| 숫자 | 무시되는 것 |
| 0 | 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 1 | 숨겨진 행 및 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 2 | 오류 및 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 3 | 숨겨진 행, 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 4 | 아무것도 아님 |
| 5 | 숨겨진 행만 |
| 6 | 오류만 |
| 7 | 숨겨진 행 및 오류 |
이제 실제 상황에서 AGGREGATE 함수를 사용하는 몇 가지 예를 살펴보겠습니다.
예제 1: AGGREGATE를 사용하여 오류 무시
이 Excel 스프레드시트에는 축구 선수 목록, 경기 수, 득점 수, 그리고 경기당 골 비율이 포함되어 있습니다. 모든 선수의 평균 경기당 골 비율을 계산하는 것이 목표입니다.
AVERAGE 함수만 사용하려면 다음 수식을 입력합니다.
=AVERAGE(Player_Goals[Games per goal])
C1 셀에 입력하면 참조된 범위에 #DIV/0! 오류가 포함되어 있으므로 오류가 반환됩니다.
AGGREGATE 함수를 사용하면 이러한 오류를 무시하고 나머지 데이터의 평균을 반환할 수 있습니다. C2 셀에 다음 수식을 입력합니다.
=AGGREGATE(1,6,Player_Goals[Games per goal])
- 1 (인수 a ): AVERAGE 함수
- 6 (인수 b ): Excel에서 오류를 무시하도록 지시
- Player_Goals[골당 경기 수]: 참조 영역
IFERROR 함수를 사용하여 오류를 빈 값으로 바꾸는 방법도 있습니다.
예제 2: AGGREGATE를 사용하여 숨겨진 행 무시(참조)
같은 스프레드시트를 사용하여 다음 목표는 팀이 넣은 총 골 수를 계산하는 것입니다. 구체적으로, 두 가지 합계를 표시하려고 합니다. 첫 번째는 모든 선수의 득점을 합산한 전체 합계이고, 두 번째는 필터를 적용한 후 표에 표시된 선수의 득점만 합산한 합계입니다.
전체 합계를 계산하려면 C1 셀에 다음을 입력합니다.
=SUM(Player_Goals[Goals scored])
15개 이상의 게임을 플레이한 플레이어만 표시하는 등의 필터를 열 중 하나에 적용한 후에도 방금 적용한 SUM 수식에는 필터링된 행이 여전히 포함됩니다.
바로 이 부분에서 AGGREATE 함수가 도움이 됩니다. 필터링된 행을 계산에서 제외할 수 있기 때문입니다. 실제로 AGGREGATE 함수는 행 헤더를 우클릭하고 [숨기기]를 클릭하여 숨긴 행을 무시하려는 경우에도 사용할 수 있습니다. C2에 다음을 입력합니다.
=AGGREGATE(9,5,Player_Goals[Goals scored])
- 9 (인수 a): SUM 함수
- 5 (인수 b): 숨겨진 행을 무시하도록 지시
- Player_Goals[Goals scored]: 참조 영역
이제 이 수식의 결과가 셀 C1에 사용한 SUM 수식의 결과와 다르다는 점에 유의하세요. 왜냐하면 이 수식은 표시된 행만 고려하기 때문입니다.
예제 3: AGGREGATE를 사용하여 숨겨진 행(배열) 무시
20경기 이하를 뛰었던 선수들의 가장 높은 골 수 두 개를 나열하고 싶다고 가정해 보겠습니다. 필터를 적용한 다음 수식을 생성할 수도 있지만, 이 데모의 목적을 위해 먼저 수식을 만들어 보겠습니다. C1 셀에 다음 수식을 입력합니다.
=AGGREGATE(14,5,Player_Goals[Goals scored],{1;2})
- 14 (인수 a): LARGE 함수
- 5 (인수 b): 숨겨진 행을 무시하도록 지시
- Player_Goals[Goals scored]: 값의 배열
- {1;2}: 가장 큰 값(1)과 두 번째로 큰 값(2)을 별도의 행(;)으로 반환하도록 지시
Enter 키를 누르면 Excel에서 상위 두 값을 반환하도록 설정했기 때문에 C1과 C2 셀을 덮는 펼쳐진 배열이 결과로 나오는 것을 볼 수 있습니다.
이제 플레이한 게임 열을 필터링하여 20개 이하의 게임을 플레이한 플레이어만 포함시키고, 앞서 입력한 AGGREGATE 수식의 결과가 숨겨진 행을 무시하도록 변경되는지 확인합니다.
AGGREGATE 함수 사용 시 주의 사항
Excel 통합 문서에서 AGGREGATE 함수를 사용하기 전에 다음 사항을 꼭 확인하세요.
- AGGREGATE 함수는 세로 범위에만 적용되며 가로 범위에는 적용되지 않습니다. 따라서 가로 범위를 참조할 때 AGGREGATE 함수는 숨겨진 열의 행을 무시하지 않습니다.
- AGGREGATE 수식의 인수 c는 여러 워크시트에 걸쳐 있는 동일한 셀이나 셀 범위일 수 없습니다(3D 참조라고도 함).
- AGGREGATE 함수는 계산 오류를 우회하는 좋은 방법이지만, 오류를 완전히 무시하는 습관은 들이지 마세요. 오류는 존재하는 데에는 이유가 있으며, 데이터 문제를 해결하는 데 도움이 될 수 있습니다.
- AGGREGATE 함수의 배열 형식은 배열 인수에 계산이 포함된 경우 숨겨진 행, 중첩된 소계 또는 중첩된 집계를 무시하지 않습니다.
마치며
Excel 표의 행을 숨겨서 AGGREGATE 함수에 표시되는 내용만 포함되도록 하는 또 다른 방법은 슬라이서를 삽입하는 것입니다 . 슬라이서는 필터링을 훨씬 더 간편하게 만들기 위해 클릭할 수 있는 대화형 버튼입니다. 이와 관련해서는 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| 완벽한 대시보드를 만드는데 도움이 될 Excel 팁 (4) | 2025.07.21 |
|---|---|
| Excel에서 표 이름을 바꾸지 않았다면 오늘부터 시작하세요 (1) | 2025.07.19 |
| Excel의 조건부 서식으로 정신이 번쩍 들었던 6가지 사례 (1) | 2025.07.17 |
| Excel의 PIVOTBY 함수를 사용하는 방법 (3) | 2025.07.14 |
| Excel에서 유출된 배열의 서식을 지정하는 방법 (0) | 2025.07.13 |
