엑셀 AGGREGATE 함수는 숨겨진 행이나 오류를 제외하고 현재 보이는 범위의 데이터를 집계하는 함수입니다.
=AGGREGATE(사용할함수, [집계방식], 인수1, [인수2], ...)
- 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 (백분위수-포함) / 17 = QUARTILE.INC (사분위수-포함)
- 18 = PERCENTILE.EXC (백분위수-제외) / 19 = QUARTILE.EXC (사분위수-제외)
- 0 = SUBTOTAL/AGGREGATE 함수 무시 [기본값]
- 1 = 숨겨진 행, SUBTOTAL/AGGREGATE 무시
- 2 = 오류 값, SUBTOTAL/AGGREGATE 무시
- 3 = 숨겨진 행 및 오류 값, SUBTOTAL/AGGREGATE 무시
- 4 = 모든 값 포함
- 5 = 숨겨진 행 무시
- 6 = 오류 값 무시
- 7 = 숨겨진 행과 오류 값 무시 (현업에서는 7 옵션을 주로 사용합니다.)
- LARGE(k번째로 큰)
- SMALL(k번째로 작은)
- PERCENTILE(백분위수)
- QUARTILE(n번째 사분위수)
엑셀 AGGREGATE 함수는 범위의 숨겨진 행이나 오류를 무시하고 현재 보이는 범위에서 데이터를 집계하는 다목적 계산 함수입니다. 기존 SUBTOTAL 함수의 기능을 확장한 상위호환 함수로, 총 19가지의 계산방식을 지원합니다.
=AGGREGATE(9, 7, A1:A10) / / A1:A10 범위에서 숨겨진 행과 오류를 제외한 합계를 계산합니다.
AGRREGATE 함수는 일반적인 통계(SUM, AVERAGE 등)과 특수 계산(PERCENTILE, QUARTILE 등)을 포함하고 있어 복잡한 데이터 분석 및 자동화 보고서 제작에 폭넓게 활용할 수 있습니다.
다음과 같이 함수를 작성하면 영업팀 데이터 범위에 오류가 있는 경우, 오류 값을 무시한 최대 매출액을 찾을 수 있습니다.
=AGGREGATE(4, 6, B2:B20) / / B2:B20 범위에서 오류 값을 무시하고 최대값을 찾습니다. 사용할함수 4는 MAX, 집계방식 6은 오류값을 무시하는 옵션입니다.
다음과 같이 함수를 작성하면 부서별로 필터링 된 직원 급여 데이터 중, 상위 3명의 급여 평균을 계산할 수 있습니다.
=AVERAGE(AGGREGATE(14, 5, C2:C50, {1,2,3})) / / C2:C20 데이터에서 숨겨진 행은 무시하고 1,2,3번째 큰 값을 구한 후, 그 결과의 평균을 계산합니다.
AGGREGATE 함수는 세로 방향 데이터에 최적화되어 있습니다. 따라서, 가로 방향의 데이터에서는 '숨겨진 행 무시' 옵션이 제대로 작동하지 않을 수 있습니다.
사용할 함수로 LARGE, SMALL, PERCENTILE, QUARTILE를 사용할 경우, 두 번째 인수를 반드시 입력해야 합니다. 그렇지 않으면 #VALUE! 오류가 발생합니다.
집계방식 인수는 기본값으로 0(중첩된 SUBTOTAL과 AGGREGATE 함수만 무시)이 사용됩니다.
AGGREGATE 함수는 3D 참조(여러 시트 범위 참조)를 지원하지 않습니다. (예: Sheet1:Sheet10!A1:A10 사용 불가)
