Aggregate(옵션 적용 집계) 함수(1) - 구문, 함수, 오류 값

1. 의미

목록이나 데이터베이스에서 Sum, Average, Max, Large 등 여러 가지 집계 함수를 적용할 때 숨겨진 행, 오류 값 무시 등의 옵션을 제공하는 함수입니다.

 

2. 구문

=ag까지 입력하면 Aggregate 함수가 표시되므로 탭키를 누릅니다.

그러면 1 - Average 등 숫자와 함수명이 표시되는데

이때 =Aggregate( 다음을 마우스로 클릭하면
인수 입력 방식이 보통은 하나인데, 이 함수는 두 가지가 표시됩니다.
위 쪽이 배열형이고, 아래는 참조형입니다.

참조형과 배열형의 구문은 아래와 같이 다릅니다.

 

가. 참조형
AGGREGATE(function_num, options, ref1, [ref2], …)

참조형이 적용되는 함수는 SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, VAR.S, VAR.P, MEDIAN, MODE.SNGL입니다.

 

나. 배열형
AGGREGATE(function_num, options, array, [k])

네 번째 인수가 [k]로 대괄호 사이에 있어 옵션인 것처럼 보이지만 입력하지 않으면 #VALUE! 에러가 발생합니다.

배열형이 적용되는 함수는 LARGE, SMALL, PERCENTILE.INC(, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC입니다.

 

다. 참조형과 배열형 비교

참조형과 배열형은 적용되는 함수가 다르고, 배열형은 세 번째 인수가 array이고, 네 번째 인수가 순번을 나타내는 k인 점이 참조형과 다릅니다.

참조형은 ref1, ref2가 반복될 수 있는데, ref는 (단일) 셀 주소, 이름, 범위(여러 개의 셀 주소) 등이 될 수 있고,
배열형의 경우 array는 배열, 배열 수식 또는 셀 범위에 대한 참조(다시 말해 범위 또는 이름)가 될 수 있습니다.

 

라. 첫 번째 인수 function_num

위 캡처 화면에서는 번호가 12번까지만 보였는데 모두 나열하면 아래와 같습니다.

function_num	함수
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
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

 

마. 두 번째 인수 options

함수를 선택하면 해당하는 번호가 입력되며, 아래 화면에서는 평균에 해당하는 1을 입력했는데, 1 다음에 쉼표(,)를 입력하면 아래 화면과 같이 옵션이 표시되고 마찬가지로 숫자가 왼쪽에 표시됩니다.

위 화면을 정리하면 아래와 같습니다.

Options	의미
0 또는 생략	중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
1	숨겨진 행, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
2	오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
3	숨겨진 행, 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
4	아무것도 무시 안 함
5	숨겨진 행 무시
6	오류 값 무시
7	숨겨진 행 및 오류 값 무시

옵션에 중복되는 요소들이 있으므로 중복을 제거하면 아래와 같이 4가지가 남습니다.

Aggregate1함수.xlsx

0.01MB

 

 

① 중첩된 Subtotal 함수
데이터 탭 > 개요 그룹 > 부분합을 클릭하고 그룹화할 항목으로 지점명을 선택하고, 확인 버튼을 클릭하면

지점명 별로 소계, 그리고 총합계가 구해지는데,

D21셀을 클릭한 후 Aggregate함수 사용 시 합계를 구하기 위해 첫 번째 인수로 9를 입력하고, subtotal은 빼고 계산하기 위해 두 번째 옵션 인수로 0을 입력하고, 세 번째 인수로 범위 D2:D20을 마우스로 끌어서 지정합니다.
완성된 수식은 =AGGREGATE(9,0,D2:D20)입니다.

Subtotal 함수나 Aggregate 함수 모두 합계를 구할 때는 첫 번째 인수로 9를 사용합니다.

② 중첩된 Aggregate 함수
이번에는 D21셀의 Aggregate 함수를 무시하는 옵션을 사용해 보겠습니다.

D22셀에 =AGGREGATE(9,0,D2:D21)라고 입력하면 D21셀의 Aggregate함수와 D9셀 등의 Subtotal함수를 무시하고 합계를 구하기 때문에 판매금액 합계의 네 배가 되지 않고 순수한 판매금액만의 합계를 구해줍니다.

다시 말해 D2셀에서 D21셀까지 마우스로 끌어보면 상태 표시줄에 순수 판매금액의 합계 8,791,000의 4배인 35,164,000이 표시됩니다. Sum함수는 Subtotal함수와 Aggregate 함수를 모두 포함해서 계산하는 것을 알 수 있습니다.

③ 숨겨진 행
데이터 탭 > 개요 그룹 > 부분합을 클릭한 후 맨 아래 왼쪽의 모두 제거 버튼을 눌러 부분합을 제거하고, 17행과 18행의 Aggregate 함수도 지웁니다.

그리고, 9행을 클릭한 후 마우스 오른쪽 버튼을 누르고 숨기기를 클릭합니다.

그런 다음, D17셀에 커서를 놓고 홈 탭 > 편집 그룹 > 자동 합계를 클릭하면

=SUM(D2:D16)라고 수식이 자동으로 입력되는데 엔터 키를 누르면

9행이 숨기기 되어 있어도 9행을 포함해서 합계를 구합니다.

그러나, Aggregate 함수 사용 시 옵션 1을 사용하면 숨겨진 행을 무시하기 때문에 숨겨진 행을 제외하고 합계를 구해줍니다.

숨겨진 행 무시가 1뿐만 아니라 3, 5, 7에도 있기 때문에 이 중 어떤 것을 사용해도 결과는 같습니다.

④ 오류 값
숨기기를 취소하고, D13셀에 =1/0이라고 입력하면 0으로 나눌 수 없기 때문에 #DIV/0 에러가 발생합니다.
이때 Subtotal 함수로 합계를 구하는데 더할 범위를 D2:D16으로 지정하면 D13셀이 #DIV/0이기 때문에 결괏값도 #DIV/0라고 나옵니다.

그러나, Aggregate 함수를 사용하면서 에러 값을 무시하는 옵션 2, 3, 6, 7중 하나를 사용하면 오류값을 제외하고 합계를 구하기 때문에 결괏값이 8630000이라고 정확히 표시됩니다.

바. 범위는 세로로 지정되어야 함

Aggregate 함수는 데이터 열 또는 세로 범위에 대해 설계되고, 데이터 행 또는 가로 범위에 대해 설계되지 않았으므로
AGGREGATE(1, 1, ref1)와 같이 옵션 1을 사용하더라도 가로 범위를 소환하면 숨겨진 행을 무시하지 않기 때문에 당초 값에 변화가 없지만(포함), 세로 범위에서 행을 숨기면 집계에 영향을 미쳐 당초 값과 다른 값이 구해집니다(제외).

예를 들어 아래와 같은 예에서
행 또는 열을 숨기기 이전에는 가로나 세로의 평균이 같은데(첫 번째 인수 1은 평균),

H열과 6행을 숨기면 J2셀의 값은 범위가 가로 방향이기 때문에 변화가 없는데(포함), G8셀의 수식은 범위가 세로 방향이기 때문에 숨겨진 행을 제외하고 평균을 계산해서 값이 달라집니다.

Aggregate1(완성).xlsx

0.01MB