데이터 기초 통계 분석도 엑셀에서! AGGREGATE 함수 사용 방법 알아보기

지난 번에 이어 엑셀에서도 단순한 사칙연산 외 데이터 분석을 하기 위한 기초적은 통계를 할 수 있는 함수를 알아보려고 한다.

바로 AGGREGATE 함수!

파이썬 같이 널리 알려진 개발도구에서도 AGGREGATE를 통해 percentile, quantile, median 등 기초 통계 분석을 하는데,

엑셀에서도 마찬가지로 무려 19개 함수와 함께 사용할 수 있다.

오늘은 AGGREGATE 함수 사용방법과 예제를 공유해보겠다.

함께 참고하면 좋은 다른 함수들은 아래 포스팅을 참고하시길!

 

두 범위값들의 상관계수를 엑셀 CORREL 함수로 구하는 방법

엑셀에서도 선형회귀를 활용하여 예측을 할 수 있을까? FORECAST.LINEAR 함수 알아보기

 

엑셀 AGGREGATE 함수

엑셀 AGGREGATE 함수는 19개의 다른 함수들과 함께 데이터를 집계하거나 분석하는데 자주 활용되는 함수이다.

엑셀 자동화를 할 때도 많이 활용하는 함수 중 하나이다.

SUBTOTAL 함수의 상위 호환 함수이며 이 함수보다 더 많은 계산방식을 지원한다.

SUBTOTAL 함수 관련된 글은 아래 참고하시길!

엑셀 필터링하여 화면에 보이는 데이터만 부분합 계산하는 SUBTOTAL 함수 사용 방법

 

=AGGREGATE(사용할 함수, [집계방식], 인수1, [인수2], ...)
=AGGREGATE(function_num, [options], array, [k], ...)

 

• 사용할 함수 : 총 19개 함수 중 어떤 함수로 계산을 할 것인지
• 집계방식 : 데이터를 집계할 방식
• 인수1 : 사용할 함수에 첫 번째 인
• [인수2] : 사용할 함수 중 LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC 이렇게 6개 함수는 인수2가 필요하다.

 

AGGREGATE 함수가 지원하는 19개 함수는 아래와 같다.

사용할 함수 번호	함수	의미
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	k번째 큰 값
15	SMALL	k번째 작은 값
16	PERCENTILE.INC	범위의 k-th 백분위수 (0,1 포함)
17	QUARTILE.INC	범위의 k-th 사분위수 (0,1 포함)
18	PERCENTILE.EXC	범위의 k-th 백분위수 (0,1 제외)
19	QUARTILE.EXC	범위의 k-th 사분위수 (0,1 제외)

 

 

19개 중에 고르는 것도 힘들텐데 다음 인수 역시 집계방식을 선택해야 한다.

집계방식은 총 8개가 있다.

이건 못외울 것 같다ㅋㅋ 내가 자주 쓰는 내용이 아니라면 처음부터 사용하기에는 좀 어려운듯? 하다. 

집계방식 번호	동작 내용
0 or 생략	범위 내 SUBTOTAL, AGGREGATE 함수 무시
1	숨겨진 행, SUBTOTAL, AGGREGATE 함수 무시
2	오류값, SUBTOTAL, AGGREGATE 함수 무시
3	숨겨진 행, 오류값, SUBTOTAL, AGGREGATE 함수 무시
4	모든 값을 포함
5	숨겨진 행을 무시
6	오류값 무시
7	숨겨진 행, 오류값 무시

 

몇 가지 예시를 통해 데이터 기초통계 분석을 직접 해보자!

 

실용 예제

AGGREGATE를 활용한 함수는 인수 경우의 수만 따져도 19 x 8.... 엄청나게 다양한 조합의 분석을 할 수 있다.

이번에는 평균, 최대값, 최소값들 외에 표준편차, 분산, 백분위 등의 함수를 반영해보도록 하겠다.

엑셀 AGGREGATE 함수

 

위 데이터는 메뉴별, 월별 판매량 실적에 대한 데이터이다.

메뉴별로 월별 판매량에 대한 표준편차, 분산, 백분위를 구해보려고 한다.

 

참고로 AGGREGATE 함수는 세로방향을 기준으로 설계가 되어 있어서 인수 1에 해당하는 array는 항상 세로방향으로 범위를 설정해야 한다.

그리고 만약 인수 2가 필요한 6가지 함수에서 인수 2가 누락된 경우에는 #VALUE! 오류를 반환하니 참고할 것!  

 

먼저 첫 번째 표준편차는 7번 STDEV.S를 선택하고 범위 내 모든 값을 포함하는 4번을 입력해주었다.

그 외에는 인수 2가 필수항목이 아니니 인수 1에 데이터 범위만 지정해주면 끝!

 

=AGGREGATE(7, 4, B3:B14)   7은 표본집단의 표준편차 함수인 STDEV.S

 

분산도 항목은 동일하다.

사용할 함수에 분산 함수인 10번을 활용하면 되고 뒤에는 동일하게 모든 값을 포함하는 4번과 범위를 지정해주면 된다.

 

=AGGREGATE(10, 4, B3:B14)  10은 표본집단의 분산 함수인 VAR.S

 

백분위는 PERCENTILE 함수를 써보려고 한다.

예제처럼 25%, 50%, 75% 등 사분위수로 되어 있다면 PERCENTILE 외에 QUARTILE 함수를 써도 무방하다.

다만 뒤에 .INC와 .EXC로 나뉘어져 있는데 INC 는 including, EXC는 excluding을 의미한다.

백분위수의 0%와 100%의 포함 여부를 말한다.

우리가 흔히 계산하는 방법은 PERCENTILE.INC라고 보면 된다!

또한 PERCENTILE 함수는 인수 2가 필요한 6개 함수 중 하나이다.

인수 2에는 몇 퍼센트에 해당하는지를 선택해주면 된다.

75%의 경우에는 0.75, 25%의 경우에는 0.25를 쓰면 된다.

 

=AGGREGATE(16, 4, B3:B14, 0.75)  16은 백분위수 함수인 PERCENTILE.INC

 

중앙값은 median 함수를 활용해도 되고 백분위수 함수에서 퍼센트를 50%로 설정해줘도 된다.

 

=AGGREGATE(16, 4, B3:B14, 0.5)

=AGGREGATE(12, 4, B3:B14)   12는 중앙값 함수인 MEDIAN

 

 

AGGREGATE 함수에서 지원하는 19가지 함수 중 4가지만 예제로 본 것이다.

상황에 따라 다른 함수를 활용할수도 있고, 두 번째 인수인 집계 방식에 대해서도 그대로 사용하면 된다.

첫 번째, 두 번째 인수들은 하나하나 모두 외우기 어렵지만 엑셀에서도 수식을 입력하면 번호에 따른 함수가 나오니 걱정하지 않아도 된다.

아니면 포스팅 내용 참고하면서 함수 설정해줘도 된다!