[기타 함수] 엑셀 AGGREGATE 하나의 함수로 여러 계산 처리

엑셀 AGGREGATE 하나의 함수로 여러개의 엑셀 기본적 함수를 처리할 수 있다. 

AGGREGATE 함수가 사용자가 지정하는 함수로 연산을 처리하여 결과를 반환하기 때문이다. 

또, 숨겨진 행이나 오류 값, 중첩 소계 함수의 처리 여부를 결정할 수 있다.

이 함수는 참조와 배열을 사용하는 두 가지 형식으로 사용될 수 있다.

엑셀 AGGREGATE 함수 기본 설명

설명 : 여러 개의 엑셀 기본 함수를 사용자 지정하여, 1개 이상의 숫자 값을 처리하는 함수이다. 참조와 배열이 다른 구문으로 사용된다.

구문(참조) : AGGREGATE( function_num, options, ref1, [ref2], ... )

인수 :

function_num : 1 에서 19 사이의 숫자, 실행할 다른 함수를 의미

options : 0 에서 7 사이의 숫자, 계산에서 무시할 값을 정의

ref1, ref2, ... : 함수에서 처리할 1개 이상의 숫자 값들, 253 개까지 적용가능

구문(배열) : AGGREGATE( function_num, options, array, [k] )

인수 : 

function_num 과 options : 참조 구문과 동일

array : 함수에서 처리할 값들을 배열로 전달

k : 추가 인수가 필요한 함수에 사용되는 값

노트1 : 지정 함수 중에는 추가 인수가 필요한 함수가 있다. LARGE, SMALL, PERCENTILE, QUARTILE 등의 함수이다. 

이 함수들이 지정될 때는 ref2 인수가 k 로 적용된다.

참고1 : 옵션 > 수식 의 '수식 자동완성' 을 활성화하면, 수식 입력시 function_num 리스트를 표시한다.

[그림1] AGGREGATE 함수 function_num 함수 목록

참고2 : options 인수로 숨기기된 행, 에러 값, 중첩 소계 함수(AGGREGATE, SUBTOTAL) 무시 여부를 지정한다.

[그림2] AGGREGATE 함수 options 무시 옵션 목록

엑셀 AGGREGATE 함수 사용 예제

간단한 예제로 AGGREGATE 함수를 사용해 보자.

- 기본 처리

사용 예제1 : =AGGREGATE(9, 4, G6:G9)

사용 결과1 : 56

첫번째 인수인 function_num 이 9 이다. 이는 SUM 함수를 지정하는 숫자이다.

두번째 인수인 options 은 4 이다. 이는 '모든 무시 안 함' 을 의미이다.

세번째 인수로 ref 나 array 가 주어진다. 이 범위나 배열의 값들이 첫번째 지정 함수로 계산되는 것이다.

G6:G9 범위의 숫자를 SUM 함수로 합계를 구하는 것이다. 결과 56이 반환된다.

관련 함수 : [엑셀 모바일] SUM 함수로 인수의 합계 구하기

- 추가 인수 k 처리

사용 예제2 : =AGGREGATE(14, 4, G6:G9, 2)

사용 결과2 : 12.5

function_num 가 14 이다. 이는 LARGE 함수 사용한다는 것이다.

options 이 4로 지정되어 무시 없이 모든 처리를 수행한다.

array 로 G6:G9 의 범위가 지정되었다. 각 셀 값이 배열로 작성되어 함수로 전달된다.

k 인수로 2가 전달된다. 추가 인수가 필요할 때 k 인수가 사용된다. 

LARGE, SMALL, PERCENTILE, QUARTILE 등의 함수를 이용할 때는 k 인수가 필요하다.

4번째 인수인 k 와 ref2 의 위치는 같지만, 지정되는 함수에 따라 달리 처리된다.

지정된 LARGE 는 G6:G9 에서 2번째로 큰 값인 12.5 를 결과로 반환한다.

관련 함수 : [엑셀 모바일] LARGE 함수로 k 번째로 큰 값 구하기

[그림3] 엑셀 19개의 함수를 포함하는 AGGREGATE

- 숨기기 무시, ref 와 k

사용 예제3 : =AGGREGATE(1, 5, G6:G7, G8:G9)

사용 결과3 : 14

1 로 지정되어 AVERAGE 함수로 계산된다.

5 로 옵션 지정되어 숨기기 된 행들은 처리에서 무시된다. 만일, 6행이 숨기기 상태라면, G6 은 계산에서 무시된다. [그림2 참고]

ref1 로 G6:G7 이 참조되고, ref2 로 G8:G9 가 참조 되었다. 이 두 범위가 계산에 사용된다.

추가 인수로 사용되는 k 인수가 없다면, 이렇게 여러개의 범위를 참조하여 처리할 수 있다.

관련 함수 : [엑셀 모바일] AVERAGE 함수는 주어진 인수의 평균을 구한다

관련 문제 : [필기 풀이] 컴활2급 기출문제 2006년1회-31번 엑셀 숨기기 취소

- 오류 무시

사용 예제4 : =AGGREGATE(3, 6, G6:G9)

사용 결과4 : 4

function_num 인수로 3 이 전달되어 COUNTA 함수가 사용자 지정된다.

options 인수로 6 은 '오류 값 무시' 를 말한다. 오류는 계산에서 제외된다.

세번째 인수로 지정된 범위가 COUNTA 함수로 처리되어, 모든 비공백 셀의 갯수 4 가 결과 반환된다.

만일, G9 셀의 값이 30 이 아니라 #N/A 였다면, options 인수 6 에 의해 오류값이 무시되어 결과는 3 이 될 것이다.

관련 함수 : [엑셀 모바일] COUNTA 함수로 비공백 셀 카운트하기

엑셀 AGGREGATE 함수 주요 오류

엑셀 AGGREGATE 함수의 대부분의 오류는 #VALUE! 이다.

#VALUE! 오류 발생

- function_num 인수가 1 ~ 19 사이가 아니라면

- options 인수가 0 ~ 7 사이의 숫자가 아니라면

- function_num 인수가 14 ~ 19 인데, k 값이 없다면

- 전달된 모든 인수가 숫자가 아니라면

사용 예제3 은 refrence 형식을 사용하였고, 나머지 예제들은 array 형식을 사용한다. 

추가 인수 k가 사용된 예제2 를 잊지 말자.

이 AGGREGATE 함수는 엑셀 2010 버전부터 사용되었다. 

이전 버전을 사용중이라면 비슷한 기능을 하는 엑셀 소계 함수로 SUBTOTAL 함수가 있다는 것도 알아두자.

관련 링크 : 

컴활 출제는 NO, 유용함은 YES 엑셀 함수 목록

[기타 함수] 엑셀 SUBTOTAL 함수로 부분합 계산의 기본 배우기

마늘빵.