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

실무에서 자주 활용하는 엑셀 함수 중 대표적인 함수가 바로 SUBTOTAL 함수이다.

엑셀은 보통 숫자 데이터를 많이 활용하게 되는데,

이런 숫자 데이터로 하는게 대부분 count, sum, average 등을 통한 분석 작업이다.

SUBTOTAL 함수는 단순히 합계만 구하는게 아니라 필터링하여 화면에 보이는 데이터만 부분합으로 계산해주는 함수이다.

 

엑셀 SUBTOTAL 함수

SUBTOTAL 함수는 필터링하여 화면에 보이는 범위의 데이터만 부분합으로 계산해주는 함수이다.

이 함수로 합계, 평균, 개수, 최대값, 최소값 등 다양한 계산을 할 수 있다.

많은 데이터 중에서 내가 원하는 특정 컬럼의 값만 구하기 위해서 필터를 거는 경우가 많은데,

그냥 SUM을 해서 전체 범위를 산정하면 필터를 걸던 안걸던 해당 범위의 모든 값의 합계를 보여준다.

필터링한 데이터에 대해서만 부분합을 구해주기 때문에 필터에 따라서 결과값은 계속해서 동적으로 바뀐다.

 

=SUBTOTAL(계산방식, 범위1, [범위2], [범위3], ...)

 

 

• 계산방식 : 필터링한 데이터 범위에 대해 어떻게 계산을 할 것인지를 정할 수 있다. (아래 표 참고)
• 범위1 : SUBTOTAL 함수로 계산할 범위를 지정한다. 최대 254개의 범위를 지정할 수 있다.  

   <계산방식>

함수	필터링된 범위는 제외, 숨겨진 셀은 포함	필터링된 범위, 숨겨진 셀 모두 제외
AVERAGE (평균)	1	101
COUNT (숫자 개수)	2	102
COUNTA (값 개수)	3	103
MAX (최대값)	4	104
MIN (최소값)	5	105
PRODUCT (곱셈)	6	106
STDEV (표준집단 표준편차)	7	107
STDEVP (모집단 표준편차)	8	108
SUM (합계)	9	109
VAR.S (표준집단 분산)	10	110
VAR.P (모집단 분산)	11	111

 

 

경험 상 가장 많이 쓰는 함수는 SUM이다.

로우데이터를 쭉 놓고 보통 비용이 있는 컬럼 가장 상단에 SUBTOTAL 함수를 걸어놓고,

조건을 달리 걸어보면서 비용의 변화를 확인해보기 위해 가장 많이 사용하고 있다.

 

예제를 통해 쉽게 이해해보자!

엑셀 subtotal 함수 예제

 

날짜별로 어느 지역에서 어떤 메뉴를 어떤 가격에 마셨는지에 대한 가계부 데이터이다.

내가 궁금한건, 어떤 월에 커피값을 얼마나 냈는지, 메뉴별로 얼마나 썼는지, 지역별로는 얼마나 썼는지 등을 알고 싶다.

옆에 요약 표로 해서 조건별로 만들어놓을 수도 있겠지만 굳이 그렇게까지 정리하기엔 좀 귀찮고,

필터를 이렇게 저렇게 바꿔가면서 그때그때 확인하고 싶을 때 SUBTOTAL 함수가 유용하다.

 

1. 계산방식 정하기     

필터 조건에 따라 가격의 합이 얼마나 되는지 보고 싶기 때문에 나는 SUM으로 선택하려고 한다.

딱히 숨긴셀은 없으니 위 표에서 SUM (합계)에 해당하는 "9"를 입력한다.

 

=SUBTOTAL(9,

 

2. 범위 정하기

범위를 정할 때는 필터를 건 상태에서 범위를 정하는 것이 아니라 모든 필터를 풀고 전체 범위를 선택해주어야 한다.

가격에 대한 SUM을 구하면 되니 범위는 D3:D14로 정해주었다.

 

=SUBTOTAL(9, D13:D14)

 

 

처음에는 전체 범위에 대해서 전체 SUM이 적용될테니 총 합계인 63,500원이 결과로 나왔다.

필터를 걸어 동적으로 어떻게 변화하는지 확인해보자!

참고로 필터는 필터를 걸 카테고리를 드래그 하고 ctrl + shift + L 을 누르면 된다.

 

필터 단축기 : (카테고리 드래그 한 뒤) ctrl + shift + L

 

지역 필터 결과에 따라 동적으로 바뀌는 결과값

 

지역을 서울로 필터를 걸었더니 필터를 걸어서 화면에 보이는 가격에 대해서만 합계를 반영하여 결과값을 반환했다.

서울 지역에서 먹은 커피는 31,500원 이다.

 

참고로 필터에서 조건을 걸 수 있다.

예를들어 날짜 카테고리의 경우는 자동적으로 날짜 필터를 선택할 수 있게 되어있어 이번주, 다음달, 다음분기 등 필터를 걸 수도 있고,

숫자의 경우 부등호를 통해서 어떤 것보다 큰 값만 필터를 걸어줘 이렇게도 설정할 수 있다.

색 필터로도 따로 필터를 걸 수 있다.

아무런 규칙성이 없는 값들끼리 필터를 걸고 싶다던지, 특정 태그를 별도 컬럼을 두고 하지 않고 색을 주어 표기해둔다던지 할 때 요긴하게 쓸 수 있다.

엑셀 색 기준 필터 넣기

 

노란색으로 색을 넣은 것처럼 날짜로도, 메뉴로도 아무런 연관성이나 규칙성이 없지만 내가 따로 필터를 걸고 싶을 때 색을 넣어 구분하기도 한다.

 

마지막으로 SUBTOTAL 함수는 세로로 입력된 데이터에 대해서만 작동하게 되어 있다.

그리고 범위를 여러개 지정할 수 있다고 했는데 여러 범위가 서로 중첩될 경우에는 중첩된 범위의 부분합은 무시된다.

      

엑셀 SUBTOTAL 함수에 대해 알아보았다.

숫자를 특히 자주 다루는 엑셀에서 SUBTOTAL 함수는 기본적으로 활용하는 함수 중 하나이니,

사용법 꼭 인지하여 실무에서 활용하도록 하자!  

  

함께보면 좋은 글

실무에서 많이 쓰는 엑셀 SUMIF 함수, 조건에 맞는 합 구하는 방법

엑셀 COUNTIF COUNTIFS 함수 사용법 조건을 만족하는 셀 개수 세기!

엑셀 #DIV/0!, #N/A, #REF! 등 에러 처리 위한 IFERROR 함수 사용 방법