[엑셀] SUBTOTAL

(엑셀)SUBTOTAL.xlsx

데이터를 쉽게 분류할때, 대부분 필터 기능을 사용합니다. 

필터를 통해 분류된 값들을 통해 합계, 평균 등의 값을 구해야 하지만, SUM 등의 함수로 원하는 값을 얻지 못한 경험이 있을 것입니다. 

필터를 통해 분류된 데이터에 SUM을 사용할 경우, 필터를 통해 분류된 데이터의 합이 아닌, 해당 범위 전체의 합을 구하기 때문입니다. 

이때, 사용할 수 있는 함수가 바로 SUBTOTAL 입니다. 

= SUBTOTAL ( function_num, ref1, [ref2], ... ) 

목록이나 데이터베이스의 부분합을 구합니다. 

- function_num: subtotal이 함수 안의 범위에서 해야할 역할을 지정

- ref : 함수를 적용시킬 범위 

하단의 function_num은 크게 1-11, 101-111 두 가지 범위로 나뉘어 집니다. 

1- 11: 수동으로 숨긴행을 포함 

101- 111 : 수동으로 숨긴행의 값을 함수계산에서 제외 

필터링된 셀은 항상 제외시킵니다. 

function_num
숨겨진값 포함	숨겨진값 무시	함수	용도
1	101	AVERAGE	인수들의 평균 값
2	102	COUNT	범위에서 숫자가 포함된 셀의 개수를 구합니다.
3	103	COUNTA	범위에서 비어 있지 않은 셀의 개수를 구합니다.
4	104	MAX	최대값을 구합니다. 논리값과 텍스트는 제외합니다.
5	105	MIN	최소값을 구합니다. 논리값과 텍스트를 제외합니다.
6	106	PRODUCT	인수들의 곱을 구합니다.
7	107	STDEV.S	표본 집단의 표준 편차를 구합니다.
8	108	STDEV.P	모집단의 표준 편차를 구합니다.
9	109	SUM	인수들의 합을 구합니다.
10	110	VAR.S	표본 집단의 분산을 구합니다.
11	111	VAR.P	전체 모집단의 분산을 구합니다.

예제로 다시 한번 확인해 보겠습니다. 

(예제설명) 

- 남자와 여자의 나이 리스트 

- 남자 또는 여자로 리스트를 필터링 합니다. 

- 리스트 중 하나의 행은 수동으로 숨겨져 있습니다. 

* SUM(B5: B20) : 전체 리스트의 합 

* SUBTOTAL(9, B5:B20) : 남자로 필터링된 리스트의 합계 

* SUBTOTAL(109, B5:B20) : 남자로 필터링된 리스트의 합계(수동으로 숨겨진 셀은 무시) 

function_num이 9일 때, 109일때의 값이 왜 같은지에 대해 의문이 있을 수 있습니다. 

자동필터를 적용했을 때에는, 전체적으로 노출이 적용된 값에 대해서 적용하게 됩니다. 

그래서, 자동필터를 통해 '남자'의 값을 구하고, 그 중 한 행을 숨긴다고 하여도 전체적으로 자동필터가 적용되었기 때문에 

function_num이 9 일때와 109일 때의 값이 동일하게 나타납니다. 

대부분 활용에서 109의 function_num을 사용할 필요는 그다지 없어 보입니다. 

대부분 필터 사용시에, 자동필터를 사용하기 때문입니다. 자동필터를 사용할 경우 1-11 범위와, 101-111 범위의 function_num의 결과 값은 동일할 것입니다.