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

엑셀 SUBTOTAL 함수는 사용자가 인수로 지정한 계산(합, 곱, 평균 등)을 수행한다.

지정 숫자 인수가 달라지면 다른 함수로 계산이 이루어지는 것이다.

이 SUBTOTAL 함수를 테이블(표)의 필드(열)에 빠르고 쉽게 적용할 수 있게 한 기능이 부분합이다.

보다 정확히는 부분합은 외형을 담당하는 엑셀 그룹 기능과 계산 처리를 위한 SUBTOTAL 함수의 결합이다.

또, SUBTOTAL 함수는 요전에 배웠던 또 다른 소계 함수인 AGGREGATE 의 원형이라 할 수 있다.

엑셀 2010 이전 버전을 사용하고 있다면 SUBTOTAL 함수밖에 사용할 수 없다.

관련 문제 : 

[필기 풀이] 컴활2급 기출문제 2016년2회-22번 엑셀 부분합 설정

[엑셀 기초] 그룹과 자동 윤곽의 설정과 해제 - 부분합

보다 쉬운 SUBTOTAL 함수를 익힌다면, AGGREGATE 함수는 쉽게 이해될 것이다.

엑셀 SUBTOTAL 함수 기본 설명

설명 : function_num 인수로 전달된 숫자로 사용 함수를 선택하고, ref 인수로 범위를 참조하여 계산한다.

구문 : SUBTOTAL( function_num, ref1, [ref2], ... )

인수 :

function_num : 계산에 사용되는 함수를 지정하는 정수. 1 ~ 11 , 101 ~ 111

ref1, ref2 : 계산에 사용할 값을 포함하는 셀 참조.

노트1 : ref 인수에서 공백과 숫자가 아닌 셀은 무시되고 계산된다.

참고1 : function_num 옵션으로 사용가능한 함수는 총 11 개이다.

그러나, '숨기기' 된 셀을 계산에 포함할지, 무시할지에 따라 동일 함수 목록이 한번 더 사용된다.

[그림1] SUBTOTAL 함수 function_num 인수 목록

101 ~ 111 번으로 참조한 함수는 셀 범위 중 '숨기기' 된 셀은 계산에서 제외한다.

AGGREGATE 함수가 옵션 인수로 숨겨진 셀의 처리 방법을 전달하는 것과는 다른점이다.

참고2 : 행 단위 숨기기만에 함수가 영향을 미친다. 열 단위 숨기기는 함수의 계산과 아무런 관련이 없다.

참고3 : 자동필터로 필터링된 데이터는 무시/포함, 어느 함수 목록을 사용하던 계산에 포함되지 않는다.

관련 링크 : [필기 풀이] 컴활2급 기출문제 2016년2회-21번 엑셀 자동 필터

엑셀 SUBTOTAL 함수 사용 예제

간단한 예제로 SUBTOTAL 함수의 처리 방법을 느껴보자.

- 기본 처리

사용 예제1 : =SUBTOTAL( 1, G5:G8 )

사용 결과1 : 17.625

function_num 인수로 1이 전달되었다. 이 선택으로 SUBTOTAL 은 AVERAGE 함수로 계산된다.

ref1 인수로 G5:G8 영역이 참조되었다. 이 범위가 AVERAGE 함수의 계산에 사용된다.

SUBTOTAL 함수가 AVERAGE 함수를 사용하여 G5:G8 범위 값으로 평균을 계산한 것이다.

결과로 17.625 가 반환되었다.

[그림2] 엑셀 SUBTOTAL 함수로 평균 계산하기

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

- ref1, ref2 인수

사용 예제2 : =SUBTOTAL( 9, G5:G6, G7 )

사용 결과2 : 50

첫번째 인수 9 가 function_num 로 전달되어, 함수 목록에서 SUM 함수가 지정된다. [그림1 참고]

두번째와 세번째 인수로 계산에 사용될 수치가 범위로 전달된다.

ref1 인수인 G5:G6 범위와 ref2 인수인 G7 셀의 수치 값이 SUM 함수로 합계 계산된다.

결과로 G5:G6 의 5, 30 과 G7 의 15 가 더해져 총 50 이 결과 반환된다.

[그림3] SUBTOTAL 함수 두 참조 범위 합계

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

- 숨기기된 셀 무시

사용 예제3 : =SUBTOTAL( 102, G5:G8, H5:H8 )

사용 결과3 : 4

function_num 가 100 단위로 가면, 이 함수들은 행 숨기기 된 셀들을 계산에서 무시한다.

function_num 가 102 로 설정되어, COUNT 함수가 적용된다. 이렇게 백단위 목록의 함수라 숨기기된 셀은 무시된다.

rer1 으로 G5:G8 , ref2 로 H5:H8 의 범위가 참조되어 계산된다.

[그림4] SUBTOTAL 함수로 처리된 COUNT 함수

숫자를 포함하는 셀의 수가 G5:G8 의 4개 이다. 4 가 결과로 반환된 이유이다. [그림3 참고]

만일, H5:H8 의 범위에 숫자 값이 입력되었다면, 5 이상의 값이 반환 될 것이다.

또, 5번 행이 숨기기된 상태라면, 102 의 함수목록은 숨기기된 행을 처리에서 제외한다. [그림1 참고]

결국, COUNT 함수는 G6:G8 의 숫자셀 갯수 3 을 결과로 반환할 것이다.

[그림5] 숨기기 셀 제외 SUBTOTAL COUNT 함수

관련 함수 : [엑셀 모바일] COUNT 함수로 숫자 셀의 개수 세기

엑셀 SUBTOTAL 함수 주요 오류

엑셀 SUBTOTAL 함수에서 가장 자주 발생하는 오류이다.

- function_num 인수로 허용된 값, 즉 함수 목록 숫자가 아닌값이 주어지면 #VALUE! 오류 발생.

( function_num 인수는 1 ~ 11 , 101 ~ 111 로 제한됨 )

- 참조 범위의 데이터가 0 으로 나누기를 필요로 한다면 #DIV/0! 오류 발생.

( 숫자값을 포함하지 않는 셀 범위로 평균, 표준편차, 분산을 구하려 할 때 )

엑셀 SUBTOTAL 과 AGGREGATE 함수의 가장 큰 차이는 추가 인수 k 의 사용여부이다. 

SUBTOTAL 함수는 추가 인수를 사용할 수 없다.

AGGREGATE 함수에는 있는 LARGE, SMALL, PERCENTILE, QUARTILE 등의 함수가 빠져있는 이유이다.

SUBTOTAL 의 업그레이드된 형태의 소계 함수가 AGGREGATE 인 것이다. 

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

마늘빵.