엑셀의 AGGREGATE 함수는 목록이나 데이터베이스의 집계 결과를 구해줍니다. AGGREGATE 함수는 SUBTOTAL 함수를 대체할 수 있는 함수로 엑셀 2010 이상 버전부터 사용가능합니다.
SUBTOTAL 함수와 비교해보면 값에 오류가 있으면 제외할 수 있고 처리할 수 있는 함수가 8개(MEDIAN, LARGE, PERCENTILE.INC 등)가 추가되어서 총 19개의 함수를 지원합니다.
구문(Syntax)
참조형
AGGREGATE(function_num, options, ref1, [ref2], …)
배열형
AGGREGATE(function_num, options, array, [k])
목록이나 데이터베이스의 집계 결과를 구해준다.
인수 :
- function_num : 집계 함수 번호
num 집계함수 종류 num 집계함수 종류 1 AVERAGE 11 VAR.P 2 COUNT 12 MEDIAN 3 COUNTA 13 MODE.SNGL 4 MAX 14 LARGE 5 MIN 15 SMALL 6 PRODUCT 16 PERCENTILE.INC 7 STDEV.S 17 QUARTILE.INC 8 STDEV.P 18 PERCENTILE.EXC 9 SUM 19 QUARTILE.EXC 10 VAR.S - options : 집계 무시(제외) 방식
option 동작 0 또는 생략 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시 1 숨겨진 행, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시 2 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시 3 숨겨진 행, 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시 4 아무것도 무시 안 함(모두 포함) 5 숨겨진 행 무시 6 오류 값 무시 7 숨겨진 행 및 오류 값 무시 - ref1 : 집계 값을 구할 인수가 여러 개인 함수의 첫 번째 인수
- ref2,… : 일부 함수의 두 번째 인수(LAGE, SMALL등)
사용 예
1) 숨겨진 값을 포함하여 집계 결과 구하기
다음과 같은 '판매실적' 자료가 있을 때 AGGREGATE함수를 이용하여 집계 결과를 구해보겠습니다.
숨겨진 행을 모두 포함하여 합계를 구하려면 [C16] 셀에 다음 수식을 입력합니다.
=AGGREGATE(9,4,$F$5:$F$13)
판매금액 범위 [$F$5:$F$13]에서 숨겨진 행을 모두 포함한 합계를 148,800원이 구해졌습니다.
수식 풀이:
집계함수 종류 : 9 - 합계 구하기
집계 무시(제외) 방식 : 4 - 아무 것도 무시 안함(모두 포함)
(위의 예에서는 숨겨진 행이 없음, options가 4이면 숨김 여부에 관계 없이 결과는 동일함)
합계 외 평균 등 다른 함수의 집계 결과도 구해보겠습니다.
=AGGREGATE(1,4,$F$5:$F$13) (1 - AVERAGE)
=AGGREGATE(4,4,$F$5:$F$13) (4 - MAX)
=AGGREGATE(14,4,$F$5:$F$13,2) (14 - LARGE)
function_num이 14이면 LARGE 함수를 사용하므로 다음과 같이 네 번째 인수 k값이 필요합니다.
2) 숨겨진 행 무시(제외)하고 집계 결과 구하기
이번에는 두 번째 인수를 '숨겨진 행 무시(제외)'하도록 지정하여 숨겨진 행을 집계 결과에서 제외시켜 보겠습니다.
이 기능은 행을 '숨기기', '숨기기 취소' 하면서 집계 결과를 비교해 보는 장점이 있습니다.
아래 범위에서 8~10행을 행 숨기기(제외)하고 집계 결과를 구해보겠습니다.
행을 숨긴 후, [C22] 셀에 다음 수식을 입력해서 합계를 구합니다.
=AGGREGATE(9,5,$F$5:$F$13)
집계함수 종류 : 9 - 합계 구하기
집계 무시(제외) 방식 : 5 - 숨겨진 행 무시(제외)
숨겨진 행을 무시(제외)한 합계 86,400원이 구해졌습니다.
합계 외 평균 등 다른 함수의 집계 결과도 구해보겠습니다.
=AGGREGATE(1,5,$F$5:$F$13) (1 - AVERAGE)
=AGGREGATE(4,5,$F$5:$F$13) (4 - MAX)
=AGGREGATE(14,5,$F$5:$F$13,2) (14 - LARGE)
다음과 같이 숨겨진 행을 무시(제외)한 결과가 구해졌습니다(빨간 점선 박스 내).
3) 오류값 무시하고 집계 결과 구하기
아래와 같이 판매금액에 오류가 포함되어 있을 때 SUM, MAX, SUBTOTAL 함수 등으로 결과를 구하면 #VALUE 오류가 발생합니다. 이런 경우 AGGREGATE 함수를 이용하면 오류를 제외하고 집계 결과를 구할 수 있습니다.
=AGGREGATE(9,6,$F$5:$F$13)
집계함수 종류 : 9 - 합계 구하기
집계 무시(제외) 방식 : 6 - 오류 값 무시(제외)
합계 외 다른 함수의 집계 결과도 두 번째 인수로 6을 입력하면 됩니다.
=AGGREGATE(1,6,$F$5:$F$13) (1 - AVERAGE)
=AGGREGATE(4,6,$F$5:$F$13) (4 - MAX)
=AGGREGATE(14,6,$F$5:$F$13,2) (14 - LARGE)
다음과 같이 오류 값을 무시(제외)한 결과가 구해졌습니다.
관련 글
SUBTOTAL 함수 - 부분합 구하기 - 엑셀의 SUBTOTAL 함수는 목록이나 데이터베이스의 부분합(집계 결과)을 구해줍니다. 이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등… …
LARGE 함수 - 몇 번째로 큰 값 구하기 - 엑셀 LARGE 함수는 배열이나 범위에서 몇 번째로 큰 값을 구해줍니다. 가장 큰 값을 구하거나 2번째 또는 3번째 등 몇 번째로… …
SMALL 함수 - 몇 번째로 작은 값 구하기 - 엑셀 SMALL 함수는 배열이나 범위에서 몇 번째로 작은 값을 구해줍니다. 가장 작은 값을 구하거나 2번째 또는 3번째 등 몇 번째로… …
