엑셀 필터링 후 부분합 계산하기(SUBTOTAL 함수)

엑셀에서 데이터를 정리하다 보면 필터링을 통해서 원하는 데이터값만 확인할 때가 있습니다.

범위의 값을 합산할 때는 흔히 SUM 함수[참고 : 엑셀 표의 기능(구조적 참조)]를 많이 사용하는데

필터링해서 원하는 데이터의 합산을 확인하려 해도 전체의 합산값만 보여주는 한계가 있습니다.

아래는 6개의 광역 지방행정자치단체(광주, 대구, 대전, 부산, 서울, 인천)와 6개의 광역 지방행정자치단체에 속해 있는

구(區), 그리고 해당 구의 인구 데이터를 정리한 것입니다.

※ 인구는 실제와 다를 수 있습니다.

먼저 SUM 함수로 전체 인구의 합산한 값을 확인할 수 있게 했습니다.

이제 Ctrl+Shift+L 단축키로 필터 기능을 적용하고 '서울특별시'를 선택해 보겠습니다.

이제 우측 상단에 있는 인구수를 확인해 보겠습니다.

필터 기능을 통해서 '서울특별시'에 해당되는 데이터만 확인 가능해졌지만 인구수는 여전히

6개 광역 지방행정자치단체 인구수를 그대로 보여주고 있습니다.

SUM함수에는 필터링하거나 숨긴 셀의 데이터를 가리지 않고 주어진 범위의 셀을 합산하기 때문입니다.

이런 경우 사용할 수 있는 함수로 SUBTOTAL 함수가 있습니다.

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

○ function_num : 필수요소. 부분합에 사용할 함수를 지정하는 숫자 1-11 또는 101-111을 입력

○ ref1 : 필수 요소. 부분합을 계산할 첫 번째 명명된 범위 또는 참조

○ ref2,... : 선택 요소. 부분합을 계산할 명명된 범위 또는 참조로서 2개~254개까지 지정 가능

SUBTOTAL 함수는 필터링 후 화면에 보이는 범위의 부분합을 계산할 수 있습니다.

게다가 위에 보시는 것처럼 평균, 숫자 개수, 값 개수, 최댓값 등등 조건에 맞게

선택해서 원하는 값을 확인할 수 있습니다.

숫자 1~11까지는 필터링된 데이터 중에 숨겨진 행이 있어도 결과에 포함시킵니다.

숫자 101~111까지는 필터링된 데이터 중에 숨겨진 행이 있으면 무시하여 결과에 미포함시킵니다.

이제 6개 광역 지방행정자치단체 데이터를 이용해서 각각의 차이점을 확인해 보겠습니다.

먼저 인구수 열에 SUBTOTAL 함수를 사용하면서 function_num는 9(합계)를 입력했습니다.

다음은 6개의 광역 지방행정자치단체에 속해 있는 구(區)의 개수를 확인하기 위해서

function_num에 3(값 개수)을 입력하고

끝으로 6개의 광역 지방행정자치단체가 입력된 셀 중에서 숨긴 행을 합계에서 미포함하기

위해서 function_num에 103(값 개수 / 숨긴 행 미포함)을 입력해 보겠습니다.

이제 숨긴 행을 결과에 미포함하는 결괏값을 확인하기 위해서

서울특별시 종로구가 입력된 셀을 숨긴 후 결과를 확인해합니다.

행을 숨기기 위해서는 원하는 셀이 있는 행의 왼쪽 숫자를 마우스 우클릭한 후

숨기기를 선택하면 됩니다.

1개의 행을 숨긴 후 결과를 확인해 보았습니다.

광역은 function_num : 103

기초는 function_num : 3

인구는 function_num : 9

따라서 광역 열의 입력된 셀 중 숨긴 행은 미포함해서 '68'이라는 결괏값을

기초 열은 숨긴 행도 포함하기 때문에 '69'라는 결과를 보여줍니다.

만약 숨긴 행의 인구수를 미포함하고 쉽다면 function_num을 9가 아닌 109로 변경하면 됩니다.

간단한 함수 적용으로 필터링된 데이터에서 손쉽게 다양한 결괏값을 얻을 수 있습니다.