엑셀 필터 사용시 통계(합계, 평균, 개수..) 함수 - SUBTOTAL 첫번째

엑셀의 SUBTOTAL함수는 필터 사용시 목록이나 데이터베이스등 선택한 범위에서 필터링 후의 집계 값을 구해줍니다. 집계 결과로 합계(SUM), 평균(AVERAGE), 최대값(MAX), 최소값(MIN), 개수(COUNT, COUNTA) 등 총 11가지 형태의 집계함수를 사용할 수 있으며 필터링 뿐만 아니라 숨김 처리한 데이터 까지 제외시킬수 있습니다.

예제

 

아래 예제 파일을 보시면 지금까지 해온 다팔아 쇼핑몰의 주문내역입니다.

이번에는 I4셀에 SUM함수를 이용하여 판매된 금액의 합계를 냈습니다. 흔하게 사용하는 형태입니다. 보통은 합계를 표의 마지막에 표시하기도 하지만 지금처럼 표가 긴 경우라면 예제처럼 상단에 표시하는 편이 좋습니다. 바로 확인할 수 있을테니까요.

 

이제 하고자 하는 것은 서울지역을 필터링하고 합계를 알고 싶습니다.

필터를 적용하고 서울로 필터링해도 되지만 지금에 경우라면 좀더 간단하게 가능합니다.

 

필터 적용

1. 필터링 하고자하는 셀을 선택 후 우클릭
2. 팝업메뉴에서 필터 > 선택한 셀 값으로 필터링

하면 간단하게 서울로 필터링됩니다. 이렇게 단순한 형태의 필터링은 마우스 우클릭으로 사용하면 좀더 빠르게 필터를 적용할 수 있습니다.

 

필터 적용시 합계반영 안됨

 

필터링은 잘되었습니다. 하지만 합계 값의 변화는 없습니다. 왜냐하면 합계의 범위는 표의 전체 범위이기때문입니다.

"합계까지 필터링된 값들로 계산이 되어 나온다면 좋을텐데"라고 바로 생각하게 되죠

이 경우에 사용할 수 있는 것이 SUBTOTAL 함수 입니다.

 

 

 

 

SUBTOTAL

목록이나 데이터베이스의 부분합을 반환합니다.(집계 관련된 함수들과 같이 사용)

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

• function_num - 실제로 적용할 11가지 함수들의 번호입니다.
• ref1 - function_num으로 적용될 함수의 첫번째 인수
• ref2 - function_num으로 적용될 함수의 두번째 인수

 

보통 함수는 어떤 결과값을 돌출하기 위해 쓰입니다. 하지만 SUBTOTAL은 값 자체를 반환한다고 생각하지 않는 편이 좋습니다. 실제로 반환하는 값는 인수로 쓰인 function_num이 결정합니다. 단순하게 반환된 값에서 불필요한 데이터를 제거한다고 보시는게 맞습니다.

 

그럼 function_num의 정체를 보겠습니다. 

 

SUBTOTAL 사용법

1. 정체 확인을 위해 일단 빈셀을 아무거나 선택합니다.(B4셀 선택)
2. 수식입력줄에 =SUBTOTAL( 까지 입력합니다.
3. SUBTOTAL의 첫번째 인수인 function_num이 표시됩니다.

function_num의 힌트를 보게 되면 숫자와 함께 함수명이 보입니다. 1번은 AVERAGE, 2번은 COUNT... 9번이 SUM이네요 

즉 이 숫자들은 실제로 적용할 함수의 번호입니다. 함수를 일일이 입력할 수고를 덜어주는 역활을 하는 것이죠. 

스크롤을 쭉 내려 보면 101번부터 다시 반복합니다. 101번이 AVERAGE, 102번이 COUNT... 동일한 함수들입니다. 모두 100이 더해진 체로 같은 함수의 반복입니다. 1~11번까지를 한묶음, 101~111까지를 한 묶음으로 보시면 되겠습니다.

 

이 함수들이 실제로 계산될 함수들입니다. 1로 선택한다면 평균값 계산, 9로 선택하신다면 합계 계산이 되게됩니다.

그런데 AVERAGE 던지 SUM 이던지 이 함수도 자체의 인수들이 필요합니다. 그 인수의 역할을 하는 것이 SUBTOTAL함수의 두번째 인수인 ref1입니다. 그리고 선택된 함수의 두번째 인수가 ref2가 되는것입니다. 

인수를 3개로 하는 함수번호를 선택했다면 세번째가 ref3이 되겠죠

 

SUBTOTLA 함수의 이해

 

이렇게 SUBTOTAL은 사실상 실제 함수인 function_num에서 불필요한 요소를 제거하는 역할을 하는 것이죠

다시 예제 파일로 돌아가 ESC로 B4셀의 입력을 취소하고 데이터탭>필터를 클릭하여 필터를 해제 합니다.

SUM이 적용된 셀에 SUBTOTAL을 적용하면 이제 원하는 형태로 합계를 구할 수 있습니다.

 

SUBTOTLA 작성하기

1. I4셀 선택
2. 수식입력줄에 함수작성 => =SUBTOTAL(
3. 합계를 구해야 함으로 function_num 을 9(SUM)로 선택 => =SUBTOTAL(9,
4. function_num(9:SUM) 함수의 인수 범위를 지정(I7셀 클릭  > ctrl+shift+↓(아래방향키) )  => =SUBTOTAL(9,I7:I52
5. ENTER로 작성 완료 => =SUBTOTAL(9,I7:I52)

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

 

 

현재는 필터를 적용한 상태가 아니므로 합계 값은 완전히 SUM과 동일한 상태입니다.

다시 지역열에 서울을 우클릭 > 필터 > 선택한 값으로 필터링을 선택합니다.

 

결과

SUBTOTLA 적용시 결과

 

필터링 된 것들만의 합이 계산되어 나옵니다.

다양하게 필터링 해보세요...

 

다음 강좌로 이어집니다.

 

 

subtotal.xlsx

0.01MB