엑셀 함수 > 수학 및 삼각 함수 > AGGREGATE 함수 - 목록,데이터베이스 집계

AGGREGATE 함수 - 목록,데이터베이스 집계

엑셀의 AGGREGATE 함수는 목록이나 데이터베이스의 집계 결과를 구해줍니다. AGGREGATE 함수는 SUBTOTAL 함수를 대체할 수 있는 함수로 엑셀 2010 이상 버전부터 사용가능합니다.

SUBTOTAL 함수와 비교해보면 값에 오류가 있으면 제외할 수 있고 처리할 수 있는 함수가 8개(MEDIAN, LARGE, PERCENTILE.INC 등)가 추가되어서 총 19개의 함수를 지원합니다.

 

구문(Syntax)

AGGREGATE

참조형
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함수를 이용하여 집계 결과를 구해보겠습니다.

AGGREGATE 함수로 숨겨진 값을 포함하여 집계 결과 구하기

숨겨진 행을 모두 포함하여 합계를 구하려면 [C16] 셀에 다음 수식을 입력합니다.

=AGGREGATE(9,4,$F$5:$F$13)

 

AGGREGATE 함수로 숨겨진 값을 포함하여 집계 결과 구하기

판매금액 범위 [$F$5:$F$13]에서 숨겨진 행을 모두 포함한 합계를 148,800원이 구해졌습니다.

수식 풀이:

AGGREGATE 함수로 숨겨진 값을 포함하여 집계 결과 구하기 수식 풀이

집계함수 종류 : 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값이 필요합니다.

AGGREGATE 함수로 숨겨진 값을 포함하여 집계 결과 구하기 수식 풀이

 

AGGREGATE 함수로 숨겨진 값을 포함하여 집계 결과 구하기 최종 결과

 

 

2) 숨겨진 행 무시(제외)하고 집계 결과 구하기

이번에는 두 번째 인수를 '숨겨진 행 무시(제외)'하도록 지정하여 숨겨진 행을 집계 결과에서 제외시켜 보겠습니다.
이 기능은 행을 '숨기기',  '숨기기 취소' 하면서 집계 결과를 비교해 보는 장점이 있습니다.

아래 범위에서 8~10행을 행 숨기기(제외)하고 집계 결과를 구해보겠습니다.

AGGREGATE 함수로 숨겨진 행 무시(제외)하고 집계 결과 구하기 - 행 숨기기

 

행을 숨긴 후, [C22] 셀에 다음 수식을 입력해서 합계를 구합니다.

AGGREGATE 함수로 숨겨진 행 무시(제외)하고 집계 결과 구하기 - 수식 입력

=AGGREGATE(9,5,$F$5:$F$13)

집계함수 종류 : 9 - 합계 구하기
집계 무시(제외) 방식 : 5 - 숨겨진 행 무시(제외)

 

AGGREGATE 함수로 숨겨진 행 무시(제외)하고 집계 결과 구하기 - 결과

숨겨진 행을 무시(제외)한 합계 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)

 

다음과 같이 숨겨진 행을 무시(제외)한 결과가 구해졌습니다(빨간 점선 박스 내).

AGGREGATE 함수로 숨겨진 행 무시(제외)하고 집계 결과 구하기 - 최종 결과

 

 

3) 오류값 무시하고 집계 결과 구하기

아래와 같이 판매금액에 오류가 포함되어 있을 때 SUM, MAX, SUBTOTAL 함수 등으로 결과를 구하면 #VALUE 오류가 발생합니다. 이런 경우 AGGREGATE 함수를 이용하면 오류를 제외하고 집계 결과를 구할 수 있습니다.

AGGREGATE 함수로 오류값 무시하고 집계 결과 구하기 - 수식 입력

[C16] 셀에 다음 수식을 입력하여 오류를 무시(제외)한 합계를 구합니다.

=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)

 

다음과 같이 오류 값을 무시(제외)한 결과가 구해졌습니다.

AGGREGATE 함수로 오류값 무시하고 집계 결과 구하기 - 최종 결과

 

 


관련 글

SUBTOTAL함수 집계 결과 구하기 SUBTOTAL 함수 - 부분합 구하기 - 엑셀의 SUBTOTAL 함수는 목록이나 데이터베이스의 부분합(집계 결과)을 구해줍니다. 이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등…

SUBTOTAL 함수 - 부분합 구하기 더 보기 »

LARGE 함수 사용법 LARGE 함수 - 몇 번째로 큰 값 구하기 - 엑셀 LARGE 함수는 배열이나 범위에서 몇 번째로 큰 값을 구해줍니다. 가장 큰 값을 구하거나 2번째 또는 3번째 등 몇 번째로…

LARGE 함수 - 몇 번째로 큰 값 구하기 더 보기 »

SMALL 함수 사용법 SMALL 함수 - 몇 번째로 작은 값 구하기 - 엑셀 SMALL 함수는 배열이나 범위에서 몇 번째로 작은 값을 구해줍니다. 가장 작은 값을 구하거나 2번째 또는 3번째 등 몇 번째로…

SMALL 함수 - 몇 번째로 작은 값 구하기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top